Can detached parts in ClickHouse® be dropped?

Overview

This article explains detached parts in ClickHouse®: why they appear, what detached reasons mean, and how to clean up safely.

Use it when investigating:

You can perform two main operations with detached parts:

• Recovery: If you’re missing data due to misconfiguration or an error (such as connecting to the wrong ZooKeeper), check the detached parts. The missing data might be recoverable through manual intervention.

• Cleanup: Otherwise, clean up the detached parts periodically to free disk space.

Version Scope

Primary scope: ClickHouse 23.10+.

Compatibility note:

• In 22.6-23.9, there was optional timeout-based auto-removal for some detached reasons.
• In 23.10+, this option was removed; detached-part cleanup is intentionally manual.

Important distinction for ReplicatedMergeTree: ClickHouse® tracks expected parts from ZooKeeper and unexpected parts found locally:

• Broken expected parts increment the max_suspicious_broken_parts counter (can block startup).
• Broken unexpected parts use a separate counter and do not block startup.

Detailed actions based on the status of detached parts:

• Safe to delete (after validation):

  • ignored
  • clone.

• Temporary, do not delete while in progress:

  • attaching
  • deleting
  • tmp-fetch.

• Investigate before deleting:

  • broken
  • broken-on-start
  • broken-from-backup
  • covered-by-broken
  • noquorum
  • merge-not-byte-identical
  • mutate-not-byte-identical

Monitoring of detached parts

You can find information in clickhouse-server.log, for what happened when the parts were detached during startup. If clickhouse-server.log is lost it might be impossible to figure out what happened and why the parts were detached.

Another good source of information is system.part_log table, which can be used to investigate the history/timeline of specific parts involved in the detaching process:

SELECT
    event_time,
    event_type,
    database,
    `table`,
    part_name,
    partition_id,
    rows,
    size_in_bytes,
    merged_from,
    error,
    exception
FROM system.part_log
WHERE part_name IN ('all_1_5_0', 'all_6_10_1') -- example part names, replace with actual part names from detached_parts or clickhouse-server.log
ORDER BY
    part_name ASC,
    event_time ASC

Also system.detached_parts table contains useful information:

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

It is important to monitor for detached parts and act quickly when they appear. You can use system.asynchronous_metric/metric_log to track some metrics.

Use system.asynchronous_metrics for current values:

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

Use system.asynchronous_metric_log for history/trends:

SELECT
    event_time,
    metric,
    value
FROM system.asynchronous_metric_log
WHERE metric IN ('NumberOfDetachedParts', 'NumberOfDetachedByUserParts')
  AND event_time > now() - INTERVAL 24 HOUR
ORDER BY event_time DESC, metric;

DROP DETACHED command

The DROP DETACHED command in ClickHouse® is used to remove parts or partitions that have previously been detached (i.e., moved to the detached directory and forgotten by the server). The syntax is:

ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART ALL|partition_expr

This command removes the specified part or all parts of the specified partition from the detached directory. For more details on how to specify the partition expression, see the documentation on how to set the partition expression DROP DETACHED PARTITION|PART.

Note: You must have the allow_drop_detached setting enabled to use this command.

DROP DML

Here is a query that can help with investigations. It looks for active parts containing the same data blocks as the detached parts and generates commands to drop the detached parts.

SELECT a.*,
    concat('ALTER TABLE ',a.database,'.',a.table,' DROP DETACHED PART ''',a.name,''' SETTINGS allow_drop_detached=1;',
           ' -- db=',a.database,' table=',a.table,' reason=',a.reason,' partition_id=',a.partition_id,
           ' min_block=',toString(a.min_block_number),' max_block=',toString(a.max_block_number)) AS drop_command
FROM system.detached_parts AS a
LEFT JOIN (
    SELECT database, table, partition_id, name, active, min_block_number, max_block_number
    FROM system.parts
    WHERE active
) b
ON a.database = b.database AND a.table = b.table AND a.partition_id = b.partition_id
WHERE a.min_block_number IS NOT NULL
  AND a.max_block_number IS NOT NULL
  AND a.min_block_number >= b.min_block_number
  AND a.max_block_number <= b.max_block_number
ORDER BY a.table, a.min_block_number, a.max_block_number
SETTINGS join_use_nulls=1

The list of DETACH_REASONS: MergeTreePartInfo.h#L163

Rare but Important Edge Cases

1. Invalid detached part names with _tryN suffixes can produce NULL parsing metadata in system.detached_parts; treat these as a separate cleanup track.
2. Older versions had DROP DETACHED issues on ReplicatedMergeTree over S3 (without zero-copy); this was fixed in 2023.
3. Startup handling of unexpected parts was improved to restore closer ancestors instead of random covered parts.
4. Downgrade workflows may fail to ATTACH broken-on-start_* directly in some versions. Workaround is manual rename then attach:

SELECT
    concat('mv ', path, ' ', replace(path, 'broken-on-start_', '')) AS mv_cmd
FROM system.detached_parts
WHERE startsWith(name, 'broken-on-start_')