Primary key

How primary keys work

Hologres automatically maintains a primary key index file stored in row-oriented format, providing high-speed key-value (KV) lookups. The index maps the primary key to a RID (Row Identifier) and the clustering key. The RID is auto-generated and monotonically increasing on each UPSERT. This index enables efficient primary key conflict detection and fast data file location. When a table has a PK defined, the system locates the RID and clustering key through the PK index, then uses these to pinpoint the target data file.

Best practices

Choose columns with real business meaning as primary keys. Avoid using SERIAL-type columns as primary keys because SERIAL acquires a table-level lock during writes, which degrades write performance. Additionally, SERIAL values may overflow as data grows.

Limits

• Primary key columns must be unique and NOT NULL. You can only define the primary key in a single statement, which may include multiple columns.

• Only the table-level PRIMARY KEY constraint syntax is supported: PRIMARY KEY (col1, col2, ...). The inline (column-level) syntax such as col_name type PRIMARY KEY is not supported. For example, id bigint PRIMARY KEY returns the error the usage of PRIMARY KEY in the create table with ID columns is unsupported. Use PRIMARY KEY (id) instead.

• A composite primary key supports a maximum of 32 columns.

• The following data types cannot be used as primary key columns: FLOAT, DOUBLE, NUMERIC, ARRAY, JSON, JSONB, DATE, and other complex types. Starting from Hologres V1.3.22, DATE columns are supported as primary keys. To use a DATE column as a primary key, check your instance version and upgrade if necessary. For more information, see Instance details and Upgrade an instance.

• Row-oriented tables and row-column hybrid tables require a primary key. Column-oriented tables do not require a primary key.

• Primary keys cannot be altered after table creation. To change the primary key, you must recreate the table.

Examples

The following examples use the syntax available starting from Hologres V2.1. If your instance runs V2.0 or earlier, replace the WITH (property = 'value') clause with CALL set_table_property statements. For more information, see CREATE TABLE.

• Create a column-oriented table with a single primary key.

  • Syntax supported starting from V2.1:

    CREATE TABLE tbl_1 (
        id bigint NOT NULL,
        name text NOT NULL,
        age bigint NOT NULL,
        class text,
        reg_timestamp timestamptz NOT NULL,
        PRIMARY KEY (id)
    )
    WITH (
        orientation = 'column',
        distribution_key = 'id',
        clustering_key = 'age',
        event_time_column = 'reg_timestamp',
        bitmap_columns = 'name,class',
        dictionary_encoding_columns = 'class:auto'
    );

  • Syntax supported in all versions:

    BEGIN;
    CREATE TABLE tbl_1 (
     id bigint NOT NULL,
     name text NOT NULL,
     age bigint,
     class text,
     reg_timestamp timestamptz,
    PRIMARY KEY (id)
    );
    CALL set_table_property('tbl_1', 'orientation', 'column');
    CALL set_table_property('tbl_1', 'distribution_key', 'id');
    CALL set_table_property('tbl_1', 'clustering_key', 'age');
    CALL set_table_property('tbl_1', 'event_time_column', 'reg_timestamp');
    CALL set_table_property('tbl_1', 'bitmap_columns', 'name,class');
    CALL set_table_property('tbl_1', 'dictionary_encoding_columns', 'class:auto');
    COMMIT;

• Create a column-oriented table with a composite primary key (two columns).

  • Syntax supported starting from V2.1:

    CREATE TABLE tbl_1 (
        id bigint NOT NULL,
        name text NOT NULL,
        age bigint NOT NULL,
        class text NOT NULL,
        reg_timestamp timestamptz NOT NULL,
        PRIMARY KEY (id,age)
    )
    WITH (
        orientation = 'column',
        distribution_key = 'id',
        clustering_key = 'age',
        event_time_column = 'reg_timestamp',
        bitmap_columns = 'name,class',
        dictionary_encoding_columns = 'class:auto'
    );

  • Syntax supported in all versions:

    BEGIN;
    CREATE TABLE tbl_2 (
     id bigint NOT NULL,
     name text NOT NULL,
     age bigint NOT NULL,
     class text NOT NULL,
     reg_timestamp timestamptz NOT NULL,
    PRIMARY KEY (id,age)
    );
    CALL set_table_property('tbl_2', 'orientation', 'column');
    CALL set_table_property('tbl_2', 'distribution_key', 'id');
    CALL set_table_property('tbl_2', 'clustering_key', 'age');
    CALL set_table_property('tbl_2', 'event_time_column', 'reg_timestamp');
    CALL set_table_property('tbl_2', 'bitmap_columns', 'name,class');
    CALL set_table_property('tbl_2', 'dictionary_encoding_columns', 'class:auto');
    COMMIT;

• Create a row-oriented table with a primary key.

  • Syntax supported starting from V2.1:

    CREATE TABLE public.tbl_row (
        id text NOT NULL,
        name text NOT NULL,
        class text,
        PRIMARY KEY (id)
    )
    WITH (
        orientation = 'row',
        distribution_key = 'id',
        clustering_key = 'id'
    );

  • Syntax supported in all versions:

    BEGIN;
    CREATE TABLE public.tbl_row (
        id text NOT NULL,
        name text NOT NULL,
        class text ,
    PRIMARY KEY (id)
    );
    CALL set_table_property('public.tbl_row', 'orientation', 'row');
    CALL set_table_property('public.tbl_row', 'clustering_key', 'id');
    CALL set_table_property('public.tbl_row', 'distribution_key', 'id');
    COMMIT;

• Create a partitioned table with a primary key.

  • Syntax supported starting from V2.1:

    BEGIN;
    CREATE TABLE public.tbl_parent(
      a text , 
      b int, 
      c timestamp, 
      d text,
      ds text,
      PRIMARY KEY (ds,b)
      )
     PARTITION BY LIST(ds)
     WITH ( orientation = 'column');
    CREATE TABLE public.tbl_child_1 PARTITION OF public.tbl_parent FOR VALUES IN('20221207');
    CREATE TABLE public.tbl_child_2 PARTITION OF public.tbl_parent FOR VALUES IN('20221208');
    COMMIT;

  • Syntax supported in all versions:

    BEGIN;
    CREATE TABLE public.tbl_parent(
      a text , 
      b int, 
      c timestamp, 
      d text,
      ds text,
      PRIMARY KEY (ds,b)
      )
      PARTITION BY LIST(ds);
    CALL set_table_property('public.tbl_parent', 'orientation', 'column');
    CREATE TABLE public.tbl_child_1 PARTITION OF public.tbl_parent FOR VALUES IN('20221207');
    CREATE TABLE public.tbl_child_2 PARTITION OF public.tbl_parent FOR VALUES IN('20221208');
    COMMIT;

Related topics

• For guidelines on choosing table properties based on your query patterns, see Best practices for Hologres table properties.

• For best practices on table design and queries in key/value lookup scenarios, see Best practices for fast point queries.

• For DDL statements related to Hologres internal tables, see:

  • CREATE TABLE

  • CREATE TABLE AS

  • CREATE TABLE LIKE

  • ALTER TABLE

  • DROP TABLE