How ALTER's works in ClickHouse

How ALTER’s works in ClickHouse:

ADD (COLUMN/INDEX/PROJECTION)

Lightweight, will only change table metadata. So new entity will be added in case of creation of new parts during INSERT’s OR during merges of old parts.

In case of COLUMN, ClickHouse will calculate column value on fly in query context.

If you want to backpopulate data for old parts, you have multiple options:

MATERIALIZE (COLUMN/INDEX/PROJECTION) (PART[ITION ID] ‘’)

Will materialize this entity.

OPTIMIZE TABLE xxxx (PART[ITION ID] ‘’) (FINAL)

Will trigger merge, which will lead to materialization of all entities in affected parts.

ALTER TABLE xxxx UPDATE column_name = column_name WHERE 1;

Will trigger mutation, which will materialize this column.

DROP (COLUMN/INDEX/PROJECTION)

Lightweight, it’s only about changing of table metadata and removing corresponding files from filesystem. For Compact parts it will trigger merge, which can be heavy. issue

MODIFY COLUMN (DATE TYPE)

  1. Change column type in table schema.
  2. Schedule mutation to change type for old parts.

Mutations

Affected parts - parts with rows mathing condition.

ALTER TABLE xxxxx DELETE WHERE column_1 = 1;

  1. Will overwrite all column data in affected parts.
  2. For all part(ition)s will create new directories on disk and write new data to them or create hardlinks if they untouched.
  3. Register new parts names in ZooKeeper.

ALTER TABLE xxxxx DELETE IN PARTITION ID ’’ WHERE column_1 = 1;

Will do the same but only for specific partition.

ALTER TABLE xxxxx UPDATE SET column_2 = column_2, column_3 = column_3 WHERE column_1 = 1;

  1. Will overwrite column_2, column_3 data in affected parts.
  2. For all part(ition)s will create new directories on disk and write new data to them or create hardlinks if they untouched.
  3. Register new parts names in ZooKeeper.

DELETE FROM xxxxx WHERE column_1 = 1;

  1. Will create & populate hidden boolean column in affected parts. (_row_exists column)
  2. For all part(ition)s will create new directories on disk and write new data to them or create hardlinks if they untouched.
  3. Register new parts names in ZooKeeper.

Despite that LWD mutations will not rewrite all columns, steps 2 & 3 in case of big tables can take significiant time.

Last modified 2022.09.01: Create alters.md (cc8742b)