MergeTree in ClickHouse: granules, parts, and sparse index

MergeTree in ClickHouse: How the Engine Slices Analytics into Granules and Merges Parts

Seven Years of Pain, Three Lost Productions, and One Architectural Plaque

When I first heard about MergeTree, I thought: "Another engine with a trendy name." Then, in production, a table with 500 million bets started slowing down queries that used to fly. We looked at EXPLAIN and saw Read 250000 granules. Back then, I didn't know what a granule was.

It turned out I created a table with the wrong ORDER BY. Every query scanned 80% of all data, even though it only filtered on one column.

MergeTree is not just an engine. It's an architecture that determines how your data sits on disk, how it's compressed, and most importantly — how ClickHouse decides which chunks to read and which to skip. Understanding the internals saved me three projects. Below is a map I've been following for five years.

1. Part, Granule, Mark: A Matryoshka on Disk

ClickHouse does not store a table as a single file. It splits data into parts, inside each part into granules, and navigates using marks.

Disk structure of table bets:
/var/lib/clickhouse/data/betting/bets/
├── 202401_1_1_0/          # part #1 (January 2024)
│   ├── user_id.bin        # column user_id (binary data)
│   ├── user_id.mrk        # marks for user_id
│   ├── created_at.bin
│   ├── created_at.mrk
│   ├── amount.bin
│   ├── amount.mrk
│   └── ...
├── 202401_2_2_0/          # part #2
└── 202402_3_3_0/          # part #3 (February)

Part — the smallest unit managed by MergeTree. Each part is created on insert, then merged in the background with neighboring parts.

Granule — a block of data of index_granularity rows (default 8192). ClickHouse reads data in whole granules. You cannot read a single row — only a whole granule.

Mark — a pointer to the position of a granule in the .bin file. The .mrk file contains the offset: where the granule starts on disk and its offset.

Why this matters: when you run SELECT amount FROM bets WHERE user_id = 123, ClickHouse uses the sparse index to determine which granules might contain this user_id and reads only those. It doesn't even open the other granules.

2. Merging Parts: Why ClickHouse Doesn't Break from a Million Small Inserts

Each INSERT creates a new part on disk. If you insert 100 records 10,000 times — you'll have 10,000 parts. That's a disaster: a query would have to open 10,000 files.

How ClickHouse saves the day:

The background merge process glues small parts into larger ones. For example:

• 10 parts of 1 GB each → 1 part of 10 GB

Parameters I tweak in production:

<merge_tree>
    <min_rows_for_wide_part>100000</min_rows_for_wide_part>
    <max_bytes_for_merge>100000000000</max_bytes_for_merge>  <!-- 100 GB -->
    <merge_with_ttl_timeout>3600</merge_with_ttl_timeout>
</merge_tree>

Pro tip: if you do a large insert (1M+ rows), the part won't merge with others until a neighboring part appears. ClickHouse stores parts in ascending key order, so INSERT ... ORDER BY helps.

Where I got burned: we streamed bets via Kafka at 10-50 records per second. After a week, we had 300,000 parts. Queries slowed down because each query opened all files. We fixed it by raising min_rows_for_wide_part to 500k and increasing max_insert_block_size to 1M. The data stream had to be buffered in Kafka, but parts collapsed to 500.

3. PRIMARY KEY vs ORDER BY: The Most Common Beginner Mistake

In MySQL, PRIMARY KEY is a unique identifier. In ClickHouse, not quite.

-- I see this all the time
CREATE TABLE bets (
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
) ENGINE = MergeTree()
PRIMARY KEY (user_id)      -- ← mistake
ORDER BY (user_id);        -- ← also a mistake

The truth:

• ORDER BY determines the physical order of rows on disk. Mandatory.
• PRIMARY KEY is the same as ORDER BY if not specified. But it can be a PREFIX of ORDER BY.

Correct:

ORDER BY (created_at, user_id)   -- time first, then user
PRIMARY KEY (created_at)         -- index only on time

What happens: ClickHouse builds a sparse index based on ORDER BY. PRIMARY KEY only tells which part of ORDER BY to use for filtering.

Real example from our production:

-- Wrong (slow)
ORDER BY (user_id, created_at)
-- Query: find bets from the last hour. Index doesn't help, we scan everything.

-- Correct (fast)
ORDER BY (created_at, user_id)
-- Query: jump to the required date via index, then filter by user_id within

4. Sparse Index: How 8192 Rows Become One Index Entry

ClickHouse does NOT build an index for each row. It takes a granule (8192 rows) and writes into the index:

• minimum value of ORDER BY in that granule
• maximum value of ORDER BY

That's it. Not a B-tree, not a hash table — just a simple array of min-max pairs.

How a query speeds up:

-- Find bets for 5 minutes
SELECT * FROM bets WHERE created_at BETWEEN '2024-03-15 14:00:00' AND '2024-03-15 14:05:00';

-- The (sparse) index checks each granule:
-- Granule 1: min='2024-03-15 13:00:00' max='2024-03-15 14:00:00' → NOT matching (max < 14:05?)
-- Granule 2: min='2024-03-15 14:00:00' max='2024-03-15 15:00:00' → MATCHES (min <= 14:05)
-- Granule 3: min='2024-03-15 15:00:00' max='2024-03-15 16:00:00' → NOT MATCHING (min > 14:05)

Why it's fast: the index takes (number of granules) * 16 bytes. For 1 billion rows, that's ~1.9 million granules → 30 MB of index. The entire index fits in memory.

5. Partitioning: Jump to the Right Month

PARTITION BY is the rule by which ClickHouse places parts into different directories on disk.

PARTITION BY toYYYYMM(created_at)  -- monthly partitions

On disk:

/var/lib/clickhouse/data/betting/bets/
├── 202401/   # January 2024
├── 202402/   # February 2024
└── 202403/   # March 2024

How it speeds up queries:

SELECT * FROM bets WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- ClickHouse goes straight to folder 202402/, doesn't even open other partitions

When partitioning doesn't help:

• Small partitions (by day with 100 million rows per day → 365 partitions, each 300 MB → many files)
• Filter not on the partitioning key

My choice: toYYYYMM() for 10–100 million rows per month, toYYYYMMDD() if 1+ billion per day (but then you need a cluster).

6. .bin and .mrk Format: How Data Lies on Disk

I once poked into a table directory and saw:

$ ls -la /var/lib/clickhouse/data/betting/bets/202401_1_1_0/
-rw-r----- 1 clickhouse clickhouse 1.2G  user_id.bin
-rw-r----- 1 clickhouse clickhouse  12M  user_id.mrk
-rw-r----- 1 clickhouse clickhouse 900M  created_at.bin
-rw-r----- 1 clickhouse clickhouse  12M  created_at.mrk
-rw-r----- 1 clickhouse clickhouse 2.1G  amount.bin
-rw-r----- 1 clickhouse clickhouse  12M  amount.mrk

• .bin — actual column data, compressed with LZ4 (or ZSTD if configured)
• .mrk — marks: position of each granule in .bin

How it's read:

1. Query wants column amount for user_id=123
2. Sparse index says: this user_id might be in granules #45, #46, #47
3. ClickHouse opens user_id.mrk, takes the offset for granule #45
4. Goes to user_id.bin at that offset, reads 8192 values
5. Finds rows with the desired user_id, remembers row numbers
6. Using row numbers, computes positions in amount.mrk and reads only the needed bytes from amount.bin

Conclusion: physically, data is read only for the required columns and only for the required granules. Everything else is metadata.

7. Correct ORDER BY on a Bets Table Example

Bad ORDER BY (I did this):

CREATE TABLE betting.bets_wrong
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- index by user first

Problem: 90% of queries in our project are "show bets from the last hour" (filter by time). The index doesn't help because user_id changes faster than time. ClickHouse scans all partitions.

Correct ORDER BY:

CREATE TABLE betting.bets_correct
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2),
    sport LowCardinality(String),
    outcome Enum8('win'=1,'loss'=2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);   -- index by time first

Now:

• Query by date range: instantly jumps to the right granules
• Within a date, you can filter by user_id
• Optionally, you can add a SECONDARY INDEX (but that's another story)

8. EXPLAIN indexes = 1: See How Many Granules Are Actually Read

The most useful debugging tool:

EXPLAIN indexes = 1
SELECT user_id, sum(amount)
FROM betting.bets
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
  AND user_id = 100500
GROUP BY user_id;

Output:

Expression (Projection)
  Aggregating
    Expression
      ReadFromMergeTree (betting.bets)
        Indexes:
          Partition key:   partition_idx   (1/3 partitions, 1 read)
          Primary key:     created_at      (42/500 granules, 42 read)
          MinMax:          created_at      (0 skipped, 1 read)

What we see: out of 500 granules in the partition, only 42 were read. Filtering degree — 8%. Without the correct ORDER BY, it would be 500 out of 500.

9. Commands for Managing Partitions

View all partitions:

SELECT 
    partition,
    name,
    rows,
    bytes_on_disk,
    modification_time
FROM system.parts
WHERE table = 'bets' AND active = 1;

Drop an old partition (faster than DELETE):

ALTER TABLE betting.bets DROP PARTITION '202401';

Clears disk instantly. DELETE FROM deletes row by row, then merges — difference in hours.

Detach a partition (without deleting data):

ALTER TABLE betting.bets DETACH PARTITION '202402';
-- data moved to detached/

Attach it back:

ALTER TABLE betting.bets ATTACH PARTITION '202402';

Copy a partition to another table (real-life case):

ALTER TABLE betting.bets_archive REPLACE PARTITION '202401' FROM betting.bets;

10. OPTIMIZE TABLE — When Not Needed (and When It Suddenly Is)

OPTIMIZE TABLE forces a manual merge of parts.

Bad news: most articles recommend running it periodically. Good news: in 99% of cases, you don't need to. ClickHouse merges in the background automatically.

When I actually used OPTIMIZE:

• After loading a large block of historical data (100 million rows in one insert) — so other partitions don't wait for a scheduled merge
• Before taking a backup, to reduce the number of files in the table
• Testing — to see the actual size after compression

How to do it safely:

OPTIMIZE TABLE betting.bets PARTITION '202403' FINAL;

FINAL merges all parts into one for that partition. Without FINAL — only parts that are already ready.

My advice: don't touch OPTIMIZE in automated scripts. Background merges are well-tuned. If parts aren't merging, check max_bytes_to_merge and free disk space.

What's Next

MergeTree is the heart of ClickHouse. Now you know how it beats. Next article — on advanced indexing: skip indexes, materialized columns, and projections.

---

← Previous: ClickHouse: Complete Data Type Reference for Betting Analytics (What I Got Burned On)
→ Next: Loading Data into ClickHouse: How I Stopped Inserting One Row at a Time and Accelerated Ingestion by 500x

— Editorial Team