Insert batching and the too many parts problem

ClickHouse is built for few, large inserts. The reason is mechanical: every INSERT writes at least one new immutable part to disk, and a background process has to merge those parts into bigger ones. The faster you create parts, the more merge work you create, and when merging cannot keep up ClickHouse starts to slow your inserts down and finally rejects them.

This is part 1 of a short series. Part 2 covers async_insert, the server-side batching that rescues clients which cannot batch.

See it happen

Create the demo table and a copy, and stop merges so the numbers stay put:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

CREATE TABLE events_batched AS events;

SYSTEM STOP MERGES events;
SYSTEM STOP MERGES events_batched;

Now insert 100 rows as 100 separate statements into one table, and as one statement into the other. Synchronous inserts are forced here (async_insert = 0) because recent releases enable asynchronous inserts by default:

INSERT INTO events SETTINGS async_insert = 0 VALUES (now(), 0, 'view', '/', 10, 'US');
-- ... 99 more statements with user_id 1..99 ...

INSERT INTO events_batched SETTINGS async_insert = 0
SELECT now(), number, 'view', '/', 10, 'US' FROM numbers(100);

Count the parts:

SELECT `table`, count() AS active_parts, sum(rows) AS rows
FROM system.parts
WHERE active AND `table` IN ('events', 'events_batched')
GROUP BY `table`
ORDER BY `table`;

┌─table──────────┬─active_parts─┬─rows─┐
│ events         │          100 │  100 │
│ events_batched │            1 │  100 │
└────────────────┴──────────────┴──────┘

The same 100 rows are 100 parts or one part. Each part is a directory with a file set per column (or a compact single file set for small parts), a primary index and metadata. Every query has to open and consider all of them until merges clean up.

Partitions multiply the problem

A part belongs to exactly one partition. An insert that spans several partitions is split, and creates one part per partition touched. Inserting a batch whose rows cover twelve months into a table partitioned by month writes twelve parts. This is why a partition key with high cardinality (daily partitions over years, or partitioning by a user id) combined with unsorted input makes inserts pathologically slow. Sort or group the incoming data by partition before sending, or choose a coarser partition key.

What happens when merges fall behind

Two MergeTree settings define the limits per partition:

Setting	Default	Effect
parts_to_delay_insert	1000	Inserts are artificially delayed once a partition has this many active parts
parts_to_throw_insert	3000	Inserts fail with Too many parts

The defaults above were read from system.merge_tree_settings on 26.4. Older releases used lower numbers (150 and 300 were common), so do not copy thresholds from old posts. The error message is:

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

Raising the limits only postpones the crash and costs file descriptors, memory for part metadata and slower startup. The fix is always on the insert side.

How to size a batch

A good rule of thumb is tens of thousands to a million rows per insert, one insert every second or so per table. Fewer, larger inserts are better than more, smaller ones until the batch starts to hurt memory on the client or server. If you ingest more than a few hundred thousand rows per second into one table, the answer is usually more shards, not different settings.

Practical ways to get there:

• Batch in the client. Collect rows in memory and flush by size or by time (for example every 1 million rows or every second, whichever first). Most ClickHouse clients have a buffered writer.
• Put a queue in front. Kafka, as covered in the Kafka guide, turns a stream of small messages into controlled blocks.
• Let the server batch. When clients cannot be changed, enable asynchronous inserts.
• Throttle huge loads. A single insert of billions of rows is also a problem. Split large loads into chunks of a few million rows.
• Move whole partitions. For bulk loads between tables, see ATTACH PARTITION FROM.

Watching your insert health

This query lists partitions with the most active parts, the first thing to check when inserts get slow:

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

If the numbers grow steadily instead of oscillating, inserts outpace merges.