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.
MATERIALIZE COLUMN is not a full substitute for MATERIALIZE INDEX. On parts that are simultaneously wide + full-storage, it may rewrite column values without refreshing standalone skipping-index (or text-index) files. Ordinary skip indexes stored in skp_idx.packed are an exception: they can still be force-recomputed on wide + full-storage parts (small skip-index substreams under the default packed_skip_index_max_bytes; full-text indexes are not packed this way). On any part that is not wide + full-storage (including compact + full, compact + packed, and wide + packed), a full-part rewrite can recalculate pre-existing indexes — small parts are commonly compact while still using full part storage by default. Use MATERIALIZE INDEX for the deterministic / immediate path when an index was added to a table that already has data (metadata-only ADD INDEX), and after column rewrites on wide+full-storage parts when you need standalone index files rebuilt right away. Newly added indexes (including text indexes) on historical parts can also be materialized by a later merge when materialize_skip_indexes_on_merge is enabled and the index is not excluded via exclude_materialize_skip_indexes_on_merge; otherwise they stay unmaterialized until an explicit MATERIALIZE INDEX.
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.