ClickHouse Compression Codecs for Time-Series Data: A Benchmark

I benchmarked five compression codec setups on 100 million rows of synthetic time-series data in ClickHouse 25.11, from plain default LZ4 to per-column codecs (DoubleDelta, Delta, Gorilla) combined with ZSTD. I measured three things: storage size, query latency (cold and warm), and ingest speed.

The short version:

1. Per-column codecs + ZSTD(3) compress 4.23×, default LZ4 only 1.67×. Almost all of that comes from two columns: sorted timestamps (872×) and monotone counters (843×).
2. ZSTD(9) is not worth it. Same compression as ZSTD(3), but 28% slower ingest.
3. Better compression does not mean faster queries. Heavily compressed columns query faster cold, but ZSTD costs CPU on every read, so plain LZ4 often wins warm.
4. Gorilla can make float data bigger. It only helps on smooth, slowly-changing values.

All data, scripts and plots are in comino/blog-experiments. Numbers below are median ± IQR over 10 runs.

Setup

• Server: Hetzner CX53 with 16 vCPU (AMD EPYC), 32 GB RAM, NVMe SSD
• ClickHouse: 25.11.3.54, default settings
• Dataset: 100M rows × 6 columns, simulating Prometheus-style metrics
• Cold runs: all ClickHouse and OS caches dropped before each measurement
• Warm runs: one warmup query first, then 10 measured runs

One caveat up front: the whole dataset fits in RAM (543 MB to 1.37 GB depending on codec). So my “cold” numbers show NVMe re-read plus decompression, not the sustained disk I/O you would see when data is much bigger than memory. More on that below.

Data Generation

The 100M rows come from ClickHouse’s numbers() function:

INSERT INTO exp01_compression.source
SELECT
    toDateTime('2024-01-01 00:00:00') + intDiv(number, 50) AS timestamp,
    ['cpu_usage','disk_io','http_requests_total','mem_free','net_bytes_sent']
        [1 + (number % 5)] AS metric_name,
    50 + 30 * sin(number / 1000.0) + (rand(number) % 1000) / 2000.0 AS value,
    concat('host-', toString(number % 50)) AS host,
    ['us-east','us-west','eu-central','ap-south'][1 + (rand(number + 1) % 4)] AS region,
    intDiv(number, 50) AS counter
FROM numbers(100000000);

That gives me:

• timestamp: strict 1-second intervals, ~115 days of data
• counter: strictly increasing UInt64
• value: a sine wave plus noise, like a noisy gauge metric
• metric_name, host, region: LowCardinality strings (5, 50 and 4 distinct values)

All tables use ORDER BY (metric_name, host, timestamp). After insert, OPTIMIZE TABLE … FINAL merged everything into a single part.

This data is ideal for compression: perfectly regular timestamps, perfectly monotone counters. Real data is messier and will compress less. Keep that in mind for every ratio below.

The Five Variants

-- V1: Default (LZ4)
CREATE TABLE v1_default (
    timestamp DateTime,
    metric_name LowCardinality(String),
    value Float64,
    host LowCardinality(String),
    region LowCardinality(String),
    counter UInt64
) ENGINE = MergeTree()
ORDER BY (metric_name, host, timestamp);

-- V2: ZSTD(3) on all columns
CREATE TABLE v2_zstd (
    timestamp DateTime CODEC(ZSTD(3)),
    metric_name LowCardinality(String) CODEC(ZSTD(3)),
    value Float64 CODEC(ZSTD(3)),
    host LowCardinality(String) CODEC(ZSTD(3)),
    region LowCardinality(String) CODEC(ZSTD(3)),
    counter UInt64 CODEC(ZSTD(3))
) ENGINE = MergeTree()
ORDER BY (metric_name, host, timestamp);

-- V3: Per-column specialized + LZ4
CREATE TABLE v3_percolumn (
    timestamp DateTime CODEC(DoubleDelta, LZ4),
    metric_name LowCardinality(String) CODEC(LZ4),
    value Float64 CODEC(Gorilla(8), LZ4),
    host LowCardinality(String) CODEC(LZ4),
    region LowCardinality(String) CODEC(LZ4),
    counter UInt64 CODEC(Delta(8), LZ4)
) ENGINE = MergeTree()
ORDER BY (metric_name, host, timestamp);

-- V4: Per-column specialized + ZSTD(3)
CREATE TABLE v4_percolumn_zstd (
    timestamp DateTime CODEC(DoubleDelta, ZSTD(3)),
    metric_name LowCardinality(String) CODEC(ZSTD(3)),
    value Float64 CODEC(Gorilla(8), ZSTD(3)),
    host LowCardinality(String) CODEC(ZSTD(3)),
    region LowCardinality(String) CODEC(ZSTD(3)),
    counter UInt64 CODEC(Delta(8), ZSTD(3))
) ENGINE = MergeTree()
ORDER BY (metric_name, host, timestamp);

-- V5: Same as V4, but ZSTD(9) on most columns
CREATE TABLE v5_aggressive (
    timestamp DateTime CODEC(DoubleDelta, ZSTD(9)),
    metric_name LowCardinality(String) CODEC(ZSTD(9)),
    value Float64 CODEC(Gorilla(8), ZSTD(3)),
    host LowCardinality(String) CODEC(ZSTD(9)),
    region LowCardinality(String) CODEC(ZSTD(9)),
    counter UInt64 CODEC(Delta(8), ZSTD(9))
) ENGINE = MergeTree()
ORDER BY (metric_name, host, timestamp);

The (8) in Gorilla(8) and Delta(8) is the value width in bytes (Float64/UInt64 = 8). In V5, value stays on ZSTD(3): the Gorilla output of noisy floats is basically incompressible, so a higher ZSTD level has nothing to work with.

Storage

Sizes come from system.parts_columns (column data only, one merged part per table):

V4 and V5 differ by 141 KB on 543 MB. The specialized pre-codecs do the heavy lifting; raising ZSTD from level 3 to 9 buys nothing here.

Where the difference comes from, per column:

Why 872× on timestamps? The data is sorted, so within each (metric_name, host) group the timestamps step by exactly 1 second. DoubleDelta turns that into near-constant zeros, and ZSTD squeezes those to almost nothing. Plain LZ4 without a pre-codec gets 1.0×: it simply cannot see the pattern. Same story for the counter column with Delta.

Why does value barely compress? It’s a sine wave plus random noise. Noise has no pattern to exploit. Worse: V3 (Gorilla+LZ4) lands at 1.26×, below plain LZ4’s 1.45×. Gorilla’s XOR encoding actually expanded the noisy data.

When Gorilla Helps

Gorilla XORs each value with the previous one and stores only the changed bits. Nearly-identical neighbors compress great; noisy neighbors change all the bits and add overhead. I tested four distributions on 10M-row tables:

Rule of thumb:

• ✅ Slowly drifting values: battery levels, temperatures, counters stored as floats
• ✅ Sparse data that is mostly zeros
• ❌ Noisy gauges: CPU%, latency, throughput
• ❌ Random values

Query Performance

I ran 10 queries covering common access patterns:

-- Q1: Point lookup (1 host, 1 hour)
SELECT avg(value) FROM {table}
WHERE metric_name='cpu_usage' AND host='host-0'
  AND timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 01:00:00';

-- Q2: Range scan (1 metric, 7 days)
SELECT avg(value) FROM {table}
WHERE metric_name='cpu_usage'
  AND timestamp BETWEEN '2024-01-01' AND '2024-01-08';

-- Q3: Top-K GROUP BY host
SELECT host, count(), avg(value) FROM {table}
WHERE metric_name='cpu_usage'
GROUP BY host ORDER BY count() DESC LIMIT 10;

-- Q4: Point lookup (1 host, 1 hour, multi-agg)
SELECT avg(value), min(value), max(value) FROM {table}
WHERE metric_name='cpu_usage' AND host='host-3'
  AND timestamp BETWEEN '2024-01-05 12:00:00' AND '2024-01-05 13:00:00';

-- Q5: Full table scan, touches the value column
SELECT count() FROM {table} WHERE value > 60;

-- Q6: Heavy aggregation, reads all columns
SELECT metric_name, host, avg(value), sum(counter) FROM {table}
GROUP BY metric_name, host ORDER BY avg(value) DESC LIMIT 20;

-- Q7: p99 percentile on value
SELECT metric_name, quantile(0.99)(value) AS p99 FROM {table}
GROUP BY metric_name;

-- Q8: Distinct counts on string columns
SELECT uniqExact(metric_name), uniqExact(host) FROM {table};

-- Q9: Last value per host (argMax)
SELECT host, argMax(value, timestamp) AS last_value FROM {table}
WHERE metric_name='cpu_usage' GROUP BY host;

-- Q10: Moving average (window function)
SELECT timestamp, avg(value) OVER (ORDER BY timestamp
  ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS ma60
FROM {table}
WHERE metric_name='cpu_usage' AND host='host-0'
  AND timestamp BETWEEN '2024-01-01' AND '2024-01-02'
ORDER BY timestamp;

Cold (median ± IQR, ms, n=10)

Warm (median ± IQR, ms, n=10)

What the numbers say

Warm queries: LZ4 wins almost everywhere. Look at Q05: V1 drops from 229 ms cold to 16 ms warm. V4 only drops from 243 ms to 86 ms. Once data sits in the page cache, V1 pays almost nothing for decompression, while V4 pays the ZSTD CPU cost on every single read. That cost doesn’t go away with caching.

Cold queries: compression helps where it actually compressed something. Q06 reads the counter column, which is 420 MB in V1 but under 1 MB in V4. Less data to read, faster cold query (819 ms vs 905 ms). Q09 benefits the same way from the tiny timestamp column.

Queries on the noisy value column: LZ4 wins, cold and warm. Q07 scans value, which is roughly the same size in every variant. Same bytes to read, but ZSTD is about 3× more expensive to decompress than LZ4. V1: 401 ms cold. V4: 480 ms.

Tiny queries don’t care. Point lookups (Q01, Q04, Q10) read a few granules; all variants are identical.

To check the “ZSTD costs CPU” explanation, I pulled ProfileEvents from system.query_log for warm Q05: both variants read the same 320 MB and 40M rows, disk time is nearly identical, but V4 burns 6.8× more CPU than V1 (1.3s vs 0.19s summed across threads). That’s the decompression. I only profiled this one query; the pattern is plausible for the other value-heavy queries but I did not verify each one.

A note on “cold” here: the dataset fits in RAM and the NVMe is fast, so cold vs warm mostly measures re-read overhead, not real disk pressure. On a system where data is much bigger than RAM, V4’s 2.5× smaller footprint would matter much more than it does in my numbers. My results understate the cold-query benefit of good compression.

Ingest

Single-threaded INSERT from a source table, three batch sizes, 10 runs each (median rows/s ± IQR):

At small batches, per-INSERT overhead dominates and all variants look the same. At 1M-row batches:

• V1: 5.67M rows/s, fastest
• V4: 4.96M rows/s, 12% slower. The pre-codecs (Delta, DoubleDelta) are cheap.
• V5: 3.56M rows/s, 28% slower than V4 for zero compression gain. Clear lose-lose.

So Which One?

There is no single winner. Pick by workload:

For reference, the averages across all ten queries (mean of per-query medians):

And as a decision tree:

Is your column a sorted timestamp?
  → Yes: DoubleDelta + ZSTD(3)                    [872× on sorted 1s intervals]
  → No: Is it a monotone counter?
    → Yes: Delta(8) + ZSTD(3)                      [843× on strictly monotone]
    → No: Is it a Float?
      → Smooth/slowly changing?  → Gorilla(8) + LZ4   [up to 45× on monotone floats]
      → Noisy/random?           → LZ4 default         [Gorilla hurts here]
    → String/LowCardinality?    → ZSTD(3)             [867–908×]

The takeaways I’d actually carry into production:

1. Per-column codec choice is where the value is. V1 to V4 is 1.67× to 4.23×, and that comes from DoubleDelta and Delta, not from ZSTD.
2. Skip ZSTD(9). On this data it compressed nothing extra and cost 28% ingest.
3. Don’t put Gorilla on noisy floats. Measure first.
4. Measure on your own data. My 800×+ ratios need perfectly regular intervals. Real metrics with variable scrape intervals will land somewhere between 10× and 100× on timestamps. Still great, just not 872×.

Limitations

Honest list, so you can judge how far these numbers carry:

1. Synthetic, perfectly regular data. Real metrics compress less.
2. Everything fits in RAM, single merged part, single-threaded queries. Production has many parts, concurrent load, and data bigger than memory. Each of these shifts the trade-offs, mostly in favor of better compression.
3. Fixed query order, no randomization. Warm runs could carry cache effects between queries.
4. I profiled decompression CPU for one query only (Q05). The explanation fits the other results but isn’t individually proven.
5. FPC codec not tested. It targets floats and might beat both LZ4 and Gorilla on the noisy value column.

References

1. Pelkonen, T., et al. “Gorilla: A Fast, Scalable, In-Memory Time Series Database.” VLDB 2015. PDF
2. ClickHouse Compression Documentation
3. Optimize Codecs & Schema (ClickHouse Blog)
4. Altinity: New Encodings to Improve ClickHouse (2019)

Tested on ClickHouse 25.11.3.54, Hetzner CX53 (16 vCPU, 32 GB RAM, NVMe SSD). Raw data, scripts and plots: github.com/comino/blog-experiments.

Questions, corrections, or a consulting project? Mail me or find me on GitHub.