Subchapter 4.12

Insert Mutation Avoid Delete

rules/insert-mutation-avoid-delete.mdMarkdown2 KBView on GitHub

Avoid ALTER TABLE DELETE

Impact: CRITICAL

ALTER TABLE DELETE is a mutation that rewrites entire data parts. Use alternatives like lightweight DELETE, CollapsingMergeTree, or DROP PARTITION.

Incorrect (mutation delete):

Insert Mutation Avoid Delete · Clickhouse Best Practices · ClickHouse/agent-skills · Skills Docs

-- Mutation delete for cleanup
ALTER TABLE orders DELETE WHERE status = 'cancelled';

-- Time-based cleanup via mutation (very expensive)
ALTER TABLE sessions DELETE WHERE created_at < now() - INTERVAL 7 DAY;

CREATE TABLE orders (
    order_id UInt64,
    customer_id UInt64,
    total Decimal(10,2),
    sign Int8  -- 1 = active, -1 = deleted
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY order_id;

-- Insert order
INSERT INTO orders VALUES (123, 456, 99.99, 1);

-- "Delete" by inserting with sign = -1
INSERT INTO orders VALUES (123, 456, 99.99, -1);

-- Query collapses +1 and -1 pairs
SELECT order_id, sum(total * sign) as total
FROM orders GROUP BY order_id HAVING sum(sign) > 0;

Method	Speed	When to Use
ALTER DELETE	Slow	Rare corrections only
CollapsingMergeTree	Fast	Frequent soft deletes
Lightweight DELETE	Medium	Occasional deletes
DROP PARTITION	Instant	Bulk deletion by partition