How/Why to Sweep Async Tasks Under a Postgres Table
summary
Shows a concrete pattern: store tasks in a Postgres table and have a simple worker loop that runs a transaction, SELECT ... FOR UPDATE SKIP LOCKED a single row, do work, then DELETE ... RETURNING only after success so retries are automatic. Recommends order by random to avoid hot retry loops, using table sampling past a few hundred thousand rows, incrementing attempts inside the transaction, preferring identity over serial and JsonB or hstore for params.
AI-written summary · about
chapters (6)
- 0:00The problem
- 2:23async tasks under a postgres table
- 3:18FOR UPDATE SKIP LOCKED
- 9:19Postgres vs Alternatives
- 10:48Implementing the worker
- 18:07Conclusion
related talks
24mPostgreSQL Design Patterns
30mQueues in PostgreSQL
41mConcurrency, Scalability and Transactions -- Myths and Surprises
47mSql table as a queue - what could go wrong?
23mPostgres as a Message Broker
50mFraught Feedback: Trying and Failing to Implement Adaptive Behavior in Postgres
52mBulk data processing and PostgreSQL thingy
31m5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks