3. MergeTree — parts, granules, and the sparse index

MergeTree is the storage engine that matters. Every serious ClickHouse table uses it or one of its variants (chapter 4). Understanding it is understanding ClickHouse.

The name says what it does: data arrives as sorted parts, and a background process merges them into bigger sorted parts. It is an LSM tree, in the same family as Cassandra’s storage engine, adapted for columns and for analytics.

The short version

Every INSERT writes a new folder on disk containing sorted, compressed columns. That folder is never modified again.

In the background, ClickHouse takes several small folders and merges them into one bigger folder, keeping everything sorted. Old folders are deleted.

Queries read all the current folders and combine the results.

That is the entire engine. The details are where the performance comes from.

Data parts

INSERT INTO page_views VALUES (...);   -- creates part all_1_1_0
INSERT INTO page_views VALUES (...);   -- creates part all_2_2_0
INSERT INTO page_views VALUES (...);   -- creates part all_3_3_0

SELECT name, rows, bytes_on_disk, level, active
FROM system.parts
WHERE table = 'page_views';

   ┌─name────────┬────rows─┬─bytes_on_disk─┬─level─┬─active─┐
   │ all_1_1_0   │ 1048576 │     8123441   │     0 │      1 │
   │ all_2_2_0   │ 1048576 │     8091233   │     0 │      1 │
   │ all_3_3_0   │ 1048576 │     8140012   │     0 │      1 │
   └─────────────┴─────────┴───────────────┴───────┴────────┘

The name decodes as {partition}_{min_block}_{max_block}_{level}:

• partition — all when there is no PARTITION BY, otherwise the partition id (chapter 5)
• min_block / max_block — the range of insert block numbers this part covers
• level — how many times it has been merged; 0 means freshly inserted

After a merge:

   │ all_1_3_1   │ 3145728 │    23980112   │     1 │      1 │   ← merged
   │ all_1_1_0   │ 1048576 │     8123441   │     0 │      0 │   ← inactive
   │ all_2_2_0   │ 1048576 │     8091233   │     0 │      0 │   ← inactive
   │ all_3_3_0   │ 1048576 │     8140012   │     0 │      0 │   ← inactive

Three level-0 parts became one level-1 part spanning blocks 1 to 3. The originals become inactive and are physically deleted after about eight minutes (old_parts_lifetime), which gives running queries time to finish reading them.

Parts are immutable. Nothing is ever modified in place. This is what makes reads lock-free — a query takes a snapshot of the active part list and reads it without coordination — and it is why updates and deletes are so awkward (chapter 6).

Merges

A background pool continuously selects parts to merge, preferring parts of similar size. It is a classic LSM compaction: small parts merge into medium, medium into large.

The merge itself is a k-way merge of sorted streams — cheap, sequential, and it produces one sorted output. Along the way it can do useful work: dropping rows that TTL has expired, collapsing duplicates in a ReplacingMergeTree, or pre-aggregating in an AggregatingMergeTree (chapter 4).

Watch merges happening:

SELECT table, elapsed, progress, num_parts, total_size_bytes_compressed, memory_usage
FROM system.merges;

And their history:

SELECT event_time, table, merge_algorithm, rows_read, rows_written,
       duration_ms, peak_memory_usage
FROM system.part_log
WHERE event_type = 'MergeParts'
ORDER BY event_time DESC LIMIT 20;

Two important properties.

Merges never fully complete. ClickHouse does not merge everything into one giant part, because merging a 500 GB part costs 500 GB of read and write for negligible benefit. max_bytes_to_merge_at_max_space_in_pool (default 150 GB) caps it. A healthy large table has tens of parts, not one.

Merges compete with queries for CPU, memory and disk bandwidth. On an overloaded server, merges falling behind is both a symptom and a cause — the part count climbs, queries get slower because they read more parts, and the whole thing spirals. Chapter 14.

You can force one:

OPTIMIZE TABLE page_views FINAL;

Do this sparingly. On a large table it rewrites everything, and it is emphatically not something to run on a schedule.

Granules — the unit of reading

Inside a part, rows are grouped into granules of 8,192 rows by default (index_granularity).

The granule is the atom of the read path. ClickHouse never reads fewer than one granule. Ask for a single row and it decompresses the block containing that row’s granule and reads 8,192 values.

   PART all_1_3_1  (3,145,728 rows = 384 granules)

   granule 0    rows      0 –  8191
   granule 1    rows   8192 – 16383
   granule 2    rows  16384 – 24575
   …
   granule 383  rows 3137536 – 3145727

The marks file (.cmrk2 from chapter 2) records, for each granule and each column, exactly where its data begins in the compressed stream. That is what makes random access possible without decompressing from the start.

There is also index_granularity_bytes (default 10 MB), which caps a granule by size — so a table with very wide rows produces smaller granules rather than enormous ones. Both limits apply; whichever hits first ends the granule.

The sparse primary index

Here is the design decision that distinguishes ClickHouse from every OLTP database in these guides.

A PostgreSQL B-tree index has one entry per row. That is what makes a point lookup fast, and it means the index for a 40-billion-row table is enormous.

ClickHouse’s primary index has one entry per granule — one per 8,192 rows. It stores the value of the ordering key at the first row of each granule.

   primary.cidx  for  ORDER BY (country, ts)

   mark 0   ("DE", 2026-01-01 00:00:00)     ← first row of granule 0
   mark 1   ("DE", 2026-01-01 04:12:33)     ← first row of granule 1
   mark 2   ("DE", 2026-01-01 09:41:02)
   …
   mark 97  ("FR", 2026-01-01 00:00:00)
   …
   mark 240 ("UK", 2026-01-01 00:00:00)
   …

The arithmetic is the point. 40 billion rows ÷ 8,192 = about 5 million marks. At a few bytes each that is tens of megabytes — small enough to be held in memory permanently, for the entire table.

That is the trade in one sentence:

A dense index makes one row fast. A sparse index makes the whole index fit in RAM, which makes scanning a range fast.

For analytics, the second is worth far more.

How the index is used

A query arrives:

SELECT count() FROM page_views
WHERE country = 'UK' AND ts >= '2026-08-01';

ClickHouse performs index analysis: a binary search over the marks to find which granules could possibly contain matching rows.

   marks:   … [FR,…] [FR,…] [UK, 07-28] [UK, 08-01] [UK, 08-04] [US,…] …
                              ▲                                   ▲
                              └── first granule that may match     │
                                  everything from here to ────────┘

   granules to read: 240–247   (8 granules = 65,536 rows)
   granules skipped: 4,882,304 out of 4,882,312

Reading 65,536 rows out of 40 billion is why the query returns in milliseconds.

Notice the granule at [UK, 07-28] is included even though its first row does not match — because rows later inside it might. The sparse index gives you a superset, and the actual filter is applied to the rows within the granules read. That is fine: 8,192 extra rows is nothing.

See the analysis:

EXPLAIN indexes = 1
SELECT count() FROM page_views WHERE country = 'UK' AND ts >= '2026-08-01';

   ReadFromMergeTree (default.page_views)
   Indexes:
     PrimaryKey
       Keys: country, ts
       Condition: and((country in ['UK','UK']), (ts in [1754006400, +Inf)))
       Parts: 3/18                       ← 3 parts of 18 could match
       Granules: 8/4882312               ← 8 granules read out of 4.9 million

Granules: 8/4882312 is the number that tells you whether your table is designed correctly. Chapter 5 is about making that ratio small.

Why the “primary key” is not a primary key

In ClickHouse:

ENGINE = MergeTree
ORDER BY (country, ts)

ORDER BY defines both the physical sort order and the primary index. There is no uniqueness, no constraint, no enforcement. Insert the same row twice, you get it twice.

You may specify PRIMARY KEY separately, and it must be a prefix of ORDER BY:

ENGINE = MergeTree
PRIMARY KEY (country)
ORDER BY (country, ts, user_id)

Why do that? The index holds only the PRIMARY KEY columns, so a shorter one uses less memory, while the data is still sorted by the full ORDER BY — which matters for compression and for the deduplicating engines in chapter 4. It is a niche optimisation for very large tables.

The word “index” means something different here

If you come from PostgreSQL or MySQL, unlearn the reflex. There is one primary index per table, it is sparse, and it is the sort order. You cannot add five indexes for five query patterns. What you can add are data skipping indexes, below, which are a weaker and different thing.

Data skipping indexes

A secondary mechanism that stores a small summary per group of granules, so whole ranges can be skipped when the summary proves nothing can match.

ALTER TABLE page_views
  ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4;

GRANULARITY 4 means one summary per 4 granules (32,768 rows).

The types:

Type	Stores	Good for
minmax	min and max per block	correlated columns — e.g. an event_date when sorted by ts
set(n)	up to n distinct values	low-cardinality columns not in the sort key
bloom_filter(p)	a Bloom filter	equality on high-cardinality values
ngrambf_v1	n-gram Bloom filter	LIKE '%substring%'
tokenbf_v1	token Bloom filter	word search in text

They only ever skip; they never seek. If the summary says “maybe,” the granules are read and filtered. A skipping index on a column with values scattered uniformly across all granules skips nothing and costs storage and write time. They pay off only when the indexed values are clustered — which usually means correlated with the sort order.

Check whether one is doing anything with EXPLAIN indexes = 1; each skipping index reports its own granule count.

Reading a query’s part and granule usage

SELECT
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) AS read,
    result_rows,
    query
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY read_rows DESC
LIMIT 10;

read_rows is the honest measure of how much work a query did. A query reading 4 billion rows to return 12 is a design problem, not a tuning problem, and chapter 5 is the fix.

What goes wrong

Too many parts. The single most common ClickHouse operational failure. Caused by frequent small inserts; merges cannot keep up; queries slow down because they read hundreds of parts; eventually TOO_MANY_PARTS rejects writes. Chapter 6.

Merges falling behind under load. Symptom of an overloaded server or too-aggressive ingestion. Watch system.merges and the part count per table.

OPTIMIZE FINAL on a schedule. It rewrites everything, competes with real work, and is almost never necessary. Merges are automatic.

Expecting an index per query pattern. There is one sort order. Additional access patterns are served by projections or materialised views (chapter 11), not by more indexes.

Skipping indexes that skip nothing. Storage and write cost for no benefit, because the values are not clustered.

Tiny index_granularity. Lowering it from 8,192 to make point lookups faster inflates the primary index and marks files, potentially out of memory. Rarely the right answer.

What to take from this chapter

• MergeTree writes immutable, sorted data parts and merges them in the background. Parts are never modified — that is what makes reads lock-free.
• The part name encodes {partition}_{minblock}_{maxblock}_{level}; system.parts shows everything.
• Merges do useful work along the way (TTL, deduplication, pre-aggregation) and deliberately never merge into one part.
• A granule is 8,192 rows and is the smallest unit ClickHouse will read.
• The primary index is sparse — one entry per granule — so it fits in RAM for tables of any size. It returns a superset of granules, which is fine.
• ORDER BY is the primary key; there is no uniqueness and only one sort order per table.
• Data skipping indexes only skip, and only help when values are clustered relative to the sort order.
• EXPLAIN indexes = 1 and its Granules: read/total line is the fastest way to know whether a table is designed for its queries.

Next: the MergeTree variants, which do real work during those merges.