A ClickHouse table partitioned by toYYYYMMDD(event_time) now rejects inserts with "Too many parts" — why?

## The mechanics behind the error A `MergeTree` insert is atomic per block and writes **one immutable part per distinct partition value in that block**. Background merges then consolidate small parts into larger ones — but only inside a single partition, never across. The number of active partitions therefore sets a floor on how fragmented the table can get, and it multiplies the effect of every other fragmentation source. ClickHouse protects itself with two per-partition thresholds. Once a partition's active part count crosses `parts_to_delay_insert`, inserts are artificially slowed to give merges time to catch up. Past `parts_to_throw_insert`, inserts fail outright with the `TOO_MANY_PARTS` error. A separate guard, `max_partitions_per_insert_block`, rejects a single insert block that touches too many partitions at once — the classic symptom of a partition key with far too high cardinality. ## Why a daily key makes it likely Compare monthly and daily partitioning of the same stream: - **Monthly.** Ongoing inserts land in one partition. Merges see a steady stream of parts in that one bucket and consolidate them into progressively larger parts. Steady-state active parts stay small. - **Daily.** Ongoing inserts land in today's partition — fine so far. But any batch whose rows span midnight, and every backfill or late-arriving-event load, writes into several partitions at once. Each of those partitions merges independently and slowly, because merge scheduling favours combining similarly-sized parts and each day holds only a thirtieth of the volume. The table's total part count is roughly the per-partition count times the number of active partitions. The pathological version is a partition key with unbounded cardinality — `PARTITION BY event_time` on a raw `DateTime`, `PARTITION BY user_id`, `PARTITION BY cityHash64(...)`. There, every insert block touches hundreds or thousands of partitions and the table falls over almost immediately. ## Diagnosis Start with `system.parts`, grouping active parts by partition. The shape of the answer tells you which story you are in: a single huge partition with many parts means an ingest-rate problem; many partitions each with a handful of parts means the partition key is too fine. ```sql SELECT partition, count() AS parts, sum(rows) AS rows, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active AND database = currentDatabase() AND table = 'events' GROUP BY partition ORDER BY parts DESC LIMIT 20; ``` If many partitions each hold few rows, the partition key's cardinality is the cause. If one recent partition holds hundreds of tiny parts, look at how the writer batches — that is an ingest-side problem, and the answer lies in fewer, larger inserts. ## Fixes, in order of preference 1. **Coarsen the partition key.** Move to `toYYYYMM(event_time)`, or to weekly if retention genuinely operates at that granularity. The test is simple: partition by the unit you *drop or move*, nothing finer. If nobody ever runs `DROP PARTITION` on a single day, daily partitions are buying nothing and costing a lot. Changing the partition key means creating a new table and copying the data — plan it as a migration. 2. **Stop backfills from spraying.** A load that spans many partitions at once is worth restructuring so each batch targets one partition, which both reduces part creation and keeps individual inserts atomic per partition. 3. **Batch harder on the write path.** Fewer, larger inserts create fewer parts regardless of the partition key. This is the ingest side of the same coin and is often the co-cause. 4. **Raise the thresholds — last, and knowingly.** Increasing `parts_to_delay_insert` and `parts_to_throw_insert` removes the error without removing the fragmentation. Since primary-index analysis runs per part, a table with thousands of small parts reads far more granules than the same data in a few large ones, so you trade a loud failure for slow queries. Do it to survive an incident, not as the fix. `OPTIMIZE TABLE ... PARTITION ...` can force consolidation of one partition and is useful as a one-off after a bad backfill. It is not a schedule-it-nightly answer: it does full-rewrite I/O over the partition and competes with the merges you actually need. ## The judgment the interviewer is after The candidate who says "raise parts_to_throw_insert" has treated the alarm. The candidate who says "the partition key should match the retention unit, and daily partitions on a table nobody drops daily are pure cost" has found the cause. Mentioning that the total part count drives query planning as well as insert health shows the connection between the ingest symptom and the query-side consequence — which is the reason this question keeps getting asked.