Avoid ALTER TABLE UPDATE

Impact: CRITICAL

ALTER TABLE UPDATE is a mutation - an asynchronous background process that rewrites entire data parts affected by the change. This is extremely expensive for frequent or large-scale operations.

Why mutations are problematic:

Write amplification: Rewrite complete parts even for minor changes
Disk I/O spike: Degrades overall cluster performance
No rollback: Cannot be rolled back after submission
Inconsistent reads: SELECT may read mix of mutated and unmutated parts

Incorrect (mutation for updates):

sql

-- Rewrites potentially huge amounts of data
ALTER TABLE users UPDATE status = 'inactive'
WHERE last_login < now() - INTERVAL 90 DAY;

-- Frequent row updates via mutation
ALTER TABLE inventory UPDATE quantity = quantity - 1
WHERE product_id = 123;
-- If product exists across 100 parts, rewrites ALL 100 parts

Correct (ReplacingMergeTree):

sql

-- Table design for updates
CREATE TABLE users (
    user_id UInt64,
    name String,
    status LowCardinality(String),
    updated_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

-- "Update" by inserting new version
INSERT INTO users (user_id, name, status)
VALUES (123, 'John', 'inactive');

-- Query with FINAL to get latest version
SELECT * FROM users FINAL WHERE user_id = 123;

-- Or use aggregation
SELECT user_id, argMax(status, updated_at) as status
FROM users GROUP BY user_id;

Reference: Avoid Mutations