How to delete rows in ClickHouse

Why use ALTER TABLE DELETE in ClickHouse?

ClickHouse is append-only; direct DELETE is unavailable. Instead, ALTER TABLE … DELETE WHERE marks parts containing matching rows for background removal, giving near-instant logical deletes.

What is the exact DELETE syntax?

Run ALTER TABLE [db.]table DELETE WHERE <condition>. Conditions must reference columns, not aliases, and can combine filters with AND/OR.

Syntax details

• db.table – optional database.
• DELETE – keyword.
• WHERE – mandatory filter; omit to avoid full-table delete errors.
• Works on MergeTree-family engines (Replacing, Summing, etc.).

How do I delete stale customer accounts?

Example: remove customers created before 2023-01-01.

ALTER TABLE Customers
DELETE WHERE created_at < '2023-01-01';


ClickHouse rewrites affected parts asynchronously; queries stop returning those rows immediately.

How to delete orders with no items?

Join filters are unsupported. Instead, pre-select IDs, then delete:

-- Step 1: create temp ID list
CREATE TEMPORARY TABLE empty_orders AS
SELECT id FROM Orders o
LEFT JOIN OrderItems oi ON o.id = oi.order_id
WHERE oi.order_id IS NULL;

-- Step 2: delete using IN()
ALTER TABLE Orders
DELETE WHERE id IN (SELECT id FROM empty_orders);


Best practices for large deletes

• Batch by partition or small ID ranges to avoid long merges.
• Run in off-peak hours.
• Monitor system.mutations for status and errors.
• Set mutations_sync for synchronous waits when needed.

What are common mistakes?

Missing WHERE triggers an error; ClickHouse prevents accidental full-table wipes.
Using JOINs inside DELETE is unsupported; use temp tables or sub-queries with IN.

How to check mutation progress?

SELECT database, table, is_done, latest_fail_reason
FROM system.mutations
WHERE table = 'Customers';


When should I prefer partition drops?

Need to remove entire time-based slices? ALTER TABLE … DROP PARTITION is faster because it avoids row-level rewrites.