AnalyticDB for PostgreSQL:Compressed storage

Compression method

When you create an AOCO table, you can set the COMPRESSTYPE field to specify a compression type. Data is not compressed if this field is not specified. Valid values of this field are zstd, zlib, and lz4. You can also set the COMPRESSLEVEL field to specify a compression level. The field value is in the range of 1 to 19. A greater value indicates a higher compression ratio. We recommend that you set this field to 9.

CREATE TABLE LINEITEM (
    ....
)
WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=lz4, COMPRESSLEVEL=9)
DISTRIBUTED BY (L_ORDERKEY);

Compressed storage provides two benefits. Compressed data reduces storage costs and can reduce the amount of occupied storage space by up to 80%. Compressed data requires less I/O bandwidth for writes and queries and improves performance in I/O-intensive scenarios.

Performance testing

The following table lists the performance metrics of the zlib, zstd, and LZ4 algorithms. The zstd algorithm delivers a balanced performance in terms of the compression speed, decompression speed, and compression rate. We recommend that you use the zstd algorithm in most scenarios. The zlib algorithm is supported for compatibility purposes when specific existing data is compressed. We recommend that you do not use the zlib algorithm for new tables. The LZ4 algorithm delivers a lower compression speed and compression rate, but a much higher decompression speed than the zstd algorithm does. We recommend that you use the LZ4 algorithm in scenarios that require high query performance.