How we cut a ClickHouse delete from 12 seconds to under 2

Plutus keeps spend data current by replacing a window of rows, not appending to it. For every source, a sync deletes up to 400 days of rows and inserts them again, so a day a provider restates replaces the old row instead of sitting beside it.

Appending would double-count the overlap. Providers revise recent days, and the table is a plain MergeTree that does not deduplicate, holding aggregates with no natural row key to upsert on. The obvious alternative, a ReplacingMergeTree read with FINAL, timed out at 30 s on a one-year query that took 2.3 s without it.

In production, the delete in that path had a daily p50 of 9 to 15 s over the three days before the change, with a maximum of 27.7 s. The insert that followed took 0.17 s.

Why was the delete slow?

The cost tables were partitioned by month, 31 and 42 partitions each. A lightweight DELETE in ClickHouse walks every partition in the table, whatever its predicate matches. In a local benchmark, a delete that matched one monthly partition took 15 to 17 s, no faster than one covering the whole window.

What changed?

Yearly partitions. There are far fewer to walk, and a 400-day re-insert writes 2 parts instead of 14.

From system.query_log in production, the daily p50 of the sync delete, in seconds:

Table	Before (3 days, 10 to 130 deletes a day)	After (10 days, 35 to 150 a day)
cost_entries	9 to 14 s	1.0 to 1.8 s
usage_entries	12 to 15 s	0.7 to 1.8 s

That is about a tenfold drop, and it has held every day since the change.

What did it cost?

Yearly partitions alone made one-month reads scan about seven times the rows, because the monthly partitions had been pruning them for free. Moving the month into the sort key and filtering on it in the readers brought a one-month read back to the same 3.8M rows it scanned before.

The longer-term plan is append-only: each sync is stamped with a generation, and a small pointer row says which generation is live. That is planned, not built. Until then, partition count was the lever we had.

If your write path is delete-and-reinsert, count your partitions and the parts one re-insert writes. Those are the first two numbers to get.