Error 252: TOO_MANY_PARTS

подсказка

This error occurs when a table accumulates too many data parts, indicating that inserts are creating new parts faster than the background merge process can combine them. This is almost always caused by inserting data too frequently (many small inserts instead of fewer large batch inserts) or having an inappropriate partition key.

Quick reference

What you'll see:

Code: 252. DB::Exception: Too many parts (300). Merges are processing significantly slower than inserts. 
(TOO_MANY_PARTS)

Or:

Code: 252. DB::Exception: Too many parts (10004) in all partitions in total in table 'default.table_name'. 
This indicates wrong choice of partition key. The threshold can be modified with 'max_parts_in_total' setting.
(TOO_MANY_PARTS)

Most common causes:

Too many small inserts - Inserting data row-by-row or with very high frequency
Wrong partition key choice - Daily or hourly partitions creating thousands of partitions
Merge process can't keep up - Heavy queries blocking merge threads or insufficient resources
Small insert batches - Each insert creating a new part that needs merging

Quick diagnostic:

-- Check parts per partition
SELECT 
    partition,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active AND table = 'your_table'
GROUP BY partition
ORDER BY parts DESC
LIMIT 10;

-- Check merge activity
SELECT 
    table,
    elapsed,
    progress,
    num_parts,
    result_part_name
FROM system.merges;

Quick fixes:

-- 1. Manually trigger merges
OPTIMIZE TABLE your_table FINAL;

-- 2. Temporarily increase limit (emergency only)
ALTER TABLE your_table 
MODIFY SETTING parts_to_throw_insert = 600;

-- 3. Check and kill heavy queries blocking merges
SELECT query_id, query, elapsed 
FROM system.processes 
WHERE elapsed > 300;

KILL QUERY WHERE query_id = 'problem-query-id';

Long-term solution: Fix your insert pattern!

Batch inserts: 10K-500K rows per INSERT
Frequency: 1 insert every 1-2 seconds (maximum)
Use Buffer tables if you need more frequent small inserts
Use asynchronous inserts

Most common causes

1. Too many small inserts (most common root cause)

Each INSERT statement creates a new data part on disk. ClickHouse merges these parts in the background, but if you insert too frequently, parts accumulate faster than they can be merged.

Examples of problematic patterns:

Row-by-row inserts (one INSERT per row)
Inserts every second or multiple times per second
Very small batches (< 1,000 rows per INSERT)
Hundreds of concurrent INSERT queries

Why this happens:

A hypothetical example:

Time    Inserts/sec    Parts Created    Parts Merged    Net Parts
0:00    100           100              10              +90
0:01    100           100              10              +180
0:02    100           100              10              +270
0:03    100           100              10              +360 -> Error!

2. Inappropriate partition key

Using overly granular partition keys (daily, hourly, or by high-cardinality columns) creates too many partitions. Each partition has its own set of parts, multiplying the problem.

Problematic partition keys:

-- Daily partitions (creates 365+ partitions per year)
PARTITION BY toYYYYMMDD(date)

-- Hourly partitions (creates 8,760+ partitions per year)
PARTITION BY toYYYYMMDDhh(timestamp)

-- High-cardinality column
PARTITION BY user_id

-- Monthly partitions (recommended)
PARTITION BY toYYYYMM(date)

-- Or no partition at all
-- No PARTITION BY clause

3. Merge process blocked or slowed

Merges can be prevented or slowed by:

Heavy SELECT queries consuming all resources
Insufficient CPU or disk I/O
Mutations (ALTER operations) in progress
Parts with different projections that can't be merged
Maximum part size reached (parts won't merge further)

4. Wrong table engine or settings

Using special engines (AggregatingMergeTree, SummingMergeTree) with complex aggregations
Very large ORDER BY keys causing slow merges
max_bytes_to_merge_at_max_space_in_pool set too low
Insufficient background merge threads

5. Version-specific issues

Projection mismatch: Parts with different projection sets cannot be merged (see error: "Parts have different projection sets")
Small parts not merging: Parts below minimum merge size threshold won't merge even when idle

Common solutions

1. Fix your insert pattern (PRIMARY SOLUTION)

This is the #1 fix for 99% of TOO_MANY_PARTS errors.

Recommended insert pattern:

Batch size: 10,000 to 500,000 rows per INSERT
Frequency: 1 INSERT every 1-2 seconds
Format: Use bulk INSERT, not row-by-row

# WRONG: Row-by-row inserts
for row in data:
    client.execute(f"INSERT INTO table VALUES ({row})")

# CORRECT: Batch inserts
batch_size = 50000
for i in range(0, len(data), batch_size):
    batch = data[i:i+batch_size]
    client.execute("INSERT INTO table VALUES", batch)
    time.sleep(1)  # 1 second delay between batches

# WRONG: Inserting files too quickly
for file in *.csv; do
    clickhouse-client --query="INSERT INTO table FORMAT CSV" < $file
done

# CORRECT: Add delays between inserts
for file in *.csv; do
    clickhouse-client --query="INSERT INTO table FORMAT CSV" < $file
    sleep 1
done

2. Use Buffer tables for high-frequency small inserts

If you cannot change your application to batch inserts, use a Buffer table to accumulate data in memory before flushing to disk.

-- Create the main table
CREATE TABLE main_table (
    timestamp DateTime,
    user_id UInt64,
    value Float64
) ENGINE = MergeTree()
ORDER BY (user_id, timestamp);

-- Create buffer table in front
CREATE TABLE buffer_table AS main_table
ENGINE = Buffer(
    currentDatabase(), main_table,
    16,  -- num_layers
    10,  -- min_time (seconds)
    100, -- max_time (seconds)
    10000,   -- min_rows
    1000000, -- max_rows
    10000000,    -- min_bytes
    100000000    -- max_bytes
);

-- Application inserts into buffer_table
INSERT INTO buffer_table VALUES (...);

-- Queries can read from buffer_table (includes both buffered and persisted data)
SELECT * FROM buffer_table;

Buffer flushes when ANY condition is met:

Time: Every 10-100 seconds
Rows: When 10,000-1,000,000 rows accumulated
Bytes: When 10MB-100MB accumulated

3. Fix partition key (if applicable)

-- Check current partitions
SELECT 
    partition,
    count() AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active AND table = 'your_table'
GROUP BY partition
ORDER BY partition DESC
LIMIT 20;

-- If you see hundreds of partitions, you need to fix the partition key

-- Create new table with better partitioning
CREATE TABLE your_table_new AS your_table
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)  -- Monthly instead of daily
ORDER BY (user_id, date);

-- Copy data
INSERT INTO your_table_new SELECT * FROM your_table;

-- Swap tables
RENAME TABLE 
    your_table TO your_table_old,
    your_table_new TO your_table;

-- Drop old table after verification
DROP TABLE your_table_old;

4. Manually trigger merges (emergency fix)

-- Force merge all parts in a table
OPTIMIZE TABLE your_table FINAL;

-- For large tables, optimize specific partitions
OPTIMIZE TABLE your_table PARTITION '202410' FINAL;

-- On clusters
OPTIMIZE TABLE your_table ON CLUSTER 'cluster_name' FINAL;

предупреждение

OPTIMIZE TABLE FINAL can be resource-intensive and block inserts. Use during low-traffic periods.

5. Temporarily increase limits (emergency only - not a real fix)

-- Increase per-partition limit
ALTER TABLE your_table 
MODIFY SETTING parts_to_throw_insert = 600;  -- Default: 300

-- Increase total parts limit
ALTER TABLE your_table 
MODIFY SETTING max_parts_in_total = 20000;  -- Default: 10000

-- Increase delay threshold
ALTER TABLE your_table 
MODIFY SETTING parts_to_delay_insert = 300;  -- Default: 150

предупреждение

This is not a solution, it only buys time. You must fix the root cause (insert pattern or partition key).

6. Check for blocking merges

-- Check if merges are running
SELECT 
    database,
    table,
    elapsed,
    progress,
    num_parts,
    total_size_bytes_compressed,
    result_part_name,
    merge_type
FROM system.merges;

-- Check for stuck mutations
SELECT 
    database,
    table,
    mutation_id,
    command,
    create_time,
    is_done,
    latest_failed_part,
    latest_fail_reason
FROM system.mutations
WHERE is_done = 0;

-- Check merge thread activity
SELECT *
FROM system.metrics
WHERE metric LIKE '%Merge%' OR metric LIKE '%BackgroundPool%';

7. Increase merge capacity

<!-- In config.xml -->
<merge_tree>
    <!-- More threads for merges -->
    <max_bytes_to_merge_at_max_space_in_pool>161061273600</max_bytes_to_merge_at_max_space_in_pool>
    
    <!-- More concurrent merge tasks -->
    <background_pool_size>16</background_pool_size>
</merge_tree>

For ClickHouse Cloud users, contact support to adjust these settings.

Prevention tips

Understand the parts model: Every INSERT creates a new part. ClickHouse merges parts in the background. If inserts > merges, parts accumulate.
Follow the golden rule: One INSERT every 1-2 seconds, containing 10K-500K rows.
Use appropriate partition keys:
- Most tables: Monthly partitions or no partition
- Very large tables (> 1TB): Monthly is fine
- Don't partition by high-cardinality columns
- Guideline: < 1,000 total partitions
Use Buffer tables if your application requires high-frequency small inserts.

Monitor parts regularly:

-- Daily monitoring query
SELECT 
    database,
    table,
    count() AS parts,
    max(modification_time) AS latest_insert
FROM system.parts
WHERE active
GROUP BY database, table
HAVING parts > 100
ORDER BY parts DESC;

Avoid inserting to too many partitions at once: A single INSERT that touches > 100 partitions will be rejected (max_partitions_per_insert_block).
Test your workload: Before going to production, test your insert pattern to ensure merges keep up.
Scale appropriately: If you legitimately need more than 500K rows/second, you need a distributed cluster, not setting adjustments.

Understanding ClickHouse parts

What is a "part"?

A part is a directory on disk containing:

One file per column (data + compressed)
Index files
Metadata files

Example:

/var/lib/clickhouse/data/default/my_table/
├── 202410_1_1_0/    <- Part 1
├── 202410_2_2_0/    <- Part 2
├── 202410_3_3_0/    <- Part 3
└── 202410_1_3_1/    <- Merged part (contains parts 1, 2, 3)

The merge lifecycle:

Each INSERT creates a new part
Background threads select parts to merge based on size and age
Merged part replaces original parts
Old parts are deleted after a delay

Why too many parts is bad:

Slow SELECT queries (must read from many files)
Slow server startup (must enumerate all parts)
Filesystem limits (too many inodes)
Memory pressure (tracking metadata for each part)

Settings that control parts:

parts_to_delay_insert: 150 (default) - Start slowing down inserts
parts_to_throw_insert: 300 (default per-partition) - Throw error
max_parts_in_total: 10,000 (default) - Total across all partitions

Debugging steps

1. Identify which table and partition

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

-- Find partitions with most parts
SELECT 
    database,
    table,
    partition,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table, partition
HAVING parts > 50
ORDER BY parts DESC
LIMIT 20;

2. Check recent insert patterns

-- Analyze recent inserts
SELECT 
    toStartOfMinute(event_time) AS minute,
    count() AS num_inserts,
    sum(read_rows) AS total_rows,
    avg(read_rows) AS avg_rows_per_insert
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Insert'
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY minute
ORDER BY minute DESC
LIMIT 20;

3. Check merge activity

-- Current merges
SELECT * FROM system.merges;

-- Recent merge history
SELECT 
    event_time,
    duration_ms,
    table,
    partition_id,
    rows_read,
    bytes_read_uncompressed,
    peak_memory_usage
FROM system.part_log
WHERE event_type = 'MergeParts'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

-- Check for merge failures
SELECT 
    event_time,
    table,
    error,
    exception
FROM system.part_log
WHERE event_type = 'MergeParts'
  AND error > 0
ORDER BY event_time DESC
LIMIT 10;

4. Identify blocking issues

-- Check for parts that can't merge due to projection differences
-- Look in system.text_log for messages like:
-- "Can't merge parts ... Parts have different projection sets"

SELECT 
    event_time,
    message
FROM system.text_log
WHERE message LIKE '%Can''t merge parts%'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

Error 241: MEMORY_LIMIT_EXCEEDED - Often related, heavy merges consuming memory
Error 242: TABLE_IS_READ_ONLY - Can prevent merges from running

Quick reference​

Most common causes​

1. Too many small inserts (most common root cause)​

2. Inappropriate partition key​

3. Merge process blocked or slowed​

4. Wrong table engine or settings​

5. Version-specific issues​

Common solutions​

1. Fix your insert pattern (PRIMARY SOLUTION)​

2. Use Buffer tables for high-frequency small inserts​

3. Fix partition key (if applicable)​

4. Manually trigger merges (emergency fix)​

5. Temporarily increase limits (emergency only - not a real fix)​

6. Check for blocking merges​

7. Increase merge capacity​

Prevention tips​

Understanding ClickHouse parts​

Debugging steps​

1. Identify which table and partition​

2. Check recent insert patterns​

3. Check merge activity​

4. Identify blocking issues​

Related error codes​