Resolving 'Too many parts' Errors in ClickHouse MergeTree

The Problem: Insert Blockage

When ClickHouse returns the error Too many parts, it has stopped accepting new data for a specific table. This happens because the MergeTree engine creates a new "part" (a set of files on disk) for every insert operation. If the background merge process cannot combine these parts faster than you are creating them, the system hits a safety limit to prevent the server from crashing due to too many open file handles.

The immediate takeaway is that this is rarely a hardware limitation and almost always a data ingestion pattern problem. Increasing limits provides a temporary window of availability but does not solve the underlying cause.

Diagnostic Matrix

Symptom	Likely Cause	Primary Metric to Check
Sudden insert failure on one table	Small, frequent inserts (micro-batching)	system.parts count per partition
Slow degradation across many tables	Over-partitioning (too many unique keys)	system.parts total partitions
High CPU/IO but parts still climbing	Merge thread starvation	system.metrics (MergeTreeConcurrentMerges)

Step-by-Step Diagnostic Process

1. Identify the Offending Partition

Run this query on the ClickHouse server to find which specific partition has exceeded the threshold. By default, ClickHouse triggers this error when a partition exceeds 300 active parts.

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

2. Analyze the Part Creation Rate

Check if the background merge process is keeping up. If MergeTreeConcurrentMerges is consistently at its maximum limit while the part count rises, your disk I/O may be the bottleneck. If it is low, your insert frequency is simply too high for the merge logic to prioritize.

SELECT value FROM system.metrics WHERE metric = 'MergeTreeConcurrentMerges';

3. Evaluate the Partitioning Key

Check if you are partitioning by a high-cardinality column (e.g., partition by toYYYYMMDD(timestamp) on a table with very few rows per day). If you have thousands of partitions, each with a few parts, the overhead can trigger system-wide instability even if no single partition hits 300.

Fixes Based on Findings

Finding: Small Insert Batches

If you are inserting rows one-by-one or in batches of 10–100, you are forcing ClickHouse to create too many files.

• Client-side Buffering: Accumulate data in your application and insert in blocks of at least 1,000 to 10,000 rows.
• Asynchronous Inserts: For ClickHouse versions 21.11+, enable async_insert = 1. This tells the server to buffer data in RAM and write it in larger parts automatically.

Finding: Over-Partitioning

If the system.parts query shows hundreds of distinct partitions, your partitioning key is too granular.

• Action: Redesign the table to partition by a wider window (e.g., change toYYYYMMDD to toYYYYMM). This requires creating a new table and migrating data via INSERT INTO ... SELECT.

Finding: Emergency Recovery (Temporary)

If production is down and you need to resume inserts immediately, you can raise the threshold in the users.xml or via a SET command. Warning: This increases RAM usage and slows down SELECT queries.

-- Run as a user with administrative permissions
SET parts_to_throw_insert = 500;

Verification and Limitations

To verify the fix, monitor the active_parts count over a 30-minute window. The count should stabilize or trend downward as background merges complete.

Limitations: Manual execution of OPTIMIZE TABLE ... FINAL can force parts to merge, but it is extremely resource-intensive. Avoid using this on large production tables during peak hours as it can saturate disk I/O and trigger further timeouts.

Rollback Procedure

If you modified parts_to_throw_insert, revert it to the default (300) once the ingestion pattern is fixed to prevent future memory exhaustion:

SET parts_to_throw_insert = 300;