Sql table as a queue - what could go wrong?
summary
Shows how to make a real SQL-backed queue: use an isolated table with columns id, headers, body and a rowversion for ordering, poll instead of unreliable DB notifications (example 200 ms poll trade-off), prefer SELECT using max/avg rowversion over COUNT for fast estimates, receive with a single transactional DELETE ... OUTPUT using UPDLOCK READPAST ORDER BY rowversion to avoid races, avoid clustered identity to prevent last-page latch contention—use a heap vs clustered index layout because typical messages (~1–2 KB message size) trigger page latch hot spots.
AI-written summary · about
chapters (7)
- 0:00Start
- 8:22What makes a queue?
- 14:35Schema
- 19:27Inherent delay
- 29:05Minimizing contention
- 32:08PAGELATCH_EX Contention
- 42:12DTC is out. TransactionScope stays
related talks
30mQueues in PostgreSQL
29mPostgreSQL queues done right with PgQ
23mRethinking Message Queues: PostgreSQL's Role
19mHow/Why to Sweep Async Tasks Under a Postgres Table
44mBuilding a Distributed Queue with Delayed Jobs on PostgreSQL
48mUsing Asynchronous Message Queues with PostgreSQL
30mAn approach to unite tables and persistent queues in one system
42mAnatomy of Table-Level Locks in PostgreSQL