Table storage models supported by AnalyticDB for PostgreSQL - AnalyticDB

AnalyticDB for PostgreSQL supports three storage models for tables: row-oriented storage, column-oriented storage, and hybrid row-column storage. You can choose appropriate storage models based on your business requirements when you create tables. Row-oriented storage is suitable for scenarios in which data updates are frequent or real-time writes are performed by executing the INSERT statement. Column-oriented storage is suitable for data warehouse scenarios, such as the data queries and aggregations of a small number of columns.

Specify the storage model of a table

If you want to define a row-oriented or column-oriented table, you can specify the storage model in the WITH clause of the CREATE TABLE statement. You can also specify the compression algorithm and the compression level in the WITH clause. The following table describes the parameters.

Parameter	Description	Valid value
orientation	The storage model of the table. Valid values: `row` and `column`. Default value: `row`. Note You cannot specify the hybrid row-column storage model by using the `WITH` clause. For information about how to configure hybrid row-column storage, see the "Hybrid row-column storage tables" section of this topic.	`row` `column`
compresstype	The compression algorithm of the table. Default value for AnalyticDB for PostgreSQL V4.3 and V6.0: `none`. No compression algorithm is used. Default value for AnalyticDB for PostgreSQL V7.0: `auto`. The most appropriate compression algorithm is automatically selected based on the data attributes and system configurations.	V4.3: zlib, rle_type, none and auto. V6.0: zstd, zlib, rle_type, lz4, none and auto. V7.0: zstd, lz4, none, and auto.
compresslevel	The compression level of the table. A higher value achieves better compression but decreases the compression speed. Default value: 1.	1 to 9. The value must be an integer.
appendonly	Specifies whether to enable the append-optimized storage. Default value: `false`.	`true` `false`

Row-oriented table

By default, AnalyticDB for PostgreSQL uses the heap storage model in PostgreSQL to create row-oriented tables. Row-oriented tables are used for data that needs to be updated at a high frequency or written in real time by executing the INSERT statement. A row-oriented table with a B-tree index provides high data retrieval performance when you perform point queries.

Examples

Create a row-oriented table that uses the heap storage model.
```
CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);
```
Note
When you use Data Transmission Service (DTS) to write data to an AnalyticDB for PostgreSQL instance, the destination tables must be row-oriented tables.AnalyticDB for PostgreSQL DTS allows data synchronization in near real time. In addition to data inserted by executing the INSERT statement, DTS can synchronize data updated by executing SQL statements such as UPDATE and DELETE.
Create an append-optimized row-oriented (AORO) table by using the orientation and appendonly parameters.
```
CREATE TABLE bar (a int, b text) 
WITH (appendonly=true, orientation=row)
DISTRIBUTED BY (a);
```

Column-oriented tables

Column-oriented tables store data by column. When you access data, only relevant columns are read. Column-oriented tables are suitable for data warehouse scenarios, such as data queries and aggregations of a small number of columns. In these scenarios, column-oriented tables provide high I/O performance. However, column-oriented tables are less efficient in scenarios in which data is frequently inserted or updated. We recommend that you use a batch loading method, such as the COPY statement, to insert data into column-oriented tables. Column-oriented tables provide a data compression ratio three to five times that of row-oriented tables.

Examples

Column-oriented tables must be append-optimized tables. If you want to create a column-oriented table, you must set the appendonly parameter to true.

CREATE TABLE bar (a int, b text) 
WITH (appendonly=true, orientation=column)
DISTRIBUTED BY (a);

Hybrid row-column storage tables

Important

Only AnalyticDB for PostgreSQL V7.0 instance support hybrid row-column storage tables.

Partitioned tables and non-partitioned tables support hybrid row-column storage.

Non-partitioned tables

To achieve hybrid row-column storage, set the storage engine of non-partitioned tables to beam.

The Beam storage engine consists of a row-oriented Delta storage and a PAX-based column-oriented Base storage. When data is written, an appropriate storage model is selected based on the data write method. If you use a real-time streaming write method, such as the INSERT INTO VALUES statement, data is written to the row-oriented Delta storage to achieve real-time write performance that can match row-oriented tables. If you use a batch processing method, such as the COPY or INSERT INTO SELECT statement, data is written to the column-oriented Base storage to achieve higher throughput and write performance.

Set the storage engine to beam when you create a table

CREATE TABLE testtable (a int) USING beam;

Change the storage engine of an existing table to beam

ALTER TABLE testtable SET ACCESS METHOD beam;

Partitioned tables

The following examples show how to configure hybrid row-column storage tables. In these examples, the am_partitioned table is used.

CREATE TABLE am_partitioned(x INT, y INT)
PARTITION BY HASH (x) USING heap;

Specify the storage model of a partition when you create the partition

Set the storage model of the am_partitioned_1 partition to append-optimized column-oriented (AOCO) storage.

CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0) USING ao_column;

After creation, the am_partitioned_2 partition is in the hybrid row-column storage model.

CREATE TABLE amm_partitioned_2 PARTITION OF amm_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1) USING beam;

Change the storage model of an existing partition

Change the storage model of the am_partitioned_1 partition to AORO storage.

ALTER TABLE am_partitioned_1 SET ACCESS METHOD ao_row;

Data compression

Data compression is used for column-oriented tables or AORO tables for which the appendonly parameter is set to true. Two compression types are available.

Table-level compression.
Column-level compression. You can apply a unique compression algorithm to each column.

AnalyticDB for PostgreSQL supports the following compression algorithms:

V4.3: zlib, rle_type, none and auto.
V6.0: zstd, zlib, rle_type, lz4, none and auto.
V7.0: zstd, lz4, none, and auto.

Note

You can also specify the QuickLZ compression algorithm, but it is replaced with the zlib algorithm.The rle_type algorithm is suitable only for column-oriented tables.

Examples

Create a column-oriented table.

CREATE TABLE am_testtable(x INT, y INT)
WITH (orientation=column);

Create a column-oriented table that uses the zlib compression algorithm with a compression level of 5.

CREATE TABLE foo (a int, b text) 
WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=5);

Create a column-oriented table that uses the zstd compression algorithm with a compression level of 9.

CREATE TABLE foo (a int, b text) 
WITH (appendonly=true, orientation=column, compresstype=zstd, compresslevel=9);