Nov 18, 20267 min read

Postgres as a Job Queue: SELECT FOR UPDATE SKIP LOCKED

How to build a job queue on Postgres with FOR UPDATE SKIP LOCKED, leases, retries and cleanup, when a library such as River or pg-boss is the better call, and when a managed topic beats a jobs table.

Illustration for Postgres as a Job Queue: SELECT FOR UPDATE SKIP LOCKED

A jobs table in Postgres is the queue plenty of apps already have without deciding to: the database is there, the job can be inserted in the same transaction as the order it belongs to, and SELECT ... FOR UPDATE SKIP LOCKED lets several workers pull from it without stepping on each other.

It works well until the details pile up, such as a worker that dies holding a job, retries without backoff, a hot table that bloats faster than autovacuum cleans it, and polling that hits the database every second from every worker.

This guide builds the queue properly, then covers the libraries that already solve it and the point where a topic is the better tool.

Queue options at a glance

Encore Pub/SubHand-rolled SKIP LOCKEDRiver / pg-boss / Graphile WorkerManaged queue (SQS, Pub/Sub)
StorageSNS and SQS, or GCP Pub/Sub, in your accountYour PostgresYour PostgresCloud service
Enqueue in the business transactionNopublish after commitYesYesNo
Retries and dead-letteringBuilt in per subscriptionYou write themBuilt inBuilt in
Fan-out to several consumersYesone subscription eachExtra tables or columnsLimitedWith SNS or topics
Load on the app databaseNoneGrows with job volumeGrows with job volumeNone
Local developmentIn memory under encore runLocal PostgresLocal PostgresEmulator or cloud

The schema and the claim query

A minimal table needs a state, a run-at time for delays and backoff, an attempt counter and a lease so a crashed worker's job comes back:

CREATE TABLE jobs ( id BIGSERIAL PRIMARY KEY, kind TEXT NOT NULL, payload JSONB NOT NULL, state TEXT NOT NULL DEFAULT 'ready' CHECK (state IN ('ready', 'running', 'done', 'dead')), run_at TIMESTAMPTZ NOT NULL DEFAULT now(), attempts INT NOT NULL DEFAULT 0, max_attempts INT NOT NULL DEFAULT 10, locked_until TIMESTAMPTZ, lease_id UUID, last_error TEXT ); CREATE INDEX jobs_ready_idx ON jobs (run_at) WHERE state = 'ready';

Workers claim a batch in one statement: SKIP LOCKED, available since PostgreSQL 9.5, makes the inner SELECT skip rows another transaction has locked, so concurrent workers get different jobs instead of queuing behind each other:

UPDATE jobs SET state = 'running', attempts = attempts + 1, locked_until = now() + interval '5 minutes', lease_id = gen_random_uuid() WHERE id IN ( SELECT id FROM jobs WHERE (state = 'ready' AND run_at <= now()) OR (state = 'running' AND locked_until < now()) -- lease expired, worker died ORDER BY run_at LIMIT 10 FOR UPDATE SKIP LOCKED ) RETURNING id, kind, payload, attempts, max_attempts, lease_id;

The claim commits immediately, so the job is not held in an open transaction while it runs. On success the worker sets state = 'done' only where lease_id still matches; a stale worker whose lease was reclaimed cannot mark the new worker's run done. On failure it sets state = 'ready' and pushes run_at out with exponential backoff, or state = 'dead' once attempts reaches max_attempts, again only for its own lease. The lease handles workers that crash mid-job, which is also why job handlers must be idempotent: a job that ran but whose worker died before marking it done will run again.

Polling, LISTEN/NOTIFY and batch size

Polling every second from ten workers is ten queries a second against an index that is usually empty. A trigger that calls pg_notify('jobs', '') on insert lets idle workers LISTEN and wake up immediately, with a slow poll (every 10 to 30 seconds) as a fallback for missed notifications and for retries whose run_at arrives later. Claim in batches when jobs are small, so one round trip feeds several handlers.

Vacuum, bloat and cleanup

Every claim and completion is an UPDATE, which in Postgres writes a new row version. A busy queue creates dead tuples quickly, and if autovacuum falls behind, the partial index and the table grow and the claim query slows down. Delete or archive done rows on a schedule, keep the table narrow (large payloads in a separate table or a bucket), and consider tuning autovacuum_vacuum_scale_factor for the jobs table alone. Long-running transactions elsewhere in the database block vacuum from cleaning up, which hurts the queue first.

Use a library before you write one

River (Go), pg-boss and Graphile Worker (Node.js) implement the pattern above with retries, scheduling, unique jobs and LISTEN/NOTIFY, and they have already hit the edge cases. River in particular is built around inserting jobs in the same transaction as your own writes. Writing your own queue makes sense for a handful of job kinds with simple retry rules; past that, a library is less code to maintain.

Background work with Encore

Encore

Encore brings infrastructure from code to TypeScript and Go backends, and for most background work a Pub/Sub topic replaces the jobs table: retries, dead-lettering and fan-out come with each subscription, and none of the load lands on the application database. encore run runs Pub/Sub in memory and starts Postgres locally. On deploy to your own AWS account Encore provisions SNS, SQS and RDS and provisions an IAM role for the service; on GCP it uses Pub/Sub and Cloud SQL.

When the job has to commit with the business write, keep the jobs table in the same Encore database and use the topic only as the wake-up signal, publishing after the commit:

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

// orders/orders.ts import { api } from "encore.dev/api"; import { SQLDatabase } from "encore.dev/storage/sqldb"; import { Topic, Subscription } from "encore.dev/pubsub"; const db = new SQLDatabase("orders", { migrations: "./migrations" }); export const jobsReady = new Topic<{ kind: string }>("jobs-ready", { deliveryGuarantee: "at-least-once", }); export const placeOrder = api( { expose: true, method: "POST", path: "/orders" }, async (p: { email: string; totalCents: number }): Promise<{ id: number }> => { await using tx = await db.begin(); const order = await tx.queryRow<{ id: number }>` INSERT INTO orders (email, total_cents) VALUES (${p.email}, ${p.totalCents}) RETURNING id`; await tx.exec` INSERT INTO jobs (kind, payload) VALUES ('send-receipt', ${JSON.stringify({ orderId: order!.id })}::jsonb)`; await tx.commit(); await jobsReady.publish({ kind: "send-receipt" }); // wake-up only; the row is the source of truth return { id: order!.id }; }, ); const _ = new Subscription(jobsReady, "run-jobs", { handler: async () => { const jobs = await db.queryAll<{ id: number; payload: { orderId: number }; lease_id: string }>` UPDATE jobs SET state = 'running', attempts = attempts + 1, locked_until = now() + interval '5 minutes', lease_id = gen_random_uuid() WHERE id IN ( SELECT id FROM jobs WHERE state = 'ready' AND run_at <= now() ORDER BY run_at LIMIT 10 FOR UPDATE SKIP LOCKED) RETURNING id, payload, lease_id`; for (const job of jobs) { // Send the receipt, then: await db.exec`UPDATE jobs SET state = 'done' WHERE id = ${job.id} AND lease_id = ${job.lease_id}`; } }, });

A lost wake-up message only delays a job, since the row is still there, so pair this with a periodic sweep for expired leases and delayed retries. In Encore that sweep is a cron job, which is Encore platform managed and does not run locally or in preview environments, so call the sweep endpoint by hand when testing. If the job does not need to commit with the order, skip the table and publish the job itself to the topic.

A coding agent working in this repository can add a new job kind as a topic and subscriber in one change and watch it run under encore run, with the database and topic created from the same code on deploy.

Best fit: TypeScript or Go backends that want background work on a managed queue in their own cloud, keeping a Postgres jobs table only for work that must commit with a business write.

When to use which

Use a Postgres queue, ideally through River, pg-boss or Graphile Worker, when jobs must be enqueued atomically with your own writes and volume is modest. Use a managed queue or topic when you need fan-out, high throughput, or isolation from the application database. Many backends end up with both: a small transactional jobs table and topics for everything else.

Frequently asked questions

How does FOR UPDATE SKIP LOCKED work as a job queue?

Each worker selects the next ready job with FOR UPDATE SKIP LOCKED inside a transaction. Rows another worker has already locked are skipped instead of waited on, so several workers can claim different jobs from the same table at once without blocking each other or taking the same job twice.

When should I not use Postgres as a queue?

When throughput is high enough that job churn causes table bloat and vacuum pressure, when jobs need fan-out to several independent consumers, or when the queue would compete with your application's own queries on a small database. A managed queue or topic is a better fit there. Postgres queues shine when a job must commit in the same transaction as the business write.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders