Table Storage Format Definitions

Choose a storage model

The model you choose affects write throughput, query performance, and compression ratio. Use the following criteria to decide.

Specify the storage model

Use the WITH clause in CREATE TABLE to set the storage model and configure compression. The following parameters are supported.

Row-oriented tables

By default, AnalyticDB for PostgreSQL creates row-oriented tables using the PostgreSQL heap storage model. Row-oriented tables are optimized for:

• Real-time writes via INSERT

• Frequent UPDATE and DELETE operations

• Point queries with a B-tree index

Heap table (default)

CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);

Append-optimized row-oriented (AORO) table

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, reading only the columns referenced in a query. This makes them well-suited for data warehouse workloads — particularly queries that aggregate a small number of columns, such as:

SELECT SUM(revenue), AVG(cost) FROM orders WHERE region = 'us';

Column-oriented tables provide a compression ratio 3–5 times that of row-oriented tables. They are less efficient for frequent row-level inserts or updates — use batch loading methods like COPY for best performance.

Column-oriented tables must be append-optimized. Set appendonly=true when creating one.

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

Hybrid row-column storage tables

The hybrid row-column storage model uses the Beam storage engine, which combines two internal storage layers:

• Delta storage (row-oriented): handles real-time streaming writes via INSERT INTO ... VALUES. Delivers write performance comparable to row-oriented tables.

• Base storage (PAX-based, column-oriented): handles batch writes via COPY or INSERT INTO ... SELECT. Delivers higher throughput for bulk data.

Beam automatically routes incoming data to the appropriate layer based on the write method.

CREATE TABLE testtable (a int) USING beam;

ALTER TABLE testtable SET ACCESS METHOD beam;

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

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

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

ALTER TABLE am_partitioned_1 SET ACCESS METHOD ao_row;

Data compression

Compression is available for column-oriented tables and append-optimized row-oriented (AORO) tables (appendonly=true). Two levels of compression are supported:

• Table-level compression: applies a single algorithm to the entire table

• Column-level compression: applies a unique compression algorithm to each column

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

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

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