> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for ALTER

# ALTER

Most `ALTER TABLE` queries modify table settings or data:

| Modifier |
| - |
| [COLUMN](/reference/statements/alter/column) |
| [PARTITION](/reference/statements/alter/partition) |
| [DELETE](/reference/statements/alter/delete) |
| [UPDATE](/reference/statements/alter/update) |
| [ORDER BY](/reference/statements/alter/order-by) |
| [SAMPLE BY](/reference/statements/alter/sample-by) |
| [INDEX](/reference/statements/alter/skipping-index) |
| [PROJECTION](/reference/statements/alter/projection) |
| [CONSTRAINT](/reference/statements/alter/constraint) |
| [TTL](/reference/statements/alter/ttl) |
| [STATISTICS](/reference/statements/alter/statistics) |
| [SETTING](/reference/statements/alter/setting) |
| [APPLY DELETED MASK](/reference/statements/alter/apply-deleted-mask) |
| [APPLY PATCHES](/reference/statements/alter/apply-patches) |

<Note>
  Most `ALTER TABLE` queries are supported only for [\*MergeTree](/reference/engines/table-engines/mergetree-family/index), [Merge](/reference/engines/table-engines/special/merge) and [Distributed](/reference/engines/table-engines/special/distributed) tables.
</Note>

These `ALTER` statements manipulate views:

| Statement | Description |
| - | - |
| [ALTER TABLE ... MODIFY QUERY](/reference/statements/alter/view) | Modifies a [Materialized view](/reference/statements/create/view) structure. |

These `ALTER` statements modify entities related to role-based access control:

| Statement |
| - |
| [USER](/reference/statements/alter/user) |
| [ROLE](/reference/statements/alter/role) |
| [QUOTA](/reference/statements/alter/quota) |
| [ROW POLICY](/reference/statements/alter/row-policy) |
| [MASKING POLICY](/reference/statements/alter/masking-policy) |
| [SETTINGS PROFILE](/reference/statements/alter/settings-profile) |

| Statement | Description |
| - | - |
| [ALTER TABLE ... MODIFY COMMENT](/reference/statements/alter/comment) | Adds, modifies, or removes comments to the table, regardless if it was set before or not. |
| [ALTER DATABASE ... MODIFY COMMENT](/reference/statements/alter/database-comment) | Adds, modifies, or removes comments to the database, regardless if it was set before or not. |
| [ALTER NAMED COLLECTION](/reference/statements/alter/named-collection) | Modifies [Named Collections](/concepts/features/configuration/server-config/named-collections). |

<h2 id="combining-actions">
  Combining actions in one `ALTER`
</h2>

One `ALTER TABLE` accepts several comma-separated actions, so work that would otherwise be submitted as a sequence of statements can go in a single one:

```sql theme={null}
ALTER TABLE visits DROP COLUMN browser, DROP COLUMN referrer;
```

The actions do not have to be of the same type, and they are applied from left to right, so a later action can use what an earlier one added:

```sql theme={null}
-- a new column and an index over it
ALTER TABLE visits ADD COLUMN duration UInt32, ADD INDEX idx_duration duration TYPE minmax GRANULARITY 4;

-- a type change together with a new column
ALTER TABLE visits MODIFY COLUMN browser LowCardinality(String), ADD COLUMN page_id UInt64;

-- three actions in one statement
ALTER TABLE visits DROP COLUMN page_id, MODIFY COLUMN duration UInt64, ADD COLUMN region_id UInt32;
```

When a client waits for an `ALTER` to finish, which setting governs that wait depends on the action rather than on the statement: an action on the mutation execution path, such as `MATERIALIZE INDEX`, is covered by [`mutations_sync`](/reference/settings/session-settings/mutations#mutations_sync) while the metadata actions are covered by [`alter_sync`](/reference/settings/session-settings/alter#alter_sync), so combining the two does not bring them under one setting. See [Synchronicity of ALTER Queries](#synchronicity-of-alter-queries) for which actions each setting covers, and [Combining `MATERIALIZE INDEX` clauses](#combining-materialize-index-clauses) for the restriction that such a mixed statement meets on a `Replicated` database.

<h2 id="mutations">
  Mutations
</h2>

`ALTER` queries that are intended to manipulate table data are implemented with a mechanism called "mutations", most notably [ALTER TABLE ... DELETE](/reference/statements/alter/delete) and [ALTER TABLE ... UPDATE](/reference/statements/alter/update). They are asynchronous background processes similar to merges in [MergeTree](/reference/engines/table-engines/mergetree-family/index) 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`](/reference/system-tables/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`](/reference/statements/kill#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.

<h2 id="execution-cost-and-completion">
  Execution cost and completion
</h2>

When planning an `ALTER TABLE`, distinguish a metadata change from the work required for existing data parts. Whether the client waits for completion depends on the operation: [`alter_sync`](/reference/settings/session-settings/alter#alter_sync) controls metadata operations and `MODIFY COLUMN`, including type conversions that rewrite data; [`mutations_sync`](/reference/settings/session-settings/mutations#mutations_sync) controls background mutations such as `UPDATE`, `DELETE`, and `MATERIALIZE ...`. Do not use fixed durations as operational guidance: the cost depends on the amount of metadata, the affected parts and bytes, conversion cost, and current background load.

| Operation | Existing-data work | Default completion behavior | Main cost drivers |
| - | - | - | - |
| `ADD COLUMN` | No rewrite; old parts supply the default value at read time. | Metadata wait follows `alter_sync`. | Metadata size and replica availability. |
| `RENAME COLUMN` | No row rewrite, but it is an ordering barrier after earlier mutations. | Metadata wait follows `alter_sync`. | Earlier mutations and replica availability. |
| `DROP COLUMN` | Removes column files; this is not purely a metadata change. | Metadata and mutation ordering apply. | Number and size of column files. |
| `MODIFY COLUMN` default, comment, or representation-preserving type | No immediate rewrite. | Metadata wait follows `alter_sync`. | Metadata size and replica availability. |
| Other `MODIFY COLUMN` type changes | A `READ_COLUMN` mutation rewrites the affected column in existing parts. | Wait behavior follows `alter_sync` (default `1`; Cloud default `0`). | Affected parts and bytes, conversion cost, and background load. |
| `UPDATE`, `DELETE`, and `MATERIALIZE ...` | Background mutations rewrite affected parts. | Asynchronous by default; `mutations_sync` controls waiting. | Affected parts and bytes, and background load. |

<h2 id="synchronicity-of-alter-queries">
  Synchronicity of ALTER Queries
</h2>

By default, `ALTER` queries on non-replicated tables wait for their work to complete. With `alter_sync = 0`, an `ALTER` that performs background work can return before that work finishes. For replicated tables, the query adds instructions for the appropriate actions to `ZooKeeper`, and the actions themselves are performed as soon as possible. The query can wait for these actions to be completed on all replicas.

For `ALTER` queries that create mutations through the mutation execution path (including, but not limited to, `UPDATE`, `DELETE`, `MATERIALIZE INDEX`, `MATERIALIZE PROJECTION`, `MATERIALIZE COLUMN`, `APPLY DELETED MASK`, `APPLY PATCHES`, `CLEAR STATISTIC`, and `MATERIALIZE STATISTIC`), synchronicity is defined by the [mutations\_sync](/reference/settings/session-settings/mutations#mutations_sync) setting.

For other `ALTER` queries, including `MODIFY COLUMN` rewrites, [alter\_sync](/reference/settings/session-settings/alter#alter_sync) controls 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](/reference/settings/session-settings/other#replication_wait_for_inactive_replica_timeout) setting.

<Note>
  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. With `alter_sync = 3` the inactive replicas are not waited for, so no exception is thrown.
</Note>

<h3 id="concurrent-alter-assignment-on-one-table">
  Concurrent `ALTER` assignment on one table
</h3>

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 `ALTER`s (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), as described in [Combining actions in one `ALTER`](#combining-actions).
* Serialize `ALTER` statements and retry on code 517 until previous `ALTER`s have been applied on the replica.
* For mutation-producing `ALTER`s, wait for the previous mutation to finish using a documented observable such as [`mutations_sync`](/reference/settings/session-settings/mutations#mutations_sync) or `is_done` in [`system.mutations`](/reference/system-tables/mutations) before submitting the next one.

<h3 id="combining-materialize-index-clauses">
  Combining `MATERIALIZE INDEX` clauses
</h3>

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`](/reference/settings/session-settings/mutations#mutations_sync).

<h2 id="related-content">
  Related content
</h2>

* Blog: [Handling Updates and Deletes in ClickHouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)