Compressed storage
AnalyticDB for PostgreSQL supports three table storage formats: row-oriented tables, append-optimized row-oriented (AORO) tables, and append-optimized column-oriented (AOCO) tables. AOCO tables support compression, which you configure at table creation time. Compressed AOCO tables can reduce storage space by up to 80% and lower I/O bandwidth usage for both writes and queries.
How it works
When you create an AOCO table, set COMPRESSTYPE to one of the supported algorithms: zstd, zlib, or lz4. If COMPRESSTYPE is not set, data is stored uncompressed.
Use COMPRESSLEVEL to control the compression ratio. Valid values are 1–19. A higher value produces a better compression ratio at the cost of compression speed. A level of 9 is a good starting point for most workloads.
CREATE TABLE lineitem (
...
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=lz4, COMPRESSLEVEL=9)
DISTRIBUTED BY (l_orderkey);Choose a compression algorithm
All three algorithms trade off compression speed, decompression speed, and compression ratio differently. Use the table below to match your workload.
| Algorithm | Compression speed | Decompression speed | Compression ratio | Use when |
|---|---|---|---|---|
| zstd | 19.2 MB/s | 353.4 MB/s | 3.99 | You want a balance of compression speed, decompression speed, and storage savings — covers most workloads |
| LZ4 | 12.7 MB/s | 854.4 MB/s | 3.2 | Query throughput is the priority and you can accept lower storage savings |
| zlib | 9.5 MB/s | 154.9 MB/s | 3.83 | Existing data is already compressed with zlib and you need backward compatibility |
zstd is the recommended default. It delivers the highest compression ratio and fast decompression, making it effective for both storage savings and query performance.
LZ4 decompresses at more than twice the speed of zstd. Use it when queries are I/O-intensive and scan large volumes of data frequently, and when storage costs are secondary.
zlib is retained for compatibility. Avoid it for new tables — zstd outperforms it on every metric.
Limitations
Compression applies only to AOCO tables. Row-oriented and AORO tables do not support compression.
The compression ratios in the table above are based on benchmark data. Actual ratios vary depending on your data characteristics.