Compression and codecs

By default ClickHouse applies lz4 in the self-managed version and zstd in ClickHouse Cloud.

Measure before you change anything#

Compression ratio per column tells you where storage is going:

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'posts'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

Some columns compress spectacularly on their own. On the Stack Overflow posts dataset, FavoriteCount compresses 508.40 MiB down to 280.95 KiB — a ratio of 1853. Others, like hashes and random identifiers, barely compress at all and are better left alone.

If compressed and uncompressed sizes both read 0.00 B, the parts are stored in Compact format because they fall below min_bytes_for_wide_part. Load more data and measure again.

Types matter more than codecs#

Before reaching for a codec, fix the types. On the same Stack Overflow dataset, choosing correct types and a sensible ordering key took the table from:

┌─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ 50.16 GiB       │ 143.47 GiB        │  2.86 │
└────────────────┴───────────────────┴───────┘

down to:

┌─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ 25.15 GiB       │ 68.87 GiB         │  2.74 │
└────────────────┴───────────────────┴───────┘

Half the storage, from type choices alone. See data types — narrower integers, LowCardinality for repeated strings, and avoiding Nullable do most of this work.

Declare a codec#

Codecs are declared per column and chained left to right, with the transform first and the general-purpose compressor last:

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(Delta, ZSTD),
    gauge Float32 CODEC(Gorilla, ZSTD),
    raw_id String CODEC(NONE)
)
ENGINE = MergeTree
ORDER BY dt;

General-purpose codecs#

ZSTD(1) is a reasonable default for most columns. Levels above 3 rarely pay for themselves. Where LZ4 and ZSTD give comparable ratios, prefer LZ4 — it decompresses faster and uses less CPU.

Specialized codecs#

These transform the data before a general-purpose codec compresses it. They are data-preparation codecs and are chained, not used alone.

CREATE TABLE codec_example
(
    timestamp DateTime CODEC(DoubleDelta),
    slow_values Float32 CODEC(Gorilla)
)
ENGINE = MergeTree()
ORDER BY timestamp

Measure the codec change too#

Codecs are not a uniform win. Adding CODEC(Delta, ZSTD) to three columns of the same table produced this:

┌─table────┬─name────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ posts_v3 │ Id          │ 159.70 MiB      │ 227.38 MiB        │  1.42 │
│ posts_v4 │ Id          │ 64.91 MiB       │ 222.63 MiB        │  3.43 │
│ posts_v3 │ ViewCount   │ 45.04 MiB       │ 227.38 MiB        │  5.05 │
│ posts_v4 │ ViewCount   │ 52.72 MiB       │ 222.63 MiB        │  4.22 │
│ posts_v3 │ AnswerCount │ 9.67 MiB        │ 113.69 MiB        │ 11.76 │
│ posts_v4 │ AnswerCount │ 10.39 MiB       │ 111.31 MiB        │ 10.71 │
└──────────┬─────────────┬────────────────┴───────────────────┴───────┘

Id improved from 1.42 to 3.43 — it is a monotonically increasing sequence, exactly what Delta is for. ViewCount and AnswerCount both got worse, because their values are not sequential and the delta transform added noise.

Apply codecs column by column, and measure each one.

Rules that hold up#

• ZSTD(1) as the default for most columns. Higher levels rarely justify the CPU.
• Delta for date and integer sequences, where consecutive values are close or monotonic.
• Gorilla or FPC for float gauges that change slowly.
• LZ4 over ZSTD when the ratios are comparable — faster decompression, less CPU.
• NONE for hashes, UUIDs, and random identifiers. Incompressible data still costs CPU to attempt.

Encryption#

Codecs also cover encryption at rest per column, chained after compression:

CREATE TABLE mytable
(
    x String CODEC(AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;

Reading compressed files outside ClickHouse#

ClickHouse data files cannot be decompressed with external utilities such as lz4. Use the clickhouse-compressor utility that ships with the server.

Related#

• Data types — type choice does more than codec choice
• Monitor a running server — track compression over time
• Why ClickHouse is fast — where compression sits in the design