NOWAIT and SKIP LOCKED: distributing jobs across workers Intermediate

The problem

A shop application writes one job into a table for each order confirmation email. Two workers read the table to send them. If both SELECT the first job and send it, the customer gets two emails.

A row-level lock handles holding the right to modify a row; a queue adds one more question: when the first job is locked, should the second worker wait or take other work?

If job 1 and job 2 are two independent orders, waiting for job 1 only leaves the second worker idle while the queue still has work.

Conversely, if the worker is booking exactly seat 1, switching to seat 2 on its own is wrong. How to handle the lock depends on the business meaning.

Three options when a row is locked

Statement	When it hits a locked row	Use when
FOR UPDATE	Waits for the lock-holding transaction to end	You need that exact row and accept waiting
FOR UPDATE NOWAIT	Raises SQLSTATE 55P03 immediately	You need a fast answer so the app can retry or report busy
FOR UPDATE SKIP LOCKED	Skips that row, looks for another	Taking independent work from a queue

Remember:

• These are options of the row-locking clause, not new isolation levels.
• They only apply to row locks: the statement still takes a normal ROW SHARE table lock and can wait on a conflicting DDL.
• After a 55P03 error in an explicit transaction, you must ROLLBACK (or roll back to a savepoint) before continuing.
• The docs call the result of SKIP LOCKED an “inconsistent view”, not suitable for general purposes. An empty result does not prove the queue is empty: every eligible job may be locked by other workers, or not yet past available_at. The worker sleeps briefly with jitter and polls again.
• lock_timeout is an alternative to NOWAIT: wait up to some duration, then raise a lock-not-available error instead of failing immediately.

A safe claim pattern

A claim records that a worker has taken a job. The design has two phases: a short transaction selects the job and moves it to running; only after commit does the worker call external services.

Two workers: B skips job 1 locked by A, claims job 2, and processes it after commit

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

A locks job 1 and keeps its transaction open (done here to make the behavior visible; in practice the claim transaction must be short).

The claim statement (a CTE selects the row with a locking clause, then an UPDATE targets exactly that row):

BEGIN;
WITH picked AS (
  SELECT id
  FROM demo_queue_jobs
  WHERE status = 'pending' AND available_at <= now()
  ORDER BY available_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE demo_queue_jobs AS j
SET status = 'running', claimed_by = 'worker-b', claimed_at = now(),
    claim_version = j.claim_version + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.payload, j.claim_version;
COMMIT;

Why it is written this way:

• The docs state that a locking clause does not reach into WITH, so to lock inside a CTE the locking clause must go inside the CTE.
• You can change FOR UPDATE to FOR NO KEY UPDATE because the claim does not change key columns, so it does not block foreign key checks (see the conflict table). The measured demo below uses FOR UPDATE.
• LIMIT stops locking once enough rows are taken; you can claim in batches with LIMIT 10 and RETURNING.
• claim_version increments on every claim: an old worker cannot mark done with a stale version.
• When work is done, write the result conditionally: UPDATE ... SET status = 'done' WHERE id = $1 AND status = 'running' AND claim_version = $2. No row returned means the claim was lost; do not overwrite.

Read Committed is required

Workers run under Read Committed (the default). Under Repeatable Read and Serializable, if a candidate row was changed by another committed transaction after the snapshot, the statement throws 40001 instead of skipping.

Under Read Committed, ORDER BY with a locking clause can also return rows out of order if another transaction changes the sort value before the row is locked, so do not expect strict FIFO.

Avoid selecting and then releasing the lock before writing the claim: a SELECT ... SKIP LOCKED run on its own in autocommit releases its lock as soon as the statement ends, and another worker can pick the same job.

What if a worker dies?

Loading diagram…

PostgreSQL does not move running jobs back to pending on its own. Two cases:

1. Dies before committing the claim: the transaction rolls back once the server notices the connection is gone, and the job can be taken again. Detection can be delayed, so set idle_in_transaction_session_timeout and TCP keepalive so a dead worker’s locks get released.
2. Dies after commit: the row stays running. You need a recovery process based on a lease: a time-limited right to process, extended by heartbeats. Do not treat “over 30 seconds” as proof the worker died; recovery must update conditionally so it does not overwrite a fresh heartbeat.

Sketch (an extension beyond the demo: add columns attempts int and lease_until timestamptz; the claim increments attempts and sets lease_until = now() + interval '60 seconds'):

-- The worker extends its lease periodically; UPDATE 0 means the claim is lost, stop processing
UPDATE jobs SET lease_until = now() + interval '60 seconds'
WHERE id = $1 AND status = 'running' AND claim_version = $2;

-- Recovery: only touch expired leases, increasing backoff, stop after 5 attempts
UPDATE jobs
SET status = CASE WHEN attempts >= 5 THEN 'failed' ELSE 'pending' END,
    available_at = now() + interval '10 seconds' * power(2, attempts),
    claimed_by = NULL, lease_until = NULL
WHERE status = 'running' AND lease_until < now();

The lease_until < now() condition is rechecked after taking the row lock under Read Committed, so a heartbeat that commits first wins. The next claim increments claim_version, so the old worker, even if still alive, cannot write done.

The important gap: the worker sends the email, then dies before writing done. The new worker does not know whether the email went out.

1. Worker A claims (claim_version = 1) and commits.
2. A calls the Email API; the email is sent.
3. A dies before UPDATE done.
4. Recovery moves the job back to pending; B claims it (claim_version = 2) and sends again.

claim_version only protects state in the database. Preventing duplicate sends requires idempotency with a key that is stable per business effect, e.g. email-order-102, not per version, because retries of the same email must use the same key. See idempotent consumer.

Queue tables and bloat: the biggest risk

Every job passes through pending, running, done, so each job produces several old versions. The status column is in the partial index’s condition and available_at is in the index, so these updates create extra index entries.

If VACUUM cannot keep up, the claim’s index scan has to step over more and more dead entries, and claim latency creeps up until the queue is clearly slow even with few pending jobs. A long transaction blocking VACUUM (see VACUUM and bloat) speeds this up.

Prevention:

• Delete done jobs or partition by time: once every job in an old partition is done, DETACH PARTITION then DROP TABLE instead of a mass DELETE.
• Give the queue table its own autovacuum settings, e.g. ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01).
• Do not allow long transactions (reports, hung workers) on the same database.
• Monitor: n_dead_tup, the age of the oldest pending job, the average claim duration.
• Old partitions kept for a long time with almost no writes still need freezing (see wraparound).

When to use, when not to

Queue in PostgreSQL	Broker (Kafka, RabbitMQ…)
Independent jobs, no per-key ordering needed	Strict ordering per key or per partition
Need to enqueue in the same transaction as business data	Need replay, long retention, many consumer groups, fan-out
Moderate load; no appetite for operating another system	Throughput high enough that the queue chokes the database

This article’s rule of thumb (not a measurement, no official source): a few hundred to a few thousand claims per second is usually comfortable territory for a mid-sized database.

The real number depends on hardware, schema, indexes and VACUUM speed, so measure on your system. Well beyond that range, or when you need replay and fan-out, use a broker.

Libraries already implement much of this pattern, such as pgmq, Graphile Worker, River, pg-boss and Oban; prefer them over writing your own recovery and retry.

Real-world example

An email queue for independent orders

Orders 101 and 102 need two different emails, and worker B does not need to wait for order 101’s email to send order 102’s: that is the condition that makes SKIP LOCKED a fit.

If several jobs modify the same order and depend on order, you need an extra per-order serialization mechanism.

Real starvation does not come from “being skipped”: a job is only skipped while someone else holds its lock, i.e. a few milliseconds with a short claim transaction.

The real risks are a poison job (a job that always fails, retrying constantly and hogging the head of the queue by ORDER BY available_at) or a worker holding a lock for long. Fix with increasing backoff on available_at and a cap on attempts.

Step-by-step demo with two psql sessions

Run from the repo root; the files are in examples/src/postgres-skip-locked/. Tables are prefixed demo_, for practice databases only. Rerunning setup keeps the old state; for a clean slate use the automated demo below.

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres \
  psql -U hub -d hub -v ON_ERROR_STOP=1 \
  < examples/src/postgres-skip-locked/setup.sql

CREATE TABLE IF NOT EXISTS demo_queue_jobs (
  id bigint PRIMARY KEY,
  payload text NOT NULL,
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'running', 'done', 'failed')),
  available_at timestamptz NOT NULL DEFAULT now(),
  claimed_by text,
  claimed_at timestamptz,
  claim_version bigint NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS demo_queue_jobs_pending_idx
  ON demo_queue_jobs (available_at, id) WHERE status = 'pending';

Open two terminals (A and B) with docker compose -f examples/docker-compose.yml exec postgres psql -U hub -d hub.

-- A: lock job 1 and keep the transaction open
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT id FROM demo_queue_jobs WHERE id = 1 FOR UPDATE;

-- B: NOWAIT raises 55P03 immediately
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT id FROM demo_queue_jobs WHERE id = 1 FOR UPDATE NOWAIT;
ROLLBACK;

-- B: SKIP LOCKED skips job 1, returns id = 2 (with the initial data)
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT id FROM demo_queue_jobs
WHERE status = 'pending' AND available_at <= now()
ORDER BY available_at, id LIMIT 1
FOR UPDATE SKIP LOCKED;
ROLLBACK;

Then run the claim statement from “A safe claim pattern” in B (B gets job 2, payload email-order-102, version 1), and ROLLBACK in A to release job 1’s lock.

The automated demo creates its own schema, uses two connections, then drops its schema:

npx --prefix examples ts-node examples/src/postgres-skip-locked/demo.ts

Results verified on PostgreSQL 17:

NOWAIT: SQLSTATE 55P03
SKIP LOCKED: id=2
Atomic claim: id=2, claim_version=1
Empty result: 3 pending jobs still exist but are locked
Concurrent claims: the two workers got two different ids
Stale completion: UPDATE 0

In the empty-result check, A locks every remaining pending job so B gets nothing, while a plain count still sees three pending jobs: that is the difference between “no work available right now” and “no work left”.

The demo is a smoke test, not proof of safety under load: two connections do not guarantee real time overlap, and the demo has no heartbeat, recovery, backoff or Email API integration.

🎮 Try it

• Choose lock/isolation: SKIP LOCKED for job queues, NOWAIT for buttons that must fail fast.

Production checklist

1. Workers run Read Committed; claim and update to running in one short transaction, call external services after commit.
2. Use a CTE with FOR UPDATE SKIP LOCKED (or FOR NO KEY UPDATE when foreign keys point at the table); write done conditionally on claim_version.
3. Have lease-based recovery, an attempt limit and backoff; handle poison jobs.
4. Idempotency keys stable per business effect for every side effect.
5. Poll with sleeps and jitter; an empty result does not mean the queue is empty.
6. Fight bloat: partition or delete done jobs, dedicated autovacuum settings for the queue table, idle_in_transaction_session_timeout.
7. Monitor: oldest pending job age, claim duration, n_dead_tup, queue length.

Trade-offs

Pros

• Many workers take independent jobs without waiting on the same head-of-queue row.

• Jobs and business data are written in the same database transaction.

• The queue is observable with SQL, no extra broker to operate for simple flows.

< slot="cons">

• The queue adds write load, index scans, bloat and cleanup to the business database.

• The application owns polling, recovery, retry and duplicate effects.

• Skipping locks does not guarantee strict FIFO or fairness between jobs.

Cons

Common mistakes

Selecting and then releasing the lock before writing the claim

A SELECT ... FOR UPDATE SKIP LOCKED run on its own in autocommit releases its lock when the statement ends. Before the worker gets to UPDATE running, another worker can pick the same job. Combine select and update in the same transaction, or use the claim CTE above.

Misconception	Consequence	What to do
ORDER BY id guarantees emails go out in order	Worker B may send job 2 before A	Only use it for independent jobs; serialize when the business requires it
Claimed once means processed exactly once	A crash after the side effect causes a duplicate retry	Idempotency per effect
On a NOWAIT error, retry immediately forever	Constant lock contention	Bounded retry, backoff, deadline
No row returned means the queue is out of work	Misses rows that are locked or not yet past available_at	Poll again after a pause and monitor queue state
Running workers under Repeatable Read to be “safer”	40001 errors when a candidate row changed after the snapshot	Use Read Committed

Self-check

1. A locks job 1, job 2 is ready. Why does NOWAIT raise an error when selecting exactly job 1, while SKIP LOCKED can take job 2?
2. After the claim commits, the row lock is released. What stops a new claim query from taking the job that was just claimed?
3. A worker sends the email and dies before writing done. Why is claim_version not enough to prevent the email being sent again?
4. Why can a long-running queue table get slower over time even with only a few pending jobs?

Summary

Key takeaways

• Choose to wait, report busy or skip based on the business meaning of the row.
• SKIP LOCKED is for independent jobs; an empty result does not mean the queue is empty; workers use Read Committed.
• Claim = select and update in the same short transaction; call external services after commit.
• Recovery, retry and duplicate-effect prevention are the application’s job; queue table bloat is the biggest operational risk.

References

• PostgreSQL 17 — SELECT, The Locking Clause: syntax, limits of NOWAIT/SKIP LOCKED, LIMIT and locks in CTEs.
• PostgreSQL 17 — Explicit Locking: lock types, conflicts and behavior under Repeatable Read.
• PostgreSQL 17 — Transaction Isolation: snapshots and behavior when a row is updated.
• PostgreSQL 17 — Routine Vacuuming: cleaning up old versions and the impact of long transactions.