# 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.

- stage: evergreen
- published: 2026-03-18
- updated: 2026-06-10
- tags: clickhouse, performance

---
> 🧪 **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:

```sql
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](/garden/kafka-partition-assignment/)
  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.