How ClickHouse Finds and Reads Data (MergeTree Lookup Process)

Example table

CREATE TABLE shop.orders
(
    order_date  Date,
    country     LowCardinality(String),
    category    String,
    customer_id LowCardinality(String),
    amount_AVG  Nullable(Float64),
    amount_P95  Nullable(Float64),
    imported_at DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (customer_id, country, category, order_date)
SETTINGS index_granularity = 8192;

Example query we'll trace through the whole document:

SELECT customer_id, avg(amount_AVG)
FROM shop.orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01'
  AND customer_id = 'cust_00042'
  AND country = 'IN'
GROUP BY customer_id;

1. The building blocks: partitions, parts, and granules

• Partition — a logical bucket defined by PARTITION BY. Here, one per calendar month (202601, 202602, ...). Partitions are physically separate directories; merges never cross partition boundaries.
• Part — every INSERT creates a new part: a self-contained mini-table with its own set of files, living inside a partition's directory. A background merge process periodically combines parts in the same partition into fewer, larger ones.
• Granule — the unit the sparse index operates on. Every index_granularity rows (8192 here) form one granule. Granules are the smallest chunk ClickHouse can address without scanning row-by-row.

A part directory name like 202601_1_1_0 encodes partition, min/max block numbers, and mutation version.

2. Step 1 — Partition pruning

Before opening any index or column file, ClickHouse checks the query's predicates against each part's partition value (partition.dat) and, for range predicates, a minmax_order_date.idx file storing the min/max order_date in that part.

For our query (order_date in January 2026), only parts under partition 202601 survive. Parts for 202602, 202603, etc. are never opened at all — this is metadata-only elimination, cheapest possible pruning.

3. Step 2 — The primary index (primary.idx)

Within a surviving part, primary.idx is a sparse index: one entry per granule (not per row), storing the primary key column values (customer_id, country, category, order_date) found at the first row of that granule. It's small enough to be fully loaded into memory when the part is opened.

Conceptually, for one part:

Rows are physically sorted by (customer_id, country, category, order_date) — exactly the ORDER BY clause — so this table is monotonic column-by-column from left to right within each preceding column's group.

How the query uses it:

1. Binary search the customer_id column → cust_00042 spans marks 0–3.
2. Within that range, binary search country → IN narrows to mark 0–1 (rows 0–16383).

Only marks 0 and 1 are selected for reading. Everything else in the part — cust_00042/UK, cust_00099/* — is skipped without ever being decompressed.

Why this only works because customer_id and country are the first two key columns: if the query had filtered only on category (the 3rd key column) without customer_id/country, the values wouldn't be globally sorted — binary search breaks down and ClickHouse falls back to scanning every granule's key values sequentially, or just reading everything in the surviving partitions.

4. Step 3 — Translating granules to bytes (.mrk2 files)

primary.idx only knows about the four key columns. It has no idea where amount_AVG data physically lives in its .bin file. Every column — key or not — has its own independent mark file (column.mrk2) that maps granule number → byte offset.

Each .mrk2 entry has three fields:

Two offsets are needed because ClickHouse compresses data in blocks (commonly tens of KB to ~1MB), and a compressed block doesn't necessarily align with granule boundaries — one block can span multiple granules.

Example amount_AVG.mrk2 content:

Our query needs marks 0 and 1, so it looks up rows 0 and 1 in this table:

• Mark 0 → seek to byte 0 in amount_AVG.bin, decompressed offset 0.
• Mark 1 → seek to byte 65,412 in amount_AVG.bin, decompressed offset 0.

The same lookup happens independently in customer_id.mrk2 (to fetch the matching customer_id values for the GROUP BY) and in amount_AVG.null.dat / .null.mrk2 (the Nullable bitmap, needed to know which rows to exclude from the average). Each column's marks may point to completely different byte offsets, since compression ratios differ per-column.

5. Step 4 — Reading and decompressing

For each needed column and each selected mark:

1. fseek to the compressed offset in column.bin.
2. Read the compressed block header (stores compressed size + uncompressed size), so ClickHouse knows exactly how many bytes to pull off disk.
3. Decompress the block (LZ4 by default, or whatever codec was set).
4. Skip forward by the decompressed offset to land exactly on the granule's first row.
5. Read rows (8192) values contiguously from there.

At this point ClickHouse has, in memory, 8192-row chunks of amount_AVG, customer_id, and the null bitmap for marks 0 and 1 — aligned by row position. There's no explicit row ID anywhere; alignment is purely positional (row N of amount_AVG.bin's granule corresponds to row N of customer_id.bin's granule).

6. Step 5 — Residual filtering and aggregation

Any predicate that isn't resolvable by granule pruning is applied row-by-row (vectorized, in batches) after decompression. For example, if the query also filtered order_date > '2026-01-15', that's a range within the selected granules, not a granule-boundary condition — so ClickHouse checks it against the decompressed order_date values directly.

Finally, the engine runs the aggregation — avg(amount_AVG) — over the surviving rows, using vectorized (SIMD-batched) execution rather than a row-at-a-time loop, and produces the final grouped result.

7. Full path, end to end

WHERE order_date >= ... AND customer_id = 'cust_00042' AND country = 'IN'
   │
   ▼
1. partition.dat / minmax_order_date.idx → keep only 202601_*.parts, drop the rest
   │
   ▼
2. primary.idx (in-memory, sparse)       → binary search customer_id, then country
                                             → marks {0, 1} selected
   │
   ▼
3. column.mrk2 (per needed column)       → mark 0, 1 → (compressed_offset, decompressed_offset)
   │
   ▼
4. column.bin                            → seek, decompress block, skip to offset
   │
   ▼
5. In-memory row batch                   → apply any residual filters
   │
   ▼
6. Vectorized aggregation                → avg(amount_AVG) GROUP BY customer_id
   │
   ▼
Result set returned to client

8. Key takeaways

• primary.idx prunes granules; .mrk2 files locate bytes. They're two separate mechanisms — the first answers "which 8192-row chunks might match," the second answers "where do those chunks physically live in this specific column's file."
• Every column is independent. A query touching 2 of 20 columns opens roughly 2 columns' worth of .bin/.mrk2 files, not all 20 — this is the core payoff of the columnar layout.
• Pruning power depends entirely on ORDER BY order. Filters on columns later in the key (or not in the key at all, like category here) get progressively less benefit from primary.idx, since sort order only holds within the preceding key columns' groups. This is why choosing key column order to match actual query filter patterns matters as much as choosing the columns themselves.
• Everything is granule-grained, never row-grained. ClickHouse never indexes individual rows — it trades a small amount of over-reading (up to one extra granule's worth of "unnecessary" rows per boundary) for indexes small enough to always live in memory, even at billions of rows per part.