What looks like a one-line cleanup can turn into a database-wide traffic jam. Tom Pang explains how large Postgres
ON DELETE CASCADEoperations can hold locks across many tables, generate huge amounts of WAL, and block writes when a migration needs anAccessExclusiveLock. Missing indexes on foreign keys can make even a single-row delete trigger full table scans.The article also details logical replication pitfalls: subscriber-only child tables can be left orphaned, while published cascade children without replica identity can make deletes fail. Pang argues for soft deletes followed by carefully batched cleanup, which keeps transactions bounded and gives migrations room to proceed. The trade-off is extra application work: reads must account for deleted rows, and background jobs must eventually remove them. The practical lesson is less glamorous than a cascade, but safer at scale: keep transactions short and small.
continue reading onplanetscale.com
⚠️ This post links to an external website. ⚠️
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.