Skip to content
dethlex

Why ClickHouse wants your inserts in batches

Every INSERT into MergeTree creates a part on disk. Feed it row-by-row and you trade write throughput for a merge storm — batch, or let async inserts batch for you.

evergreenplanted tended 3 min read

🧪 Sample seed note. Placeholder content demonstrating the garden structure — replace with your own text.

MergeTree is not a B-tree you poke rows into. Every INSERT becomes a brand-new part — a directory on disk with one file pair per column, plus checksums and metadata. The table you query is the union of all active parts, and a background process constantly merges them into bigger ones. That design is why ClickHouse scans billions of rows per second. It is also why it punishes chatty writers.

What a tiny insert actually costs

Insert one row and ClickHouse still pays the full price of a part: create the directory, write every column file, fsync, register the part. On a Replicated* table it additionally writes a replication log entry that every replica must fetch and apply. One row, dozens of filesystem operations, one round of replication traffic.

Do that a thousand times a second and two things happen:

  1. Merges fall behind. The merge scheduler was designed for parts with thousands-to-millions of rows, not thousands of parts with one row.
  2. The table throttles you. Once a partition accumulates parts_to_delay_insert active parts (1000 by default), inserts get slowed down; at parts_to_throw_insert (3000) you start catching TOO_MANY_PARTS exceptions in production at the worst possible moment.

You can watch the pressure build up:

SELECT table, count() AS active_parts
FROM system.parts
WHERE active
GROUP BY table
ORDER BY active_parts DESC;

If active_parts for a hot table grows monotonically during peak traffic, your writers are winning against the merges. They will not win for long.

The rule of thumb

Aim for one insert per table per second, tens of thousands of rows per insert. The exact numbers are workload-dependent, but the shape of the rule is not: fewer, fatter inserts are always cheaper than many thin ones. In our pipelines the sweet spot landed around 50–100k rows or one second of accumulation, whichever comes first.

Where to batch

  • In the application. A buffered channel and a ticker: flush on len(buf) >= N or every second. Full control over durability — you decide what happens to the buffer on shutdown. This is what we run in the Go consumers that land Kafka topics into ClickHouse.
  • async_insert = 1. The server batches for you: inserts land in an in-memory buffer and are flushed as one part. Turn on wait_for_async_insert if you want the acknowledgment to mean “data is in a part” rather than “data is in a buffer” — with it off, a crash between buffer and flush loses rows silently.
  • The Buffer engine. Same idea, table-shaped, older. It works, but the buffer lives in RAM of the node and is lost on a hard restart, and it adds one more table to reason about. I reach for it last.

The part nobody tells you

Batching interacts with insert deduplication on replicated tables: a retried identical block is dropped by its checksum, which is exactly what you want from at-least-once consumers — but only if your batches are deterministic. Build them by offset range, not by wall clock, and a consumer restart re-produces byte-identical blocks that dedup for free.

Batch because the storage engine is built from parts. Everything else — async inserts, Buffer tables, Kafka engine — is just someone else doing the batching for you, with durability semantics you should read twice.