Data compression

Why use data compression

Large datasets stored in their original format consume significant storage space and increase disk I/O overhead during queries. Data compression provides the following benefits:

• Reduce storage costs: Compressed data can be as small as 20% to 50% of its original size.

• Improve query performance: Reduced disk I/O allows data to be read faster, which boosts performance for I/O-intensive workloads.

• Lower network overhead: In a distributed environment, transferring compressed data between nodes reduces network bandwidth consumption.

Supported compression algorithms

SelectDB supports the following compression algorithms. Choose one based on the compression ratio and performance requirements of your workload.

How it works

SelectDB compresses data with the following characteristics:

• Column-level compression: SelectDB uses a columnar storage format. Data within the same column shares the same data type and similar distribution, which typically yields better compression than row-based storage.

• Compression before encoding: Data is compressed before column encoding techniques such as dictionary encoding or Run-Length Encoding (RLE) are applied, which improves the compression ratio.

• Page-level compression: Each page in a data file is compressed independently, which enables efficient processing of large datasets while maintaining a high compression ratio and decompression performance.

• Configurable compression policy: Specify a table-level compression setting in the PROPERTIES clause of a CREATE TABLE statement. The setting applies to all columns in the table.

Factors affecting compression efficiency

The following factors affect the actual compression ratio:

• Data redundancy: The higher the data redundancy, the better the compression. For example, columns with low cardinality (such as status codes or region codes) often achieve extremely high compression ratios.

• Data type: Numeric types (such as INT and BIGINT) and fixed-length types generally compress well. Highly random data, such as random strings or UUIDs, compresses poorly.

• Data order: Sorting data by a specific column reduces variance between adjacent values, which improves compression. Setting an appropriate sort key in SelectDB increases the compression ratio.

• Column length: Shorter columns typically compress better than longer ones because compression algorithms find repeating patterns more efficiently in smaller data blocks.

• Null values: A high proportion of null values in a column improves compression, as the algorithm can encode nulls as a special pattern to save space.

• Choice of compression algorithm: Choose an algorithm based on test results with your actual data.

Use data compression

Specify algorithm at table creation

To set the compression algorithm for a table, specify the compression parameter in the PROPERTIES clause of a CREATE TABLE statement:

CREATE TABLE example_table (
    id INT,
    name STRING,
    age INT
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
    "compression" = "zstd"
);

Valid values for the compression parameter are no_compression, lz4, lz4f, lz4hc, zstd, snappy, and zlib. By default, SelectDB uses lz4 compression.

Choose a compression algorithm

Consider the following recommendations when selecting a compression algorithm:

• General workloads (ZSTD recommended): ZSTD provides a good balance between compression ratio and read/write performance, making it suitable for most Online Analytical Processing (OLAP) workloads.

• High write throughput workloads (LZ4 recommended): If your application requires high write throughput, the low overhead of LZ4 helps reduce write latency.

• Maximum compression workloads (Zlib recommended): If storage cost is your primary concern and write speed is not critical (for example, cold data archiving), Zlib provides the highest compression ratio.

• Test before you decide: Run benchmark tests with your real data before deploying to production.