ClickHouse Detached Parts: When and How to Clean Up Safely

ClickHouse moves a data part into the detached subdirectory whenever something prevents it from being used: a checksum mismatch, a fetch from another replica that succeeded but produced a duplicate, a manual ALTER TABLE ... DETACH, or a recovery flow. The parts are not deleted automatically. Over time the detached folder can grow large enough to exhaust disk space, yet some of those parts may still be the only copy of recoverable data. The right cleanup procedure depends on the reason recorded for each part.

This guide shows how to inventory detached parts, classify them, and remove the ones that are clearly disposable.

List Detached Parts and Their Reasons

The system.detached_parts table records every detached part along with the reason it was moved. Group by reason to see the distribution before deciding what to do:

SELECT database, table, reason, count() AS parts
FROM system.detached_parts
GROUP BY database, table, reason
ORDER BY parts DESC;

Track totals at the server level via system.asynchronous_metrics:

SELECT metric, value
FROM system.asynchronous_metrics
WHERE metric IN ('NumberOfDetachedParts', 'NumberOfDetachedByUserParts');

NumberOfDetachedByUserParts counts parts that an operator detached explicitly. The remainder were produced by the engine itself.

Reason Categories

Not every reason is safe to drop. Classify each entry before you act on it.

ignored parts are duplicates or extra copies that the engine refused to attach. clone parts are local copies the server made before fetching a fresh version from another replica. Both are usually disposable.

broken and broken-on-start indicate corruption detected by checksum verification. These may still contain the only readable rows for a range and deserve a closer look before deletion.

Drop Detached Parts Safely

ClickHouse requires allow_drop_detached=1 for the destructive command. Enable it per session, then issue the drop:

SET allow_drop_detached = 1;

ALTER TABLE database.table_name DROP DETACHED PARTITION 'partition_id';
-- or
ALTER TABLE database.table_name DROP DETACHED PART 'part_name';
-- or
ALTER TABLE database.table_name DROP DETACHED PARTITION ALL;

Use PART for surgical removal of a single detached part and PARTITION to remove every detached part for a partition ID. DROP DETACHED PARTITION ALL wipes every detached part for the table, which is only appropriate when you have already validated each entry.

Recover Instead of Drop

If a detached part might contain data you still need, reattach it rather than delete it:

ALTER TABLE database.table_name ATTACH PART 'part_name';

Once attached, run a SELECT count() and a few sample queries to confirm the rows look correct, then decide whether to keep the part or detach and drop it again.

A Practical Cleanup Workflow

1. Run the system.detached_parts grouping query and save the results.
2. For each (database, table, reason) combination, decide whether the category is disposable, transient, or worth investigating.
3. Back up any partition you are unsure about with BACKUP TABLE or by copying the directory off the server.
4. Set allow_drop_detached = 1 and issue targeted DROP DETACHED PART commands.
5. Re-run the inventory query and confirm only transient reasons remain.

Common Pitfalls

• Dropping tmp-fetch parts mid-fetch corrupts the in-progress operation. Always filter by reason before issuing destructive commands.
• allow_drop_detached is per-session. Setting it globally in users.xml makes accidental drops too easy in interactive sessions.
• DROP DETACHED PARTITION ALL does not log which parts it removed. If you need an audit trail, drop parts one at a time.
• Detached parts created by ALTER TABLE ... DETACH PARTITION have an empty reason. Treat them as intentional and confirm with the team before removing.
• On disks with low free space, listing the contents of the detached directory can be cheaper than running ClickHouse queries.

Frequently Asked Questions

Q: Can detached parts in ClickHouse be dropped? A: Yes. Use ALTER TABLE ... DROP DETACHED PART|PARTITION with allow_drop_detached=1. Validate the reason first so you do not delete parts that the engine is still using or that hold recoverable data.

Q: What is the difference between DROP DETACHED PART and DROP DETACHED PARTITION? A: PART removes a single named directory under detached/. PARTITION removes every detached part with the matching partition ID. Use PART for precision and PARTITION for bulk cleanup of one logical partition.

Q: Why do I see parts with reason clone? A: ClickHouse cloned the local part to detached/ before replacing it with a freshly fetched copy from another replica, usually after a checksum mismatch. The cloned copy is preserved in case the fetch fails or operators want to inspect the original.

Q: How do I tell if a detached part still has unique data? A: Attach it to a temporary table or use ALTER TABLE ... ATTACH PART, then query a sample of rows. Compare row counts against the active partition before deciding.

Q: Will ClickHouse ever delete detached parts automatically? A: No. Detached parts persist until you run an explicit DROP DETACHED command or remove the directory from disk. Build a cleanup routine into your operational playbook.