ClickHouse ALTER TABLE statements look like ordinary DDL, but their real cost ranges from a sub-millisecond metadata update to a multi-hour, blocking rewrite of every data part. The cost class of an operation is determined by what happens to data parts, not by the syntax on the wire, so a MODIFY COLUMN and an ADD COLUMN can sit in opposite corners of the safety spectrum. On SharedMergeTree, the same class also propagates differently, because metadata goes through Keeper while rewrites happen per replica. This article maps every common ALTER to its cost class, explains how that class shifts between MergeTree and SharedMergeTree, and closes with a decision matrix operators can apply before running a migration against a live cluster.
Every ALTER TABLE statement in ClickHouse falls into exactly one of three cost classes, and the class — not the keyword in the SQL — determines whether the operation is safe to run during business hours. The taxonomy comes from the gap analysis in clickhouse/clickhouse#105539, where maintainers acknowledge that the official documentation describes individual commands without unifying them under a cost framework.
Metadata-only. These statements update only the table's metadata catalog; no data part on disk is touched. They do not block the issuing client and complete in microseconds. Typical examples include ADD COLUMN (including columns with DEFAULT expressions that can be evaluated at read), RENAME COLUMN, DROP COLUMN, and MODIFY COLUMN when the new type is compatible with the old one (for example, switching a column to Nullable or changing only a DEFAULT expression).
Async mutation. These enqueue a mutation entry and return to the client immediately, while the actual work — rewriting whole data parts — proceeds in the background, similar to a merge. The statement does not block, yet typical duration ranges from minutes to hours depending on data volume and part count. The canonical examples are ALTER TABLE … DELETE WHERE and ALTER TABLE … UPDATE WHERE, along with MATERIALIZE INDEX, MATERIALIZE PROJECTION, and MATERIALIZE COLUMN. On SharedMergeTree, the propagation speed of these is governed by the mutations_sync setting; on classic replicated MergeTree, the entry is written to ZooKeeper and applied by each replica independently.
Sync part rewrite. These require the server to rewrite data parts inline before acknowledging the statement. The client blocks for the full duration, which again spans minutes to hours. The defining case is MODIFY COLUMN with a non-compatible type conversion (for instance, UInt32 → String), where each existing part must be re-encoded. Unlike the previous two classes, the cost is paid before the DDL returns, and on SharedMergeTree it must be paid once per replica because each replica holds its own copy of the data parts.
The SharedMergeTree wrinkle previewed here is that the propagation channel differs per class: metadata-only changes ride Keeper and are visible cluster-wide almost instantly; async mutations follow the mutations_sync background path; and sync rewrites repeat per replica. The same logical ALTER therefore leaves a very different latency footprint on ClickHouse Cloud versus a self-hosted cluster, and that is what the rest of this article unpacks.
A subset of ALTER TABLE statements never touch the bytes of a data part. They update the table's structure descriptor in memory and on disk, then return. Because no part files are rewritten, no merge is blocked, and no SELECT is paused, these statements complete on the order of microseconds. On SharedMergeTree, the same statements propagate instantly through Keeper rather than being applied per replica, which makes them the only class that is genuinely safe to run against a live cluster at any time of day.
ADD COLUMN — Registers a new column in the metadata. Whether the column carries a DEFAULT, a materialized expression, or an ALIAS, the existing parts are not rewritten. The default is computed lazily on read for old rows and on insert for new rows, so the statement itself is metadata-only even when the expression is non-trivial.RENAME COLUMN — Updates the column name in the metadata only. Column files keep their on-disk identifiers, and no part-level work is scheduled.MODIFY COLUMN (compatible change) — Toggling Nullable, changing a DEFAULT, adjusting TTL, or modifying a column codec that does not require a type change updates metadata only. The statement blocks nothing and finishes in microseconds.COMMENT COLUMN — Stores a string in the table metadata; no data files are read or written.CLEAR COLUMN on a partition — Resets column values inside a single partition, which is implemented as a metadata action plus a small per-part fix-up; it is in practice metadata-only at the table level.MODIFY COLUMN REMOVE <attribute> — Stripping a single attribute such as DEFAULT, ALIAS, MATERIALIZED, CODEC, or COMMENT from one column is a metadata edit only.DROP COLUMN belongs here tooDROP COLUMN looks dangerous, but ClickHouse stores each column as a separate file inside every data part, so a drop is effectively a per-part file delete rather than a row-level rewrite. According to the OneUptime guide on ClickHouse ALTER operations, this is why DROP COLUMN on a MergeTree table typically completes almost instantly even on tables holding terabytes of data. The cost scales with the number of parts, not the number of rows, and INSERT traffic is not blocked.
On SharedMergeTree, every entry in the green tier above propagates through Keeper the moment the DDL is committed, so all replicas see the new schema without any background synchronization. This is the only cost class where the operator can ignore the cluster size when scheduling the change.
A handful of ClickHouse ALTER statements look instant on the wire but quietly schedule a full part rewrite in the background. The cluster pays the bill in CPU, disk I/O, and merge bandwidth for minutes to hours after the DDL returns, which makes this cost class the most operationally deceptive of the three. Operators tend to treat the green check on the client as a green light for production; it is not.
The following operations are all async mutations under the hood:
ALTER ... DELETE WHEREALTER ... UPDATE WHEREMATERIALIZE COLUMN (used to backfill values into parts written before an ADD COLUMN)MATERIALIZE INDEXMATERIALIZE PROJECTIONAPPLY DELETED MASKAPPLY PATCHESCLEAR STATISTIC and MATERIALIZE STATISTICBecause MergeTree parts are immutable, every mutation reads each affected part, applies the change, and writes a new replacement part (ClickHouse ALTER reference). The ALTER itself is not blocked, but the rewrite is a background process comparable in cost to a large merge, and in many conditions it amounts to a full-table rewrite (BigData Boutique on managing mutations). For DELETE WHERE and UPDATE WHERE, duration of minutes to hours is typical (clickhouse/clickhouse#105539).
The partial-visibility gotcha is the real foot-gun. Mutations are applied part by part in submission order, and there is no atomicity boundary: a SELECT running mid-mutation will see already-rewritten parts alongside not-yet-rewritten parts (ClickHouse ALTER reference). A DELETE WHERE is therefore not a transactional operation, and any read path or downstream consumer must tolerate the intermediate state. Inserted data is also only partially affected: rows inserted before the mutation was submitted will be mutated, rows inserted after will not.
On SharedMergeTree, the propagation axis shifts again. Metadata-only ALTERs ride through Keeper almost instantly, but async mutations rely on background synchronization across replicas, controlled by the per-session mutations_sync setting (clickhouse/clickhouse#105539, ALTER UPDATE reference). The default is asynchronous, so the statement returns well before every replica has applied the rewrite. Raising mutations_sync makes the client wait for the mutation to land on all replicas, but it does not shorten the work itself, which still typically runs for minutes to hours.
This tier is the yellow zone: safe to schedule, not safe to ignore. The minimum hygiene is to monitor system.mutations while the operation runs, confirm the affected part count is what you expected, and ensure downstream consumers can tolerate partially mutated data. Anything that cannot accept that intermediate state should either be paused for the duration or reclassified as a scheduled maintenance window.
Sync part rewrites are the red tier of ALTER TABLE cost classes. Unlike metadata-only changes that return in microseconds, or async mutations that return immediately and apply in the background, operations in this class hold the client connection open until every affected data part has been read, transformed, and written back to disk. For large tables that means minutes to hours of blocked wall-clock time, with active CPU and I/O pressure that competes with live query traffic. Per the cost-class issue tracking operator reports (clickhouse/clickhouse#105539), this tier is the most common source of production incidents when teams assume a schema migration will be cheap.
MODIFY COLUMNThe clearest example is MODIFY COLUMN with a true type conversion where the on-disk encoding is incompatible, for example String to UInt64, or UInt32 to Int64. Because MergeTree parts are immutable, every existing part must be rewritten with the new binary encoding before the statement returns. Operators should distinguish this from compatible changes (such as toggling Nullable, or altering default expressions), which remain metadata-only and complete in microseconds.
ADD COLUMN with a heavy defaultADD COLUMN is usually safe, but it slips into the sync-rewrite class when the default expression cannot be evaluated lazily. A non-deterministic expression — now(), rand(), or any function whose value depends on context — forces ClickHouse to materialize the value into every existing row at ALTER time, effectively performing a full table rewrite rather than computing the default at read or insert. Static literals and deterministic expressions, by contrast, can be stored as metadata only.
DROP PARTITION on large partitionsDROP PARTITION looks like a metadata delete, but for a multi-partition table where the dropped partition spans many parts, it behaves like a sync rewrite of the removed region: all the parts must be located, unlinked, and the partition marker must be cleared before the statement completes. On tables where a single partition holds a large share of total data, this can take as long as a full MODIFY COLUMN rewrite of a comparable volume.
On SharedMergeTree, the cluster-wide cost is multiplied by the replica count. Metadata-only changes propagate instantly through Keeper, and async mutations synchronize in the background via mutations_sync. Sync rewrites, however, must be executed independently on every replica, so an operation that blocks one node for an hour blocks every replica in parallel, and the cluster cannot service reads from a replica that is still mid-rewrite. Plan replica-aware maintenance windows accordingly.
Run red-tier operations only in a maintenance window, or — preferably — against a shadow table that receives replicated traffic and can be cut over after validation. Never combine a sync rewrite with a live read-heavy workload on the same replica.
On SharedMergeTree, the wire-level syntax of an ALTER TABLE statement is identical to vanilla ReplicatedMergeTree, but the propagation mechanism splits by cost class rather than by statement type.
Metadata-only changes (for example, ADD COLUMN, RENAME COLUMN, and compatible MODIFY COLUMN such as changing a default value or toggling Nullable) touch no data parts. The initiator writes a single metadata record to Keeper (ClickHouse Keeper or ZooKeeper), every replica reads it on its next metadata refresh, and the new schema is observable across the cluster within milliseconds. A Cloud user adding a column can therefore read it from any replica immediately after the statement returns. This channel effectively turns metadata-only DDL into a cluster-wide configuration push rather than a replication job.
Async mutations (ALTER … DELETE WHERE, ALTER … UPDATE WHERE, MATERIALIZE INDEX, MATERIALIZE COLUMN, and similar) are queued on the initiator and then propagated as mutation entries that each replica schedules independently. The cluster-wide synchronicity is governed by the mutations_sync setting, which controls how aggressively replicas wait for one another before acknowledging the mutation. Because scheduling is per-replica and depends on background merge bandwidth, replication can lag the initiator by anywhere from seconds to the time required to rewrite one part on each replica. Mutations do not block inserts, and they are totally ordered by creation time on each replica, but a SELECT running mid-mutation will see partial visibility because mutated and not-yet-mutated parts coexist. Operators are expected to monitor via system.mutations rather than wait on the originating session.
Sync part rewrites (most commonly MODIFY COLUMN with an incompatible type conversion, such as String → FixedString of a different size, or numeric widening that requires value rewriting) propagate through Keeper only as an instruction; the actual data rewrite happens locally on every replica. Each replica must independently read every existing part, write replacement parts, and atomically swap them in. Total wall-clock cost therefore grows roughly linearly with replica count, and because rewrite speed depends on local disk and CPU contention, replicas can finish at noticeably different times, producing a window of replica skew during which different nodes return different values for the same column.
The operator implications follow directly:
For deeper mechanics and version-specific tuning, see the sibling article on SharedMergeTree propagation and the ALTER statements reference.
The matrix below is the single artifact to consult before submitting any ALTER. Rows are the most common ALTER families an operator will issue against a MergeTree or SharedMergeTree table. Each row combines a cost class with a one-line verdict that fits a runbook entry.
| ALTER family | Cost class | Blocks statement? | MergeTree typical duration | SharedMergeTree propagation | Business-hours verdict |
|---|---|---|---|---|---|
ADD COLUMN | Metadata-only | No | µs | Instant via Keeper | Safe, no monitoring needed |
RENAME COLUMN | Metadata-only | No | µs | Instant via Keeper | Safe, no monitoring needed |
MODIFY COLUMN (compatible: default, Nullable, codec) | Metadata-only | No | µs | Instant via Keeper | Safe, no monitoring needed |
MODIFY COLUMN (type conversion, e.g. UInt32 → String) | Sync part rewrite | Yes | Minutes to hours | Per-replica rewrite | Schedule out of hours or use shadow-table swap |
DROP COLUMN | Async mutation | No (mutation runs in background) | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
DELETE WHERE | Async mutation | No | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
UPDATE WHERE | Async mutation | No | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
MATERIALIZE COLUMN | Async mutation | No | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
MATERIALIZE INDEX | Async mutation | No | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
MATERIALIZE PROJECTION | Async mutation | No | Minutes to hours | Background sync via mutations_sync | Safe to submit, monitor system.mutations and disk I/O |
ATTACH/DETACH PARTITION | Metadata-only | No | µs | Instant via Keeper | Safe, no monitoring needed |
The two settings to internalize are mutations_sync and alter_sync. mutations_sync governs the mutation-class statements (DELETE, UPDATE, the three MATERIALIZE forms, plus APPLY DELETED MASK, APPLY PATCHES, and MATERIALIZE STATISTIC); it defaults to 0 (async), so the statement returns before the rewrite finishes. alter_sync governs metadata-only statements on replicated tables and lets a metadata-only ALTER optionally wait until all replicas have observed the change through Keeper. Both apply only to replicated engines; on non-replicated tables every ALTER runs synchronously (ClickHouse ALTER reference, mutations_sync, alter_sync). Progress is observed through the system.mutations table (system.mutations).
mutations_sync = 0 (default async, fire-and-forget) or to wait for completion before declaring the migration done.