Fix ClickHouse TOO_MANY_PARTS: Batching and Async Inserts

Your application sends many INSERT queries, and each query has only a few rows. Sometimes it has one row. For example, a web service writes one event for each request.

At first, everything works. The table is small, and merges keep up. When traffic grows, new parts arrive faster than merges can combine them. The part count grows.

Before the error, inserts become slow. ClickHouse delays each insert when a partition has too many active parts. This delay is your early warning.

If you do not act, INSERT queries fail with this error on 26.8.1.2041:

Code: 252. DB::Exception: Too many parts (3000). Merges are processing significantly slower than inserts. (TOO_MANY_PARTS)

The limit in the message comes from the parts_to_throw_insert setting.

The error does not mean the host is out of resources. CPU, memory and disk can be free. One partition of one table reached its part limit.

This article shows how to reduce part creation. You will monitor new parts, batch inserts on the client, and use async inserts.

Why it happens

A MergeTree table stores data in parts. A part is a directory on disk. A part is immutable. ClickHouse never adds rows to an existing part.

Each INSERT writes at least one new part. If the table has partitions, one INSERT writes one part for each partition that it touches. A one-row INSERT creates a whole part with one row.

Background merges combine small parts into bigger parts. Merges use CPU and disk, and they have a speed limit. If new parts arrive faster than merges can combine them, the part count grows.

ClickHouse protects the server with two limits on the active parts in one partition:

• parts_to_delay_insert: ClickHouse delays INSERT queries when a partition has more active parts than this value. The default is 1000.
• parts_to_throw_insert: ClickHouse rejects INSERT queries with TOO_MANY_PARTS when a partition has more active parts than this value. The default is 3000.

Check both values on your server with this query:

SELECT name, value, changed
FROM system.merge_tree_settings
WHERE name IN ('parts_to_delay_insert', 'parts_to_throw_insert');

These are MergeTree table settings. They are not session settings. Do not raise them as a first fix. A higher limit hides the problem and makes SELECT queries slower.

The root cause is simple: each insert is too small for the insert rate. Bigger inserts fix this.

How to confirm

Run these queries on the affected server. They show if small inserts cause the problem.

Step 1: Count new parts per minute

Use system.part_log. Each row with event_type = 'NewPart' is one new part from an INSERT.

SELECT
    toStartOfMinute(event_time) AS minute,
    database,
    `table`,
    count() AS new_parts,
    sum(rows) AS total_rows,
    round(sum(rows) / count()) AS avg_rows_per_part
FROM system.part_log
WHERE event_type = 'NewPart'
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY minute, database, `table`
ORDER BY minute DESC, new_parts DESC
LIMIT 50;

Read the result in this way:

• A low avg_rows_per_part means the inserts are small. A value from 1 to 100 is a clear sign.
• A high new_parts value means merges must do much work.

If the query fails because system.part_log does not exist, the part log is off. Enable it in the server configuration.

Step 2: Count active parts in each partition

The limits parts_to_delay_insert and parts_to_throw_insert apply to one partition.

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

Compare active_parts with your limits. The query in "Why it happens" shows them.

Step 3: Check the server-wide maximum

This metric shows the highest part count in any partition.

SELECT metric, value
FROM system.asynchronous_metrics
WHERE metric = 'MaxPartCountForPartition';

Step 4: Check if the server threw the error

SELECT name, code, value, last_error_time
FROM system.errors
WHERE name = 'TOO_MANY_PARTS';

If value is above zero, the server has thrown this error since the last restart.

Result

Small inserts cause your problem if both statements are true:

• avg_rows_per_part is small.
• active_parts grows over time.

If avg_rows_per_part is large and the part count still grows, merges are the problem.

The settings

All settings in this table are session settings. Set them in a settings profile, with SET, or with the SETTINGS clause. None of them needs a restart.

These defaults come from the ClickHouse master branch, commit 9f79cc1, 30 October 2026. Defaults change between releases. Check them on your own server:

SELECT name, value, changed
FROM system.settings
WHERE name IN (
    'async_insert',
    'wait_for_async_insert',
    'async_insert_busy_timeout_max_ms',
    'async_insert_max_data_size',
    'min_insert_block_size_rows',
    'min_insert_block_size_bytes'
);

The column changed is 1 if someone changed the value from the default.

Note these points:

• Check async_insert first. On an older release it is false, so async inserts are off.
• Older releases can use a different name for async_insert_busy_timeout_max_ms.
• The two min_insert_block_size_* settings do not join separate INSERT queries. They apply when the server forms blocks, for example in INSERT ... SELECT. They do not fix a client that sends one row at a time.

How to fix it

You have two options. Use the first one when you can. Use the second one when you cannot change the client.

Option 1: Batch inserts on the client

Collect rows in the client. Send one INSERT for many rows. A good target is 10,000 to 100,000 rows for each INSERT. Send about one INSERT per second for each table.

This is the best fix. The server does less work. You also control the batch size and the retry logic.

Why these values:

• A part with 10,000 or more rows has low overhead.
• One INSERT per second gives at most 60 new parts per minute for each table and each partition. Merges can handle this rate on most hardware.

ClickHouse describes this in Selecting an insert strategy.

Option 2: Use async inserts

Async inserts move the batching to the server. The server collects data from many clients. It writes one part when the buffer flushes.

Turn on async_insert and keep wait_for_async_insert on. Choose where to set them.

A settings profile in users.xml. Use this for all queries of one user or role.

<clickhouse>
    <profiles>
        <default>
            <async_insert>1</async_insert>
            <wait_for_async_insert>1</wait_for_async_insert>
        </default>
    </profiles>
</clickhouse>

Change the profile name if your ingest user has its own profile.

A settings profile in SQL. Use this if you manage users with SQL.

CREATE SETTINGS PROFILE IF NOT EXISTS ingest_profile
SETTINGS async_insert = 1, wait_for_async_insert = 1
TO ingest_user;

SET for the session. Use this for a test in one connection.

SET async_insert = 1;
SET wait_for_async_insert = 1;

The SETTINGS clause for one query. Use this when only one writer needs async inserts.

INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (now(), 'login', 42);

Use these example values:

• async_insert = 1. This turns on the buffer. It is the main fix.
• wait_for_async_insert = 1. The client gets success only after the server writes the data. This protects you from data loss.
• async_insert_busy_timeout_max_ms = 1000. The buffer flushes at most once per second for each query shape. Latency goes up by at most one second. You get bigger parts than with 200 ms.
• async_insert_max_data_size: keep 10 MiB at first. Raise it only if parts are still small and the buffer flushes by size. A bigger buffer uses more memory.

Set the timeout in a test session:

SET async_insert_busy_timeout_max_ms = 1000;

The server keeps separate buffers for different query texts and different settings. If clients send many different INSERT texts, you still get many parts. Use the same INSERT text in all clients.

See the ClickHouse documentation on asynchronous inserts for more details.

How to verify the fix

Run the system.part_log query from "How to confirm" again. Wait 10 to 15 minutes after the change. Check two things:

• new_parts per minute goes down.
• avg_rows_per_part goes up.

Then check the active parts again. The value of MaxPartCountForPartition must stop growing. It must go down when merges catch up.

If you use async inserts, check the async insert log. First, check that the log is enabled.

SELECT name
FROM system.tables
WHERE database = 'system'
  AND name = 'asynchronous_insert_log';

If the query returns no row, the log is off. Enable it in the server configuration before you continue.

Then count flushes and rows for each table:

SELECT
    toStartOfMinute(event_time) AS minute,
    database,
    `table`,
    uniqExact(flush_query_id) AS flushes,
    count() AS async_inserts,
    sum(rows) AS total_rows,
    countIf(status != 'Ok') AS failed
FROM system.asynchronous_insert_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY minute, database, `table`
ORDER BY minute DESC, flushes DESC
LIMIT 50;

Many async_inserts for few flushes means that batching works. Compare flushes with new_parts from system.part_log. They must be close.

Check failed too. A value above zero means that some inserts failed. Read the exception column for these rows.

Risks and trade-offs

Data loss with wait_for_async_insert = 0. With this value, the server returns success when the data is in the buffer. The data is not on disk yet. If the server crashes before the flush, the server loses the buffered data. The client already received success, so it does not retry.

With this value, the client also does not see flush errors. A parsing error or a flush error shows only in the async insert log. Use wait_for_async_insert = 0 only if you accept data loss and you monitor the log. Keep it at 1 for all data that you cannot rebuild.

Latency. With wait_for_async_insert = 1, each INSERT can wait for the buffer timeout. A timeout of 1000 ms adds up to one second to the INSERT time. Design your clients for this delay.

Memory. The server holds buffered data in memory. A big async_insert_max_data_size and many different query shapes increase memory use.

Duplicates. If an INSERT times out on the client, the client can retry. The data can then arrive twice. Check how async inserts deduplicate data on your version.

Client batching is still better. Async inserts help when you cannot change the clients. If you can batch on the client, do it. You get less load on the server and clearer failure handling.

Do not raise the part limits first. A higher parts_to_throw_insert delays the error. It does not remove the cause. SELECT queries read more parts and become slower.

Summary

• Each INSERT creates at least one part for each partition it touches. Many small inserts create too many parts.
• Count new parts per minute with system.part_log. Check avg_rows_per_part.
• Best fix: batch inserts on the client. Send 10,000 to 100,000 rows about once per second for each table.
• Second fix: turn on async_insert and keep wait_for_async_insert = 1. Check the default on your version first.
• Never use wait_for_async_insert = 0 for data that you cannot lose. A server crash loses buffered data.

References

• Selecting an insert strategy
• Asynchronous Inserts (async_insert)