Analytics PipelineClickHouse & Materialized Views
Guide Real-Time Data Ingestion Pipeline Implementation

Running ALTER TABLE Migrations Without Downtime

The difference between a ClickHouse schema change that finishes in milliseconds and one that rewrites terabytes for hours comes down to a single distinction: whether the ALTER touches only table metadata or forces a mutation across every existing part. This guide separates the metadata-only operations (ADD COLUMN, most MODIFY COLUMN widening, comments and defaults) from the mutation-triggering ones (DROP COLUMN, type changes that rewrite data, ALTER ... UPDATE), shows how to apply them safely ON CLUSTER, and how to watch system.mutations so a live backfill never becomes an unplanned outage — the operational counterpart to validating payloads inside the schema validation and evolution control plane.

Prerequisites

Metadata-only vs. mutation-triggering changes

ClickHouse stores columns as independent files inside each part. That layout is why adding a column is nearly free — the server records the new column in the table metadata and synthesizes its default at read time until a background merge materializes it — while dropping one, or changing a type in a way that alters the on-disk encoding, forces the server to rewrite every affected part as a mutation. Knowing which bucket an operation falls into before you run it is the whole game: metadata-only changes are instant and safe under live traffic; mutations run asynchronously in the background and compete with ingestion and merges for I/O.

Which ALTER TABLE operations are metadata-only versus mutation-triggering An ALTER TABLE statement is classified into one of two paths. Metadata-only operations — ADD COLUMN, MODIFY COLUMN that only widens a type, MODIFY DEFAULT, and comments — update just the table metadata and complete almost instantly with no data rewrite, so they are safe under live traffic. Mutation-triggering operations — DROP COLUMN, a type change that alters on-disk encoding, and ALTER UPDATE or DELETE — enqueue an asynchronous mutation that rewrites every affected part in the background, tracked in system.mutations, competing with ingestion and merges for disk and CPU until it completes. ALTER TABLE classify op rewrite data? no Metadata-only · instant ADD COLUMN · MODIFY (widen) MODIFY DEFAULT · COMMENT safe under live traffic — no data rewrite yes Mutation · asynchronous DROP COLUMN · type re-encode ALTER UPDATE / DELETE rewrites every affected part tracked in system.mutations I/O cost

Step 1 — Add a column (metadata-only, instant)

ADD COLUMN with a default is the safest schema change there is. It updates metadata and returns immediately; existing parts are unaffected and the default is computed on read until a merge writes it. Add the column before any writer starts sending it, so producers and schema never disagree.

sql
ALTER TABLE analytics.events
    ADD COLUMN IF NOT EXISTS revenue Nullable(Float64) DEFAULT NULL AFTER event_type;

Expected: the statement completes in milliseconds. A SELECT revenue FROM analytics.events returns NULL for every pre-existing row with no rewrite. Adding a column with a non-literal DEFAULT expression (e.g. DEFAULT now()) is still metadata-only; the expression is evaluated lazily at read time.

Step 2 — Modify a column, knowing which side of the line it lands on

Widening a type in a way that does not change the encoding — Int32 → Int64, String → wider String — is metadata-only. Anything that re-encodes on disk (String → Int64, changing an Enum’s underlying width, Float64 → Decimal) enqueues a mutation.

sql
-- Metadata-only: widening, no data rewrite.
ALTER TABLE analytics.events MODIFY COLUMN user_id UInt64;

-- Mutation-triggering: re-encodes every part. Expect a background rewrite.
ALTER TABLE analytics.events MODIFY COLUMN event_type Enum8('view'=1,'checkout'=2,'signup'=3);

Before running a MODIFY COLUMN in production, rehearse it on a staging copy and confirm whether a row appears in system.mutations — that is the definitive test of which path you triggered, and it costs nothing to check first.

Step 3 — Apply the change ON CLUSTER

On a replicated deployment, run the ALTER once with ON CLUSTER so ClickHouse propagates it to every replica through Keeper, rather than issuing it per node and risking schema skew. The DDL is replicated; for ReplicatedMergeTree, mutations then run independently on each replica.

sql
ALTER TABLE analytics.events ON CLUSTER analytics_cluster
    ADD COLUMN IF NOT EXISTS revenue Nullable(Float64) DEFAULT NULL;

Confirm every replica accepted the DDL before proceeding:

sql
SELECT hostName() AS host, count() AS has_col
FROM clusterAllReplicas('analytics_cluster', system.columns)
WHERE database = 'analytics' AND table = 'events' AND name = 'revenue'
GROUP BY host;

Every host should report has_col = 1. A missing host means the DDL did not land there — usually a Keeper connectivity issue, which the Keeper quorum recovery guide covers.

Step 4 — Backfill with ALTER … UPDATE and monitor the mutation

To populate a newly added column from existing data — or to correct historical rows — use ALTER TABLE ... UPDATE. This is a mutation: it rewrites every part matching the WHERE predicate in the background. Scope the predicate as tightly as possible so you rewrite the minimum data.

sql
-- Backfill only the partitions that need it; a broad predicate rewrites everything.
ALTER TABLE analytics.events
    UPDATE revenue = 0.0
    WHERE revenue IS NULL AND event_ts >= '2026-07-01';

The statement returns immediately; the work happens asynchronously. Track it in system.mutations, which is the single source of truth for progress and failure:

sql
SELECT
    mutation_id,
    command,
    parts_to_do,                                  -- remaining parts; 0 = done
    is_done,
    latest_fail_reason                            -- non-empty means it is stuck
FROM system.mutations
WHERE database = 'analytics' AND table = 'events' AND is_done = 0
ORDER BY create_time DESC;

parts_to_do counting down to 0 with is_done = 1 means success. A non-empty latest_fail_reason (a bad expression, an out-of-range cast) means the mutation is stuck and retrying — fix the cause or cancel it, do not wait it out.

Step 5 — Control, cancel, and pace mutations

A mutation you started can be stopped. KILL MUTATION cancels one that is misbehaving or too expensive to finish during peak load; parts already rewritten stay rewritten, and the rest are abandoned.

sql
-- Cancel a runaway or mistaken mutation.
KILL MUTATION
WHERE database = 'analytics' AND table = 'events'
  AND mutation_id = '0000000042';

Pace heavy backfills so they do not starve ingestion. The relevant levers are number_of_free_entries_in_pool_to_execute_mutation (how much merge-pool headroom must exist before a mutation runs) and background_pool_size. Prefer running large backfills during low-traffic windows, and split a single enormous UPDATE into partition-scoped statements so each mutation is bounded and independently observable.

Verification

Confirm the column exists with the intended type across the whole table, and that no mutation is left hanging:

sql
SELECT name, type, default_kind, default_expression
FROM system.columns
WHERE database = 'analytics' AND table = 'events' AND name = 'revenue';

Then check that every mutation on the table has drained:

sql
SELECT
    count()                          AS total_mutations,
    countIf(is_done = 0)             AS still_running,
    countIf(latest_fail_reason != '') AS failed
FROM system.mutations
WHERE database = 'analytics' AND table = 'events';

still_running = 0 and failed = 0 is the clean end state. If a backfill ran, spot-check the data itself — SELECT count() FROM analytics.events WHERE revenue IS NULL AND event_ts >= '2026-07-01' should return 0 once the mutation from Step 4 completes.

Gotchas and edge cases

  • DROP COLUMN is a full mutation, not a metadata edit. Dropping a column rewrites every part to remove that column’s files. On a large table this is hours of background I/O — schedule it, monitor parts_to_do, and never assume it is instant the way ADD COLUMN is.
  • A stuck mutation blocks every later mutation on the table. Mutations execute in submission order per part; one wedged on latest_fail_reason stalls everything queued behind it. Watch system.mutations after any mutating ALTER, and KILL the blocker rather than piling more changes on top.
  • ADD COLUMN defaults are lazy, so a query filtering the new column can be slow at first. Until a merge materializes the default into parts, ClickHouse computes it on read. A WHERE new_col = x over historical data evaluates the default per row until merges catch up — force it with OPTIMIZE TABLE ... FINAL on a maintenance window if you need it materialized immediately.
  • ON CLUSTER DDL waits on every replica and can hang on a dead one. The statement blocks until all hosts respond (or distributed_ddl_task_timeout fires). A partitioned or offline replica leaves the ALTER pending in the Keeper DDL queue; clear the dead host or raise the timeout, and coordinate with the replica failover runbook before forcing it.

Up: Schema Validation & Evolution