ATTACH PARTITION FROM: the cheapest way to move data

INSERT INTO target SELECT * FROM source reads every row, decompresses it, sorts it, compresses it again and writes it. For a table of hundreds of gigabytes that is a lot of CPU and I/O for data that already sits in the right shape. If both tables have the same structure, you can skip all of it:

ALTER TABLE target ATTACH PARTITION ID '202603' FROM source;

This adds the parts of that partition from source to target without rewriting them. On a local disk it creates hard links to the existing files, so the cost is proportional to the number of files, not the number of bytes. It is the fastest way to move data between MergeTree tables, hence the clickbait-worthy title: it is a metadata operation, not a data load.

A worked example

Two tables with the same definition, loaded in a staging table first:

CREATE TABLE metrics (ts DateTime, sensor_id UInt32, metric LowCardinality(String), value Float64)
ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (metric, sensor_id, ts);

CREATE TABLE metrics_staging AS metrics;

INSERT INTO metrics_staging
SELECT toDateTime('2026-03-01 00:00:00') + number, number % 10, 'temp', number
FROM numbers(100000);

INSERT INTO metrics_staging
SELECT toDateTime('2026-04-01 00:00:00') + number, number % 10, 'temp', number
FROM numbers(1000);

SELECT partition_id, name, rows
FROM system.parts
WHERE `table` = 'metrics_staging' AND active
ORDER BY name;

┌─partition_id─┬─name─────────┬───rows─┐
│ 202603       │ 202603_1_1_0 │ 100000 │
│ 202604       │ 202604_2_2_0 │   1000 │
└──────────────┴──────────────┴────────┘

Attach March into the production table:

ALTER TABLE metrics ATTACH PARTITION ID '202603' FROM metrics_staging;

SELECT 'metrics' AS tbl, count() AS rows FROM metrics
UNION ALL
SELECT 'metrics_staging', count() FROM metrics_staging;

┌─tbl─────────────┬───rows─┐
│ metrics_staging │ 101000 │
│ metrics         │ 100000 │
└─────────────────┴────────┘

The source still has everything. ATTACH PARTITION FROM copies; it never moves. Dropping the partition in the staging table afterwards is safe, because the parts in metrics have their own links to the files:

ALTER TABLE metrics_staging DROP PARTITION ID '202603';

After this, metrics still holds 100000 rows and metrics_staging 1000. On the local filesystem you can see why: the same data.bin has link count 2 and the same inode in both part directories.

Replacing instead of adding

ATTACH adds parts next to whatever is already in the partition. If you are rebuilding a partition and want the result to be exactly the staging contents, use REPLACE PARTITION, which swaps atomically:

ALTER TABLE metrics REPLACE PARTITION ID '202603' FROM metrics_staging;

Readers see either the old partition or the new one, never a mixture. A rebuild job becomes: build the corrected partition in staging, run the checks, then replace. Nothing in production changes until the last step.

Conditions

The destination must match the source in:

• column structure,
• partition key,
• sorting key and primary key,
• storage policy (the disk set must be compatible),
• and, if either table uses a fixed index_granularity, that setting. The destination must also contain all indexes and projections of the source.

If something differs the statement fails, so trying it is cheap. Engines may differ inside the MergeTree family: attaching a partition from a MergeTree into a ReplacingMergeTree table worked in the test on 26.4.

On a replicated table

On a Replicated* destination the replica that runs the statement checks the data and publishes the new parts to the replication log. Other replicas attach them from their own copy when the checksums match, and download them from a peer otherwise. Run it on one replica, not on all.

Things to watch

• It is not free of the usual rules. Parts keep their size and layout. If the staging table was filled by thousands of tiny inserts, you attach thousands of tiny parts. Prepare the staging table well: large inserts, then OPTIMIZE TABLE ... FINAL on the partition if needed.
• Hard links need a local filesystem. On object-storage disks the mechanism differs and cost may differ too; measure before relying on it for very large partitions. On a local disk, space is only freed when the last link to a file goes away.
• The destination partition is not locked while you prepare. Make sure no other writer touches the partition you are replacing.

When to use it

• A nightly or backfill job builds a partition in a scratch table and publishes it with REPLACE PARTITION.
• You migrate data from an old table definition to a new one with the same keys.
• You seed a test or staging environment from production tables on the same server.

If the keys or partitioning differ, you cannot use it; fall back to INSERT SELECT, in chunks of a partition at a time so each statement is a bounded unit of work.

Reference: manipulating partitions.