In a ClickHouse MergeTree table, what does the PARTITION BY clause actually do?

## What a partition is Every row inserted into a `MergeTree` table is assigned a partition id by evaluating the `PARTITION BY` expression. An insert produces one new part per distinct partition value in the block. Parts belong to exactly one partition, and background merges only ever combine parts **within** the same partition — never across. So a partition behaves like an independent sub-table sharing the schema, the sorting key, and the indexes. Without a `PARTITION BY` clause, the table has a single partition. That is a perfectly valid and often correct choice. ## What it is for Four things, in rough order of importance: 1. **Whole-unit data lifecycle.** `ALTER TABLE events DROP PARTITION '202401'` removes a month of data by dropping its parts. It is close to instantaneous and costs no merge work, unlike `ALTER TABLE ... DELETE`, which is a mutation that rewrites every affected part. If your retention policy is "keep 13 months", partitioning by month is what makes it cheap. 2. **Moving data around.** `DETACH PARTITION` and `ATTACH PARTITION` (and `ATTACH PARTITION FROM` another table with the same schema) let you move or copy a slice of data at file level — the standard backfill and archival mechanism. 3. **TTL and tiered storage.** `TTL event_date + INTERVAL 6 MONTH TO VOLUME 'cold'` is evaluated per part, and partitioning aligned with the TTL expression means whole partitions age out together instead of leaving each part half-expired. 4. **Coarse pruning.** Before touching any index, ClickHouse discards partitions whose partition-key values cannot match the predicate. Each part additionally carries min/max values for the columns used in the partition expression, so a filter on `event_date` prunes even though the partition key is `toYYYYMM(event_date)`. ## What it is not It is not an index and it is not a sort order. Within a partition, everything about performance is decided by `ORDER BY` and the sparse primary index. A common junior mistake is to reach for a finer partition key to "make queries faster" — a partition key of `toYYYYMMDD(event_time)` plus a filter on a customer id prunes nothing on that customer id and creates thirty times as many parts as monthly partitioning would. It is also not required. Small or medium tables with no retention story are usually better off with no partition key at all, letting merges consolidate everything into a few large parts. ## Choosing the granularity The rule of thumb the ClickHouse community converged on is: partition by the unit you **delete or move**, and keep the number of partitions modest — typically monthly for time-series, occasionally weekly or daily for very high-volume tables with short retention. Two costs push back on fine partitions: - **Part count.** Every insert that spans N partitions writes N parts. Merges cannot consolidate across partitions, so the steady-state part count scales with the number of active partitions. High part counts slow query planning (index analysis runs per part), consume file handles and memory, and eventually trigger insert throttling and the `Too many parts` error. - **Metadata and startup.** Server start and many system operations enumerate parts; tables with tens of thousands of partitions are noticeably worse to operate. A high-cardinality expression — a user id, a hash, a raw timestamp — is the anti-pattern. ClickHouse guards the worst case with `max_partitions_per_insert_block`, which rejects an insert block touching too many partitions at once. ```sql CREATE TABLE events ( event_date Date, tenant_id UInt32, user_id UInt64, payload String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (tenant_id, event_date, user_id) TTL event_date + INTERVAL 13 MONTH DELETE; ``` ## Inspecting partitions `system.parts` is the operational view: one row per part, with `partition`, `active`, `rows`, and `bytes_on_disk`. Grouping active parts by partition tells you both the data distribution and whether any partition has accumulated an unhealthy number of parts. The virtual columns `_partition_id` and `_part` let a query see which partition and part a row came from. ```sql SELECT partition, count() AS parts, sum(rows) AS rows FROM system.parts WHERE active AND table = 'events' GROUP BY partition ORDER BY partition; ``` ## The interview answer Say what it does (independent buckets of parts, no cross-partition merges), why you'd want it (drop/detach/TTL as whole units, plus coarse pruning), and what it is not (an index — that's the sorting key's job). Then name over-partitioning as the failure mode. That covers the whole surface.