Skip to main content
Most ALTER TABLE queries modify table settings or data:
Most ALTER TABLE queries are supported only for *MergeTree, Merge and Distributed tables.
These ALTER statements manipulate views: These ALTER statements modify entities related to role-based access control:

​
Mutations

ALTER queries that are intended to manipulate table data are implemented with a mechanism called “mutations”, most notably ALTER TABLE … DELETE and ALTER TABLE … UPDATE. They are asynchronous background processes similar to merges in MergeTree tables that to produce new “mutated” versions of parts. For *MergeTree tables mutations execute by rewriting whole data parts. There is no atomicity — parts are substituted for mutated parts as soon as they are ready and a SELECT query that started executing during a mutation will see data from parts that have already been mutated along with data from parts that have not been mutated yet. Mutations are totally ordered by their creation order and are applied to each part in that order. Mutations are also partially ordered with INSERT INTO queries: data that was inserted into the table before the mutation was submitted will be mutated and data that was inserted after that will not be mutated. Note that mutations do not block inserts in any way. A mutation query returns immediately after the mutation entry is added (in case of replicated tables to ZooKeeper, for non-replicated tables - to the filesystem). The mutation itself executes asynchronously using the system profile settings. To track the progress of mutations you can use the system.mutations table. A mutation that was successfully submitted will continue to execute even if ClickHouse servers are restarted. There is no way to roll back the mutation once it is submitted, but if the mutation is stuck for some reason it can be cancelled with the KILL MUTATION query. Entries for finished mutations are not deleted right away (the number of preserved entries is determined by the finished_mutations_to_keep storage engine parameter). Older mutation entries are deleted.

​
Synchronicity of ALTER Queries

For non-replicated tables, all ALTER queries are performed synchronously. For replicated tables, the query just adds instructions for the appropriate actions to ZooKeeper, and the actions themselves are performed as soon as possible. However, the query can wait for these actions to be completed on all the replicas. For ALTER queries that creates mutations (e.g.: including, but not limited to UPDATE, DELETE, MATERIALIZE INDEX, MATERIALIZE PROJECTION, MATERIALIZE COLUMN, APPLY DELETED MASK, APPLY PATCHES, CLEAR STATISTIC, MATERIALIZE STATISTIC) the synchronicity is defined by the mutations_sync setting. For other ALTER queries which only modify the metadata, you can use the alter_sync setting to set up waiting. You can specify how long (in seconds) to wait for inactive replicas to execute all ALTER queries with the replication_wait_for_inactive_replica_timeout setting.
For all ALTER queries, if alter_sync = 2 and some replicas are not active for more than the time, specified in the replication_wait_for_inactive_replica_timeout setting, then an exception UNFINISHED is thrown.

​
Concurrent ALTER assignment on one table

On replicated tables, submitting several separate ALTER statements against the same table in quick succession can fail with CANNOT_ASSIGN_ALTER (code 517). The replicated path raises this when the replica has not yet applied some previous ALTERs (metadata version still behind the common metadata — the server may say the replica “still not applied some of previous alters” or “Probably too many alters executing concurrently”). That condition can remain true even after an earlier ALTER has already been assigned. This is a general concurrent metadata-ALTER / mutation condition — it is not limited to mutation-only statements. Ordinary concurrent metadata alters (ADD / DROP / MODIFY, and similar) can raise the same retryable code (see for example the retry path covered by tests/queries/0_stateless/03518_alter_logical_race.sh). Approaches that avoid the race:
  • Combine independent metadata operations into a single multi-clause ALTER when the grammar allows it (for example multiple ADD INDEX clauses).
  • Serialize ALTER statements and retry on code 517 until previous ALTERs have been applied on the replica.
  • For mutation-producing ALTERs, wait for the previous mutation to finish using a documented observable such as mutations_sync or is_done in system.mutations before submitting the next one.

​
Combining MATERIALIZE INDEX clauses

Multiple MATERIALIZE INDEX clauses can appear in one ALTER. The covered case in-tree is packing several ADD INDEX clauses together with MATERIALIZE INDEX for those same new indexes in a single statement (see tests/queries/0_stateless/02911_add_index_and_materialize_index.sql). That packed ADD INDEX + MATERIALIZE INDEX form mixes an AlterCommand segment with a MutationCommand segment, so DatabaseReplicated rejects it with QUERY_IS_PROHIBITED (InterpreterAlterQuery::validateReplicatedDatabaseSegments). Treat the 02911 example as valid for ordinary (non-DatabaseReplicated) databases; on DatabaseReplicated, keep metadata changes and materialize mutations in separate statements. In the current implementation, each MATERIALIZE INDEX clause is resolved against the table metadata snapshot when the mutation is prepared, so materialize-only multi-clause forms on already-existing indexes follow the same preparation path (mutation-only, so they stay within one segment). That exact shape is not yet covered by a focused stateless test; treat it as current implementation behavior rather than a separately guaranteed contract until such coverage exists. If you need ordered mutation apply, you can still issue one MATERIALIZE INDEX per statement and wait with mutations_sync.

​
Related content

Last modified on August 5, 2026