clickhouse 'Too many parts' slowing merges: how to fix the insert pattern

TL;DR

Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.

Code: 252. DB::Exception: Too many parts (300). Merges are processing significantly slower than inserts. (TOO_MANY_PARTS)

Steps

1. Count active parts on the table: SELECT count() FROM system.parts WHERE table = 'your_table' AND active. Expected: hundreds of active parts on one partition. That is the pileup.

2. Check the insert pattern in the query log: look at INSERT frequency and rows per insert over the last hour. Expected: many inserts per second with tiny row counts, sometimes one row at a time.

3. Batch client-side. Accumulate rows and insert every few seconds instead of per event, aiming for batches of at least tens of thousands of rows. Expected: new part creation slows to a trickle and merges start catching up.

4. Alternatively, turn on async inserts (async_insert set to 1) so ClickHouse buffers small inserts into bigger parts server-side. Expected: part count drops without changing the client much, at the cost of slight insert latency.

5. As a stopgap, raise the too-many-parts threshold in the server config. Expected: the error stops, but merge lag keeps growing underneath. Only use this to buy time while you fix the inserts.

6. For replicated tables, check the merge queue: SELECT * FROM system.replicationqueue WHERE table = 'yourtable'. Expected: merges queued far behind the insert flood, confirming the bottleneck is merge throughput, not replication.

Use this when

• Inserts fail with TOOMANYPARTS naming a part count in the hundreds.
• system.parts shows hundreds of active parts on one partition.
• The query log shows a high frequency of tiny inserts.
• Merge backlog grows while insert rate stays constant.

Not for this skill when

• Merges are stuck on one giant part rather than many small ones. That is a stuck merge, a different problem.
• Parts are many but merges keep up and inserts succeed. Hundreds of parts alone is not an emergency.
• The table just finished a huge backfill. The backlog drains on its own once the flood stops.

Variant phrasings

• clickhouse TOOMANYPARTS merges slower than inserts
• clickhouse too many parts error how to fix
• clickhouse small inserts too many parts
• clickhouse async_insert batching
• clickhouse merges falling behind inserts

Why it happens

Every ClickHouse insert creates at least one new data part, and a background merge process continuously combines small parts into larger ones. Tiny frequent inserts create parts far faster than merges can combine them: the part count explodes, each query has to consult more parts, merges compete with each other for IO, and eventually the server refuses new inserts to protect itself. The insert pattern is the root cause; the merge lag is the symptom.

Edge cases

• Async inserts add a small delay before data is queryable and buffer in memory. Do not use them if you need sub-second visibility with a crash-safe guarantee.
• Deduplication on ReplicatedMergeTree happens at merge time, so tiny inserts also delay dedup. Batching fixes both.
• Partitioning by too fine a key (per minute, per user) multiplies the problem: each partition gets its own part pileup. Coarsen the partition key first.
• Materialized views with their own tiny inserts inherit the same disease. Batch the source inserts and the views calm down too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Thread query: clickhouse 'Too many parts' slowing merges: how to fix the insert pattern

TL;DR: Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.