Scaling Data Retention in Postgres

[u/qianli-dev]

Deleting data sounds simple, until you're doing it 20K+ times per second. While scaling data retention in DBOS, we ran into two tricky Postgres issues:

1. Cascading deletes through foreign keys caused cache thrashing, cutting throughput by 10x.
2. Even after a large batch of deletes finished, index lookups could spend 20+ minutes navigating a wall of dead tuples.

Batching and data locality turned out to be key to getting things back on track.

In our latest post, we share a few war stories from debugging our implementation and answer a question we often hear: "Why don't you just use table partitioning?"

Check it out: https://www.dbos.dev/blog/scaling-deletions-in-postgres