How Postgres DELETE can actually scale

The piece argues that DELETE in Postgres - despite a poor reputation - can be made to scale for high-throughput systems by understanding MVCC behavior and tuning for cache and index locality. MVCC makes deletions logical (rows become dead tuples until VACUUM reclaims them), which causes churn: cascading deletes via foreign keys force random disk I/O as widely dispersed child rows are removed, and index scans can stall if many dead tuples accumulate at the front of an index. The context is a durable workflows/queues workload processing tens of thousands of workflows and steps per second, where flexible retention policies require per-item deletes rather than coarse partition drops.

Concrete fixes produced large gains: drop foreign keys and delete related tables in separate, creation-order batches to exploit physical locality; add an anti-join to avoid removing data for still-active long-running workflows; and run manual VACUUM before/after retention passes to prevent pathological index scans (they found cases where an index lookup took 20 minutes because of millions of unchecked dead tuples). Partition-dropping alternatives are faster but too inflexible for heterogeneous retention and long-lived workflows. Combined, locality-aware batching plus manual vacuuming improved deletion performance by roughly an order of magnitude, making Postgres DELETE practical at scale for these use cases.