Fix ClickHouse "Too Many Parts" Error: Root Causes and Minimal Fixes

Quick Answer

• Conclusion: The "Too many parts" exception occurs when the number of active data parts in a ClickHouse partition or table exceeds the configured limits (parts_to_throw_insert or max_parts_in_total), typically because background merges cannot keep up with frequent small inserts.
• First checks: Run SELECT table, partition, count() FROM system.parts WHERE active GROUP BY table, partition ORDER BY count() DESC LIMIT 10; to identify which tables and partitions have excessive parts, and check system.merges for stuck merge tasks.
• Minimal fix: Batch inserts to at least 1,000 rows per statement (ideally 10,000–100,000), or enable async_insert=1 and wait_for_async_insert=1 on the server or per-query to reduce part creation frequency.
• Environment boundary: Applies to ClickHouse MergeTree-family tables (including ReplicatedMergeTree and Distributed tables) in production OLAP workloads with high-frequency writes, high-cardinality partition keys, or slow background merges.

What Problem It Solves

ClickHouse stores data in immutable parts within partitions. Each insert creates one or more new parts, and background merges consolidate them over time. When inserts arrive faster than merges can process, the number of active parts grows until it hits the hard limit, and ClickHouse throws:

Too many parts (300). Merges are processing significantly slower than inserts

This exception rejects new inserts, causing data pipeline failures and potential data loss in downstream systems. The problem typically appears in production environments with:

• High-frequency small batch inserts (e.g., per-event writes)
• High-cardinality partition keys (e.g., partitioning by user ID or request ID)
• Insufficient storage I/O or CPU for background merges
• Tables using MergeTree-family engines with aggressive partitioning

Root Cause Analysis

The exception is triggered by two server-side settings:

The root cause is almost always a mismatch between insert frequency and merge throughput. Each INSERT statement creates a new part. If you insert 1 row at a time, you create 1 part per insert. With 300 inserts per partition before merges complete, you hit the limit.

Common contributing factors:

1. Small batch sizes: Inserting row-by-row or in tiny batches creates parts faster than merges can consolidate them.
2. High-cardinality partition keys: Partitioning by fields like user_id or request_id creates thousands of partitions, each accumulating parts independently.
3. Slow merges: Low disk I/O, insufficient CPU, or heavy concurrent queries starve the background merge pool.
4. Unfinished mutations: Stuck ALTER TABLE mutations or merges can block the merge queue.

Minimal Working Configuration

Step 1: Diagnose the Problem

Identify which tables and partitions are affected:

SQL
-- Find tables with the most active parts
SELECT
    database,
    table,
    count() AS active_parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY active_parts DESC
LIMIT 10;

-- Find partitions with the most parts in a specific table
SELECT
    partition,
    count() AS active_parts
FROM system.parts
WHERE active AND table = 'your_table'
GROUP BY partition
ORDER BY active_parts DESC
LIMIT 10;

-- Check for stuck merges
SELECT *
FROM system.merges
WHERE NOT is_done;

Step 2: Apply the Minimal Fix

Option A: Batch inserts (recommended)

Instead of inserting row-by-row, accumulate data and insert in larger batches:

SQL
-- Instead of 10,000 single-row inserts:
INSERT INTO events (event_time, user_id, event_type)
VALUES
    ('2024-01-01 10:00:00', 1001, 'click'),
    ('2024-01-01 10:00:01', 1002, 'view'),
    -- ... at least 1,000 rows, ideally 10,000–100,000

Option B: Enable asynchronous inserts

Set these on the server (in config.xml under <async_insert>) or per-query:

SQL
SET async_insert = 1;
SET wait_for_async_insert = 1;

Or pass as URL parameters for the HTTP interface:

http://localhost:8123/?async_insert=1&wait_for_async_insert=1

Option C: Force a merge to recover immediately

If the table is already in a bad state, force merges to catch up:

SQL
OPTIMIZE TABLE your_table FINAL;

This blocks until the merge completes, so run it during low-traffic periods.

Parameters and Environment Variables

Set them per-query:

SQL
SET parts_to_throw_insert = 500;
SET max_parts_in_total = 200000;

Or in config.xml:

XML
<merge_tree>
    <parts_to_throw_insert>500</parts_to_throw_insert>
    <max_parts_in_total>200000</max_parts_in_total>
</merge_tree>

Common Errors and Fixes

Error: Too many parts (300). Merges are processing significantly slower than inserts

Fix: Check system.parts to find the affected table and partition. Batch inserts (at least 1,000 rows per statement) or enable async_insert=1. Verify storage I/O and disk space are adequate for background merges.

Error: Part 0 is not active, but it's not in the queue

Fix: This indicates incomplete merges or mutations. Check system.merges and system.mutations for stuck tasks. Run OPTIMIZE TABLE your_table FINAL to force pending merges, or wait for background tasks to complete.

Error: Cannot parse input: expected 'Part' before ...

Fix: This is a data format or schema mismatch. Validate the VALUES portion of your insert statement, ensuring data types align with the table schema, especially for partition key columns.

Error: Memory limit (total) exceeded: would use 9.37 GiB ... maximum: 9.31 GiB

Fix: Your batch is too large for available memory. Reduce batch size, or adjust max_memory_usage in user.xml or per-query. Do not set it so high that it causes system OOM.

Production Notes and Security Checks

Operational Guidelines

• Batch size trade-offs: Larger batches reduce part creation but increase client memory and network bandwidth usage. A single batch that is too large can cause timeouts or OOM. Start with 10,000–50,000 rows and tune based on your data size and cluster resources.
• Async insert latency: async_insert=1 adds up to 100ms or 1MB of buffered data before writing, which may be unacceptable for real-time dashboards. Set wait_for_async_insert=0 only if you can tolerate data loss on server crash.
• Partition key redesign: If a high-cardinality partition key is the root cause, you may need to rebuild the table with a lower-cardinality key (e.g., toYYYYMM(event_time) instead of user_id). Plan this as a data migration during a maintenance window.
• Limit adjustments: Raising parts_to_throw_insert or max_parts_in_total is a temporary mitigation, not a fix. Higher limits increase file descriptor usage and metadata overhead, degrading query performance and risking disk exhaustion.

Security Recommendations

• Restrict ALTER TABLE and global setting changes to admin users only.
• Use SSL-encrypted connections for client-to-server communication.
• Regularly back up monitoring data from system.parts and system.merges to track part growth trends.

FAQ

Q: Why is raising parts_to_throw_insert not the preferred solution?

A: Raising the limit only postpones the exception without addressing the root cause—frequent small inserts or a high-cardinality partition key. Higher limits increase filesystem metadata overhead, degrade query performance, and can lead to disk space exhaustion. The correct approach is to optimize the insert strategy and partition key so parts naturally stay within reasonable limits.

Q: What are the trade-offs between async_insert=1 and client-side batching?

A: Async insert lets ClickHouse buffer data server-side and write it as larger batches, requiring no client code changes. The downside is added data visibility latency (up to 100ms or 1MB of buffered data) and potential data loss on server crash unless wait_for_async_insert=1 is set (which reduces performance). Client-side batching gives you explicit control over batch size and timing but requires maintaining a buffer in your application. Choose based on your real-time requirements and data-loss tolerance.

Q: How do I know if my partition key is reasonable?

A: A good partition key has low cardinality, such as date or region. Avoid high-cardinality fields like user ID or request ID. Query system.parts to check if a single partition's part count grows continuously. Also use EXPLAIN to verify queries scan only necessary partitions—if queries frequently span many partitions, your partition key may be too granular.

Official References

• ClickHouse Knowledge Base: Exception "Too many parts"
• ClickHouse Knowledge Base: Exception "Too many parts" (alternate URL)