Few ClickHouse errors frustrate newcomers like "Too many parts." It usually appears right after someone wires up a pipeline that inserts rows one-by-one or in tiny batches - and it appears even faster when ClickHouse is backed by S3 storage. The error isn't a bug; it's ClickHouse telling you that your insert pattern is fighting its storage engine. Understanding why turns a baffling production incident into a one-line fix.

The mental model: parts and merges

Every insert into a ClickHouse MergeTree table creates a new part - an immutable, sorted chunk of data on disk. ClickHouse continuously merges small parts into larger ones in the background, which is how it stays fast to query. The catch is that merging can't keep up if you create parts faster than it can combine them. Insert ten thousand rows in ten thousand separate statements and you've made ten thousand tiny parts; the merge process falls behind, the part count climbs, and ClickHouse throws "Too many parts" to protect itself from a state it can't recover from.

The S3 twist

On local disk this is bad; on S3 it's worse. Each part involves object-storage operations, and S3's per-request latency and cost make many small parts especially painful - more objects, more requests, slower merges, higher bills. The same anti-pattern that's merely inefficient locally becomes acutely expensive when every part is a set of S3 objects. S3-backed ClickHouse punishes small, frequent inserts harder than any local setup.

What actually works

• Batch your inserts. The single most important fix: insert large batches (tens of thousands to millions of rows) per statement, not row-by-row. One big insert makes one part; a thousand small ones make a thousand.
• Let ClickHouse buffer for you. Use asynchronous inserts (async_insert) so ClickHouse accumulates many small inserts server-side and flushes them as fewer, larger parts - ideal when clients can't easily batch.
• Insert through a queue. Put a buffer (Kafka plus the Kafka engine, or a Buffer table) between a high-rate source and the table, so data lands in big batches regardless of how fast events arrive.
• Don't over-partition. Too many partitions multiply parts, since parts live within partitions. Choose a partition key (often by month) coarse enough to avoid scattering data.

A safe S3 ingestion pattern

The reliable shape for streaming data into S3-backed ClickHouse is: events flow into a buffer, the buffer accumulates a large batch, and the batch is inserted in one statement. Whether the buffer is Kafka, an application-side accumulator, or async_insert, the principle is identical - convert a stream of small writes into a trickle of large ones before it reaches the table. The merge process stays ahead, the part count stays low, and S3 sees far fewer, larger objects.

Tuning checklist

• Insert in large batches; never row-by-row in a loop.
• Enable async_insert when clients can't batch themselves.
• Keep partitioning coarse - monthly is a common default.
• Watch the active part count per table; rising counts are the early warning before the error.
• On S3, weigh request cost and latency when sizing batches - bigger is generally better.

Troubleshooting: symptom to action

• "Too many parts" errors → batch larger and/or enable async inserts; check for over-partitioning.
• Part count climbing steadily → inserts are outpacing merges; reduce insert frequency, increase batch size.
• Slow queries plus many small parts → merges are behind; fix the insert pattern and let merges catch up.

The takeaway

"Too many parts" is almost always a symptom of one habit: inserting small and often. ClickHouse is built to ingest big batches and merge them in the background, and on ClickHouse over S3 that preference becomes a hard requirement. Batch your writes - directly, asynchronously, or through a queue - and the trap that stops so many pipelines simply never springs.