ClickHouse MergeTree, measured: parts, granules and what ORDER BY does to 10 million rows

If you understand one thing about ClickHouse, make it MergeTree. Almost every table you create uses it, and the way it stores rows explains most of ClickHouse’s speed and most of its surprises: why a query reads 8,192 rows when you asked for one, why ORDER BY matters more than any index, and why thousands of tiny inserts can bring a server to its knees.

This is day 1 of a daily ClickHouse series. Instead of repeating the docs, every post runs its own experiment. Today: the same 10 million rows stored under three different sorting keys, measured for disk size and rows read. Everything below ran on ClickHouse 26.10, and the SQL is included so you can reproduce it.

What MergeTree is

MergeTree is ClickHouse’s main table engine family. The name describes how it works:

• Every INSERT writes a new, immutable chunk of data called a part
• Rows inside a part are sorted by the table’s ORDER BY key
• A background process keeps merging small parts into bigger ones

Here is the table we will use. It stores page views for 100 websites:

CREATE TABLE page_views
(
    site_id     UInt32,
    event_time  DateTime,
    user_id     UInt64,
    url         String,
    duration_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (site_id, event_time);

Parts: every INSERT writes a new directory

After loading 10 million rows in three INSERT statements, system.parts shows what landed on disk:

SELECT partition, name, level, rows, part_type
FROM system.parts
WHERE table = 'page_views' AND active
ORDER BY name;

partition  name            level  rows     part_type
202610     202610_1_1_0    0      664141   Wide
202610     202610_2_2_0    0      1111953  Wide
...
202611     202611_9_9_0    0      1111953  Wide
202611     202611_10_10_0  0      888047   Wide

Three inserts produced ten parts, because ClickHouse cuts large inserts into blocks of about a million rows and writes each block as its own part. The name follows a fixed pattern: partition_minBlock_maxBlock_level. 202610_1_1_0 is partition October 2026, insert block 1 to block 1, merged zero times.

Each part is a directory with one compressed file per column, so a query that touches two columns reads only two files. Small parts are the exception. A part under min_bytes_for_wide_part (10 MiB by default) is written in Compact format, with all columns in a single file, because thousands of tiny files would cost more than they save. A two-row insert shows it:

name            level  rows  part_type
202611_11_11_0  0      2     Compact

Merges keep the part count down

Parts are never modified. Instead, ClickHouse merges them in the background into larger, still sorted parts. After a merge the same table looks like this:

partition  name           level  rows     part_type  marks
202610     202610_1_8_2   2      8000000  Wide       978
202611     202611_9_11_1  1      2000002  Wide       246

202610_1_8_2 covers blocks 1 to 8 and has been through two rounds of merging. A few rules worth knowing:

• Parts from different partitions are never merged together
• Merges stop growing a part at about 150 GiB (max_bytes_to_merge_at_max_space_in_pool)
• Old parts are kept for 480 seconds after a merge (old_parts_lifetime), then deleted
• If one partition reaches 1,000 active parts, inserts are slowed down; at 3,000, they fail with a “too many parts” error

That last rule is why ClickHouse wants batched inserts. Send thousands of rows per INSERT, not one row at a time. If your clients cannot batch, turn on async_insert and let the server do the batching.

Granules: the unit ClickHouse reads

Inside each part, rows are grouped into granules. A granule holds up to 8,192 rows (index_granularity) or about 10 MiB of data (index_granularity_bytes), whichever limit is reached first. That byte limit keeps granules sensible when rows are wide.

The granule is the smallest amount of data ClickHouse reads. Ask for one row and it still reads the whole granule that contains it. That sounds wasteful, but reading 8,192 values from a compressed column is very fast, and it lets the index stay tiny.

The marks column above counts them: 8 million rows ÷ 8,192 is 977 granules, plus one final mark, gives the 978 marks you see.

The sparse primary index

A B-tree index in PostgreSQL or MySQL has one entry per row. ClickHouse’s primary index has one entry per granule: the ORDER BY key of the granule’s first row. That makes it small enough to keep entirely in memory:

SELECT name, rows, formatReadableSize(primary_key_bytes_in_memory) AS pk_in_memory
FROM system.parts
WHERE table = 'page_views' AND active;

name           rows     pk_in_memory
202610_1_8_2   8000000  7.64 KiB
202611_9_11_1  2000002  1.92 KiB

Under 10 KiB of index for 10 million rows. On disk, a recent wide part contains:

• primary.cidx: the compressed primary index
• <column>.bin: the compressed column data
• <column>.cmrk2: marks, which map each granule number to its position inside the .bin file

A query uses them in two steps. First it searches the index for the granules whose key range can match the WHERE clause. Then it uses the marks to jump straight to those granules in each column file, and reads only those.

Seeing it work with EXPLAIN

EXPLAIN indexes = 1 shows how many granules survive the index:

EXPLAIN indexes = 1
SELECT sum(duration_ms) FROM page_views WHERE site_id = 42;

PrimaryKey
  Keys:
    site_id
  Condition: (site_id in [42, 42])
  Parts: 2/2
  Granules: 13/1222

Running four different filters against the same 10 million rows, and reading read_rows from system.query_log, gives this:

Filter	Granules kept	Rows read
site_id = 42	13 of 1,222	106,496
site_id = 42 and a one-hour event_time range	1 of 977	8,192
One-hour event_time range only	243 of 977	1,990,656
user_id = 123456	1,222 of 1,222	10,000,002

Each row tells you something:

• First key column: ClickHouse binary-searches the index and keeps 1% of the table.
• Full key prefix: one granule. This is the best case, and 13 × 8,192 = 106,496 shows that reads really happen in whole granules.
• Second key column only: the index still helps, because site_id has just 100 distinct values. Inside each site_id the rows are sorted by time, so ClickHouse can skip granules whose time range cannot match. With a high-cardinality first column this trick stops working, and the query becomes a full scan.
• Column outside the key: no help at all. All 10 million rows are read.

Partitioning played a part too. The time filters were limited to October, so the November part was skipped before the primary index was even consulted (“Parts: 1/2” in the EXPLAIN output).

Experiment: one dataset, three sorting keys

Everything so far used one table. To see how much the sorting key matters, I loaded the same 10 million rows into three tables that differ only in ORDER BY:

CREATE TABLE f_site_time (site_id UInt32, event_time DateTime, user_id UInt64, url String, duration_ms UInt32)
ENGINE = MergeTree ORDER BY (site_id, event_time)
SETTINGS default_compression_codec = 'LZ4';

CREATE TABLE f_time_site AS f_site_time ENGINE = MergeTree ORDER BY (event_time, site_id)
SETTINGS default_compression_codec = 'LZ4';

CREATE TABLE f_user_time AS f_site_time ENGINE = MergeTree ORDER BY (user_id, event_time)
SETTINGS default_compression_codec = 'LZ4';

The data has 100 sites, one row per site per second, 500,000 users, 5,000 URLs and a random duration_ms. I pinned the codec to LZ4 on purpose: ClickHouse 26.10 can pick a different codec for different parts, which would muddy a size comparison. That behaviour gets its own post later in this series.

Size on disk

Column	(site_id, event_time)	(event_time, site_id)	(user_id, event_time)
site_id	176 KiB	412 KiB	28.2 MiB
event_time	38.3 MiB	609 KiB	37.8 MiB
user_id	42.3 MiB	42.3 MiB	3.9 MiB
url	801 KiB	12.3 MiB	45.4 MiB
duration_ms	35.9 MiB	35.9 MiB	35.9 MiB
Total	117.4 MiB	91.5 MiB	151.2 MiB

Same rows, same codec, and the biggest table is 65% larger than the smallest. The pattern is simple: a column compresses well when equal or similar values end up next to each other. Whatever comes first in ORDER BY becomes long runs of identical values, and the columns that follow it inherit some of that order. duration_ms is random, so no sort order helps it, which makes it a useful control: it is 35.9 MiB in every table.

Two details are worth noticing. In the (site_id, event_time) table, url shrinks to 801 KiB because in this dataset each site visits its URLs in a repeating pattern, and sorting by site lines those patterns up. And event_time is 38 MiB there even though it is sorted inside each site: plain LZ4 does not exploit “each value is one more than the last”. A Delta codec would, which is a topic for the codecs post.

Rows read

Each table answered the same three queries (SELECT sum(duration_ms) ... WHERE ...). Rows read, from system.query_log:

Filter	(site_id, event_time)	(event_time, site_id)	(user_id, event_time)
site_id = 42	106,496	10,000,000	10,000,000
One hour of event_time	1,982,464	368,640	10,000,000
user_id = 123456	10,000,000	10,000,000	8,192

Every key is excellent for one query shape and a full scan for at least one other. The middle column holds the least obvious result: with event_time first, site_id = 42 reads every row. The first column has a different value in almost every granule, so knowing the site tells ClickHouse nothing about which granules to skip. Swap the order to (site_id, event_time) and the time filter still prunes 80% of the table, because only 100 sites sit in front of it.

How to choose ORDER BY

The experiment turns into a short set of rules:

• Start from your queries. The column in most WHERE clauses goes first
• Put low-cardinality columns before high-cardinality ones. Filters on later columns can then still skip granules, and compression improves
• A high-cardinality first column (a timestamp, a user id) makes every other key column nearly useless for filtering
• Keep it short. Three or four columns is plenty
• The key does not enforce uniqueness. Duplicates are allowed
• If you set PRIMARY KEY separately, it must be a prefix of ORDER BY

Partitioning is a different tool. Use it to drop or move old data cheaply, usually by month. Partitioning by day or by customer creates too many small parts, and the official guidance is that you rarely need anything finer than a month.

No single key serves every query. When you need a second access path, like user_id on a table sorted by site, there are better options than a second copy of the table: data skipping indexes, projections and materialized views, each with its own cost. Those are coming up in this series, measured the same way.

Checklist

• Batch your inserts, or turn on async_insert
• Pick ORDER BY from your most common filters, low cardinality first
• Measure: the same data can differ by 65% on disk depending on sort order
• Check EXPLAIN indexes = 1 for any slow query: look at the “Granules” line
• Watch active parts per partition in system.parts
• Partition by month at most, and only if you need to manage data by time

FAQ

What is a granule in ClickHouse?

A block of up to 8,192 rows (or about 10 MiB) inside a part. It is the smallest unit ClickHouse reads, and the primary index has one entry per granule.

Is the ClickHouse primary key unique?

No. It defines sort order and drives the sparse index, but it does not reject duplicate rows. For deduplication, look at ReplacingMergeTree.

Why does my query read more rows than it returns?

Because ClickHouse reads whole granules. A filter that matches one row still reads the 8,192-row granule around it, which is cheap in a compressed column store.

What causes the “too many parts” error?

Too many small inserts into one partition. By default inserts slow down at 1,000 active parts per partition and fail at 3,000. Batch your inserts, use async_insert, and avoid very fine-grained partitions.