Using PostgreSQL for queueing
published on 2023/09/25
Postgres queue tech consists of two parts: announcing and listening for new jobs (pub/sub) and mutual exclusion (row locks). Both are provided out-of-the-box since Postgres 9.5, released in 2016.
By combining NOTIFY and LISTEN, Postgres makes adding pub/sub to any application trivial. In addition to pub/sub, Postgres also provides one-job-per-worker semantics with FOR UPDATE SKIP LOCKED. Queries with this suffix acquire row locks on matching records, and ignore any records for which locks are already held. Applied to job records, this feature enables simple queue processing queries, e.g. SELECT * FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1.
Combined, these two features form the basis for resource-efficient queue processing. Importantly SKIP LOCKED provides an “inconsistent” view of one’s data. That inconsistency is exactly what is needed from a queue; jobs already being processed (i.e. row-locked) are invisible to other workers, offering distributed mutual exclusion. These locks pave the way for both periodic batch processing, and real-time job processing by NOTIFYing LISTENers of new jobs.