How to DROP COLUMN in ClickHouse

Why would you drop a column in ClickHouse?

Free storage, simplify schema, or remove sensitive data. ClickHouse reads column-oriented files; unused columns still consume disk until dropped.

What is the safest way to remove a column?

Use ALTER TABLE … DROP COLUMN in a maintenance window and verify no materialized views or queries depend on the column.

How does ALTER TABLE … DROP COLUMN work?

ClickHouse rewrites table metadata instantly.Old parts become obsolete and are purged by the background merge process, so the operation is usually fast.

Syntax breakdown

ALTER TABLE [db.]table
DROP COLUMN column_name [, column_name2 …]
[ON CLUSTER cluster_name];

Add ON CLUSTER for distributed DDL across a cluster.

Example: remove an unused field

ALTER TABLE Orders
DROP COLUMN legacy_status;

The legacy_status column vanishes from Orders; queries referencing it will now fail.

Drop several columns at once

ALTER TABLE Customers
DROP COLUMN temp_flag,
DROP COLUMN deprecated_phone;

Multiple DROP statements can be chained in one command.

Can I undo a DROP COLUMN?

No built-in UNDO exists.Restore from backup or recreate the column and reload data.

Best practices before dropping

1️⃣ Search your codebase for references. 2️⃣ Check materialized views. 3️⃣ Take a backup or snapshot. 4️⃣ Use staging first.

Performance impact

The metadata update is O(1). Disk cleanup happens asynchronously, so production impact is minimal.

Common use cases

• Retire A/B test flags
• Remove PII after retention period
• Trim legacy ETL columns

What happens on replicated tables?

Include ON CLUSTER to propagate the command.Without it you must run the statement on each shard/replica.

End-to-end ecommerce demo

-- Orders table holds purchase data
CREATE TABLE Orders (
id UInt64,
customer_id UInt64,
order_date DateTime,
total_amount Decimal(10,2),
promo_code String
) ENGINE = MergeTree()
ORDER BY id;.

-- Decide promo_code is obsolete
ALTER TABLE Orders DROP COLUMN promo_code;

The column and its data disappear from new parts immediately.

What errors should I expect?

UNKNOWN_IDENTIFIER if the column does not exist; CANNOT_DROP_COLUMN if a materialized view still depends on it.

Next steps

After dropping columns, consider running OPTIMIZE TABLE … FINAL to expedite disk space reclamation.

FAQ

Does DROP COLUMN lock the table?

Only a lightweight metadata lock; read/write queries continue.

How long until disk space is freed?

Space is released once old parts are merged or TTL moves them out—usually minutes to hours depending on load.

Can I drop a column used in a primary key?

No.The key definition must remain valid; recreate the table if you need a different primary key.

.