CONCEPT Cited by 1 source

ClickHouse detached partition archival¶

Definition¶

ALTER TABLE <t> DETACH PARTITION '<key>' is the ClickHouse mechanism for physically removing a whole partition from the active queryable dataset while keeping the underlying data files intact in a detached/ directory. Detached parts can then be copied to external cold storage (S3, NAS) by the operator — or re-attached with ATTACH PARTITION if they're needed again.

This gives ClickHouse a user-space cold-tier substrate without rehydration: the data is never deleted, it's moved off the hot path; queries that target it either recall it via ATTACH or query it via an external table engine pointed at the archive location.

Verbatim from the 2025-12-09 Redpanda IoT pipeline post: "ClickHouse's Table Partitioning is especially useful for storing and cataloging historical data. It enables fast pruning and detached archival, where old partitions can be physically detached from the active dataset and moved to external storage (like S3 or NAS)." (Source: sources/2025-12-09-redpanda-streaming-iot-and-event-data-into-snowflake-and-clickhouse)

Canonical example:

ALTER TABLE telemetry_events DETACH PARTITION '202501';

Lifecycle¶

Partition identified — typically on time- partitioned MergeTree by month or day, the cutoff is obvious ("everything older than N months").
DETACH PARTITION — ClickHouse renames the partition directory into the table's detached/ subdirectory. Queries on the live table no longer see its rows. The operation is metadata-only and near-instant.
Copy to external storage — operator copies the detached/<part>/ directories to S3 / NAS via external tooling (S3 CLI, rsync).
Local delete (optional) — once the copy is verified, DROP DETACHED PARTITION or manual removal of the detached/ directory frees local disk.
Re-attach on demand (optional) — if the partition is needed again for reporting, it can be copied back and ALTER TABLE <t> ATTACH PARTITION '<key>' restores it.

Why not just `DROP PARTITION`¶

Reversibility — detached partitions can be re-attached later without re-ingesting from upstream.
Separation of concerns — DETACH is a query-hiding operation; the copy-to-cold-storage step is orthogonal and the operator can sequence it however they want.
Compliance / legal-hold — data that must be preserved but not served stays intact; an ATTACH is the only path to re-expose it.
Cold-tier query via external engines — S3-backed ClickHouse S3 table function or an external Iceberg/Parquet layer can query the archived parts without re-attaching.

Composes with¶

concepts/clickhouse-mergetree-partition-by-time — partition key = archival granularity.
concepts/clickhouse-ttl-policy — TTL automates the same tier transition declaratively; DETACH is the manual equivalent for cases where the operator needs explicit control over timing / verification.
concepts/hot-cold-tier-compression-codec-split — the archival step is often paired with a compression codec change (LZ4 on hot SSD → ZSTD on cold S3).

Caveats¶

DETACH doesn't actually move bytes. The parts are still on the same filesystem until the operator copies them out. Until then, disk is not freed.
No automatic cold-tier query integration. Unlike Snowflake's transparent compute-storage separation, detached ClickHouse partitions require explicit tooling to query.
Background merges don't touch detached/. Partitions that were mid-merge when detached may be fragmented.
ATTACH requires identical schema (at attach time) — column additions/removals between detach and re-attach can break.

Seen in¶

sources/2025-12-09-redpanda-streaming-iot-and-event-data-into-snowflake-and-clickhouse — canonical wiki introduction. Redpanda IoT-pipeline tutorial post frames DETACH PARTITION as the manual cold-tier offload mechanism, paired with S3 / NAS as target external storage.