AnalyticDB for PostgreSQL supports two storage models for tables: row-oriented storage and column-oriented storage.
Row-oriented table
By default, AnalyticDB for PostgreSQL uses the heap storage model in PostgreSQL to create row-oriented heap tables. Row-oriented tables are used for data that needs to be updated at a high frequency or written in real time by using the INSERT statement. A row-oriented table with a B-tree index provides high data retrieval performance when you query a small amount of data at a time.
Example:
CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);
Column-oriented table
Data within a column-oriented table is stored by column. When you access data, only relevant columns are read. Column-oriented tables are used in data warehousing scenarios such as data queries and aggregations of a small number of columns. In these scenarios, column-oriented tables provide efficient I/O. However, column-oriented tables are less efficient in scenarios that require frequent update operations or a large number of INSERT operations. We recommend that you use a batch loading method such as COPY to insert data into column-oriented tables. Column-oriented tables provide a data compression ratio three to five times higher than that provided by row-oriented tables.
Example:
CREATE TABLE bar (a int, b text)
WITH (appendonly=true, orientation=column)
DISTRIBUTED BY (a);
Data compression
- Table-level compression.
- Column-level compression. You can use a unique compression algorithm for each column.
AnalyticDB for PostgreSQL only supports the following compression algorithms:
- AnalyticDB for PostgreSQL V4.3 supports zlib and RLE_TYPE.
- AnalyticDB for PostgreSQL V6.0 supports Zstandard (zstd), zlib, RLE_TYPE, and lz4.
Examples:
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);