ClickHouse ALTER TABLE: Operations

Tip: Add Column

ALTER TABLE events ADD COLUMN user_agent String;

Gotcha: Column Type Can't Change

ClickHouse doesn't support changing column types. Create a new table and migrate.

Tip: Drop Partition

ALTER TABLE events DROP PARTITION 202401;

Instantly removes a month of data.

Gotcha: Mutations Are Async

ALTER TABLE events DELETE WHERE status = 'spam';

Runs in the background. Check system.mutations for progress.

Tip: Modify Column

ALTER TABLE events MODIFY COLUMN user_agent LowCardinality(String);

Gotcha: Lightweight Delete (PG 24.3+)

DELETE FROM events WHERE status = 'spam';

Newer ClickHouse versions support lightweight deletes.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ALTER TABLE in ClickHouse is a mutation operation that rewrites entire parts. I learned this the hard way when an ALTER on a 500GB table took 6 hours. Unlike OLTP databases, ClickHouse doesn't modify data in place — it creates new parts with the change applied and replaces the old parts when the mutation is done. For frequent schema changes, I design tables with extra Nullable columns pre-added, so 'adding a column' is just enabling an existing column.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)