ALTER TABLE ... FETCH PARTITION: Replication Data Synchronization

ALTER TABLE ... FETCH PARTITION downloads a partition (or a single part) from another replica's ZooKeeper/Keeper path and places it in the local table's detached directory. It is the building block for moving data between Replicated* tables that don't share replication state — across clusters, across shards, or into a freshly created table during recovery.

FETCH is a copy from a remote replica operation. It pulls data over the network from whichever healthy replica is registered under the ZooKeeper path you point it at, without touching the source. This distinguishes it from ATTACH PARTITION FROM, which only works between tables that already have the parts on the same server.

Syntax

ALTER TABLE table_name [ON CLUSTER cluster]
    FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper';

• partition_expr is the partition ID (or the partition expression in single quotes), or a part name when using FETCH PART.
• path-in-zookeeper is the ZooKeeper/Keeper table path of the source replica set — for example /clickhouse/tables/01-01/visits. You point at the shard's table path, not at an individual replica.

A typical fetch-then-attach pair:

ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PARTITION 201902;

You can also fetch and attach a single part:

ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PART 201901_2_2_0;

How FETCH Works

The operation runs in three stages:

1. Validation — ClickHouse checks that the partition exists under the given ZooKeeper path and that the source table structure matches the local table.
2. Download — the data is pulled from the most appropriate healthy replica, selected automatically from those registered under the path. The downloaded files land in the table's detached/ directory.
3. Attach — a separate ATTACH PARTITION|PART promotes the detached data into the active table.

Two properties matter for planning a migration:

• FETCH only works on Replicated* tables, because it reads replica metadata from ZooKeeper/Keeper. A plain MergeTree table has no ZooKeeper path to fetch from or into.
• FETCH is not itself replicated. It places parts into the detached directory of the local server only. The subsequent ATTACH, however, is replicated — once you attach on one replica, the data propagates to the other replicas of the destination table normally.

This asymmetry is the key mental model: fetch is a local pull, attach is a cluster-wide commit.

FETCH vs. ATTACH PARTITION FROM vs. Backup

These three approaches all move partition data, but they solve different problems.

Rule of thumb: reach for FETCH when the data lives on a different replicated cluster or a replica you want to pull from. Use ATTACH PARTITION FROM when both tables sit on the same node (it's a near-instant hardlink copy). Use ClickHouse backups for durable point-in-time recovery and cold storage. See ALTER TABLE in ClickHouse for the full family of partition manipulation commands.

Migration Workflow

A common task is moving data from an old replicated cluster into a new one. The destination table must already exist with a matching schema and its own Replicated* engine.

1. Find the source ZooKeeper path

On the source cluster, read the path and replica name from system.replicas:

SELECT database, table, zookeeper_path, replica_name
FROM system.replicas
WHERE database = 'db1';

2. List partitions to migrate

SELECT DISTINCT database, table, partition_id
FROM system.parts
WHERE database = 'db1' AND active
ORDER BY partition_id;

3. Generate fetch + attach statements

For many partitions, generate the DDL rather than hand-writing it. Run this on the source to emit ready-to-paste statements:

SELECT DISTINCT
    'ALTER TABLE ' || database || '.' || table ||
    ' FETCH PARTITION ' || quote(partition_id) || ' FROM ' || quote(zookeeper_path) || ';\n' ||
    'ALTER TABLE ' || database || '.' || table ||
    ' ATTACH PARTITION ' || quote(partition_id) || ';'
FROM system.parts
INNER JOIN system.replicas USING (database, table)
WHERE database = 'db1' AND active
FORMAT TSVRaw;

Run the generated statements on the destination cluster, which must have network access to the source's ZooKeeper/Keeper and replicas. Adding ORDER BY rand() spreads load if you parallelize across many tables.

4. Check for stale detached parts first

Before fetching, make sure the destination's detached directory is clean — leftover parts from a previous run can get attached alongside the new ones:

SELECT database, table, partition_id, name, reason
FROM system.detached_parts
WHERE database = 'db1';

Remove unwanted entries with ALTER TABLE ... DROP DETACHED PART '...' (requires allow_drop_detached = 1) before proceeding.

5. Verify after attaching

-- New parts should now be active:
SELECT partition_id, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE database = 'db1' AND table = 'users' AND active
GROUP BY partition_id
ORDER BY partition_id;

-- Nothing should be left stranded in detached:
SELECT count() FROM system.detached_parts
WHERE database = 'db1' AND table = 'users';

Monitoring Fetch Progress

FETCH of a large partition can take a while because it transfers all parts over the network. Track in-flight fetches and throughput:

-- Active fetch operations and their progress:
SELECT
    database, table, partition_id, result_part_name,
    elapsed, formatReadableSize(bytes_read_compressed) AS read,
    formatReadableSize(total_size_bytes_compressed) AS total
FROM system.replicated_fetches;

-- The mutation/replication command itself, if still running:
SELECT command, is_done, last_exception
FROM system.replication_queue
WHERE database = 'db1' AND table = 'users';

system.replicated_fetches shows live byte counts so you can estimate completion. If a fetch errors, the cause surfaces in last_exception and in the server log.

Common Issues

• Table is not replicated — FETCH requires a Replicated* engine on both ends. Convert a plain MergeTree first; see converting MergeTree to Replicated.
• No active replica has part ... / structure mismatch — the partition isn't present under the path, all source replicas are unhealthy, or column types differ. Confirm the path with system.replicas and that schemas match exactly.
• Wrong ZooKeeper path — point at the shard's table path (the zookeeper_path value), not at a .../replicas/<name> sub-path. A bad path fails validation.
• Attached partition is incomplete — if you fetched while the source was still merging, you may pull intermediate parts. Re-running FETCH is safe; it overwrites the detached copy.
• Detached parts left behind after ATTACH — usually means a part name collided or was already detached for another reason. Inspect system.detached_parts.reason before re-attaching. For replica-level resync issues, see ClickHouse replication problems diagnosis.

Best Practices

1. Pre-create the destination schema with an identical ORDER BY, PARTITION BY, and column set. Structure mismatches abort the fetch.
2. Clean detached before each run so stale parts don't get attached with fresh data.
3. Verify, then drop the source — only after confirming row counts on the destination should you decommission the old cluster (SYSTEM DROP REPLICA ... FROM ZKPATH ... clears replica metadata without deleting data).
4. Throttle large migrations with max_replicated_fetches_network_bandwidth if the fetch competes with production traffic.
5. Prefer ATTACH PARTITION FROM for same-server copies — it's effectively free (hardlinks) versus a network transfer.
6. Treat FETCH as a building block, not a backup. For durable, schedulable recovery points, use incremental backups instead.

For background on how replication state, queues, and Keeper coordinate these operations, see the ClickHouse replication guide.

How Pulse Helps

Partition-level migrations and replica resyncs are easy to start and hard to verify. Pulse continuously monitors your ClickHouse replication health — surfacing stuck system.replication_queue entries, lagging fetches in system.replicated_fetches, and unexpected growth in system.detached_parts — so you can tell a migration apart from a real replication fault. When a FETCH/ATTACH workflow leaves parts stranded or a replica falls behind, Pulse flags it with the exact table and partition involved, and our team can advise on the safest recovery path. Learn more at pulse.support.

Frequently Asked Questions

Q: Does FETCH PARTITION delete the data from the source?

No. FETCH is a copy — it downloads parts to the local detached directory and leaves the source untouched. If you want to move data and delete it from the source, that's a separate operation; MOVE PARTITION TO TABLE moves a partition to another table and deletes it from the source (both tables must reside on the same server and share identical schema, partition key, and storage policy). Note that MOVE PARTITION (without TO TABLE) is a different command — it relocates a partition to a different disk or volume within the same table and does not delete anything from a source table.

Q: Why does FETCH only work on Replicated tables?

FETCH reads the list of healthy replicas and their parts from the ZooKeeper/Keeper path. A plain MergeTree table has no such path, so there is nothing to fetch from or into. Convert the table to ReplicatedMergeTree first if you need cross-cluster transfer.

Q: Is the FETCH operation replicated to other replicas?

No. FETCH places parts in the detached directory of the local server only. The subsequent ATTACH PARTITION is replicated, so once you attach on one node the data propagates to the other replicas of the destination table.

Q: How do I find the correct ZooKeeper path to fetch from?

Query SELECT zookeeper_path FROM system.replicas on the source cluster. Use that value verbatim in FROM '...'. Point at the shard's table path (e.g. /clickhouse/tables/01-01/visits), not at an individual replica sub-path.

Q: Can I monitor how far along a large fetch is?

Yes. Query system.replicated_fetches, which shows elapsed, bytes_read_compressed, and total_size_bytes_compressed for each in-flight fetch. The replication command itself appears in system.replication_queue with is_done and last_exception.

Q: What's the difference between FETCH PARTITION and ATTACH PARTITION FROM?

FETCH PARTITION downloads parts from a remote replica over the network and works only on Replicated* tables. ATTACH PARTITION FROM copies parts between two tables that already exist on the same server (using fast hardlinks) and works on any MergeTree-family table. Use FETCH to cross clusters; use ATTACH ... FROM for local copies.