Storage models-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports two storage models: row-oriented and column-oriented. Choose the model when you create a table — row-oriented stores data by row, column-oriented by column, and each is optimized for different workloads.

Row-oriented tables

Row-oriented tables are the default. They use PostgreSQL's heap storage architecture: each row is written as a single unit on disk, so operations that read or write entire rows are fast.

Use row-oriented tables when:

Your workload is OLTP — rows are frequently inserted, updated, or deleted.
Your tables are small — dimension tables that are updated regularly are a typical example.
Your queries are point lookups — retrieving specific rows by key, especially with B-tree indexes.

Note

When you use Data Transmission Service (DTS) to import data into AnalyticDB for PostgreSQL, use row-oriented tables as the destination. DTS synchronizes data in near real time and handles UPDATE and DELETE statements in addition to inserts.

No WITH clause is needed — row-oriented storage is the default:

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

Column-oriented tables

Column-oriented tables store each column separately. When a query runs, the database reads only the columns it needs — reducing I/O for analytical workloads that access a small number of columns across many rows.

Use column-oriented tables when:

Your workload is OLAP — aggregations, group-by, and time-window queries over a small number of columns.
Your tables are large fact tables — loaded in batches and rarely updated.
Storage efficiency matters — column-oriented tables can achieve up to 5x higher compression than row-oriented tables, because adjacent values in the same column are often similar and compress well.

Column-oriented tables have two key limitations:

Frequent single-row writes are inefficient. Use bulk loading methods such as COPY instead of individual INSERT statements.
Column-oriented tables must be append-optimized (AO) tables. Set appendonly=true when creating the table.

To create a column-oriented table:

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

Choose a storage model

Feature	Row-oriented (heap)	Column-oriented (AO)
Storage engine	Heap	Append-optimized
Default	Yes	No (requires `WITH` clause)
Best for	OLTP, dimension tables, frequent updates	OLAP, fact tables, bulk loads
Compression	Standard	Up to 5x higher than row-oriented
INSERT pattern	Single-row or batch	Batch (`COPY`) recommended
UPDATE / DELETE	Full support	Limited; not optimized for frequent modifications
Query pattern	Efficient when accessing all or most columns	Efficient when accessing a few columns across many rows

Use these rules to decide:

Mixed or general-purpose workloads — row-oriented. Best combination of flexibility and performance.
Frequent single-row inserts, updates, or deletes — row-oriented.
Bulk-loaded, rarely modified data — column-oriented.
Queries that read all or most columns — row-oriented. Queries that read a small subset of many columns — column-oriented.

Change the storage model of a table

You can only set the storage model at table creation time. To change it for an existing table: create a replacement table with the new storage model, copy the data over, drop the original, and rename the new table.

Important

This process is not atomic. To prevent data loss, restrict write access to the original table before you begin.

The following example converts foo from row-oriented to column-oriented storage:

-- Step 1: Create a new column-oriented table with the same schema
CREATE TABLE foo_tmp (LIKE foo) WITH (appendonly=true, orientation=column);

-- Step 2: Copy data from the original table
INSERT INTO foo_tmp SELECT * FROM foo;

-- Step 3: Drop the original table
DROP TABLE foo;

-- Step 4: Rename the new table
ALTER TABLE foo_tmp RENAME TO foo;

-- Step 5: Reassign permissions
GRANT ALL PRIVILEGES ON foo TO user1;
GRANT SELECT ON foo TO user2;