Why ClickHouse Is Fast: Understanding MergeTree Internals

>GLB_ // tools · ClickHouse

ClickHouse benchmarks get shared a lot. What gets shared less is why a single node can aggregate billions of rows in a couple of seconds. The short answer: it doesn’t read most of them. The long answer is the MergeTree engine, and that’s what this post walks through, with queries you can run locally.

TL;DR

Data is stored by column, compressed, in immutable parts.
Rows inside a part are sorted by ORDER BY.
Every 8192 rows form a granule; the primary index stores one entry per granule, not per row.
Marks map each granule to a byte offset inside the compressed column files.
Filters on the sorting key let ClickHouse skip granules before reading a single value.
Background merges keep the number of parts under control.

Setup

docker run -d --name ch \
  --ulimit nofile=262144:262144 \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server

docker exec -it ch clickhouse-client

Create a table and load 10M synthetic events:

CREATE TABLE events
(
    event_date  Date,
    user_id     UInt64,
    event_type  LowCardinality(String),
    duration_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);

INSERT INTO events
SELECT
    toDate('2026-01-01') + (number % 180)      AS event_date,
    number % 100000                            AS user_id,
    ['view', 'click', 'purchase'][number % 3 + 1] AS event_type,
    rand() % 5000                              AS duration_ms
FROM numbers(10000000);

1. Columnar storage

In a row store, reading one column means reading every row. In ClickHouse, each column lives in its own file. SELECT avg(duration_ms) reads duration_ms and nothing else.

Columns of a single type also compress far better than mixed rows. The default codec is LZ4; you can switch per column (ZSTD, Delta, DoubleDelta, Gorilla, T64):

ALTER TABLE events MODIFY COLUMN duration_ms UInt32 CODEC(T64, ZSTD(1));

Check what you’re actually storing:

SELECT
    column,
    formatReadableSize(sum(column_data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS uncompressed
FROM system.parts_columns
WHERE table = 'events' AND active
GROUP BY column;

LowCardinality(String) is dictionary encoding: event_type is stored as small integers plus a dictionary, which is why it barely shows up in that output.

2. Parts: every INSERT is a new immutable directory

Each INSERT writes a new part: a directory with the data for that batch, already sorted by ORDER BY. Parts are never modified in place.

SELECT name, partition, rows, level, formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE table = 'events' AND active
ORDER BY name;

Part names follow <partition>_<min_block>_<max_block>_<level>, e.g. 202601_1_1_0. level increases each time the part comes out of a merge.

Inside a part you’ll find (wide format):

202601_1_1_0/
├── user_id.bin        # compressed column data
├── user_id.cmrk2      # marks (granule -> offset)
├── duration_ms.bin
├── duration_ms.cmrk2
├── primary.cidx       # sparse primary index
├── minmax_event_date.idx  # min/max of partition key
├── partition.dat
├── count.txt
└── columns.txt

Small parts use the compact format (all columns in one data.bin) until they cross min_bytes_for_wide_part / min_rows_for_wide_part. Exact file extensions vary with version and compression settings; the structure doesn’t.

You can see the real files from the container:

docker exec ch ls /var/lib/clickhouse/store/

3. Granules and the sparse primary index

This is the core idea.

A traditional B-tree index has one entry per row. ClickHouse’s primary index has one entry per granule, a block of index_granularity rows (8192 by default, adaptive by bytes via index_granularity_bytes).

For 10M rows that’s ~1,200 index entries. The whole index fits in memory trivially.

primary index (one row per granule)
granule 0  -> (user_id=0,     event_date=2026-01-01)
granule 1  -> (user_id=81,    event_date=...)
granule 2  -> (user_id=163,   event_date=...)
...

When you filter on user_id = 42, ClickHouse binary-searches the index, finds the granule range that could contain 42, and reads only those granules. The index can’t tell you the exact row, only “it’s somewhere in here”. That’s the trade-off: a tiny index in exchange for reading up to 8192 extra rows.

See it with EXPLAIN indexes = 1:

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 42;

Output (abbreviated; exact counts depend on how many parts exist at that moment):

ReadFromMergeTree (default.events)
Indexes:
  MinMax
    Condition: true
  Partition
    Condition: true
  PrimaryKey
    Keys:
      user_id
    Condition: (user_id in [42, 42])
    Parts: 6/6
    Granules: 6/1226

A handful of granules out of ~1,200. That’s the “speed”.

Now filter on a column that’s not a prefix of the sorting key:

EXPLAIN indexes = 1
SELECT count() FROM events WHERE duration_ms > 4990;

Granules will be the full count. Full scan. Same engine, same hardware, completely different cost. ORDER BY is the most important decision you make in a ClickHouse table.

Rules of thumb:

Put the columns you filter on most at the start of ORDER BY.
Order them from lower to higher cardinality when they’re filtered together; it improves both pruning and compression.
PRIMARY KEY defaults to ORDER BY. If you set it explicitly, it must be a prefix of ORDER BY.

4. Marks: from granule to bytes

The index tells ClickHouse which granules. The marks file tells it where they are.

For each column and each granule, a mark stores two offsets:

Offset of the compressed block inside column.bin.
Offset of the granule inside the decompressed block.

So reading granule 1,103 of duration_ms is: seek → decompress one block → slice. No scanning from the start of the file. This is also why each column needs its own marks: compressed sizes differ per column.

5. Partition pruning (and why it’s not the main lever)

PARTITION BY toYYYYMM(event_date) gives each part a minmax index over the partition key. Queries filtering on event_date can drop entire parts before touching the primary index.

EXPLAIN indexes = 1
SELECT count() FROM events WHERE event_date = '2026-03-15';

Look at Parts: 1/6 under MinMax / Partition.

Partitioning is primarily a data management tool (TTL, DROP PARTITION, MOVE PARTITION), not a query optimization one. Too many partitions means too many small parts, which hurts more than it helps. Monthly is a sane default for event data.

6. Merges: keeping part count under control

Many inserts → many parts → many files to open per query. Background merges combine parts of the same partition into larger ones, re-sorting by ORDER BY (cheap, since inputs are already sorted: it’s a merge sort).

SELECT event_type, merge_reason, part_name, rows, duration_ms
FROM system.part_log
WHERE table = 'events'
ORDER BY event_time DESC
LIMIT 10;

system.part_log must be enabled in server config; it is on by default in recent images.

This explains the classic error:

Too many parts (300). Merges are processing significantly slower than inserts

Cause: thousands of tiny inserts. Fixes:

Batch on the client side (thousands to hundreds of thousands of rows per insert).
Or let the server batch: SET async_insert = 1, wait_for_async_insert = 1.

Merges are also where specialized engines do their work: ReplacingMergeTree dedupes, SummingMergeTree / AggregatingMergeTree pre-aggregate. More on that in the next post.

7. Skipping indexes for non-key columns

When you need to filter on something that isn’t in ORDER BY, data skipping indexes store a summary per N granules:

ALTER TABLE events
    ADD INDEX idx_duration duration_ms TYPE minmax GRANULARITY 4;

ALTER TABLE events MATERIALIZE INDEX idx_duration;

Types: minmax, set(N), bloom_filter, tokenbf_v1, ngrambf_v1. They only help when values are correlated with the sort order or rare enough that most blocks can be ruled out. On random data, a skip index costs CPU and saves nothing. Verify with EXPLAIN indexes = 1 before keeping one.

8. The execution side: PREWHERE and vectorization

Two more things make the reads that do happen cheap:

PREWHERE: ClickHouse reads the filter columns first, evaluates the condition, and only then reads the remaining columns for matching rows. It moves WHERE conditions to PREWHERE automatically (optimize_move_to_prewhere = 1).
Vectorized execution: operators process columns in blocks (arrays), not row by row, which plays well with CPU caches and SIMD.

Putting it together

For SELECT avg(duration_ms) FROM events WHERE user_id = 42:

1. Partition minmax  -> which parts can match?
2. Primary index     -> which granules inside those parts?
3. Marks             -> byte offsets of those granules per column
4. PREWHERE          -> read user_id granules, filter
5. Read duration_ms  -> only for surviving rows
6. Vectorized avg()  -> over a handful of blocks

Every step is about not reading data. Fast storage helps, but the design goal is to make disk I/O proportional to the answer, not to the table.

Checklist

ORDER BY matches your most common filters (prefix order matters).
Low-cardinality strings use LowCardinality(String).
Partition by month (or coarser), not by day per tenant.
Inserts are batched or use async_insert.
Every “slow query” investigation starts with EXPLAIN indexes = 1.