Insert in batches

ClickHouse wants batches. One insert per event creates a flood of tiny parts and more merge work than the server can keep up with.

The reason follows from the storage model. Every INSERT creates at least one new part on disk, a folder with a file per column. A part holding one row costs almost as much bookkeeping as a part holding 100,000 rows. Insert row by row at even a modest rate and you create thousands of parts per minute, while the background merges fall further and further behind.

ClickHouse defends itself when that happens. Inserts start failing with:

DB::Exception: Too many parts (300 with average size of 2.31 KiB) in table 'analytics.events'.
Merges are processing significantly slower than inserts.

Don’t reach for the setting that raises the limit. This error is the database telling you your inserts have the wrong shape.

Batch at the source

Buffer events in the application, the agent, or the queue consumer, then send one block in a supported format:

cat events.jsonl | clickhouse-client --query \
  "INSERT INTO analytics.events FORMAT JSONEachRow"

A good starting size is 10,000 to 100,000 rows per insert. One insert with 50,000 rows creates one healthy part. 50,000 single-row inserts create 50,000 parts and a merge storm.

Bound the buffer by count and by time, so low traffic still arrives. My rule is “flush at 50,000 rows or after 5 seconds, whichever comes first”. Without the time bound, a quiet service’s events sit in memory forever. Without the count bound, a traffic spike eats all your memory.

Measure the difference yourself

Insert the same thousand events twice into a disposable table, once as a single batch and once as a thousand tiny inserts. Time both, then compare the parts:

SELECT count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE table = 'events_test' AND active;

The batched version finishes in a fraction of the time and shows one part. The row-by-row version shows hundreds of parts for identical data. Each of those parts is waiting for a merge the batch never needed.

Once you’ve seen it, you’ll never write a loop of single inserts again.

One caveat before you build buffering into every producer. If your writers are many and small, say serverless functions that each see a few events, client-side batching gets awkward. There is no long-lived process to hold the buffer. That’s the case server-side asynchronous inserts were built for, and it’s where the next lesson picks up.