ClickHouse ALTER TABLE: Columns, Partitions, Mutations, and Best Practices

ALTER TABLE in ClickHouse covers a wide range of operations: adding and removing columns, changing indexes and projections, modifying the sort key, manipulating partitions, updating TTL, and running mutations (asynchronous data rewrites for DELETE and UPDATE). The semantics differ sharply between metadata-only changes (fast, blocking, applied immediately) and mutations (asynchronous, rewrite whole parts, take time). Getting this distinction right is the difference between a smooth schema change and a multi-hour stall. This guide covers every category, the gotchas, and the patterns that work on production-sized tables.

Two Categories of ALTER

The docs put it cleanly: "ALTER queries that are intended to manipulate table data are implemented with a mechanism called 'mutations'". Everything else is a synchronous metadata change.

Mutations are not atomic across a table: "concurrent SELECT queries may see mixed mutated and non-mutated data."

Column Operations

Adding a column

ALTER TABLE analytics.events
    ADD COLUMN ip_country LowCardinality(String) DEFAULT '';

Fast and metadata-only. The column is added with the specified default; existing rows return the default value at query time. To actually write the default into each part, follow with:

ALTER TABLE analytics.events MATERIALIZE COLUMN ip_country;

That second statement is a mutation and can take a while on large tables.

Adding a column at a specific position

ALTER TABLE analytics.events
    ADD COLUMN session_id UUID AFTER user_id;

FIRST and AFTER <column> control positioning. Position is purely a display convenience for SELECT *; storage layout is unaffected.

Dropping a column

ALTER TABLE analytics.events DROP COLUMN debug_payload;

Metadata-only and fast. You cannot drop a column that is part of the primary key, the sort key, the partition key, or a sampling key.

Renaming a column

ALTER TABLE analytics.events RENAME COLUMN payload TO payload_json;

Fast on Atomic databases. Supported on MergeTree-family tables.

Modifying a column type

ALTER TABLE analytics.events MODIFY COLUMN user_id Int64;

This works if the new type can be cast losslessly from the old one (Int32 to Int64, String to FixedString(N) of sufficient size, etc.). For incompatible changes, ClickHouse rejects the statement; the recourse is to add a new column, backfill, and drop the old one.

Materialized and default expressions

ALTER TABLE analytics.events MODIFY COLUMN day Date MATERIALIZED toDate(event_time);
ALTER TABLE analytics.events MODIFY COLUMN payload String DEFAULT '';

Changes the expression. Existing data is not automatically rewritten; use MATERIALIZE COLUMN day to backfill.

Partition Operations

ClickHouse exposes partition operations as a distinct category. They are powerful because they manipulate whole data parts without reading every row.

-- Move data into another table that has the same structure (atomic, fast)
ALTER TABLE analytics.events_archive ATTACH PARTITION '202604' FROM analytics.events;
ALTER TABLE analytics.events DETACH PARTITION '202604';
ALTER TABLE analytics.events DROP PARTITION '202603';

-- Freeze for backup
ALTER TABLE analytics.events FREEZE PARTITION '202604';

-- Replace one partition's contents with another's (atomic on Atomic engine)
ALTER TABLE analytics.events REPLACE PARTITION '202604' FROM analytics.events_v2;

For time-series tables, DROP PARTITION is the right tool for retention; it is dramatically faster than DELETE and reclaims disk immediately. Pair with monthly partitioning (PARTITION BY toYYYYMM(event_time)) for predictable retention behavior.

Index, Projection, and Constraint Operations

-- Skip index
ALTER TABLE analytics.events ADD INDEX idx_user user_id TYPE minmax GRANULARITY 4;
ALTER TABLE analytics.events MATERIALIZE INDEX idx_user;  -- mutation; builds for existing data
ALTER TABLE analytics.events DROP INDEX idx_user;

-- Projection
ALTER TABLE analytics.events ADD PROJECTION by_user (SELECT * ORDER BY user_id, event_time);
ALTER TABLE analytics.events MATERIALIZE PROJECTION by_user;
ALTER TABLE analytics.events DROP PROJECTION by_user;

-- Constraint
ALTER TABLE analytics.events ADD CONSTRAINT no_future CHECK event_time <= now() + INTERVAL 1 DAY;
ALTER TABLE analytics.events DROP CONSTRAINT no_future;

ADD INDEX and ADD PROJECTION are metadata-only; they apply to new writes immediately. To make them apply to existing parts, follow with MATERIALIZE INDEX or MATERIALIZE PROJECTION (mutations).

Modifying the Sort Key

ALTER TABLE analytics.events MODIFY ORDER BY (event_time, user_id, session_id);

You can extend the sort key by appending columns that:

• Were already present in the column list.
• Have DEFAULT or MATERIALIZED expressions.
• Were added in the same ALTER as new columns.

You cannot remove or reorder existing key columns. For a real reorder, rebuild the table.

TTL and Settings

ALTER TABLE analytics.events
    MODIFY TTL event_time + INTERVAL 90 DAY DELETE,
              event_time + INTERVAL 30 DAY TO VOLUME 'cold';

ALTER TABLE analytics.events MODIFY SETTING merge_with_ttl_timeout = 3600;
ALTER TABLE analytics.events RESET SETTING merge_with_ttl_timeout;

TTL changes are metadata-only; the new policy applies to subsequent merges. To force application to existing parts: ALTER TABLE ... MATERIALIZE TTL (a mutation).

Mutations: UPDATE and DELETE

ALTER TABLE analytics.events DELETE WHERE event_time < '2025-01-01';
ALTER TABLE analytics.events UPDATE user_id = 0 WHERE user_id IS NULL;

Both are mutations. They run asynchronously in the background and rewrite entire data parts that match the predicate. Track with:

SELECT database, table, mutation_id, command, parts_to_do, is_done
FROM system.mutations
WHERE table = 'events' AND NOT is_done;

To wait until a mutation finishes, set mutations_sync before the statement:

SET mutations_sync = 1;             -- wait on the current replica
SET mutations_sync = 2;             -- wait on all replicas
ALTER TABLE analytics.events DELETE WHERE event_time < '2025-01-01';

For large delete workloads, prefer dropping a partition (ALTER TABLE ... DROP PARTITION) over running a mutation. Mutations rewrite parts; drop partition does not.

Lightweight DELETE

DELETE FROM analytics.events WHERE event_time < '2025-01-01';

Lightweight DELETE FROM ... is the newer alternative to a DELETE mutation. It marks rows as deleted using a hidden mask column rather than rewriting parts. Reads filter the mask out; merges eventually compact the deletes away. Faster for selective deletes, but slightly slower for reads until the next merge. See the official docs for the version where it became stable.

ON CLUSTER and Sync Modes

ALTER TABLE analytics.events ON CLUSTER my_cluster ADD COLUMN ip_country String DEFAULT '';

ON CLUSTER runs the ALTER on every node defined in the cluster. The alter_sync setting (default 1) controls how long the statement waits for replicas to acknowledge:

• alter_sync = 0: fire and forget.
• alter_sync = 1: wait for the current replica.
• alter_sync = 2: wait for all active replicas. Throws UNFINISHED if a replica exceeds replication_wait_for_inactive_replica_timeout.

For schema changes you want to verify before moving on, alter_sync = 2 is the safer choice.

Order of Operations

Multiple actions in a single ALTER TABLE are applied in order, left to right:

ALTER TABLE analytics.events
    DROP COLUMN debug_payload,
    ADD COLUMN session_id UUID DEFAULT '00000000-0000-0000-0000-000000000000',
    ADD INDEX idx_session session_id TYPE minmax GRANULARITY 4,
    MATERIALIZE INDEX idx_session;

Keep schema-only changes together and run mutations as a follow-up. Mixing them in one statement is legal but harder to reason about when something fails halfway.

Common Errors

• ALTER of column is forbidden: you are trying to change a column that is part of the key. See the related error guide.
• Cannot find column X: typo, or the column was already dropped on a different replica.
• Mutation is stuck: usually a part that cannot be merged, often due to disk pressure or a corrupted file. Check system.mutations and system.replication_queue.
• MODIFY ORDER BY is supported only for MergeTree: log-family tables do not have a sort key to modify.
• Distributed DDL was not applied on N hosts: a replica was unreachable when the cluster-wide ALTER ran. Re-run with ON CLUSTER.

How Pulse Helps With Schema Changes

The hardest part of ALTER TABLE in production is not the syntax, it is operational visibility: long-running mutations that nobody noticed have stalled, schema changes that landed on one shard but not another, and TTL modifications that doubled disk I/O without anyone realizing. Pulse continuously tracks system.mutations and DDL convergence across ClickHouse clusters and surfaces mutations that have not made progress, replicas with stale schemas after an ALTER, TTL changes that suddenly increased merge load, and tables where lightweight DELETE has accumulated too many masked rows. Connect your ClickHouse cluster to Pulse and let it watch the long tail of schema evolution.

Frequently Asked Questions

Q: How do I know if an ALTER is a mutation or a metadata change?

Mutations are anything that changes per-row data: UPDATE, DELETE, MATERIALIZE COLUMN/INDEX/TTL. Everything else (ADD/DROP/MODIFY COLUMN, ADD/DROP INDEX, MODIFY ORDER BY, partition operations, settings, TTL definition changes) is metadata-only. Check system.mutations after a doubtful ALTER; if it does not show up there, it was metadata.

Q: Can I cancel a running mutation?

KILL MUTATION WHERE mutation_id = '...' cancels a mutation. Already-rewritten parts stay rewritten; remaining parts are skipped. Useful when you realize a mutation will run for hours and you have a better plan.

Q: Will adding a column block writes?

No. ADD COLUMN is metadata-only. Existing rows return the default at query time; new writes can include the column from the moment the ALTER completes.

Q: Is changing a column's type expensive?

If the type change is compatible (the data can be cast in place), it is metadata-only and fast. If it is incompatible, ClickHouse rejects the statement, and the canonical workaround is add-new-column + backfill + drop-old, which is a mutation-grade operation.

Q: How do I make ALTER wait until it finishes?

For metadata-only changes, ALTER is already synchronous by default (alter_sync = 1). For mutations, set mutations_sync = 1 (current replica) or 2 (all replicas) before the statement.

Q: Can I run multiple ALTERs in parallel?

Metadata-only ALTERs on different tables can run concurrently. On the same table they serialize. Mutations on the same table also serialize. Run schema-altering DDL during low-traffic windows when possible.

Q: What's the difference between DELETE FROM and ALTER TABLE ... DELETE?

DELETE FROM is lightweight delete (mask-based, faster, available on recent versions). ALTER TABLE ... DELETE is a classic mutation that rewrites whole parts. For occasional cleanups, prefer lightweight delete. For large historical purges, prefer ALTER TABLE ... DROP PARTITION.