clickhouse too many parts merge errors

Not specified by the original author.

TL;DR

Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.

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

Steps

1. Count active parts on the table: SELECT count() FROM system.parts WHERE table = 'your_table' AND active. Expected: hundreds of active parts on one partition. That is the pileup.

2. Check the insert pattern in the query log: look at INSERT frequency and rows per insert over the last hour. Expected: many inserts per second with tiny row counts, sometimes one row at a time.

3. Batch client-side. Accumulate rows and insert every few seconds instead of per event, aiming for batches of at least tens of thousands of rows. Expected: new part creation slows to a trickle and merges start catching up.

4. Alternatively, turn on async inserts (async_insert set to 1) so ClickHouse buffers small inserts into bigger parts server-side. Expected: part count drops without changing the client much, at the cost of slight insert latency.

5. As a stopgap, raise the too-many-parts threshold in the server config. Expected: the error stops, but merge lag keeps growing underneath. Only use this to buy time while you fix the inserts.

6. For replicated tables, check the merge queue: SELECT * FROM system.replicationqueue WHERE table = 'yourtable'. Expected: merges queued far behind the insert flood, confirming the bottleneck is merge throughput, not replication.

Use this when

• Inserts fail with TOOMANYPARTS naming a part count in the hundreds.
• system.parts shows hundreds of active parts on one partition.
• The query log shows a high frequency of tiny inserts.
• Merge backlog grows while insert rate stays constant.

Not for this skill when

• Merges are stuck on one giant part rather than many small ones. That is a stuck merge, a different problem.
• Parts are many but merges keep up and inserts succeed. Hundreds of parts alone is not an emergency.
• The table just finished a huge backfill. The backlog drains on its own once the flood stops.

Variant phrasings

• clickhouse TOOMANYPARTS merges slower than inserts
• clickhouse too many parts error how to fix
• clickhouse small inserts too many parts
• clickhouse async_insert batching
• clickhouse merges falling behind inserts

Why it happens

Every ClickHouse insert creates at least one new data part, and a background merge process continuously combines small parts into larger ones. Tiny frequent inserts create parts far faster than merges can combine them: the part count explodes, each query has to consult more parts, merges compete with each other for IO, and eventually the server refuses new inserts to protect itself. The insert pattern is the root cause; the merge lag is the symptom.

Edge cases

• Async inserts add a small delay before data is queryable and buffer in memory. Do not use them if you need sub-second visibility with a crash-safe guarantee.
• Deduplication on ReplicatedMergeTree happens at merge time, so tiny inserts also delay dedup. Batching fixes both.
• Partitioning by too fine a key (per minute, per user) multiplies the problem: each partition gets its own part pileup. Coarsen the partition key first.
• Materialized views with their own tiny inserts inherit the same disease. Batch the source inserts and the views calm down too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Thread query: clickhouse 'Too many parts' slowing merges: how to fix the insert pattern

TL;DR: Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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.

5. 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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Lower the query's memory footprint first (fewer columns, pre-aggregation, smaller LIMIT-free sorts), then allow disk spilling with max_bytes_before_external_group_by and max_bytes_before_external_sort. Raising max_memory_usage alone just moves the crash.

Code: 241. DB::Exception: Memory limit (for query) exceeded

Use this when

• Queries fail with error 241 memory limit exceeded
• Big GROUP BY, JOIN, or ORDER BY blows the limit
• You need the query to finish, slower, rather than die

Not for this skill when

• ClickHouse reports too many parts instead
• The query completes but is slow
• You are tuning Postgres, not ClickHouse

Steps

1. Read the error's numbers. They tell you the actual limit and usage:

Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 4.19 MiB), maximum: 10.00 GiB

Expected output: the configured max and how far over the query went. If usage is 10x the limit, restructuring beats tuning.

2. Cut the memory the query needs. The biggest wins in order:

-- select only needed columns, avoid SELECT *
-- pre-aggregate before the big join
-- add a LIMIT to exploratory queries
SELECT user_id, count() FROM events GROUP BY user_id

Expected output: the query fits. Wide rows and SELECT * through a GROUP BY are the most common avoidable blowups.

3. Enable external aggregation and sorting so the query spills to disk instead of dying:

SET max_bytes_before_external_group_by = 20000000000;
SET max_bytes_before_external_sort = 20000000000;

Expected output: GROUP BY and ORDER BY spill to disk past 20GB instead of throwing error 241. The query gets slower but completes.

4. For joins, spill the join to disk as well:

SET max_bytes_before_external_join = 20000000000;

Expected output: large joins complete via disk instead of dying. Alternatively restructure: filter the big side before the join, or use a GLOBAL IN subquery when the small side is truly small.

5. Only then consider raising the per-query limit, and do it per query, not globally:

SET max_memory_usage = 20000000000;

Expected output: a 20GB per-query cap for this session. Raising the global default invites one bad query to starve the whole server.

Variant phrasings

clickhouse memory limit for query exceeded

Error 241. Work steps 2-4 before reaching for step 5.

clickhouse group by memory limit

max_bytes_before_external_group_by is the direct fix. Also check for high-cardinality GROUP BY keys, grouping by a UUID column is often the real problem.

clickhouse join memory exceeded

Spill with max_bytes_before_external_join, or shrink the build side. The join algorithm loads one side fully into memory.

Why it happens

ClickHouse executes queries in memory for speed and enforces a per-query cap (max_memory_usage, default 10GB). GROUP BY builds a hash table of all groups, JOIN builds a hash table of one side, and ORDER BY buffers everything, so any of them can exceed the cap on large data. The external-spill settings trade speed for completion.

Edge cases

• max_memory_usage_for_user caps the user across concurrent queries, a single query can pass while the user cap still kills it.
• Dictionaries and IN-subqueries also consume memory outside the main query accounting.
• Spilling needs disk space and disk speed, on small cloud disks the spill itself can fail.
• Memory tracking is approximate, a query can die slightly under the nominal limit.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_1BhuxqMOPCVsvpU6ri2pqw

Resolved from the public thread: https://vectle.com/posts/pst_1BhuxqMOPCVsvpU6ri2pqw

Thread query: clickhouse memory limit exceeded query fix

TL;DR: Lower the query's memory footprint first (fewer columns, pre-aggregation, smaller LIMIT-free sorts), then allow disk spilling with max_bytes_before_external_group_by and max_bytes_before_external_sort. Raising max_memory_usage alone just moves the crash.