The Migration That Forgot to Batch
2026-07-08 - 8 min read
“Too many parts” in ClickHouse usually comes down to batching. A worked example: a pipeline change that removed it, and a fix that keeps retries safe.
Most "too many parts" errors I see in ClickHouse come down to how inserts are batched. Sometimes nothing batches at all, or the batches are too small. Sometimes the batching was right when someone wrote it, and then the pipeline or the data model changed around it and nobody revisited it.
On a recent engagement it was a pipeline change, and about 40% of writes were failing. Here's how I found it, and how to fix it without breaking retries.
What changed
The client had moved ingest onto a durable workflow engine. The new pipeline took in events through an edge API, which wrote each upload to object storage and started one workflow task per object. A worker fetched the object and inserted its rows into ClickHouse. The object key, the task ID and the ClickHouse insert_deduplication_token all came from the same content hash, so a retry at any layer collapsed to one stored block.
That property is the point of the design, and the client's team built it well. But the pipeline it replaced had a streaming consumer in the middle, and that consumer batched: each insert carried hundreds to a thousand rows. The batching went away with the broker, and each upload became one insert of a handful of rows.
The migration was correct in almost every way, but it dropped the batching the old consumer used to do.
Before · streaming pipeline
After · durable workflow engine
The object key, the task ID and the insert's deduplication token all come from the same content hash. That's what made the new pipeline safe to retry.
In pre-production, memory pressure came first. Then ClickHouse began rejecting inserts outright, with an error of this shape (values elided):
Code: 252. DB::Exception: Too many parts (… with average size of …) in table '…'. Merges are processing significantly slower than inserts. (TOO_MANY_PARTS)Why ClickHouse rejects writes with "too many parts"
Every insert into a MergeTree table creates at least one new part on disk, and background merges combine small parts into bigger ones. Hundreds of concurrent inserts of a few rows each produce parts faster than the merge pool can combine them. Once a partition crosses its part limit, ClickHouse refuses new inserts.
How MergeTree merges scale
Parts are immutable and sorted by the table's ORDER BY key. A background merge takes a continuous range of parts from one partition and streams them through a k-way merge into one new sorted part. The merge keeps one cursor per input part in a heap, and each output row is one O(log k) heap step. For k parts holding n rows in total, that's O(n log k), and every row is read and written once.
Merged parts get merged again later, so the parts form a tree. ClickHouse's merge selector aims to keep it balanced, with each level roughly b times bigger than the one below (the base in its SimpleMergeSelector). A row is rewritten once per level. If inserts arrive as parts of s rows and parts grow to S rows, each row is written about log_b(S/s) times. Making inserts 100 times smaller adds only log_b(100) levels.
The part count is what scales linearly. Each insert creates at least one part per partition it touches, and every part has fixed costs whatever its size: metadata, index and mark files, checksums, any skip indexes rebuilt on merge, and in a replicated table its own entry in Keeper. At q inserts a second, the merge pool has to retire about q parts a second, and for tiny parts most of that work is overhead. When the pool falls behind, the active parts in a partition climb past parts_to_delay_insert (1,000 by default on current versions), where ClickHouse starts slowing inserts down, and then past parts_to_throw_insert (3,000), where it refuses them.
Insert shape is rarely the first suspect. Here there were three plausible culprits before the data pointed at a fourth:
| Suspect | Evidence | Verdict |
|---|---|---|
| A retry storm | Rejected inserts were retried and piled on | Real, but an amplifier |
| A recent index rebuild | Finished, with no mutations running | Ruled out |
| A large secondary index | Same index, identical table, another environment: healthy at a few hundred parts | Costlier merges, not the cause |
| Insert shape | Single-digit rows per insert in the failing environment | The cause |
The secondary index was the popular theory, because every merge rebuilds it. A controlled comparison ruled it out: the identical table in another environment was healthy, and a third copy without the index was fine too. The index made merges more expensive, but it wasn't why they fell behind.
SELECT
toStartOfFiveMinutes(event_time) AS t,
count() AS inserts,
round(avg(written_rows)) AS rows_per_insert,
countIf(exception_code = 252) AS too_many_parts
FROM system.query_log
WHERE query_kind = 'Insert'
AND has(tables, 'default.events')
AND type != 'QueryStart'
AND event_time > now() - INTERVAL 1 HOUR
GROUP BY t
ORDER BY t;Why the obvious fix breaks retries
The obvious fix is to put the batcher back, inside the worker: collect rows from several objects and insert them together. It's the natural first move, and here it would have broken retries:
The unit you retry has to be the unit you deduplicate.
In a pipeline like this one, the unit of retry is the object. ClickHouse only recognises a retried insert if it arrives with the same deduplication key. Merge objects A, B and C into one insert and that insert's key is a hash of all three. If the insert commits but B's task never sees the acknowledgement, the workflow engine retries B, and B lands in a different batch with D and E, under a new key. B's rows are now stored twice. Which tasks share a batch depends on timing, so no merged key can be reproduced on retry.
A shared in-process batcher also couples failures. Hundreds of tasks wait on one flush; if that flush hangs, they all time out together and retry together. It also makes a rejected row hard to attribute back to the upload that sent it.
ClickHouse only recognises a retried insert if it arrives with the same deduplication key.
✕ A batcher in the worker
key = hash(A, B, C)key = hash(B, D, E)✓ Server-side async inserts
buffered → one partkey = hash(B)Batching at the edge, before the object and its key exist, keeps the same rule: one bigger object, one key.
The fix that keeps retries safe
Let ClickHouse do the batching. Each worker keeps sending one insert per object, with that object's own key, and the server buffers concurrent inserts and writes them out as one part:
INSERT INTO default.events
SETTINGS
async_insert = 1,
wait_for_async_insert = 1, -- ack only after the flush: no lost acks, errors stay per insert
async_insert_deduplicate = 1, -- each buffered insert is deduplicated by its own key
insert_deduplication_token = 'obj-3f9a2c' -- derived from the object's content hash
FORMAT JSONEachRowClickHouse writes the buffered inserts out as one part, but it still checks each insert's key on its own. wait_for_async_insert = 1 matters as much as the rest. The worker gets its acknowledgement only after the flush, so an acknowledged insert is never lost, backpressure still works, and a bad row still fails the request that sent it.
On this engagement I had ruled out async inserts earlier the same day, because the documentation warned against async deduplication with dependent materialized views. That warning was stale for the version the cluster was running: a recent ClickHouse release had lifted the restriction. Checking that option again against the version actually running reversed the decision.
If traffic spikes still produce floods of small objects, batch before the key exists. Here, a day later, some buffered inserts timed out waiting for a flush during spikes. The follow-up batched at the edge, per source, flushing on rows, bytes or linger time, before the object and its key were created. The uploads got bigger, but each object still had exactly one key.
What it did on this engagement
| In pre-production | Before | After |
|---|---|---|
| Rejected inserts | About 40% | Zero over a 30-minute check |
| New parts | At least one per insert | Two orders of magnitude fewer than inserts |
| Keys per upload | One | Still one |
The parts figure was measured while a backlog was draining. Server-side async inserts went on in production too and have stayed on, and production now carries its full ingest load on a small memory footprint.
What to check on your own pipeline
- Check insert shape before capacity. "Too many parts" usually comes from too many small inserts. Check rows per insert and inserts per second before touching indexes or memory. Aim for thousands of rows per insert and about one insert per second per table.
- Batch where the idempotency key is minted, or let the server batch. Don't merge keys downstream unless you add row-level idempotency or an ingest ledger first.
- A version check isn't enough. Query
system.settingsas the user that writes, because a service's compatibility setting can keep an old default alive after an upgrade. - Watch
DelayedInserts, not just rejections. ClickHouse slows inserts long before it refuses them.
When this isn't the right approach: if you don't need deduplication, or batch membership is deterministic, a client-side batcher is simpler. A broker consumer, for example, can derive a stable key from an offset range. And a Buffer table only fits when you can lose buffered rows on a restart; on current versions, async inserts usually replace it.
Related
- ClickHouse & real-time data services: ingestion, schema design and migrations.
- ClickHouse Migrations Without Transactions (ch-migrate 0.6): the same retry-safety problem, for schema changes.
- The ClickHouse Memory Win That Didn't Show Up On the Dashboard