A simple job queue in PostgreSQL with SKIP LOCKED
Short answer: If your app already uses PostgreSQL, a jobs table can be a perfectly good background queue. Workers claim the next job with SELECT ... FOR UPDATE SKIP LOCKED, which makes each worker skip rows another worker has already locked, so several workers can pull from the same table without taking the same job or waiting on each other. Add a status, attempt count and run-after time, index the pending jobs, keep the claiming transaction short and clean up finished jobs; when volumes get very large or you need complex routing, move to a dedicated queue.
Why use the database you already have
Background jobs, such as sending emails, generating PDFs, syncing with an accounting tool or resizing images, keep requests fast. Dedicated queue systems are excellent, but each one is another service to run, monitor and back up. For many small apps, PostgreSQL can do the job, with a bonus: you can create a job in the same transaction as the order that triggered it, so you never get an order without its confirmation job, or a job for an order that rolled back.
The table
CREATE TABLE jobs (
id bigserial PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending',
attempts int NOT NULL DEFAULT 0,
run_after timestamptz NOT NULL DEFAULT now(),
locked_until timestamptz,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
Claiming a job safely
The problem with a naive "select the oldest pending job, then mark it running" is that two workers can select the same job before either updates it. Locking the row with FOR UPDATE fixes that, but then the second worker waits for the first.
PostgreSQL's SELECT documentation describes the alternative: with SKIP LOCKED, rows that can't be locked immediately are skipped. It notes that this gives an inconsistent view of the data, so it's not for general-purpose queries, but it can be used to avoid lock contention with multiple consumers accessing a queue-like table, which is exactly this case.
UPDATE jobs SET status = 'running', attempts = attempts + 1,
locked_until = now() + interval '5 minutes'
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending' AND run_after <= now()
ORDER BY run_after
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
Each worker gets a different job, or nothing if the queue is empty.
Do the work outside the claim transaction
Commit the claim right away, then do the work, then mark the job done or failed in a separate statement. Holding a transaction open while sending an email or calling an API keeps locks and connections busy and can interfere with database maintenance. The locked_until column acts as a timeout: if a worker crashes, a periodic sweep returns jobs whose locked_until has passed to pending.
Failures and retries
On error, record last_error, and either set status = 'pending' with a later run_after for a retry, or status = 'failed' after a maximum number of attempts. Increase the delay with each attempt so a broken external service isn't hammered. Make job handlers safe to run twice, because a crash after doing the work but before marking it done means it will run again.
Keep it fast
PostgreSQL's documentation on partial indexes describes indexing only the rows queries actually care about; its example is an index on just the unbilled orders, a small but frequently accessed slice of the table. Pending jobs are the same pattern:
CREATE INDEX jobs_pending ON jobs (run_after) WHERE status = 'pending';
The index stays small even when the table holds millions of finished jobs.
Delete or archive completed jobs regularly. A jobs table that only grows becomes slow to scan and bloats over time.
Waking workers
Workers can poll every few seconds, which is fine for most apps. For near-instant pickup, combine the table with PostgreSQL's notification feature: send a notification when a job is inserted, and have idle workers listen and then claim with SKIP LOCKED. The table stays the source of truth; the notification is only a nudge.
See what's happening
Because the queue is a table, monitoring is a query away. Track how many jobs are pending, how old the oldest pending job is, and how many failed in the last hour, and alert when the oldest pending job is older than a few minutes. A simple admin page listing failed jobs with their last error, plus a "retry" button that resets them to pending, saves a lot of support time.
When to move to a dedicated queue
A PostgreSQL queue suits thousands of jobs an hour comfortably for typical apps. Consider a dedicated queue or a job framework when:
- volumes are high enough that the jobs table competes with your main workload
- you need fan-out to many consumers, priorities across many queues or complex routing
- multiple services in different languages need to share queues
Many job libraries for popular frameworks already implement this pattern on PostgreSQL; using one saves writing retries and sweeps yourself.
A worked example
An illustrative Ottawa print shop's ordering site generated proof PDFs during checkout, and busy afternoons made checkout slow. The developer added a jobs table, created the PDF job in the same transaction as the order, and ran two worker processes claiming jobs with FOR UPDATE SKIP LOCKED. A partial index kept claiming fast, retries handled a flaky storage service, and a nightly clean-up removed finished jobs older than a month. Checkout became quick again without adding new infrastructure.
FAQ
Can two workers ever get the same job?
Not with this claim pattern, unless a job times out and is reclaimed while the first worker is still running it. Set realistic timeouts.
Does SKIP LOCKED work in other databases?
MySQL 8 and some others support a similar clause; check your version's documentation.
How many workers can I run?
Start with a few and watch database load and connection counts.
Should jobs store full data?
Store IDs and small parameters, and read current data when the job runs.
If your app needs background jobs and you'd rather not add another service, tell us what the jobs do and we'll help you design the queue.