Choosing ClickHouse codecs per column profile

The table

This is the audit table of a digital out-of-home network: one row per ad play, coming from about 15,000 screensfleet size at the time of measurement, rounded in elevators and malls. Every row is billing evidence, so nothing gets sampled or summarized away. The names below are generic; the shape is the real one.

CREATE TABLE playback_log
(
    uid          UUID      CODEC(ZSTD(1)) TTL started_at + toIntervalMonth(1),
    device_id    UInt32    CODEC(T64, ZSTD(1)),
    order_id     UInt32    CODEC(ZSTD(1)),
    asset_id     UInt32    CODEC(ZSTD(1)),
    started_at   DateTime  CODEC(DoubleDelta, ZSTD(1)),
    ended_at     DateTime  CODEC(DoubleDelta, ZSTD(1)),
    imported_at  DateTime  DEFAULT now('UTC') CODEC(DoubleDelta, ZSTD(1)),
    INDEX idx_imported imported_at TYPE minmax GRANULARITY 16
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(started_at)
ORDER BY (started_at, order_id, device_id)
TTL started_at + toIntervalYear(1)
SETTINGS index_granularity = 8192;

Three things to notice before we get to the codecs:

• The sort key starts with the timestamp. This decides more than any codec does. Keep it in mind for the next section.
• Two TTLs. The row lives one year. The uid column lives one month, because it only exists to deduplicate, and after 30 days a retransmission from an offline screen doesn't arrive anymore. Carrying a UUID on billions of rows for a year would be waste.
• ReplacingMergeTree is the safety net for duplicates, and it only collapses them during merges. That's a separate article.

What each column compresses to

In ClickHouse, CODEC(...) takes two layers. First an encoding that changes the shape of the data (Delta, DoubleDelta, Gorilla, T64, and a few newer ones). Then a general-purpose compressor that runs on the result (LZ4, ZSTD). Most tables I see only set the second layer, or neither.

This is what the production table reports today, per column, from system.parts_columns:

Measured 2026-09-03 over 100.78 billion rows, 410.33 GiB on disk. uid is not listed: the column TTL drops it after 30 days, so most parts don't carry it anymore.

To get the same report on your own table, this reads metadata only, so it's safe on a busy server:

SELECT
    column,
    type,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS raw,
    formatReadableSize(sum(column_data_compressed_bytes))   AS on_disk,
    round(sum(column_data_uncompressed_bytes)
        / sum(column_data_compressed_bytes), 1)             AS ratio,
    round(sum(column_data_compressed_bytes) / sum(rows), 2) AS bytes_per_row
FROM system.parts_columns
WHERE database = currentDatabase()
  AND table = 'playback_log'
  AND active
GROUP BY column, type
ORDER BY ratio DESC;

Same codec, same type, 17x apart

started_at and ended_at are both DateTime with DoubleDelta, ZSTD(1). One compresses 269.6x375 GiB raw to 1.39 GiB, about a hundredth of a byte per row, the other 15.6x. The only difference is that started_at is the first column of the sort key.

DoubleDelta stores the difference of the differences between consecutive values. In a column that is sorted, the first delta is already small, and the second one is almost always zero. The column turns into a long run of zeros, and a run of zeros is the best input ZSTD can get.

ended_at is started_at plus a few seconds of play time. The deltas are small, but the column isn't sorted, so the second-order delta keeps some noise: 15.6x. imported_at is when the batch reached the server. Screens upload in batches and go offline for hours sometimes, so the gaps are irregular: 2.9x, with the same codec.

So the biggest number in this table is really a sort-key result wearing a codec name. Decide ORDER BY for your queries first. The codecs take what the ordering leaves.

T64 on the device id

There are about 15,000 devices stored in a UInt32, a type that can hold four billion. T64 transposes a block of 64 integers so that bits of the same position are stored together. The high bits are zero on every row, and after the transposition they pack down to nothing.

It still ended up as the worst column in the table, 2.4x. Inside one granule (8,192 rows ordered by time) the device id jumps around, because every play comes from a different screen. Sorting by device first would fix that column and break the timestamp. The sort key serves the queries, which filter by period and order, so the device id keeps its 1.67 bytes.

Where plain ZSTD won

order_id and asset_id aren't monotonic and their range isn't short enough for T64 to matter. I tried the encodings on them too. None beat plain ZSTD(1), so plain ZSTD(1) it is. Finding out that the specialized codec doesn't help is a result, and it's one worth writing down so nobody re-tests it next year.

Level 1 everywhere. Higher ZSTD levels are a separate test, on your own merge load, and I won't claim numbers for that here.

What it means for your disk

Bytes per row is the number that survives the move from one table to another. Multiply it by your own volume and retention and you have the disk you need, before you run a single test. The raw width of this seven-column row is 40 bytes (16 for the UUID, 12 for the three UInt32, 12 for the three DateTime); 4.37 is what's left after codecs and the column TTL.

How I tested

The documentation tells you what each codec does. It doesn't tell you which one wins on your data. I got to this configuration by loading millions of real rows into test tables and measuring, column by column, around 60 variationsmy own benchmark; count from memory, approximate in total. Two measurements per variation: bytes on disk, and the time of the heaviest query we ran in production. Some variations win on disk and lose on the query. You want both numbers side by side.

The loop is short. One variation is one table, and you only change the thing you're testing:

-- same structure, engine and sort key as production
CREATE TABLE codec_test_v12 AS playback_log;

-- change only the codec under test
ALTER TABLE codec_test_v12
    MODIFY COLUMN device_id UInt32 CODEC(Delta, ZSTD(1));

-- millions of rows, so you cross many granules and several parts
INSERT INTO codec_test_v12
SELECT * FROM playback_log
WHERE started_at >= '2026-08-01' AND started_at < '2026-08-08';

-- measure merged parts, not fresh inserts
OPTIMIZE TABLE codec_test_v12 FINAL;

-- then run the parts_columns query above, and your heaviest query 3 times

Three details that changed results for me:

1. Thousands of rows are not enough. Codecs work per block, and the sort key only shows its effect once rows span many granules. Use millions.
2. Run OPTIMIZE ... FINAL before measuring. Fresh inserts sit in small parts; the ratio you'll live with is the one after merges.
3. Keep the heavy query in the loop. Disk is the cheap half of the trade. A codec that saves 10% of disk and adds 30% to your worst query is a bad deal on most budgets.

Today the whole instance runs on three combinations: ZSTD(1) on 257 columnscensus of system.columns, 2026-08-26: 257 / 74 / 35 columns, DoubleDelta, ZSTD(1) on 74, T64, ZSTD(1) on 35. Three combinations were left after about sixty test tables.

What this doesn't tell you

• If your timestamp is not the sort key, expect something like ended_at, around 15x, and be happy with it.
• The 5.5x overall is pulled down by device_id and by uid during its first month. A narrow table with no UUID would look better, and would also lose its deduplication.
• Codecs are a disk and I/O story. The ceiling for ClickHouse queries on this workload was RAM, not disk. A good codec never made a query fit in memory.
• This is one table, one workload, one measurement date. Copy the method before you copy the DDL.

Filed under the problem it solves

• clickhouse
• clickhouse codecs
• compression ratio
• sort key
• bytes per row
• doubledelta
• t64
• disk budget