Fix ClickHouse "Too Many Parts" Error Before It Stops Inserts (2026)

The ClickHouse server is refusing new inserts because the ClickHouse storage engine for a specific table has accumulated too many small data parts.

This error, Too many parts, means the database can’t keep up with merging small data files on disk into larger ones. ClickHouse stores data in immutable "parts," and for efficiency, it periodically merges these smaller parts into bigger ones. When inserts are very frequent or the merge process is lagging, the number of small parts can overwhelm the system’s ability to manage them, leading to insert failures.

Here are the most common reasons and how to fix them:

1. Insufficient Merge Worker Threads: ClickHouse uses background threads to merge data parts. If there aren’t enough threads, merging falls behind.

Diagnosis: Check the merge_thread_active and merge_thread_active_max metrics in system.merges. If merge_thread_active is consistently close to merge_thread_active_max, you need more threads.
Fix: Increase the max_concurrent_merges_in_queue setting in your ClickHouse configuration file (e.g., /etc/clickhouse-server/config.xml or a file in /etc/clickhouse-server/config.d/). A good starting point is to increase it from its default (often 16) to 32 or 64.
```
<max_concurrent_merges_in_queue>64</max_concurrent_merges_in_queue>
```
Why it works: This setting directly controls how many merge operations ClickHouse can attempt to run concurrently, allowing it to process the backlog of small parts faster.

2. Extremely High Insert Rate: If your application is sending data to ClickHouse faster than it can merge, you’ll hit this limit. This is especially true for very small, frequent inserts.

Diagnosis: Monitor the insert_rows_read and insert_bytes_read metrics in system.events for the table in question. Compare this rate to merge_rows_read and merge_bytes_read in system.merges. If inserts are significantly outpacing merges, this is the cause.
Fix: Implement batching on your application side. Instead of sending 100 rows individually, send them in a single INSERT statement as a batch of 100. Tune batch size to be between 10,000 and 100,000 rows or 50-100MB per batch.
```
-- Instead of:
-- INSERT INTO my_table VALUES (1, 'a');
-- INSERT INTO my_table VALUES (2, 'b');
-- ...

-- Do this:
INSERT INTO my_table VALUES (1, 'a'), (2, 'b'), ...;
```
Why it works: Larger batches create fewer, bigger parts, which are more efficient for ClickHouse to merge. It drastically reduces the overhead of writing and managing many small parts.

3. Inefficient MergeTree Engine Configuration (e.g., min_bytes_for_wide_part too small): The MergeTree family of engines has settings that control when ClickHouse decides to create wide parts (where columns are stored separately) versus compact parts (where columns are interleaved). If min_bytes_for_wide_part is too small, ClickHouse might create many small, wide parts, which are less efficient to merge.

Diagnosis: Examine the engine definition for your table.
```
DESCRIBE TABLE my_table;
```
Look for min_bytes_for_wide_part.
Fix: Increase min_bytes_for_wide_part to a larger value, like 1073741824 (1 GiB) or 2147483648 (2 GiB). This setting is usually configured per table or globally.
```
ALTER TABLE my_table MODIFY SETTING min_bytes_for_wide_part = 2147483648;
```
Or in config.xml:
```
<merge_tree>
    <min_bytes_for_wide_part>2147483648</min_bytes_for_wide_part>
</merge_tree>
```
Why it works: This encourages ClickHouse to delay creating wide parts until a part reaches a substantial size, reducing the number of small, inefficient parts that need merging.

4. Incorrect index_granularity for High-Cardinality Data: index_granularity determines how many rows are in each index granule. If it’s too small, it can lead to more parts being created, especially with high-cardinality primary keys.

Diagnosis: Check the index_granularity for your table.
```
DESCRIBE TABLE my_table;
```
The default is 8192.

Fix: Increase index_granularity. A common recommendation for high insert rates is 16384 or 32768.

ALTER TABLE my_table MODIFY SETTING index_granularity = 32768;

Or in config.xml:

<merge_tree>
    <index_granularity>32768</index_granularity>
</merge_tree>

Why it works: A larger index granularity means fewer index entries per part, reducing the metadata overhead and potentially the number of parts created, especially when combined with frequent inserts.

5. Disk I/O Bottleneck: If your storage is too slow, ClickHouse cannot perform merges fast enough, even with sufficient threads.

Diagnosis: Monitor disk I/O utilization (e.g., iostat -xz 1 on Linux) and latency. If disks are consistently at 100% utilization or have high read/write latencies, this is a bottleneck. Also, check system.events for read_bytes and write_bytes and compare them to your disk’s capabilities.
Fix: Upgrade to faster storage (SSDs, NVMe). If using network storage (NFS, S3), ensure it’s provisioned for sufficient IOPS and throughput.
Why it works: Faster disks allow ClickHouse to read and write data parts much more quickly, enabling merges to keep pace with insert operations.

6. Excessive ALTER TABLE ... MODIFY COLUMN or DROP COLUMN Operations: These operations can trigger background merges to rewrite parts, adding to the merge load.

Diagnosis: Check system.merges for a high number of merges related to ALTER operations. Look at system.query_log for frequent ALTER statements.
Fix: Schedule ALTER operations during off-peak hours. If possible, consolidate multiple ALTER operations into a single one. Avoid frequent column additions/deletions on very active tables.
Why it works: ALTER operations, especially DROP COLUMN, require ClickHouse to rewrite data parts to remove or modify columns. Doing this frequently on a table with many small parts exacerbates the "too many parts" problem.

After addressing these, you might encounter Memory limit exceeded errors if your system is now trying to merge massive parts simultaneously.