By default, AnalyticDB for PostgreSQL creates tables using row-store with no compression. To use column-store and compression, specify the storage options in your CREATE TABLE statement.
When to use column-store
Column-store tables perform best when your workload matches one or more of these patterns:
-
Query a subset of columns — Your queries reference only a few columns out of many. Column-store reads only the columns needed, skipping the rest.
-
Run complex analytical queries — Aggregations, GROUP BY, and window functions over large datasets benefit from the compressed, column-aligned layout.
-
Reduce storage cost — Column data shares the same data type, so compression ratios are significantly higher than row-store.
Enable column-store and compression
Add the following WITH clause to your CREATE TABLE statement:
WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576, OIDS=false)Storage parameters
| Parameter | Description | Value in example |
|---|---|---|
APPENDONLY |
Enables append-optimized storage, required for compression | true |
ORIENTATION |
Sets the storage layout: column for column-store, row for row-store |
column |
COMPRESSTYPE |
Compression algorithm | zlib |
COMPRESSLEVEL |
Compression level | 5 |
BLOCKSIZE |
Block size in bytes | 1048576 |
OIDS |
Assigns a system object identifier to each row. Set to false for analytical tables |
false |
What's next
-
Define storage models for tables — Full reference for row-oriented and column-oriented storage in AnalyticDB for PostgreSQL.