AnalyticDB:Storage models

Row-oriented tables

By default, AnalyticDB for PostgreSQL creates row-oriented tables. Row-oriented tables use the same heap storage architecture as PostgreSQL. Each row is stored as a single unit on disk, which makes row-oriented tables efficient for operations that access entire rows.

Row-oriented tables are best suited for:

• OLTP workloads where individual rows are frequently inserted, updated, or deleted.

• Small tables such as dimension tables that are regularly modified.

• Point lookup queries that retrieve specific rows by key, especially when B-tree indexes are used.

To create a row-oriented table, run the following statement. No WITH clause is required because row-oriented storage is the default:

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

Column-oriented tables

Column-oriented tables store data by column. When a query runs, the database reads only the columns referenced in the query, which reduces I/O for analytical workloads that access a small number of columns across many rows.

Column-oriented tables are best suited for:

• Data warehousing and OLAP workloads that aggregate data over a small number of columns.

• Large fact tables that are loaded in batches and rarely updated.

• Storage-sensitive scenarios because column-oriented tables can achieve up to 5x higher compression ratios than row-oriented tables. Columns of the same data type compress more efficiently because adjacent values are similar.

Column-oriented tables have the following limitations:

• They are less efficient for frequent single-row inserts, updates, or deletes. Use bulk loading methods such as COPY to insert data.

• A column-oriented table must be an append-optimized (AO) table. You must set appendonly=true when you create the table.

To create a column-oriented table, run the following statement:

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

Choose a storage model

The following table compares row-oriented and column-oriented tables to help you choose the appropriate storage model.

Use the following guidelines when you select a storage model:

• For most general-purpose or mixed workloads, use row-oriented tables. They provide the best combination of flexibility and performance.

• If you frequently insert, update, or delete individual rows, use row-oriented tables.

• If you bulk-load large volumes of data that is rarely modified, use column-oriented tables.

• If your queries typically access all or most columns, use row-oriented tables. If your queries typically access a subset of many columns, use column-oriented tables.

Change the storage model of a table

You can specify the storage model only when you create a table. To change the storage model of an existing table, create a new table with the desired storage model, copy data from the original table, drop the original table, and rename the new table.

The following example converts the foo table 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;