Repartitioning ClickHouse Tables Without Downtime

ClickHouse has no ALTER TABLE ... MODIFY PARTITION BY statement: the partition expression is fixed at creation time, and existing data parts are physically laid out according to it. But "you can't alter PARTITION BY" does not mean "you must drop and recreate the table." There are two production patterns that let you change a table's partitioning strategy without downtime — one that makes the partitioning adjustable by design, and one that rebuilds layout in the background using fast partition swaps. This guide covers both, when each applies, and the gotchas.

Why You Can't Just Alter PARTITION BY

The partition key determines how rows are grouped into parts on disk. Every existing part carries a partition ID derived from the original expression. Changing the expression retroactively would require re-reading and re-writing every part — exactly the work ClickHouse avoids by making partitioning immutable. This is why the partition key, like the sort key, is one of the choices that is genuinely hard to change after the fact (see CREATE TABLE design).

So the real question is not "how do I alter the partition expression" but "how do I get new data onto a different partitioning scheme, and optionally migrate old data, without a disruptive recreate-and-copy."

Pattern 1: Adjustable Partitioning via a Materialized Key Column

The trick — documented in the Altinity Knowledge Base — is to never partition by a raw expression directly. Instead, partition by a MATERIALIZED column that computes the partition key, and reference that column in PARTITION BY. Because ALTER TABLE ... MODIFY COLUMN can change a materialized column's expression as a metadata-only operation, you can change how new data is partitioned without touching the table definition's PARTITION BY clause.

Set It Up at Creation Time

CREATE TABLE tbl
(
    ts            DateTime,
    key           UInt32,
    partition_key Date MATERIALIZED toDate(toStartOfInterval(ts, toIntervalYear(1)))
)
ENGINE = MergeTree
PARTITION BY (partition_key, ignore(ts))
ORDER BY key;

Here partition_key is computed from ts (yearly buckets to start), and PARTITION BY references the column rather than the raw expression. The ignore(ts) term is a way to keep ts referenced in the partition expression without it actually contributing to the partition ID.

Change Granularity Later

When yearly partitions turn out to be too coarse, switch new data to monthly with a single metadata-only ALTER:

ALTER TABLE tbl
    MODIFY COLUMN partition_key Date
    MATERIALIZED toDate(toStartOfInterval(ts, toIntervalMonth(1)));

After this statement, newly inserted rows are partitioned monthly. Existing parts keep their original yearly partition IDs. The Altinity KB notes that the partition ID "can be generated on the application side and inserted to ClickHouse as is," which is the same idea taken one step further — compute the bucket outside the database and store it.

What This Does and Does Not Do

This is the cleanest "without downtime" answer when you can plan ahead. The cost is one extra column and a slightly unusual PARTITION BY. If your table is already live without this column, you cannot retrofit the materialized column into the existing PARTITION BY — that part of the definition is fixed — so you fall back to Pattern 2.

Pattern 2: Rebuild Into a New Layout Using Partition Swaps

When you need existing data on a new partitioning scheme — or the table was never set up with an adjustable key — the canonical approach is to build a second table with the target partitioning and move data across using fast, metadata-level partition operations rather than row-by-row copies where possible.

Step 1: Create the Target Table

CREATE TABLE events_v2
(
    event_time DateTime,
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)   -- new, coarser scheme
ORDER BY (user_id, event_time);

Step 2: Move Data In

If the source and target share the same partition key, ORDER BY, primary key, storage policy, and structure, you can use ATTACH PARTITION FROM (copies, source retained) or MOVE PARTITION TO TABLE (moves, source emptied) — both operate on whole parts and are near-instant:

-- Copies partition '202604' from events into events_v2 (source keeps its data)
ALTER TABLE events_v2 ATTACH PARTITION '202604' FROM events;

But the whole point of repartitioning is that the keys differ, so partition-level swaps usually do not apply across the scheme change itself. When the partition expressions differ, you must re-sort the data with an INSERT ... SELECT:

INSERT INTO events_v2 SELECT * FROM events;

This re-buckets every row under the new partition key. For large tables, do it partition-by-partition off the source to bound memory and let you resume on failure:

INSERT INTO events_v2
SELECT * FROM events
WHERE event_time >= '2026-04-01' AND event_time < '2026-05-01';

Step 3: Cut Over Atomically

Once events_v2 is fully populated and verified, swap names atomically (on an Atomic database):

EXCHANGE TABLES events AND events_v2;

EXCHANGE TABLES swaps the two table names in a single atomic step, so readers and writers see either the old table or the new one — never a half-migrated state. Keep the old table around briefly as a rollback path, then DROP TABLE events_v2 (now holding the old data) once you are confident.

Keeping Up With Live Writes

If the table receives continuous inserts, the INSERT ... SELECT backfill races against new data. Two common tactics:

• Dual-write to both tables from the application during the migration window, then backfill only the historical range.
• Backfill closed partitions first (months that no longer receive writes), then handle the current partition in a short maintenance window just before EXCHANGE TABLES.

Choosing Between the Two Patterns

A pragmatic hybrid: set up Pattern 1 going forward, and only run Pattern 2's INSERT ... SELECT against the historical partitions you actually care about re-bucketing.

Best Practices

1. Get partitioning right at creation when you can. Repartitioning is always more work than choosing well upfront. Monthly partitioning (toYYYYMM) is the safe default for time-series; avoid partitioning by high-cardinality identifiers. See the MergeTree guide.

2. Use the materialized-key pattern proactively on tables where you genuinely expect data volume — and therefore the ideal partition granularity — to change over time.

3. Watch part counts during and after the change. Mixing partition schemes or backfilling can spike the active part count. Monitor with count of active parts per partition and watch for too many parts.

4. Migrate partition-by-partition. Bounded INSERT ... SELECT ranges are resumable, cap memory, and let you verify row counts incrementally.

5. Verify before the swap. Compare count() and a few aggregates between old and new tables before EXCHANGE TABLES. Keep the old table as a rollback for at least a few days.

6. For replicated tables, account for convergence. Run DDL ON CLUSTER and confirm the new table and any swap have propagated to all replicas before dropping the old one. See ALTER TABLE sync modes.

Common Issues

• "Partition key does not match" on ATTACH/MOVE. ATTACH PARTITION FROM, REPLACE PARTITION, and MOVE PARTITION TO TABLE all require the source and destination to share the same partition key, ORDER BY, primary key, structure, and storage policy. Across a partitioning-scheme change these differ, so you need INSERT ... SELECT, not a partition swap.

• Trying to retrofit the materialized column into an existing PARTITION BY. You cannot change a live table's PARTITION BY clause to reference a new column — that clause is fixed. The materialized-key pattern only works if it was in place at creation time. Otherwise use Pattern 2.

• Part explosion from a too-granular new scheme. If the new key is finer (e.g. daily instead of monthly) and inserts are small, you can hit the same too-many-parts and merge-backlog problems the repartition was meant to fix. Validate the new granularity against insert batch sizes first.

• Backfill racing live writes. Without dual-write or a maintenance window, rows inserted into the old table after the backfill started are lost in the cutover. Plan the write path explicitly.

How Pulse Helps With Repartitioning

Repartitioning is risky precisely because the table spends time in a mixed or duplicated state, and the failure modes — runaway part counts, a backfill that quietly fell behind live writes, a replica that never received the swapped table — are operational, not syntactic. Pulse continuously profiles ClickHouse tables and surfaces exactly these signals: partition counts and active-part growth per table, merge backlog building up under a new finer scheme, tables whose partitioning does not fit their query and retention patterns, and DDL or table swaps that landed on some replicas but not others. That visibility turns a tense migration into one you can watch converge. Connect your ClickHouse cluster to Pulse and let it watch partitioning health before and after a change.

Frequently Asked Questions

Q: Can I change PARTITION BY with an ALTER TABLE statement?

No. ClickHouse has no ALTER TABLE ... MODIFY PARTITION BY. The partition expression is fixed at creation. You either design for adjustability with a materialized partition-key column (Pattern 1) or rebuild into a new table and swap (Pattern 2).

Q: Will changing the materialized partition-key expression rewrite my existing data?

No. MODIFY COLUMN on a materialized column is a metadata-only change. Only rows inserted after the ALTER use the new expression; existing parts keep their original partition IDs. To re-bucket old data you must explicitly INSERT ... SELECT it into a table with the new scheme.

Q: Can I ATTACH PARTITION FROM between tables with different partition keys?

No. Partition swaps (ATTACH PARTITION FROM, REPLACE PARTITION, MOVE PARTITION TO TABLE) require both tables to have the same partition key, ORDER BY, primary key, structure, and storage policy. A repartition changes the key, so you need INSERT ... SELECT to re-sort the rows.

Q: How do I cut over to the new table without downtime?

Use EXCHANGE TABLES old AND new on an Atomic database. It swaps both table names atomically, so clients always see one complete table. Keep the old data (now under the new name) as a rollback until you're confident.

Q: Is it safe to have parts under two different partition schemes in one table at once?

Yes — with the materialized-key pattern this is expected and supported. Each part is self-describing via its own partition ID. The only practical concern is that an overly granular new scheme can increase part counts, so watch merges and part totals.

Q: What if the table is replicated?

Both patterns work on ReplicatedMergeTree. The metadata ALTER and any table creation/swap should run ON CLUSTER and you should confirm convergence across replicas (alter_sync = 2) before dropping the old table. See the ALTER TABLE guide.