6. An insert, traced — and why batching is mandatory

Writing to ClickHouse is simple and has exactly one rule that you must not break. This chapter is the trace, the rule, and what to do when you cannot follow it.

The trace

INSERT INTO page_views (ts, tenant_id, country, user_id, url, duration)
VALUES (…), (…), (…);        -- 500,000 rows

1. Parse and form blocks. ClickHouse reads the incoming rows and assembles them into blocks — column-oriented chunks, by default up to max_insert_block_size (1,048,576 rows). A 500,000-row insert is one block.

2. Route to partitions. The PARTITION BY expression is evaluated per row, and the block is split by partition. An insert spanning three months creates at least three parts, not one. This is why inserting data spread over a wide time range is much more expensive than inserting recent data — and a reason not to backfill years of history in one statement with monthly partitions.

3. Sort. Each per-partition block is sorted by the ORDER BY key, in memory.

4. Serialise, encode, compress. Chapter 2’s pipeline, per column: values → codec → LZ4/ZSTD → bytes.

5. Write the part. A temporary directory is written with all the column files, the marks, the primary index, and checksums. When complete, it is atomically renamed into place.

   tmp_insert_all_57_57_0/     →  all_57_57_0/

That rename is the commit point. A crash before it leaves a tmp_ directory that is cleaned up on startup; a crash after it leaves a complete, valid part. This is the whole of ClickHouse’s atomicity story for inserts: one block, one part, one rename.

6. Acknowledge. For a replicated table, ClickHouse also writes the part’s metadata into Keeper and — depending on insert_quorum — may wait for other replicas (chapter 12).

7. Background merges take over. Chapter 3.

   INSERT 500,000 rows
        │
        ├── form blocks (≤1,048,576 rows)
        ├── split by PARTITION BY expression   ← one part per partition touched
        ├── sort by ORDER BY key
        ├── per column: codec → compress → bytes
        ├── write tmp_insert_… directory
        └── atomic rename → all_57_57_0        ← committed here
                    │
                    ▼
          background merges combine it with its neighbours

The rule

Insert in large batches. Thousands to millions of rows per INSERT. Not one row at a time. Not one row per HTTP request.

Every insert creates at least one part, regardless of size. One row creates a part containing one row: a directory, a file per column, marks, an index, checksums. The overhead dwarfs the data by orders of magnitude.

Then the merge process must clean up after you. It merges small parts into bigger ones, but it has finite throughput. Insert faster than merges can keep up and the part count climbs monotonically. Queries get slower — each one opens and reads every active part. Eventually:

   DB::Exception: Too many parts (300). Merges are processing
   significantly slower than inserts.

ClickHouse first delays inserts artificially (parts_to_delay_insert, default 150), then rejects them (parts_to_throw_insert, default 300). Both numbers are per partition.

Raising the thresholds is not the fix

The instinct on seeing TOO_MANY_PARTS is to raise parts_to_throw_insert. That removes the alarm and keeps the fire. The part count continues to climb, queries get slower and slower, memory use grows, and you end up with a table that takes hours to merge back into health.

The fix is always to insert in bigger batches, or to use async inserts, below.

The target: one insert every one to ten seconds, of 10,000 to 1,000,000 rows. That is the shape ClickHouse is designed for.

Async inserts — when you cannot batch

Sometimes the application genuinely produces one row at a time — an HTTP endpoint, a webhook receiver, many small services with no shared buffer.

Asynchronous inserts move the batching into ClickHouse itself:

INSERT INTO page_views SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (…);

The server accumulates incoming rows in an in-memory buffer per table and flushes when either fills:

   async_insert_max_data_size    = 10 MiB   (roughly; version-dependent)
   async_insert_busy_timeout_ms  = 200 ms

Small inserts from many clients become one part. The part-count problem goes away.

The one decision:

• wait_for_async_insert = 1 (the default) — your INSERT returns after the buffer is flushed to disk. Slower per call, and durable.
• wait_for_async_insert = 0 — returns as soon as the row is buffered. Fast, and a server crash loses the buffer.

Use 1 unless you have explicitly decided the data is disposable. This is the same trade as Redis’s appendfsync or Kafka’s acks — the fast option is fast because it is not durable yet.

Async inserts are also the sensible default for ClickHouse Cloud and for anything behind a load balancer where you cannot control batching at the client.

The Buffer engine — the older answer

Predating async inserts, and still occasionally useful:

CREATE TABLE page_views_buffer AS page_views
ENGINE = Buffer(default, page_views, 16, 10, 100, 10000, 1000000, 10000000, 100000000);

Rows accumulate in RAM and flush to the real table on time or size thresholds. Writes go to the buffer table; reads from it merge buffered rows with the underlying table.

It loses data on crash, and it complicates SELECT behaviour. Prefer async inserts for new work; you will still meet Buffer in existing systems.

Ingesting from Kafka

The native integration, because it is how a large fraction of ClickHouse data arrives:

CREATE TABLE kafka_queue (
    ts DateTime, tenant_id UInt32, country String, url String
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'page_views',
         kafka_group_name = 'clickhouse',
         kafka_format = 'JSONEachRow',
         kafka_max_block_size = 1048576;

CREATE MATERIALIZED VIEW kafka_to_table TO page_views AS
SELECT ts, tenant_id, country, url FROM kafka_queue;

The Kafka engine is a consumer; the materialised view is the pump that moves rows into the real table (chapter 11). ClickHouse batches naturally here — it consumes up to kafka_max_block_size rows before writing a part — so the part-count problem largely solves itself.

Two things to know. A Kafka engine table can only be read once — reading it consumes the messages, so never SELECT from it to “check.” And offsets are committed after the block is written, giving at-least-once delivery; combined with insert deduplication (chapter 4), retries are usually safe. See the Kafka guide for what the consumer side is doing.

UPDATE and DELETE — mutations

They exist, and the syntax announces that something unusual is happening:

ALTER TABLE page_views UPDATE duration = 0 WHERE tenant_id = 7;
ALTER TABLE page_views DELETE WHERE ts < '2025-01-01';

They are ALTER statements, not DML, because that is what they are: a schema-level operation that rewrites data.

What actually happens:

1. The statement returns almost immediately. It has not done the work.
2. A mutation is queued.
3. In the background, ClickHouse rewrites every data part that could contain a matching row — reading all columns, applying the change, writing new parts.
4. Old parts are dropped when nothing is reading them.

So DELETE WHERE ts < '2025-01-01' on a 40 TB table reads and rewrites a large fraction of 40 TB. It may take hours or days. It competes with everything else.

Watch it:

SELECT database, table, mutation_id, command, parts_to_do, is_done,
       latest_fail_reason
FROM system.mutations WHERE NOT is_done;

KILL MUTATION WHERE mutation_id = '0000000042';

parts_to_do counting down is your progress bar. A mutation stuck with a latest_fail_reason will retry forever and block later mutations on the same table — kill it.

Lightweight DELETE is the newer alternative:

DELETE FROM page_views WHERE tenant_id = 7;

This marks rows deleted with an internal mask column rather than rewriting parts immediately; the rows are filtered out of queries at once and physically removed at the next merge. Much cheaper and it does not block. It is still not a transactional delete, it does add per-row overhead to reads until merges catch up, and its implementation has changed across versions — check your release’s notes.

What to do instead

For retention: DROP PARTITION (chapter 5). Instant, free, and the correct answer.

For correcting data: ReplacingMergeTree and insert a new version (chapter 4).

For soft deletion: an is_deleted column plus a filter — or a ReplacingMergeTree with is_deleted.

For “remove one tenant’s data”: if you can partition by tenant and the tenant count is small, DROP PARTITION. Otherwise a lightweight DELETE and patience.

The mental model that keeps you out of trouble: ClickHouse is append-only, with an escape hatch that costs a full rewrite. Design so you use the escape hatch rarely.

Insert performance settings

The few worth knowing:

max_insert_block_size            1048576   rows per block
min_insert_block_size_rows       1048576   squash small blocks before writing
min_insert_block_size_bytes      268435456
async_insert                     0         → 1 for many small inserts
wait_for_async_insert            1         keep it at 1
insert_deduplicate               1         identical blocks ignored (replicated)
max_partitions_per_insert_block  100       guards against accidental over-partitioning

That last one is a genuine safety net: it stops a single insert creating hundreds of parts because the data spanned many partitions. If you hit it, look at whether you are backfilling across a wide time range, and split by partition instead of raising the limit.

What you would type

-- part counts per table — the number to watch
SELECT database, table, count() AS parts, sum(rows), formatReadableSize(sum(bytes_on_disk))
FROM system.parts WHERE active
GROUP BY database, table ORDER BY parts DESC;

-- inserts happening now and their sizes
SELECT event_time, table, rows, part_type, duration_ms
FROM system.part_log WHERE event_type = 'NewPart'
ORDER BY event_time DESC LIMIT 20;

-- async insert activity
SELECT * FROM system.asynchronous_inserts;

-- merges currently running
SELECT table, elapsed, progress, num_parts, formatReadableSize(memory_usage)
FROM system.merges;

The parts count per table is the single most important operational number in ClickHouse. Alert on it. A few dozen per partition is healthy; hundreds is a warning; the delay threshold is 150 and rejection is 300.

What goes wrong

One insert per row. The cardinal sin. TOO_MANY_PARTS, then a table that takes hours to recover.

Raising parts_to_throw_insert. Hides the problem while it gets worse.

Backfilling across many partitions in one statement. Hundreds of parts from one insert. Load partition by partition.

DELETE/UPDATE used routinely. They are bulk rewrites. If your workload needs them often, ClickHouse is the wrong database for that table.

wait_for_async_insert = 0 without meaning it. Silent data loss on a crash.

Reading a Kafka engine table to “check the data.” You just consumed those messages.

Duplicate data after a retry that you expected to be deduplicated — deduplication is per identical block on replicated tables. Change one byte, or use a non-replicated table, and it does not apply.

What to take from this chapter

• An insert forms blocks, splits by partition, sorts, compresses, writes a temporary directory, and atomically renames it. That rename is the commit.
• Batch. 10,000 to 1,000,000 rows, every 1–10 seconds. Every insert makes at least one part per partition it touches.
• TOO_MANY_PARTS means merges cannot keep up. Raising the threshold is never the fix.
• Async inserts move batching into the server — use them when the client cannot batch, and keep wait_for_async_insert = 1.
• The Kafka engine + materialised view is the standard ingestion pipeline and batches naturally.
• UPDATE/DELETE are ALTER statements that rewrite whole parts asynchronously. Watch system.mutations; kill stuck ones.
• Prefer DROP PARTITION for retention and ReplacingMergeTree for corrections. Lightweight DELETE is the cheaper modern option for genuine deletes.
• Alert on part count per table.

Next: what happens when you ask a question.