A DELETE that should have been a partition drop

A retention job deleting eleven million rows a month from a partitioned table, one batch at a time, without touching the partition boundary.

-- what it did
DELETE FROM events WHERE created_at < ? LIMIT 5000;
-- 4 hours, every month, and the replica lagged

-- what the table supports
ALTER TABLE events DROP PARTITION p2023_01;
-- 1.8 seconds

-- the job predated the partitioning by two years and
-- nobody revisited it.

The partitioning had been done specifically to make this cheap and the job that motivated it was never changed, which is the ordinary way an optimisation goes unclaimed. Deleting by partition also requires the retention boundary to align with the partition boundary — a policy of “eighteen months” becomes “drop the partition that is entirely older than eighteen months”, which retains slightly more and is worth the trade.