Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

Choosing Compression Codecs for Analytics Columns

The default LZ4 codec ClickHouse applies to every column is a throughput compromise, not a storage-optimal choice — a timestamp column that would shrink 10x under DoubleDelta, ZSTD instead sits at 2x, and every scan pays the difference in bytes read for the life of the table. This page is a per-column decision guide: when to reach for ZSTD over LZ4, how Delta, DoubleDelta, T64, and Gorilla transform the data before the general compressor sees it, and how to measure the result directly from system.columns instead of guessing.

Prerequisites

How a codec pipeline transforms a column

A ClickHouse column codec is a pipeline, written left to right in CODEC(...). Transform codecs (Delta, DoubleDelta, T64, Gorilla) rewrite the raw values into a more compressible form — usually smaller integers or longer runs of zeros — and then a general-purpose codec (LZ4 or ZSTD) does the actual entropy coding. Putting a transform first is what turns a slowly increasing timestamp column into a stream of tiny, near-constant deltas that ZSTD then crushes.

Per-column codec pipelines: a transform codec feeds a general compressor before writing the .bin file Three columns each run a two-stage codec pipeline. Timestamp uses DoubleDelta then ZSTD(1); counter uses Delta then ZSTD(3); low-cardinality name uses a dictionary then ZSTD(1). Each writes a compressed .bin file whose size shows in system.columns as data_compressed_bytes versus data_uncompressed_bytes. event_ts monotonic DoubleDelta 2nd-order diffs ZSTD(1) entropy code event_ts.bin ~10x smaller req_count slow rise Delta(8) 1st-order diffs ZSTD(3) strong ratio req_count.bin small residuals metric_name low card. LowCardinality dictionary ids ZSTD(1) id stream metric_name.bin dense ids system.columns data_compressed_bytes ÷ data_uncompressed_bytes = measured ratio

Step 1 — Baseline the current per-column footprint

You cannot improve what you have not measured. Read compressed and uncompressed bytes per column so you know which columns actually cost storage and which codecs they currently use.

sql
SELECT
    name,
    type,
    compression_codec,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts_columns
WHERE database = 'analytics' AND table = 'events' AND active
GROUP BY name, type, compression_codec
ORDER BY sum(data_compressed_bytes) DESC;

The columns at the top of this list — usually wide strings and high-entropy numerics — are where a codec change pays off. A column already at ratio 20x is not worth tuning; a 1.5x string column is.

Step 2 — Pick a general compressor: LZ4 vs ZSTD

Every column ends in a general codec. The choice is a throughput-versus-ratio trade:

  • LZ4 — the default. Very fast compress and decompress, modest ratio. Correct for hot columns scanned on every query where CPU, not disk, is the bottleneck.
  • ZSTD(level) — 1.5–3x better ratio than LZ4 at higher CPU cost. ZSTD(1) is nearly free and almost always beats LZ4 on ratio; ZSTD(3) is the sweet spot for cold analytical data; levels above ZSTD(6) rarely justify the write-time CPU for marginal gains.
sql
-- Cold, rarely-scanned wide string: favour ratio.
ALTER TABLE analytics.events MODIFY COLUMN payload String CODEC(ZSTD(3));

-- Hot column touched by every query: favour decode speed.
ALTER TABLE analytics.events MODIFY COLUMN event_type LowCardinality(String) CODEC(LZ4);

Step 3 — Add a transform codec for numeric and time columns

General compressors alone leave a lot on the table for structured numerics. Chain a transform first:

  • Delta(n) — stores first-order differences; ideal for slowly-changing counters and sequence ids. n is the byte width of the underlying type.
  • DoubleDelta — stores second-order differences; the go-to for monotonically increasing timestamps and auto-increment ids where the step is nearly constant.
  • Gorilla — XOR-based, tuned for floating-point gauges (metrics that wobble around a value).
  • T64 — crops the unused high bits of integers by transposing a block into a bit matrix; strong on bounded-range integers like enums, small counts, and Date.
sql
ALTER TABLE analytics.events
    MODIFY COLUMN event_ts   DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    MODIFY COLUMN req_count  UInt32        CODEC(Delta(4), ZSTD(3)),
    MODIFY COLUMN cpu_pct    Float32       CODEC(Gorilla, ZSTD(1)),
    MODIFY COLUMN status     UInt16        CODEC(T64, ZSTD(1));

These pair naturally with the LowCardinality(String) and DateTime64(3) typing conventions used throughout the columnar storage and compression reference — the codec is the second half of the storage decision the column type starts.

Step 4 — Set codecs at CREATE TABLE for new tables

For a fresh table, declare codecs inline so the very first part is written optimally instead of waiting for a rewrite.

sql
CREATE TABLE analytics.metrics
(
    `ts`          DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    `series_id`   UInt64        CODEC(Delta(8), ZSTD(1)),
    `metric_name` LowCardinality(String) CODEC(ZSTD(1)),
    `value`       Float64       CODEC(Gorilla, ZSTD(1)),
    `count`       UInt32        CODEC(T64, ZSTD(1)),
    `payload`     String        CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (metric_name, series_id, ts)
SETTINGS index_granularity = 8192;

Step 5 — Rewrite existing parts to apply codec changes

MODIFY COLUMN CODEC only affects parts written after the change. Force existing parts to adopt the new codec by rewriting them.

sql
-- Re-materialize the column's codec on all existing parts.
ALTER TABLE analytics.events MODIFY COLUMN payload String CODEC(ZSTD(3));
OPTIMIZE TABLE analytics.events FINAL;

On a large table, prefer rewriting partition by partition (OPTIMIZE TABLE ... PARTITION 'p' FINAL) to bound the merge load rather than rewriting everything at once.

Verification

Re-run the Step 1 footprint query and compare ratios before and after. To attribute the win to a specific column, diff the compressed size directly:

sql
SELECT
    name,
    compression_codec,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts_columns
WHERE database = 'analytics' AND table = 'events' AND active
  AND name IN ('event_ts', 'req_count', 'payload', 'status')
GROUP BY name, compression_codec
ORDER BY sum(data_compressed_bytes) DESC;

A successful timestamp change shows event_ts jumping from ~2x under plain LZ4 to 8–12x under DoubleDelta, ZSTD(1). Confirm the change actually reached disk by checking that no old-codec parts remain:

sql
SELECT DISTINCT compression_codec
FROM system.parts_columns
WHERE database = 'analytics' AND table = 'events' AND name = 'event_ts' AND active;

A single codec string means every active part was rewritten; two rows means the rewrite is incomplete.

Gotchas and edge cases

  • Delta/DoubleDelta on random data makes it bigger. These transforms assume smooth sequences. Applied to a high-entropy random UInt64 (a hash, a random id), the deltas are as random as the input but the codec adds framing overhead, so the column grows. Reserve them for monotonic or slowly-varying data and measure — never apply blindly.
  • Codec changes are lazy by design. MODIFY COLUMN CODEC updates only metadata for future parts, so a footprint query right after the ALTER shows no change. This surprises engineers who expect an immediate shrink — you must OPTIMIZE ... FINAL (or wait for background merges) for existing data to adopt the codec.
  • High ZSTD levels tax the write path, not just storage. ZSTD(15) on a high-velocity ingestion table can make compression the insert bottleneck and starve background merges of CPU, which then trips TOO_MANY_PARTS. Keep ingest-hot columns at ZSTD(1)–ZSTD(3) and reserve high levels for cold, append-rarely tables — the merge pressure interaction is covered in how MergeTree handles background merging.
  • LowCardinality already compresses; do not double-encode. Wrapping a genuinely high-cardinality string in LowCardinality bloats it with a dictionary that never pays off, and stacking Delta on the dictionary ids helps nothing. Use LowCardinality(String) CODEC(ZSTD(1)) for repeating labels and a plain String CODEC(ZSTD(3)) for free text.

Up: Columnar Storage & Compression