Insert in batches

ClickHouse performs best when clients insert batches. One tiny insert per event creates many small parts and too much merge work.

The reason follows from the storage model. Every INSERT creates at least one new part on disk — a directory 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 event rate and you’re creating 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.

If you see this error, don’t reach for the setting that raises the limit. The error is the database telling you your inserts are shaped wrong.

Batch at the source

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

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

A good starting shape is batches of 10,000 to 100,000 rows. 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 time so low traffic still arrives. The 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 the buffer indefinitely; without the count bound, a traffic spike balloons memory.

Measure the difference yourself

Insert the same thousand events as one batch and as many tiny inserts in a disposable table. Compare elapsed time and part counts:

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 one waiting for a merge that the batch never needed.

One caveat before you build elaborate 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. That’s the situation server-side asynchronous inserts were built for, and it’s exactly where the next lesson picks up.