Table Design

The CREATE TABLE command is used to create a new table in the database. Below are some tips for creating new tables within Tacnode.

Table Storage Mode

Tacnode tables support three storage modes: row storage, column storage, and mixed row and column storage. Each method has its own advantages and use cases.

Row storage: Stores individual rows together. This is the standard layout in traditional relational databases and is the default method for table storage.
Columnar storage: Organizes data by storing each column together. This is particularly efficient for analytical workloads as it allows querying specific columns instead of all data, reducing I/O and improving query performance.
Hybrid row and columnar storage: Combines row and columnar storage, beneficial for diverse queries like HTAP scenarios. However, hybrid storage tends to be larger.

The choice of table storage mode depends on your application's specific requirements and query patterns.

-- Create a row table (default storage mode).
CREATE TABLE row_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
);
 
-- Create a columnar table
CREATE TABLE columnar_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING columnar;
 
-- Create a hybrid table
CREATE TABLE hybrid_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING hybrid;

For more syntax instructions on creating tables, please refer to CREATE TABLE.

The Primary Key (PK) uniquely identifies each row within the table. Typically, a primary key is established to ensure data uniqueness and integrity. Consider the following when configuring a primary key:

Uniqueness: Each primary key must have a distinct value, which Tacnode enforces automatically.
Efficiency: Choose a column or group of columns commonly used in queries and joins to enhance performance. If the primary key consists of multiple columns, the order of these columns affects index lookup efficiency, especially with constraints on the leading column. For example, if the primary key is (a, b, c) and the query condition is WHERE a = 5 AND b >= 42 AND c < 77, the index will scan entries starting from the first with a = 5 and b = 42, to the last with a = 5.
Stability: Avoid using columns that change frequently as primary keys, as this can slow down updates. Define a primary key by specifying the PRIMARY KEY constraint in the table definition:

CREATE TABLE t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
);

Avoid these common mistakes when defining primary keys:

Don't always use an auto-incrementing ID field as a primary key if it is not used in all user queries, as this can affect performance.
Avoid defining a primary key for a single monotonic column, such as an auto-increment column or a timestamp column, as this can impact performance.

Row Table

Row storage is the default mode for tables, or you can specify USING ROW to indicate row storage.

CREATE TABLE row_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
);
 
CREATE TABLE row_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING ROW;

Columnar Table

Specify USING COLUMNAR to indicate columnar storage.

CREATE TABLE columnar_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING columnar;

Hybrid Table

Specify USING HYBRID to indicate mixed row and column storage.

CREATE TABLE hybrid_t(
    id int PRIMARY KEY,
    name text,
    age smallint,
    gender char(1)
) USING hybrid;

Modify Table Storage Mode

You can dynamically change the storage format of a table using ALTER TABLE SET ACCESS METHOD. This operation supports online modifications and will consume some computational and storage resources during the process, but it does not affect read and write operations.

Modification examples:

-- Switch to row storage
ALTER TABLE xx SET ACCESS METHOD row;
 
-- Switch to columnar storage
ALTER TABLE xx SET ACCESS METHOD columnar;
 
-- Switch to hybrid storage
ALTER TABLE xx SET ACCESS METHOD hybrid;

Check Table Storage Mode

You can check the current table's storage format by querying the system tables.

SELECT amname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = (select 'public.trade'::regclass::oid);
 amname
--------
 hybrid
(1 row)

CREATE TABLE LIKE

Generates an empty table with the structure of an existing table. This involves creating an empty table that mirrors the structure and constraints of the specified target table, including attributes like NOT NULL. The CREATE TABLE LIKE command is often used in database administration and maintenance, creating a temporary table with the same layout as another table or duplicating a table's structure into a different schema.

CREATE TABLE t2(LIKE t INCLUDING ALL);

You can also specify the storage mode of the table:

CREATE TABLE t2(LIKE t INCLUDING ALL) USING columnar;
CREATE TABLE t2(LIKE t INCLUDING ALL) USING hybrid;

CREATE TABLE AS SELECT (CTAS)

SELECT allows you to create a table based on a statement. With CREATE TABLE AS SELECT, you can duplicate an entire table, select specific records, or replicate the table's structure. Note that this command does not copy indexes or constraints, including NOT NULL, primary keys, foreign keys, and others.

CREATE TABLE t2 AS SELECT * FROM t WHERE age > 18;

Auto-increment Table

To create a table with an auto-incrementing primary key, use the SERIAL data type for the primary key column. Note that while SERIAL ensures a monotonous increase, it may impact write performance. Consider using business fields as the primary key. An example is provided below:

> CREATE TABLE t(id serial PRIMARY KEY, b int);
CREATE TABLE
> INSERT INTO t(b) VALUES (1), (2), (4);
INSERT 0 3
> SELECT * FROM t;
 id | b
----+---
  1 | 1
  2 | 2
  3 | 4
(3 rows)

Foreign Key Table

Consider this example to illustrate how to create a table with foreign key constraints: There are two tables, "weather" and "cities." Every row added to the "weather" table must align with a valid entry in the "cities" table.

CREATE TABLE cities (
        name     varchar(80) PRIMARY KEY,
        location point
);
 
CREATE TABLE weather (
        city      varchar(80) REFERENCES cities(name),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

Partitioned Table

Partitioning is commonly used to enhance query performance and manage large datasets more effectively. For an introductory overview and examples of partitioned tables, please refer to Partitioned Table.

Schema Evolution

Facilitates online table structure modifications to ease operational and maintenance tasks. For further information, consult the document Schema Evolution.

Table Design

On this page