ADD INDEX
ALTER TABLE [db.]table_name [ON CLUSTER cluster] ADD INDEX [IF NOT EXISTS] name expression TYPE type [GRANULARITY value] [FIRST|AFTER name] - Adds index description to tables metadata.
DROP INDEX
ALTER TABLE [db.]table_name [ON CLUSTER cluster] DROP INDEX [IF EXISTS] name - Removes index description from tables metadata and deletes index files from disk. Implemented as a mutation.
MATERIALIZE INDEX
ALTER TABLE [db.]table_name [ON CLUSTER cluster] MATERIALIZE INDEX [IF EXISTS] name [IN PARTITION partition_name] - Rebuilds the secondary index name for the specified partition_name. Implemented as a mutation. If IN PARTITION part is omitted then it rebuilds the index for the whole table data.
CLEAR INDEX
ALTER TABLE [db.]table_name [ON CLUSTER cluster] CLEAR INDEX [IF EXISTS] name [IN PARTITION partition_name] - Deletes the secondary index files from disk without removing description. Implemented as a mutation.
The commands ADD, DROP, and CLEAR are lightweight in the sense that they only change metadata or remove files.
Also, they are replicated, syncing indices metadata via ClickHouse Keeper or ZooKeeper.
Index manipulation is supported only for tables with
*MergeTree engine (including replicated variants).Concurrent ALTER and multi-clause MATERIALIZE INDEX
On replicated tables, rapid separate ALTERs against one table can raise CANNOT_ASSIGN_ALTER (code 517) when previous ALTERs have not yet been applied on the replica (metadata still behind — can remain true after an earlier alter was already assigned). This is a general concurrent metadata-ALTER / mutation condition (not mutation-only); serialize/retry, wait for prior mutation-producing alters via mutations_sync / is_done in system.mutations, or combine independent metadata operations into one multi-clause ALTER when the grammar allows it. See Synchronicity of ALTER Queries and Concurrent ALTER assignment.
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 (tests/queries/0_stateless/02911_add_index_and_materialize_index.sql). That packed form is for ordinary (non-DatabaseReplicated) databases — DatabaseReplicated rejects mixed ADD INDEX + MATERIALIZE INDEX segments with QUERY_IS_PROHIBITED. Materialize-only multi-clause forms on already-existing indexes follow the same metadata-snapshot prepare path in the current implementation, but 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. For ordered apply, issue one MATERIALIZE INDEX per statement and wait with mutations_sync.