Using Hybrid Row-Column Storage

Scenarios

In real-world service scenarios, a table must efficiently handle both high-concurrency point queries and updates (for example, for orders, bills, and logs) and support fast batch queries and aggregation for analytical workloads (for example, statistical reports and trend analysis). Traditional row storage and column storage have their own advantages:

• Row storage is suitable for real-time scenarios with frequent point queries and writes, for example, querying order details by order number.
• Column storage is suitable for batch analysis queries, such as calculating the total transaction amount within a period of time.

Hybrid row-column storage provided by DWS stores both row- and column-format data in one table. The data of the two formats is independently maintained and synchronously updated. The query optimizer selects the optimal access mode based on the actual query path.

Its core advantages are as follows:

• No need to split tables or copy data: One table supports both detailed query and batch analysis.
• Automatic selection of the optimal path: Row storage is used for point queries, and column storage is used for querying aggregation fields No additional development is required.
• Optimal performance: OLAP query efficiency is ensured without sacrificing OLTP performance.
• Compatible with original row-store table design: You can quickly enjoy the performance benefits of column-store and row-store optimizations without adjusting the application structure.
• Unified management and reduced O&M costs: One physical table covers two scenarios, simplifying data synchronization, backup, and permission control.

This section describes how to design and use hybrid row-column storage structures based on typical scenarios and performance comparison, helping you process data efficiently.

Comparison of Row Storage, Column Storage, Hybrid Row-Column Storage

Constraints

• Only clusters of version 9.1.1.100 or later support this function.
• HStore tables are used, that is, enable_hstore_opt is set to on.
• Materialized views are not supported.
• When a small amount of data is copied to a database in real time, the performance of hybrid row-column storage deteriorates by 10% compared with that of the HStore table.
• A hybrid row-column store table can be exchanged only with another hybrid row-column store table. After capacity expansion and redistribution, the exchange is not allowed.
• After DROP COLUMN is executed on a hybrid row-column store table, the deleted columns still occupy system column number resources. The maximum number of available columns in the table is still 1,600.

Usage Suggestions

• In only OLTP scenarios (such as high-frequency point queries and writes), if the space usage is sensitive, hybrid row-column store tables are recommended to balance space usage and point query performance. If the space usage is not sensitive and robust performance is required, row-store tables are recommended.
• In only OLAP scenarios (such as statistics analysis and report), HStore tables are recommended.
• In both detailed and statistical queries where hot and cold columns are difficult to distinguish, hybrid row-column store tables are recommended to achieve excellent performance in each scenario.
• For real-time data import, PBE addBatch is recommended.
• You are advised not to modify column definitions unless necessary. If data rewriting is triggered, the row-store part of a hybrid row-column store table will be rewritten, which is time-consuming.

Syntax Reference

Create a hybrid row-column store table. For more information, see CREATE TABLE. (Only 9.1.1.100 and later versions support this function.)

1
2
3
4
5
6
7
8

CREATE TABLE < table name> (
   <Column definition>
)
WITH (
orientation = column,          -- Use the column storage architecture.
enable_hstore_opt = on,         -- Enable hstore_opt.
    storage_mode = 'mix'           --Create a hybrid row-column store table.
);

Example:

-- Hybrid row-column storage stores data in both row and column formats. It is suitable for mixed services.
CREATE TABLE tbl_mix (
    a INT,
    b TEXT
)
WITH (
    orientation = column,
    enable_hstore_opt = on,
    storage_mode = 'mix'
);

Example of Using Hybrid Row-Column Storage

1. Create a simple data table.

   1 2 3

   DROP TABLE IF EXISTS data; CREATE TABLE data(a INT, b BIGINT, c VARCHAR(10), d VARCHAR(10)); INSERT INTO data values(generate_series(1,100),1,'asdfasdf','gergqer');

2. Expand the table data to 200,000 records.

   1 2 3 4 5 6 7 8 9 10 11 12

   INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; INSERT INTO data SELECT * FROM data; SELECT COUNT(*) FROM data;

   

3. Create a simple hybrid row-column store table.

   1 2 3 4 5 6 7 8 9 10 11 12

   DROP TABLE IF EXISTS rowmode_test1; CREATE TABLE rowmode_test1 ( a INT, b BIGINT, c VARCHAR(10), d VARCHAR(10) ) WITH ( orientation = column, enable_hstore_opt = on, storage_mode = 'mix' );

4. Import data to the hybrid row-column store table.

   1	INSERT INTO rowmode_test1 SELECT * FROM data;

5. Create a view to query the auxiliary table of cudesc through custom functions.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

   DROP FUNCTION IF EXISTS get_cudesc_data_info; CREATE FUNCTION get_cudesc_data_info(target_table_name text) RETURNS TABLE ( col_id int, cu_id oid, min text, max text, row_count int, cu_mode int, size bigint, cu_pointer text, magic int, extra text, src_info text, xid xid, dr_bucket_id int, dict_key bytea, cu_data bytea, cu_data_size_kb numeric ) AS $$ DECLARE cudesc_relid oid; cudesc_relname text; query text; BEGIN SELECT relcudescrelid INTO cudesc_relid FROM pg_class WHERE relname = target_table_name; SELECT relname INTO cudesc_relname FROM pg_class WHERE oid = cudesc_relid; IF cudesc_relname IS NULL THEN RAISE NOTICE 'CU descriptor table not found for %.', target_table_name; RETURN; END IF; query := FORMAT('SELECT *, octet_length(cu_data)::numeric/1024 AS cu_data_size_kb FROM cstore.%I', cudesc_relname); RETURN QUERY EXECUTE query USING target_table_name; END; $$ LANGUAGE plpgsql;

6. Query the DN name.

   1	SELECT * FROM pgxc_node;

   

   View the command output and record the value of node_name corresponding to DN1, for example, dn_6001_6002.

7. Directly connect to DN1 to query records. Replace datanode1 with the result obtained in 6, for example, dn_6001_6002.

   1 2 3 4 5 6 7

   EXECUTE DIRECT ON(datanode1) $$ SELECT col_id, cu_id, row_count, size, cu_pointer FROM ( SELECT col_id, cu_id, row_count, size, cu_pointer FROM get_cudesc_data_info('rowmode_test1') where col_id = -16 and cu_id = 1002 ) $$;

   The following is an example of the command output:

   

The preceding example is for the row storage. The system inserts a special identifier record for each row group into the column storage auxiliary table. The fields are as follows:

• col_id: The value is fixed at –16, indicating that the record corresponds to row group metadata rather than common column data.
• cu_id: ID of the CU to which the row group belongs. The ID rule is the same as that for column-store CUs.
• row_count: total number of rows in the current CU group, for example, 60,000
• size: disk space occupied by the compressed rows, in bytes
• cu_pointer: stores the structure information of row group. It consists of four fields separated by vertical bars (|):
  1. Version number: The current value is 1, indicating the metadata version used by the row group.
  2. Number of rows in a single group: maximum number of rows (for example, 2,000) contained in each row group
  3. Number of groups: number of row groups (for example, 30) contained in the CU
  4. Initial offset: initial offset (for example, 0) of the row group in the physical storage file

Row storage leverages the CU architecture to inherit the execution optimization and compression management advantages of the column storage engine. It maintains high-performance point queries while drastically lowering storage costs, making it a cost-effective choice for hybrid workloads.

GUC Parameters Related to Hybrid Row-Column Store Tables