Choosing column codecs: Delta, DoubleDelta, Gorilla and friends

By default ClickHouse compresses every column with LZ4. That is a good general choice, but time series data often compresses far better when a specialised codec first reshapes the values and LZ4 or ZSTD then squeezes the result. The codecs Delta, DoubleDelta and Gorilla all do this, and picking the wrong one is easy because their names suggest they are interchangeable. They are not. All results below were measured on ClickHouse 26.4.

What each codec exploits

Codec	Stores	Works well when
Delta	Difference between each value and the previous one	Values grow steadily or stay close to the previous value
DoubleDelta	Difference of the differences	Values grow at a nearly constant rate, such as regular timestamps or monotonically increasing counters
Gorilla	XOR of each float with the previous one	Floating-point values that change slowly or repeat
T64	Crops unused high bits for integer blocks	Integers that use only a small part of their type range
GCD	Divides by the greatest common divisor	Integers that are all multiples of something

A specialised codec does not compress by itself. It is a transform, always followed by a general-purpose codec. Write both, as in CODEC(DoubleDelta, LZ4), or the default is applied after the transform.

Two rules cover most mistakes:

1. The column must be in sorted or near-sorted order for the delta codecs to help. Rows are stored in ORDER BY order, so a timestamp column that is part of the sorting key (and ideally the leading one inside a group) gives tiny deltas. The same column in an unsorted position gives random jumps, and the codec does nothing useful.
2. Gorilla is for floats. On integer or DateTime columns ClickHouse refuses it:

Code: 36. DB::Exception: The combination of compression codecs Gorilla(4), LZ4 is meaningless, because it does not make sense to apply a floating-point time series codec to non-floating-point columns (Note: you can enable setting 'allow_suspicious_codecs' to skip this check). (BAD_ARGUMENTS)

That check is a safeguard. You can override it, but you will not gain anything. For integers and timestamps use Delta or DoubleDelta.

The experiment

A table with the same data in several columns, each with a different codec: timestamps one second apart, a step-like float (it holds each value for 50 rows), and a noisy integer counter growing by 7 per row.

CREATE TABLE codec_test
(
    ts_default   DateTime,
    ts_delta     DateTime CODEC(Delta, LZ4),
    ts_dd        DateTime CODEC(DoubleDelta, LZ4),
    ts_zstd      DateTime CODEC(ZSTD(1)),
    ts_dd_zstd   DateTime CODEC(DoubleDelta, ZSTD(1)),
    v_default    Float64,
    v_gorilla    Float64 CODEC(Gorilla, LZ4),
    v_dd         Float64 CODEC(DoubleDelta, LZ4),
    v_zstd       Float64 CODEC(ZSTD(1)),
    c_default    UInt64,
    c_delta      UInt64 CODEC(Delta, LZ4),
    c_dd         UInt64 CODEC(DoubleDelta, LZ4)
)
ENGINE = MergeTree
ORDER BY ts_default
SETTINGS min_bytes_for_wide_part = 0;

INSERT INTO codec_test
SELECT
    t, t, t, t, t,
    20 + (intDiv(number, 50) % 40) / 4,
    20 + (intDiv(number, 50) % 40) / 4,
    20 + (intDiv(number, 50) % 40) / 4,
    20 + (intDiv(number, 50) % 40) / 4,
    number * 7 + rand() % 3, number * 7 + rand() % 3, number * 7 + rand() % 3
FROM (SELECT number, toDateTime('2026-01-01 00:00:00') + number AS t FROM numbers(1000000));

OPTIMIZE TABLE codec_test FINAL;

The sizes come from system.columns:

SELECT name AS column, compression_codec AS codec,
       formatReadableSize(data_compressed_bytes) AS compressed,
       formatReadableSize(data_uncompressed_bytes) AS uncompressed,
       round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio
FROM system.columns
WHERE `table` = 'codec_test' AND database = currentDatabase()
ORDER BY name;

┌─column─────┬─codec───────────────────────┬─compressed─┬─uncompressed─┬─ratio─┐
│ c_dd       │ CODEC(DoubleDelta, LZ4)     │ 727.19 KiB │ 7.63 MiB     │  10.7 │
│ c_default  │                             │ 3.84 MiB   │ 7.63 MiB     │     2 │
│ c_delta    │ CODEC(Delta(8), LZ4)        │ 2.16 MiB   │ 7.63 MiB     │   3.5 │
│ ts_dd      │ CODEC(DoubleDelta, LZ4)     │ 4.72 KiB   │ 3.81 MiB     │ 828.3 │
│ ts_dd_zstd │ CODEC(DoubleDelta, ZSTD(1)) │ 4.54 KiB   │ 3.81 MiB     │ 860.6 │
│ ts_default │                             │ 3.83 MiB   │ 3.81 MiB     │     1 │
│ ts_delta   │ CODEC(Delta(4), LZ4)        │ 19.49 KiB  │ 3.81 MiB     │ 200.4 │
│ ts_zstd    │ CODEC(ZSTD(1))              │ 2.54 MiB   │ 3.81 MiB     │   1.5 │
│ v_dd       │ CODEC(DoubleDelta, LZ4)     │ 22.15 KiB  │ 7.63 MiB     │ 352.7 │
│ v_default  │                             │ 76.19 KiB  │ 7.63 MiB     │ 102.5 │
│ v_gorilla  │ CODEC(Gorilla(8), LZ4)      │ 21.68 KiB  │ 7.63 MiB     │ 360.4 │
│ v_zstd     │ CODEC(ZSTD(1))              │ 41.47 KiB  │ 7.63 MiB     │ 188.4 │
└────────────┴─────────────────────────────┴────────────┴──────────────┴───────┘

The counter columns use rand(), so your numbers will differ a little, but the ordering will not.

Reading the results

• Regular timestamps: DoubleDelta turns a perfectly regular series into almost nothing (a ratio in the hundreds), and plain Delta gets most of the way. A general-purpose ZSTD alone only manages 1.5, because it sees no repeated byte patterns. This is the single most valuable codec choice in a time-series table.
• Noisy counters: with a little noise added, DoubleDelta (10.7) beats Delta (3.5), since the second difference stays tiny while the first difference is dominated by the growth rate. The default is 2.
• Step-like floats: Gorilla and DoubleDelta both do very well, and the default already gets 100. The extra gain is real but small in absolute bytes. The data was deliberately easy: values repeat for 50 rows. In a trial run with random noise added to every float, the picture reversed: Gorilla gave a ratio of only about 1.2, DoubleDelta made the column slightly larger than uncompressed, and the default managed no compression at all. Noisy floats are simply poorly compressible, and no codec fixes that. Rounding the values at the source does.
• ZSTD instead of LZ4 after a delta transform shaves a bit more (4.54 vs 4.72 KiB for timestamps) at some CPU cost. Do it for cold, large columns, not by default.

Practical guidance

• Timestamps in the sorting key: CODEC(DoubleDelta, ZSTD(1)) or CODEC(DoubleDelta, LZ4).
• Monotonic counters and ids: Delta for steady growth, DoubleDelta when the growth is regular.
• Floats that move slowly (temperatures, gauges, prices): Gorilla, then verify; for noisy floats the default may be as good.
• Small-range integers: T64; multiples of a common factor: GCD.
• Do not reach for these on high-cardinality strings (use LowCardinality), on random ids, or on columns that are not in sorted order.
• Newer float-oriented codecs (FPC, ALP) exist in current releases. Measure them on your own data with the same method.
• Always measure on your data. Change a codec on a copy (CREATE TABLE ... AS, INSERT SELECT), compare data_compressed_bytes, and also time your queries: a transform codec costs a little CPU on read.

To change the codec of an existing column, use ALTER TABLE t MODIFY COLUMN c DateTime CODEC(DoubleDelta, LZ4). The change applies to newly written parts; existing parts are rewritten with the new codec as they are merged, and OPTIMIZE TABLE ... FINAL forces that.