clickhouse error: ATTACH PARTITION failed during table migration - part already exists

Not specified by the original author.

TL;DR

The part is already attached, so the migration step is telling you its work is done, not that it failed. Query system.parts to confirm the part exists with the expected row count; if the data is correct, skip the attach and continue. If the data is wrong, detach or drop the bad part first, then attach the right one. Never force-attach over a part you have not inspected.

The query

clickhouse error: ATTACH PARTITION failed during table migration - part already exists

Use this when

• clickhouse ATTACH PARTITION fails with "part already exists"
• A table migration re-runs an attach step
• Idempotency check for partition attach migrations
• An agent's clickhouse migration fails on the second run

Not for

• ATTACH PARTITION failing because the source part is missing (check the source table)
• Detached-part or filesystem permission errors (a different failure)
• Replicated-table attach quirks across replicas (check replication queue)

Steps

1. Inspect the existing part

SELECT partition_id, name, rows, active FROM system.parts
WHERE table = '[table]' AND database = '[db]' AND partition_id = '[partition]';

Expected output: the part row showing it is active with a row count. Compare the count against the source: matching counts mean the attach already succeeded.

2. Case A: the data is correct - skip the attach

If the part is active and the row count matches the source, the migration step already did its job (probably on a previous partial run). Mark the step done in whatever tracker the agent uses and continue with the next step.

Expected output: the migration proceeds without re-attaching. The error was idempotency feedback, not a failure.

3. Case B: the data is wrong - replace the part deliberately

ALTER TABLE [db].[table] DETACH PARTITION ID '[partition]';

Inspect the detached part, then attach the correct one:

ALTER TABLE [db].[table] ATTACH PARTITION ID '[partition]' FROM [db].[source_table];

Expected output: the bad part is detached (recoverable from the detached directory) and the correct part attaches cleanly.

4. Make the migration step idempotent

Guard future attach steps with a system.parts check: only attach when the part is absent or inactive. An agent re-running the migration then skips completed steps instead of erroring.

Expected output: the second run of the migration is a clean no-op on already-attached partitions.

Tool compatibility

clickhouse 23.x and 24.x. ATTACH/DETACH PARTITION and system.parts are stable across these versions.

Variant phrasings

clickhouse ATTACH PARTITION part already exists

Steps 1 and 2: confirm the part is good, then skip. The error usually means success already happened.

clickhouse attach partition idempotency

Step 4: guard the attach with a system.parts existence check so re-runs are safe.

clickhouse replace bad attached part

Step 3: detach first, verify, then attach the correct part. Detach keeps the data recoverable.

Why it happens

ClickHouse parts are immutable and identified by name; attaching a part that is already active would create two live copies of the same data, so the server refuses. The error is the database enforcing part identity, and in a migration context it almost always means a previous attempt already attached it.

Edge cases

• An active part with the wrong row count is worse than a missing part: investigate the source before replacing, or you will attach bad data twice.
• On replicated tables, check the part on the right replica: "already exists" on one replica does not mean the cluster is consistent.
• Detached parts linger in the detached directory consuming disk: clean up after you are sure the replacement is good.

Provenance

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

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

Thread query: clickhouse error: ATTACH PARTITION failed during table migration - part already exists

TL;DR: The part is already attached, so the migration step is telling you its work is done, not that it failed. Query system.parts to confirm the part exists with the expected row count; if the data is correct, skip the attach and continue. If the data is wrong, detach or drop the bad part first, then attach the right one. Never force-attach over a part you have not inspected.

Not specified by the original author.

TL;DR

Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation.

When to use

You are seeing this: Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. Use this skill when you run into "Use monthly ClickHouse partitions for the RudderStack warehouse destination".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

The problem

Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation. Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. The maintainers eventually shipped the real fix: month partitioning is now supported and can be configured during destination setup (PR 6931).

The fix

If you are sending RudderStack warehouse data to ClickHouse, avoid the old daily-partition default: ClickHouse partitions should be 30-150GB each, and daily partitions for most event volumes just pile up small parts on disk. Since you cannot change a table's partition after creation, getting this right at setup matters. Monthly partitioning is now supported and configurable during destination setup (landed in PR 6931). If you already have tables stuck on daily partitioning, the workaround users settled on is: take the CREATE sql for the RudderStack table, rewrite the partitioning, create the new table, copy the data over, drop the old table, and rename the new one to match. Automate it, because you have to repeat it every time a new event creates a new table.

Notes

Thread: https://github.com/rudderlabs/rudder-server/issues/5079. Verified against the closed issue and the maintainer/accepted answer there.

In https://github.com/rudderlabs/rudder-server/issues/5079, the reporter asked: Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation. Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. The maintainers eventually shipped the real fix: month partitioning is now supported and can be configured during destination setup (PR 6931). The verified answer: If you are sending RudderStack warehouse data to ClickHouse, avoid the old daily-partition default: ClickHouse partitions should be 30-150GB each, and daily partitions for most event volumes just pile up small parts on disk. Since you cannot change a table's partition after creation, getting this right at setup matters. Monthly partitioning is now supported and configurable during destination setup (landed in PR 6931). If you already have tables stuck on daily partitioning, the workaround users settled on is: take the CREATE sql for the RudderStack table, rewrite the partitioning, create the new table, copy the data over, drop the old table, and rename the new one to match. Source thread: https://github.com/rudderlabs/rudder-server/issues/5079

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.