A working job queue in one Postgres table, built claim-by-claim with SELECT FOR UPDATE SKIP LOCKED, LISTEN/NOTIFY wakeups, backoff, and a lease reaper - plus an honest account of where the dead tuples get you.
PostgreSQL 9.5 added SKIP LOCKED to the locking clause in January 2016, and that one keyword is the difference between a queue table and a traffic jam. Without it, ten workers all lock the oldest ready row and nine of them wait. With it, nine of them step over the locked row and take the next one.
The case against doing this at all is strong, so start there. Every claim in a Postgres job queue is an UPDATE, and every UPDATE writes a new heap tuple and leaves the old one dead. Run 1,000 claims per second and you are producing millions of dead tuples per hour on one small table, plus index churn on every index that covers the changing columns. Autovacuum becomes the real capacity limit long before CPU does, and a queue that backs up bloats a table your application also reads from. A dedicated broker like SQS, Redis Streams, or RabbitMQ never has this problem because deleting a message is not a row version.
So what has to be true for the Postgres approach to win? Three things. First, transactional enqueue matters to you: the job and the business row it describes commit in the same transaction, which deletes the dual-write and outbox problem instead of managing it. Second, your sustained throughput lives in the hundreds to low thousands of jobs per second, not tens of thousands. Third, you would rather tune one database than operate two systems. When all three hold, a single table plus SKIP LOCKED beats a broker on correctness and on operational surface area, which is why pgmq, Graphile Worker, and River all build on this exact primitive.
This build follows one job all the way through: insert, claim, run, fail, back off, and get rescued after a worker crash. It is plain SQL and about 120 lines of Node. You need Postgres 12 or newer, Node 20 or newer, npm i pg, and a DATABASE_URL in the environment. The file is worker.mjs, and you will run it as node worker.mjs --seed.
import pg from 'pg'
const { Pool } = pg
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 8,
})
async function main() {
const { rows } = await pool.query('select now() as t')
console.log('connected at', rows[0].t)
await pool.end()
}
main()Start with a pg.Pool sized to the number of workers plus a little headroom. max: 8 is deliberate: a queue worker holds a connection for the duration of each claim, so pool size is your real concurrency ceiling, not the loop count. pg is CommonJS, so destructure the default import rather than relying on named exports.
state, run_at, attempts, locked_at and last_error are the whole state machine. run_at doubles as the delay mechanism and the retry schedule, so there is no separate scheduler. payload is jsonb because you will want to query it during an incident, and max_attempts lives per job so a flaky webhook can get 10 tries while a payment gets 3.
The claim query only ever looks at pending rows, so index only those: where state = 'pending' keeps jobs_ready_idx roughly the size of the backlog rather than the size of history, and a finished job leaves the index entirely. Then set per-table storage parameters. The default autovacuum_vacuum_scale_factor of 0.2 means Postgres waits until 20 percent of the table is dead before vacuuming, which on a hot queue table is far too patient; 0.02 with a raised cost limit keeps the bloat bounded.
make_interval(secs => $3::float8 / 1000) turns a JavaScript millisecond delay into a real interval without any clock arithmetic in Node, so a worker in another timezone cannot disagree about when a job is due. The --seed flag gives you 20 jobs to chase through the rest of the build. In your own app this insert belongs inside the transaction that created the row the job is about; that is the entire reason to be here rather than on SQS.
This is the version people write first, and it is worth running before fixing. order by run_at limit 1 for update picks the oldest ready job and takes a row lock. Every other worker picks the same row and blocks. When the winner commits, the losers re-evaluate the where clause against the new row version under READ COMMITTED, find state is no longer pending, and return zero rows. Ten workers, one job per lock round trip, nine empty hands.
skip locked tells the executor to ignore rows it cannot lock immediately and keep scanning until it satisfies the limit. Worker A takes job 1, worker B skips it and takes job 2, no waiting anywhere. The tradeoff is real and often glossed over: your order by becomes advisory. Under load, jobs are processed in roughly the order they were queued, never exactly. If you need strict per-entity ordering, add a partition key and claim one job per key, or use for update nowait and treat the error as backpressure.
The two-statement version has a hole: the row lock disappears at commit, so if you plan to update the row afterwards there is a window where another worker sees it as pending. Fold the whole thing into one UPDATE ... RETURNING whose subquery does the locking. One statement is its own transaction, so no begin, no pooled client checkout, and the claim is atomic by construction. attempts = attempts + 1 increments on claim rather than on failure, which is what stops a job that hard-crashes its worker from being retried forever.
A flat map from kind to an async function is all the dispatch you need. On success, delete the row. Keeping completed jobs in the same table feels friendlier for debugging and costs you on every scan, every vacuum cycle and every index page; if you want history, copy to a jobs_done table or emit to your log pipeline instead. The delete still leaves a dead tuple, so vacuum is doing work either way.
One statement handles both outcomes of a failure. If attempts has reached max_attempts the job goes to dead and stops being claimable; otherwise it returns to pending with run_at pushed out by power(2, attempts) seconds, capped at an hour by least. Because the delay is computed from the column inside the UPDATE, the schedule is consistent even if two workers race on the same job after a lease expiry. Truncating last_error to 500 characters keeps a stack-trace storm from bloating your rows.
The worker is a while loop around claim(). An unknown kind throws instead of crashing the loop, which matters during a deploy where the enqueuer knows about a job type the worker does not yet. Note the cost of polling: at one second per idle tick, four workers issue 240 claim queries per minute against an empty table forever. That is cheap on one database and absurd across fifty services, which is the next thing to fix.
Wrapping the insert in a CTE lets pg_notify('jobs', '') fire in the same statement, so the notification is queued and delivered when that transaction commits, never before. On the consumer side, replace the fixed sleep with waitForWork, which resolves either on timeout or when wakeAll fires. Keep the timeout: NOTIFY is fire-and-forget, and any notification sent while a listener is disconnected is simply gone. The poll is the floor, the notify is the latency optimisation.
LISTEN is session state, so it cannot live on a pooled connection that gets handed to someone else between queries. Use a standalone pg.Client and attach an error handler, because a dropped listener silently stops delivering wakeups and your queue quietly degrades to one-second polling. This is also where PgBouncer bites: in transaction pooling mode LISTEN does not work at all, so the listener needs a direct connection or session pooling.
Because the claim commits immediately, nothing holds a lock while the handler runs. If the process is killed mid-job the row sits in running forever, so locked_at acts as a lease and reap() returns anything older than five minutes to pending. Two consequences you have to design for: the lease must exceed your slowest handler or you will double-run healthy jobs, and delivery is at-least-once, so handlers need to be idempotent. A row already claimed once carries a higher attempts, which is your signal to treat a retry as suspicious.
Four concurrent loops on a pool of eight is where SKIP LOCKED finally earns its keep: run node worker.mjs --seed and watch four jobs claimed at once with zero lock waits. SIGINT flips running to false and calls wakeAll, so idle loops exit immediately while an in-flight handler finishes before its loop rechecks the flag. The STATS query is the one dashboard that matters: count by state plus max(now() - run_at), which is your true queue latency. If oldest for pending climbs while claims stay flat, you are out of workers; if dead climbs, a handler is broken.
Roughly 120 lines gives you claiming without contention, delayed jobs, exponential backoff, a dead-letter state, crash recovery through leases, sub-poll latency via LISTEN/NOTIFY, and a latency gauge - all in the database that already holds your data, and all enqueueable inside an existing transaction.
What it does not give you is escape from MVCC. Each job in this design writes at least three row versions: the insert, the claim, and the delete. At 500 jobs per second that is over a million dead tuples an hour on one table, and the honest ceiling is set by whether autovacuum can keep up alongside your normal write traffic, not by how fast SKIP LOCKED scans. Watch pg_stat_user_tables.n_dead_tup for jobs and the last_autovacuum timestamp; if dead tuples trend upward across a whole day, tighten the scale factor further, partition by kind, or move the highest-volume job type to a broker and keep the transactional ones here.
The piece worth arguing about is attempts = attempts + 1 at claim time. It bounds the damage from a poison job that kills its worker, and it also means a job can be marked dead purely because a Kubernetes node was drained three times in a row. Counting infrastructure crashes separately from application failures needs a fourth column and a policy decision about which of the two you are actually willing to give up on.