clickhouse too many parts merge errors

TL;DR

Batch inserts into fewer, larger writes (at least ~1MB per insert, ideally much more), and use async inserts or a buffer table for high-frequency small writes. Too many parts means too many tiny inserts, not too much data.

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

Use this when

• Inserts fail with error 252 too many parts
• system.parts shows thousands of active parts for a table
• You insert many small batches per second

Not for this skill when

• Queries die with memory limit exceeded
• Merges are slow but parts stay bounded
• You are sizing Kafka partitions

Steps

1. Confirm the diagnosis in system tables:

SELECT table, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE active AND database = 'analytics'
GROUP BY table ORDER BY parts DESC LIMIT 10;

Expected output: the worst tables by active part count. Hundreds of parts per partition with tiny row counts each is the classic tiny-insert pattern.

1. Batch your inserts. The rule is simple: fewer, bigger inserts:

# instead of inserting per event, buffer and flush every N seconds or M rows
buffer.append(event)
if len(buffer) >= 10000:
    client.insert("events", buffer)
    buffer.clear()

Expected output: part creation rate drops proportionally. Each insert creates at least one part per partition, so 1000 inserts/sec means 1000 parts/sec before merges catch up.

1. For high-frequency writers, turn on async inserts and let the server batch:

SET async_insert = 1;
SET wait_for_async_insert = 1;

Expected output: the server buffers small inserts and flushes them as larger parts. This is the lowest-effort fix for many small writers.

1. If merges genuinely cannot keep up, check merge pressure, not just part count:

SELECT * FROM system.merges;
SHOW SETTINGS LIKE '%merge%';

Expected output: active merges and the background pool settings. Raising number_of_free_entries_in_pool_to_execute_mutation or max_replicated_merges_in_queue helps only when merges are the bottleneck; usually the fix is still fewer inserts.

1. As emergency relief, force a dedupe-friendly optimize, but fix the writer after:

OPTIMIZE TABLE events FINAL;

Expected output: parts collapse toward one per partition. This is a bandage: without fixing insert batching, the count climbs right back.

Variant phrasings

clickhouse merges are processing significantly slower than inserts

The standard companion message to error 252. It means exactly what it says: slow down inserts or speed up merges, and slowing inserts is the reliable lever.

clickhouse too many parts per partition

Same root cause at the partition level. Check whether one partition key value receives a disproportionate share of tiny inserts.

clickhouse async insert not working

Verify the settings applied to the right user/profile, and that the client is not setting async_insert=0 per query. The server-side defaults live in the user profile XML.

Why it happens

Every insert creates new data parts, and background merges continuously combine them into larger ones. When the insert rate exceeds the merge rate for a sustained period, active parts accumulate past the max_parts_in_total style guards and the server starts rejecting writes to protect itself. The data volume is irrelevant; a million 1KB inserts hurt more than one 1GB insert.

Edge cases

• Replicated tables merge per replica, so the pressure multiplies across replicas.
• min_insert_block_size_rows and min_insert_block_size_bytes squash tiny blocks server-side, raise them as a complement to client batching.
• Mutations (ALTER UPDATE/DELETE) also create parts, heavy mutation workloads need the same batching discipline.
• TTL-driven merges add background load, check system.merges for TTL merges competing with regular ones.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_B7upejLMfL8vHS-S1lghoQ