ClickHouse "Too many parts": Why Inserts Fail and How to Fix It

ClickHouse "Too many parts" usually shows up on a day when everything else looks normal. Inserts start taking longer, then the ingest pipeline begins to fail with Too many parts (N). Merges are processing significantly slower than inserts, and data starts piling up in queues or getting dropped. The error message is honest: ClickHouse is creating data parts faster than it can merge them. The fix is almost never to raise the limit. It is to create fewer parts.

How parts and merges work

Every INSERT into a MergeTree table writes at least one new part: a directory of sorted, compressed column files on disk. ClickHouse doesn't update a big file in place. Instead, background merges keep combining small parts into larger ones. This is what makes inserts fast and queries efficient, as long as merges keep up.

Two details matter for this error:

• An insert creates at least one part per partition it touches. One INSERT with rows for 50 different partitions writes 50 parts.
• Merges only happen within a partition. Parts from different partitions are never merged together.

Materialized views add to it. Every insert into the source table also inserts into each view's target table, so each target gets new parts as well.

The limits behind the error

ClickHouse protects itself with MergeTree settings that react to the number of active parts:

• parts_to_delay_insert (default 1000): once a partition has this many active parts, ClickHouse starts slowing inserts down on purpose, to give merges a chance to catch up.
• parts_to_throw_insert (default 3000; it was 300 before version 23.6): above this number of active parts in a single partition, inserts fail with "Too many parts".
• max_parts_in_total (default 100,000): a limit on active parts across the whole table, which is what you hit when you have too many partitions rather than too many parts in one.

The delay phase is your early warning. If inserts have been getting slower for days, this is often why.

Step 1: find where the parts are

Start with active parts per partition, as the ClickHouse troubleshooting guide suggests:

SELECT database, table, partition_id,
       count() AS active_parts,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       formatReadableSize(avg(bytes_on_disk)) AS avg_part
FROM system.parts
WHERE active
GROUP BY database, table, partition_id
ORDER BY active_parts DESC
LIMIT 20;

Read the result with three questions in mind:

1. One partition with thousands of small parts? Something is writing small, frequent inserts into it. The average part size of a few kilobytes or megabytes gives it away.
2. Thousands of partitions with a few parts each? The partition key is too fine-grained, for example per user, per device or per hour on years of data. You are heading for max_parts_in_total, and queries suffer too.
3. Parts are normal-sized but still piling up? Inserts may be fine, and merges are the problem.

Step 2: check that merges are actually running

SELECT database, table, round(elapsed) AS sec,
       round(progress * 100) AS pct, num_parts,
       formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
ORDER BY elapsed DESC;

If merges are running but slowly, look at the server: disk throughput, free disk space (a merge needs room to write the new part before the old ones are removed), CPU, and competing background work such as mutations. If nothing is merging at all, check the server log for merge errors, and whether somebody ran SYSTEM STOP MERGES during maintenance and never ran SYSTEM START MERGES.

Step 3: stop creating so many parts

This is the real fix, and it is usually on the application side.

Batch your inserts. ClickHouse recommends batches of at least 1,000 rows, ideally 10,000 to 100,000, and roughly one insert query per second per table. A service that sends one row per HTTP request, from fifty pods, can generate thousands of parts a minute. Buffer in the application, in a queue consumer, or in your ingest tool, and flush by size or time.

Use async inserts when batching on the client isn't practical. With async_insert = 1 the server collects small inserts into a buffer and writes them as one part when the buffer reaches a size, time or query-count threshold:

INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (...);

Keep wait_for_async_insert = 1, which is the default. Then the client gets an acknowledgement only after the data is flushed. Setting it to 0 makes inserts "fire and forget", and the ClickHouse documentation calls that very risky, because the client may never learn that an insert failed.

Fix the partition key. Partitions are a data management tool, not an index. A monthly partition on a date column is the classic choice; daily can make sense with high volume and short retention. Partitioning by user ID, customer ID or anything with thousands of values multiplies parts and stops merges from doing their job. Changing the partition key means creating a new table and moving the data, so get it right early. We touched on this when describing how we build ClickHouse warehouses from legacy data.

Getting out of the hole

Once the source of small parts is fixed, merges usually catch up by themselves. Watch the active part count from step 1: it should level off and then fall. For a single badly fragmented partition you can force a merge with OPTIMIZE TABLE ... PARTITION ..., but that is heavy work on a large table, so run it outside peak hours, one partition at a time, and avoid FINAL on whole big tables. Make sure there is enough free disk space before you start.

Data that failed to insert during the incident is another matter. If your producers don't retry, or your queue's retention is shorter than the outage, some of it is gone. Check what your pipeline does with failed inserts before the next incident, not during it.

Monitor it before it fails

1. Maximum active parts per partition per table, with an alert well below parts_to_delay_insert.
2. Total active parts per table compared with max_parts_in_total.
3. Insert latency and insert errors in your ingest pipeline.
4. Number and duration of running merges, and free disk space on the data volume.

These belong on the same dashboard as the rest of your metrics that predict database incidents. A slow climb in part counts is visible for days before the first insert fails.