Learning Objectives
- Read a part's own name to determine which partition it belongs to, which insert blocks it covers, and how many times it's been merged
- Explain what PARTITION BY does, how it's independent of ORDER BY, and why merges never combine parts across a partition boundary
- Predict, given a PARTITION BY expression and a sequence of inserts, how many partitions and initial parts a table will accumulate
- Explain why an overly fine-grained partition key is a common anti-pattern, and what problem coarse partitioning is actually meant to solve
Why This Matters
You already know, from Module 2, that every INSERT produces a part and that parts get merged together later in the background. That's enough to avoid the single-row-INSERT trap, but it's not enough to read a real table's health at a glance, or to make a good call on how to organize months or years of data. Two things fill that gap: the part's own name, which is a small, readable history of everything that's happened to it, and PARTITION BY, a second dimension of physical layout that decides which parts are even eligible to merge with each other. Get the second one wrong — and it's a genuinely common mistake — and you end up with a table full of thousands of tiny directories that no amount of background merging can fix, because the partitioning itself is what's keeping them apart.
A part's name is a small history, if you know how to read it
Query system.parts on any MergeTree table and the name column won't look like a random identifier — it's a structured string that tells you exactly what happened to that part:
SELECT partition, name, active, rows, bytes_on_disk
FROM system.parts
WHERE table = 'events'
ORDER BY name;
┌─partition─┬─name───────────┬─active─┬────rows─┬─bytes_on_disk─┐
│ 202401 │ 202401_1_1_0 │ 1 │ 28400 │ 412088 │
│ 202401 │ 202401_2_2_0 │ 0 │ 31900 │ 459331 │
│ 202401 │ 202401_3_3_0 │ 0 │ 29750 │ 427664 │
│ 202401 │ 202401_1_3_1 │ 1 │ 90050 │ 1288910 │
└───────────┴────────────────┴────────┴─────────┴───────────────┘
A part name has the shape <partition_id>_<min_block>_<max_block>_<level> (a trailing _<mutation> shows up too, once a part has been touched by a mutation — not something this lesson needs). Reading 202401_1_3_1: 202401 is the partition this part belongs to; 1 and 3 are the range of insert-block numbers it covers (a monotonically increasing counter per INSERT — unrelated to the execution "block" from the previous module's lifecycle lesson, just an unfortunate reuse of the same word); 1 is the level — how many times a merge has folded parts together to produce this one. A level of 0 means "born directly from an INSERT, never merged." A level of 1 means "the result of one merge." The three rows above with active = 0 are exactly the three level-0 parts that a background merge just combined into the new level-1 part covering blocks 1 through 3 — they're still sitting on disk for a moment, but they're no longer used by any query, and ClickHouse will delete them shortly.
That active column is the mechanic worth internalizing here: a part doesn't vanish the instant it's merged. The merge writes the new, combined part first, and only after that succeeds does it mark the inputs inactive and schedule them for cleanup. You can watch a table's whole merge history play out just by reading system.parts over time — level going up, part count going down, the same rows accounted for in fewer and fewer files.
PARTITION BY: a second dimension merges can't cross
Everything so far — parts, blocks, levels, merges — has been happening inside one partition, 202401. PARTITION BY is what defines what a partition even is: an expression, evaluated per row at insert time, that groups rows into named buckets before anything else happens. A common choice is a month derived from a timestamp column:
CREATE TABLE events
(
user_id UInt64,
event_type String,
event_time DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);
Notice PARTITION BY and ORDER BY are doing two completely different jobs on two completely different columns. ORDER BY — the subject of the next two lessons — controls how rows are sorted inside a part, which is what the sparse primary index is built on. PARTITION BY controls something upstream of that entirely: which partition a row's part gets written into in the first place, before sorting or indexing even come into it. A row's partition is decided once, at insert time, and never changes.
The one rule that makes partitions matter mechanically, not just organizationally: a merge only works on parts that share the same partition value. A part in 202401 and a part in 202402 will never be merged into each other, no matter how small either one is or how long they sit there. Partitioning isn't a hint about merge priority — it's a hard boundary the background merge process cannot cross.
INSERT INTO events VALUES (1001, 'page_view', '2024-01-15 10:22:31');
INSERT INTO events VALUES (1002, 'click', '2024-02-03 09:10:02');
┌─partition─┬─name─────────┬─active─┬─rows─┐
│ 202401 │ 202401_1_1_0 │ 1 │ 1 │
│ 202402 │ 202402_2_2_0 │ 1 │ 1 │
└───────────┴──────────────┴────────┴──────┘
Two single-row parts, sitting in a table that's supposedly fine with tiny parts merging away in the background — except these two never will merge with each other. Every future January insert can eventually merge with 202401_1_1_0. Every future February insert can eventually merge with 202402_2_2_0. The two groups are permanently separate populations.
Why that makes an operational tool out of partitioning, not a query-speed one
Because a whole partition is a self-contained, independently mergeable unit, it's also independently droppable and movable as a single operation — ALTER TABLE events DROP PARTITION 202401 removes an entire month in one metadata operation, instead of a row-by-row DELETE. That's the actual reason PARTITION BY exists day to day: cheap bulk lifecycle operations on natural, coarse buckets of data — typically for retention (drop data older than N months) or for moving cold data to cheaper storage.
Common Misconception
"I should partition by something specific, like user_id or the exact day, so ClickHouse can skip straight to the partition I need — the same way partition pruning speeds up queries in other systems I've used."
Partitioning does let ClickHouse skip whole partitions that can't match a query's filter, so there's a kernel of truth here. But treating it as the speed lever, and picking a fine-grained key to maximize how often that pruning kicks in, backfires. Every partition has its own separate, independent pool of parts — more partitions means more, smaller pools, each of which starts back at square one with tiny level-0 parts that can only ever merge with others in that same narrow pool. Push the partition key down to something as fine as a single day or a user_id, and you can end up with thousands of partitions, each permanently stuck with small, barely-merged parts — which is slower to query, not faster, because ClickHouse now has to open and coordinate across a huge number of small files and directories instead of a manageable number of well-merged ones. The query-speed work is ORDER BY's job, covered next lesson. PARTITION BY should stay coarse — months or weeks, not days or user IDs — precisely so each partition's parts have room to actually merge down over time.
Predict, Then Verify
A table is created with PARTITION BY toYYYYMM(event_time). The following inserts happen, in order, over one afternoon:
- Two separate INSERT statements, both with
event_timevalues in January. - One INSERT statement with an
event_timevalue in February. - One more INSERT statement, back in January.
Predict: immediately after all four inserts (before any merge has run), how many partitions exist, and how many parts exist in total? Then predict which parts are even eligible to be merged with each other by a future background merge.
Reveal the answer →
Two partitions (202401 and the February partition) and four parts total — one level-0 part per INSERT statement, since partitioning doesn't change the one-part-per-INSERT rule from Module 2. Of those four parts, only the three January parts are eligible to ever be merged with each other, because they share the same partition value. The single February part can never be merged with any of them, no matter how long the table runs — it's permanently in a separate mergeable pool, waiting only for other February parts to join it.
Summary
A part's name — <partition>_<min_block>_<max_block>_<level> — is a readable record of its own history: which partition it lives in, which insert blocks it covers, and how many merges produced it, with level 0 meaning "straight from an INSERT" and each merge bumping the level by one. system.parts' active column shows this history in motion: a merge writes its combined output first, then marks the smaller inputs inactive, and they're deleted shortly after. PARTITION BY adds a second, independent dimension of physical layout on top of all that: it decides, once, at insert time, which named bucket a row's part belongs to — and the one hard rule that makes it matter is that a merge can only combine parts sharing the same partition value, never across partitions. That's what makes PARTITION BY an operational tool (cheap bulk drops and moves of whole coarse buckets like months) rather than the main query-speed lever — pushing it down to something fine-grained like a day or a user_id fragments a table into many small, separately-mergeable pools that stay small forever, which hurts performance instead of helping it.
Quiz
1. A part is named 202403_5_8_2. What does the 2 at the end tell you?
2. You query system.parts and see a row with active = 0 for a part that still has real row and byte counts. What does that mean?
3. A table is PARTITION BY toYYYYMM(event_time), ORDER BY (event_type, event_time). Two parts exist: one in partition 202401 with 200 rows, one in partition 202402 with 150 rows. Why might these never merge into a single part, no matter how long the table runs?
4. A team partitions a large events table by exact day (PARTITION BY toDate(event_time)) instead of by month, hoping finer partitions will make the table faster. What's the most likely actual outcome?
5. What is PARTITION BY actually best suited for, given that it isn't the primary tool for making individual queries faster?