Hologres:table storage formats: column-oriented, row-oriented, and hybrid

Column-oriented

In column-oriented tables, data is stored by column. Column-oriented storage uses the ORC format by default. It encodes data using various encoding algorithms (such as RLE and dictionary encoding) and then applies mainstream compression algorithms (such as Snappy, Zlib, Zstd, and Lz4) to further compress the encoded data. Combined with mechanisms like bitmap indexes and late materialization, this approach enhances both storage efficiency and query performance.

Row-oriented

In row-oriented tables, data is stored by row. Row-oriented storage uses the SST format by default. Data is stored in key-ordered, compressed blocks. Indexes such as Block Index and Bloom Filter, along with a background compaction mechanism, organize files to optimize point query efficiency.

• (Recommended) Set Primary Key

  The system stores a primary key index file for each table at the underlying storage layer. For more information, see Primary Key. When a row-oriented table has a primary key (PK), the system automatically generates a row identifier (RID) to locate an entire row of data. The system also sets the PK as both the distribution key and the clustering key, enabling rapid location of the shard and file that contain the data. For queries based on the primary key, the system scans only one primary key index to quickly retrieve all columns of the full row, thereby improving query efficiency. The following is an SQL example.

  • Table creation syntax supported from V2.1:

    CREATE TABLE public.tbl_row (
        id TEXT NOT NULL,
        name TEXT NOT NULL,
        class TEXT,
        PRIMARY KEY (id)
    )
    WITH (
        orientation = 'row',
        clustering_key = 'id',
        distribution_key = 'id'
    );
    -- Example of PK-based point query
    SELECT * FROM public.tbl_row WHERE id ='1111';
    
    -- Query multiple keys
    SELECT * FROM public.tbl_row WHERE id IN ('1111','2222','3333');   

  • Table creation syntax supported by 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;
    
    -- Example of PK-based point query
    SELECT * FROM public.tbl_row WHERE id ='1111';
    
    -- Query multiple keys
    SELECT * FROM public.tbl_row WHERE id IN ('1111','2222','3333');    

  The diagram is as follows.

• (Not Recommended) Inconsistent PK and Clustering Key settings

  If you create a row-oriented table and define different fields for the PK and Clustering Key, the system first locates the Clustering Key and RID based on the PK during queries. It then uses the Clustering Key and RID to retrieve the full row data. This requires two scans and results in reduced performance. SQL examples are as follows.

  • Table creation syntax supported from V2.1, setting a row-oriented table with inconsistent PK and Clustering Key:

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

  • Table creation syntax supported by all versions, setting a row-oriented table with inconsistent PK and Clustering Key:

    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', 'name');
    CALL set_table_property('public.tbl_row', 'distribution_key', 'id');
    COMMIT;

  The diagram is as follows.

In summary: Row-oriented tables are highly suitable for primary key-based point query scenarios, enabling high QPS point queries. When creating a table, define only the PK. The system automatically configures the PK as both the Distribution Key and Clustering Key to optimize query performance. Avoid defining different fields for the PK and Clustering Key, as this degrades performance.