Resolving the "Too many parts" exception in ClickHouse

Learn how to diagnose and resolve the “Too many parts” exception by batching inserts, using asynchronous inserts, and choosing an appropriate partitioning key.

What the exception means

ClickHouse throws the Too many parts exception when an INSERT would exceed a configured limit on the number of active data parts in a MergeTree table. The exception often includes the message Merges are processing significantly slower than inserts.

Each synchronous INSERT creates at least one data part. If an insert contains rows for several partition values, ClickHouse can create one part for each affected partition. Background merges combine smaller parts into larger parts, but if new parts are created faster than ClickHouse can merge them, the number of active parts continues to grow.

The exception can be triggered by either of these settings:

• parts_to_throw_insert, which limits the number of active parts in a single partition.
• max_parts_in_total, which limits the total number of active parts in a table.

Diagnose the cause

Use the following query to find partitions with the most active parts:

SELECT
    database,
    table,
    partition_id,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
  AND database = '<database_name>'
  AND table = '<table_name>'
GROUP BY
    database,
    table,
    partition_id
ORDER BY active_parts DESC;

To check the table-wide limit enforced by max_parts_in_total, count all active parts in the table:

SELECT
    database,
    table,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
  AND database = '<database_name>'
  AND table = '<table_name>'
GROUP BY
    database,
    table;

Common causes include:

• Frequent, small synchronous inserts.
• A high-cardinality partitioning key.
• Inserts that contain rows for many partition values.
• Background merges that cannot keep up because of limited storage throughput, insufficient free disk space, or other resource contention.

You can inspect currently running merges in system.merges and review the server logs for merge failures.

Resolve the exception

Batch synchronous inserts

Batch rows on the client before inserting them. Each batch should contain at least 1,000 rows and ideally 10,000–100,000 rows. Aim for approximately one synchronous INSERT per second. Fewer, larger inserts create fewer parts and reduce the work required from background merges.

Use asynchronous inserts

If client-side batching is not practical, use asynchronous inserts so that ClickHouse can batch incoming data on the server:

INSERT INTO <table_name>
SETTINGS
    async_insert = 1,
    wait_for_async_insert = 1
VALUES (...);

Keep wait_for_async_insert = 1 so that ClickHouse acknowledges an insert only after the data has been written successfully.

Review the partitioning key

Use a low-cardinality partitioning key and avoid partitioning by values such as user or request identifiers. ClickHouse merges parts only within the same partition, so a large number of partitions prevents effective merging. For guidance, see Choosing a partitioning key.

Investigate merge bottlenecks

If inserts are already batched appropriately, check storage performance, available disk space, and competing background tasks. The merge rate depends on the storage system, the table engine, the sorting key, compression, and the available CPU and I/O capacity.

Avoid increasing part limits as the primary fix

Increasing parts_to_throw_insert or max_parts_in_total does not address the cause of excessive part creation. Higher limits can delay the exception, but they can also increase filesystem and metadata overhead and reduce query performance. Change these settings only after identifying the cause and confirming that the system has enough capacity.

Verify the recovery

Run the diagnostic query again after changing the insert strategy or partitioning scheme. The number of active parts should stabilize and then decrease as background merges catch up. Continue monitoring insert failures, free disk space, and system.merges until the backlog has cleared.