Postgres DELETE operations have a reputation for poor scalability in high-performance environments. This reputation stems from Multi-Version Concurrency Control (MVCC), the mechanism Postgres uses to ensure transaction isolation. Instead of physically removing a row, Postgres marks it as 'dead' by stamping it with a transaction ID. This allows other concurrent transactions to see a consistent snapshot of the data. However, this creates 'churn,' where dead tuples accumulate and must be reclaimed by a process called VACUUM. For systems requiring complex data retention—where different types of data must be kept for different durations—the flexibility of a standard DELETE is often preferred over the rigid structure of dropping entire database partitions.
This graph visualizes the performance bottlenecks and the impact of MVCC churn on deletion speeds.
Engineers at DBOS encountered significant performance bottlenecks when implementing retention policies for a durable workflow system. The primary issue was a lack of cache locality. Because workflows consist of many steps recorded in separate tables, using a standard 'ON DELETE CASCADE' foreign key forced Postgres to fetch widely separated data pages from disk for every step deletion. To solve this, the team dropped the foreign keys and moved to a phased deletion strategy. By deleting rows in their physical creation order, they ensured that the database could reuse cached pages, significantly improving performance. They also implemented an 'anti-join' to ensure that data associated with still-active workflows was not accidentally purged during these batch operations.
This diagram explains the structural relationship between workflows and steps that the team optimized.
This SQL snippet represents the standard 'ON DELETE CASCADE' approach that the team moved away from.
Despite these improvements, the team discovered a 'pathological' index lookup bug where a simple query took 20 minutes to complete. This occurred because Postgres does not update index entries synchronously during a DELETE; a query walking an index might encounter millions of dead tuples that have not yet been cleaned by autovacuum. To resolve this, the team introduced manual VACUUM operations before and after each retention pass. This combination of removing foreign keys to improve cache locality, using anti-joins for safety, and manual vacuuming to clear index paths allowed their system to process tens of thousands of workflows and steps per second while maintaining strict data retention policies.