Taking Out The Trash In Postgres

A Postgres-backed durable workflow system found that deleting expired workflow data can become more expensive than executing workflows because MVCC turns deletions into dead tuples that remain until VACUUM reclaims them. Foreign-key cascades from workflow records to widely dispersed step-output records caused severe cache-locality problems, requiring many disk-page reads and writes. Removing the foreign keys and deleting each table independently in physical creation order substantially improved performance, while anti-joins prevented deletion of data belonging to active long-running workflows. A further issue arose when an indexed query for the next batch boundary scanned millions of dead tuples at the oldest end of the index, causing occasional 20-minute lookups; manual VACUUM runs before and after retention passes resolved this. Although time-based table partitioning could make retention faster by dropping entire files, it was rejected because long-running workflows and varied retention policies make partitions too inflexible.

No tracked trend matches for this post yet.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.