2. Columns and compression

Chapter 1 said each column lives in its own file, compressed. This chapter is what that actually means, because the details determine how much disk you use and how fast your queries run — and because two of the settings here can halve your storage with one line of DDL.

The files

Create a table and look at the disk:

CREATE TABLE page_views (
    ts       DateTime,
    user_id  UInt64,
    country  LowCardinality(String),
    url      String,
    duration UInt32
) ENGINE = MergeTree
ORDER BY (country, ts);

/var/lib/clickhouse/data/default/page_views/
└── all_1_1_0/                        ← a "data part" (chapter 3)
    ├── ts.bin              compressed data
    ├── ts.cmrk2            marks — where each granule starts
    ├── user_id.bin
    ├── user_id.cmrk2
    ├── country.bin
    ├── country.cmrk2
    ├── country.dict.bin    the dictionary for LowCardinality
    ├── url.bin
    ├── url.cmrk2
    ├── duration.bin
    ├── duration.cmrk2
    ├── primary.cidx        the sparse primary index (chapter 3)
    ├── columns.txt         the schema of this part
    ├── count.txt           how many rows
    └── checksums.txt

Two files per column. The .bin holds compressed data; the .cmrk2 holds marks, which are the offsets telling ClickHouse where each granule begins inside the compressed stream. Without marks you would have to decompress from the beginning to reach row five million; with them you jump straight to the right block.

(Older versions used separate .mrk/.mrk2 files, and very wide tables can use a compact format that packs all columns into one data.bin. The principle is unchanged.)

How a column becomes bytes

Take the duration column: a stream of UInt32 values.

   1. VALUES              1420, 890, 2100, 340, 1180, …
                                │
   2. SERIALISE           4 bytes each, little-endian, contiguous
                                │
   3. CODEC (optional)    Delta, DoubleDelta, Gorilla, T64 …
                          transform the numbers to make them
                          more compressible
                                │
   4. COMPRESS            LZ4 (default) or ZSTD, in blocks of
                          ~64 KB to 1 MB
                                │
   5. WRITE               append to duration.bin
                          record the offset in duration.cmrk2

Step 3 is the interesting one and it is what distinguishes ClickHouse from “a database with compression turned on.”

Codecs — transforming before compressing

A general-purpose compressor like LZ4 finds repeated byte sequences. It is very good at that and completely blind to the fact that your data is a sequence of numbers with structure.

Consider a timestamp column, sorted ascending:

   1755730000, 1755730001, 1755730003, 1755730004, 1755730007, …

As bytes, consecutive values differ only in the last byte or two — but LZ4 sees 4-byte patterns that never quite repeat. Compression is mediocre.

Now apply Delta first: store the differences.

   1755730000, 1, 2, 1, 3, 1, 1, 2, 1, …

Suddenly almost every value is a small integer, and LZ4 compresses that magnificently. The same column can go from 3× to 40×.

The codecs worth knowing:

Codec	Transforms	Best for
`Delta(n)`	value → difference from previous	timestamps, IDs, any monotonic series
`DoubleDelta`	difference of differences	timestamps at a regular interval — near-free
`Gorilla`	XOR with previous, keep changed bits	floating-point metrics that change slowly
`T64`	transposes a 64-value block, strips unused high bits	integers with a limited actual range
`FPC`	float-specific predictor	some float workloads
`ZSTD(level)`	general compression	anything; level 1–22, default 1
`LZ4`	general compression	the default — fast
`LZ4HC(level)`	LZ4, harder	better ratio, same fast decompression
`NONE`		already-compressed blobs

Codecs chain, transform first and compressor last:

CREATE TABLE metrics (
    ts     DateTime  CODEC(DoubleDelta, ZSTD(1)),
    host   LowCardinality(String),
    cpu    Float32   CODEC(Gorilla, ZSTD(1)),
    memory UInt64    CODEC(T64, ZSTD(1)),
    req_id UUID      CODEC(NONE)          -- random, do not waste CPU
) ENGINE = MergeTree
ORDER BY (host, ts);

That table is a good template for time-series data, and the difference against no codecs is routinely 3–5× on top of ordinary compression.

LZ4 or ZSTD?

The default is LZ4: very fast to compress and extremely fast to decompress (gigabytes per second per core), with a moderate ratio.

ZSTD compresses noticeably better — commonly 30–50% smaller — at some cost in compression speed. Decompression is still fast, though slower than LZ4.

The rule of thumb:

Hot data queried constantly, on fast local NVMe: LZ4. You are CPU-bound on decompression and disk is cheap relative to the query rate.
Large or cold data, or anything on network/object storage: ZSTD. You are I/O-bound, so fewer bytes wins easily.
Archival: ZSTD(3) or higher. Beyond about level 3 you pay a lot of CPU for a little ratio; measure before going further.

Set it per column, or globally:

ALTER TABLE page_views MODIFY COLUMN url String CODEC(ZSTD(3));

Existing parts keep their old codec until they are merged or you run OPTIMIZE.

Why sorting is the whole game

Here is the point that ties this chapter to chapter 5.

Compression works because similar values are adjacent. What decides adjacency? The ORDER BY clause of the table.

Sort by (country, ts) and the country column becomes long runs of identical values: 40 million "UK" in a row, then 30 million "FR". That compresses to almost nothing.

Sort by (user_id, ts) instead and country becomes effectively random — a different country every few rows. The same data, the same codec, and five to twenty times more disk.

   ORDER BY (country, ts)          ORDER BY (user_id, ts)

   country column:                 country column:
   UK UK UK UK UK UK UK UK …       UK FR US UK DE UK JP FR …
   ▲ compresses ~200×              ▲ compresses ~3×

This is why chapter 5 insists the ordering key is the most important decision in ClickHouse. It determines what you can skip when reading (chapter 3) and how well everything compresses.

`LowCardinality` — the one-line win

If a String column has few distinct values relative to its row count, wrap it:

country LowCardinality(String)

ClickHouse stores a dictionary of the distinct values and replaces each row with a small integer index into it.

   PLAIN String                    LowCardinality(String)

   "United Kingdom"                dictionary: 0→"United Kingdom"
   "France"                                    1→"France"
   "United Kingdom"                            2→"Germany"
   "Germany"
   "United Kingdom"                data: 0, 1, 0, 2, 0
                                   (stored as UInt8 here)

The gains are not only storage. Comparisons operate on integers rather than strings, GROUP BY uses integer hashing, and the dictionary usually stays in cache. Queries filtering or grouping on the column get materially faster, often 2–5×.

Use it when the column has fewer than roughly 10,000 distinct values — country, status, hostname, event type, log level, currency, device model. That threshold is soft; the real test is whether the dictionary stays small relative to the data.

Do not use it for high-cardinality columns. A LowCardinality(String) on user emails builds an enormous dictionary that must be maintained per part, and it is slower and larger than a plain String. ClickHouse will warn you above a threshold, and the setting allow_suspicious_low_cardinality_types exists to prevent the obvious mistakes.

It works with numbers too — LowCardinality(UInt32) for a status code — though the win is smaller.

Seeing your own compression

The single most useful system-table query in this chapter:

SELECT
    name,
    type,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
FROM system.columns
WHERE table = 'page_views' AND database = 'default'
GROUP BY name, type
ORDER BY sum(data_compressed_bytes) DESC;

   ┌─name─────┬─type──────────────────┬─compressed─┬─uncompressed─┬─ratio─┐
   │ url      │ String                │ 41.20 GiB  │ 180.11 GiB   │   4.4 │
   │ user_id  │ UInt64                │  8.90 GiB  │  62.00 GiB   │   7.0 │
   │ ts       │ DateTime              │  0.41 GiB  │  31.00 GiB   │  75.6 │
   │ duration │ UInt32                │  2.10 GiB  │  31.00 GiB   │  14.8 │
   │ country  │ LowCardinality(String)│  0.09 GiB  │  46.50 GiB   │ 516.7 │
   └──────────┴───────────────────────┴────────────┴──────────────┴───────┘

Read that table top-down. The largest compressed value is where your storage budget goes, and where a better codec pays. Here it is url — a high-cardinality string. Options: ZSTD(3), extracting the domain into a LowCardinality column, or storing a hash if you only ever group by it.

A ratio near 1.0 tells you the column is already-compressed or random data; leave it alone with CODEC(NONE) and stop spending CPU on it.

Per-part detail, when you want to see whether newly written parts are behaving differently:

SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size, sum(rows)
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

Block sizes, briefly

Compression happens in blocks. Two settings control them:

min_compress_block_size (default 65,536 bytes)
max_compress_block_size (default 1,048,576 bytes)

Bigger blocks compress better and force more decompression work to read one small range. Smaller blocks are the reverse. The defaults are good. Change them only with a measured reason — this is a place where tuning usually makes things worse.

What goes wrong

No LowCardinality on obvious columns. Storage several times larger than necessary, and slower GROUP BY. The most common missed opportunity.

LowCardinality on a high-cardinality column. The opposite mistake, and it hurts: a huge dictionary per part, worse compression, slower queries.

No codecs on time-series columns. Timestamps without Delta/DoubleDelta are the second most common waste.

An ordering key that destroys compression. Sorting by a random high-cardinality column first — a UUID, a random request id — makes every other column incompressible. Chapter 5.

Nullable everywhere. Every Nullable column carries a second file of null markers and blocks some optimisations. Chapter 8 covers this; the short version is that a sentinel value (0, '') is usually better than Nullable in ClickHouse.

Storing already-compressed blobs. Gzipped JSON in a String column compresses no further and burns CPU trying. CODEC(NONE).

What to take from this chapter

Each column is two files: .bin (compressed data) and .cmrk2 (marks giving the offset of each granule).
The write path is serialise → codec → compress → write, and the codec step is what makes ClickHouse’s compression exceptional.
Delta/DoubleDelta for timestamps and monotonic values, Gorilla for slowly-changing floats, T64 for narrow integers, then LZ4 (fast) or ZSTD (smaller).
LowCardinality(String) for anything under ~10,000 distinct values — big storage win and faster grouping. Never for high-cardinality columns.
Sorting decides compression. The ORDER BY key changes storage size by an order of magnitude, before it affects a single query.
The system.columns compression query is the highest-value audit in this guide; the largest compressed column is where to spend effort.

Next: the structure those files live in — parts, granules, and the index that makes skipping possible.

Written by Sepuri Sai Krishna