Hologres:Primary key

Overview

Hologres automatically stores a primary key index file at the underlying layer. This index file uses the row-oriented storage mode to provide high-speed key-value services. The key indicates the primary key of a table, and the value indicates the row identifier (RID) and the clustering key. The RID was originally known as unique_id. RID values are automatically generated each time when you execute the UPSERT statement, and the values monotonically increase. The primary key index file can help you determine a primary key conflict and locate data files. If you configure a primary key for a table, you can quickly locate the RID and clustering key based on the primary key in the primary key index file and locate the required data file based on the RID and clustering key.

Usage notes

We recommend that you configure informative columns to constitute a primary key. We recommend that you do not configure columns of the SERIAL data type to constitute a primary key. If you write data of the SERIAL data type to a table, a table-level lock is acquired. This results in write performance degradation. As the data grows, the SERIAL data length may exceed the upper limit.

Limits

• The values of a column that is used as a primary key or multiple columns that constitute a primary key must be unique and cannot be null. You can specify multiple columns to constitute a primary key for a table in only one statement.

• You can configure up to 32 columns to constitute a primary key.

• The columns of the following data types cannot be configured as primary keys: FLOAT, DOUBLE, NUMERIC, ARRAY, JSON, JSONB, DATE, and complex data types. Hologres V1.3.22 and later allow you to configure columns of the DATE data type as primary keys. To configure a column of the DATE data type as a primary key, check the version of your Hologres instance. If the version of your Hologres instance is earlier than V1.3.22, upgrade your Hologres instance. For more information, see Instance configurations and Upgrade instances.

• You must specify a primary key for a row-oriented table or a row-column hybrid table. A primary key is optional for a column-oriented table.

• A primary key cannot be modified. If you want to change the primary key of a table, you must create another table.

Examples

This section provides examples for Hologres V2.1 and later. If the version of your Hologres instance is V2.0 or earlier, change the WITH (property = 'value') statement to the CALL set_table_property statement. For more information, see Overview.

• Create a column-oriented table and configure a column as the primary key of the table.

  • Syntax supported by Hologres V2.1 and later:

    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 by all Hologres versions:

    BEGIN;
    CREATE TABLE tbl_1 (
     id bigint NOT NULL,
     name text NOT NULL,
     age bigint,
     class text,
     reg_timestamp timesatmptz, 
    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 and configure two columns to constitute a primary key for the table.

  • Syntax supported by Hologres V2.1 and later:

    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 by all Hologres 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 and configure a primary key for the table.

  • Syntax supported by Hologres V2.1 and later:

    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 by all Hologres 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 and configure a primary key for the table.

  • Syntax supported by Hologres V2.1 and later:

    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 by all Hologres 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;

References

• For more information about how to configure table properties based on business query scenarios, see Guide on scenario-specific table creation and tuning.

• For more information about the best practices for creating tables and querying key-value pairs, see Query key-value pairs.

• For more information about the DDL statements for Hologres internal tables, see the following topics:

  • CREATE TABLE

  • CREATE TABLE AS

  • CREATE TABLE LIKE

  • ALTER TABLE

  • DROP TABLE