Resolving 'Too many parts' (Exception 252) in ClickHouse MergeTree

The Problem: Insert Failures Due to Part Accumulation

When ClickHouse returns DB::Exception: Too many parts (N), it is not a disk space error, but a flow-control mechanism. Every INSERT operation creates at least one new data part (a set of files on disk). ClickHouse relies on background merge threads to collapse these small parts into larger ones to maintain query performance.

If the rate of new parts created exceeds the rate at which the background pool can merge them, the system hits the parts_to_throw_insert limit (typically 300 per partition). At this point, ClickHouse rejects new inserts to prevent the server from crashing due to file descriptor exhaustion or catastrophic query degradation.

Diagnostic Matrix: Identifying the Root Cause

Symptom	Likely Cause	Primary Diagnostic Metric
Sudden spike in part count	Small, frequent inserts (low batch size)	system.part_log
Parts growing slowly but steadily	Over-granular partition key (e.g., by hour/minute)	system.parts (count by partition)
Merges stalled or slow	Disk I/O saturation or low background pool	system.merges / system.metric_log
Parts stuck despite low insert rate	Heavy mutations (ALTER UPDATE/DELETE)	system.mutations

Step-by-Step Recovery Process

Follow these checks in order to identify and resolve the bottleneck. Run these queries using the clickhouse-client or your preferred SQL interface with administrative privileges.

1. Quantify the Part Backlog

Determine if the issue is global or isolated to a specific partition. High part counts in a single partition usually indicate a partition key that is too granular.

SELECT
    partition,
    count()
FROM system.parts
WHERE table = 'your_table_name'
  AND active = 1
GROUP BY partition
ORDER BY count() DESC
LIMIT 10;

2. Check for Merge Activity

Verify if ClickHouse is actually attempting to merge parts. If this list is empty while part counts are high, the background pool may be exhausted or blocked.

SELECT * FROM system.merges;

3. Identify Blocking Mutations

Mutations (like ALTER TABLE ... UPDATE) can block merges of the affected parts. Check for mutations that have been running for an unusually long time.

SELECT command, is_done
FROM system.mutations
WHERE table = 'your_table_name'
  AND is_done = 0;

Applying the Fixes

Depending on the findings above, apply the corresponding resolution strategy.

Scenario A: High Part Count / Low Merge Rate

If you have a massive backlog of parts, you can force a merge for a specific partition to clear the error and resume inserts. Caution: Do not run OPTIMIZE TABLE ... FINAL on a massive table during peak load, as it creates heavy I/O pressure.

Action: Run a targeted optimize on the hot partition:

OPTIMIZE TABLE your_table_name PARTITION '2023-10-01';

Scenario B: Stuck Mutations

If a mutation is hanging and preventing merges, you may need to terminate it.

Action: Use the mutation ID found in system.mutations:

KILL MUTATION 'mutation_id';

Scenario C: Client-Side Insert Patterns

If the cause is "small inserts," the fix must happen at the producer level. ClickHouse is designed for batches of 1,000 to 100,000+ rows.

Action: Implement one of the following:

• Client-side buffering: Aggregate rows in memory before sending.
• Async Inserts: Enable set async_insert = 1;. This allows the server to buffer small inserts in memory before writing them as a single part.
• Buffer Tables: Use the Buffer engine to stage data before flushing to the MergeTree.

Long-term Prevention and Tuning

To prevent recurrence, evaluate your partition key. If you partition by toHour(timestamp) and have 5 years of data, you create thousands of partitions, each with its own parts_to_throw_insert limit, which can degrade metadata performance. Prefer toDate(timestamp) or coarser grains.

If your hardware has high I/O throughput (NVMe) but merges are slow, you can cautiously increase the background pool size in config.xml:

<background_pool_size>16</background_pool_size>

Verification and Escalation

Verification: After applying fixes, monitor the part count. Inserts should resume immediately once the active part count drops below the threshold. Verify with:

SELECT count() FROM system.parts WHERE table = 'your_table_name' AND active = 1;

Escalation: Escalate to infrastructure or database architects if:

• Part counts continue to rise despite OPTIMIZE and low insert rates.
• In a ReplicatedMergeTree setup, one replica is healthy while the other is stuck in system.replicas queue, indicating a replication lag or network failure.
• Disk I/O is consistently at 100% utilization despite minimal merge activity.