PostgreSQL Concurrent Job Claiming

Plenty of teams run their background jobs straight out of PostgreSQL instead of adding a message broker. It’s a reasonable choice for moderate volumes: the jobs live next to the data they touch, you can enqueue a job in the same transaction that creates the order, and there’s one less system to operate. The hard part is the claim. When eight workers ask “what should I do next?” at the same moment, each needs a different answer, and none of them should block waiting for the others.

This is a favourite system design follow-up because the naive version looks fine in a demo and breaks under the first real load.

Why SELECT then UPDATE hands out the same job twice

Here’s the version most people write first:

-- worker code, step 1
SELECT id FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1;
-- application gets id 41, then step 2
UPDATE jobs SET status = 'running', worker_id = 'w3' WHERE id = 41;

Between those two statements there’s a gap, and two workers can both read id 41 inside it. Both run the update, both believe they own the job, and the customer gets two emails. Adding AND status = 'pending' to the UPDATE and checking the affected row count helps, because the second worker’s update now touches zero rows, but that worker has wasted a round trip and has to start over. Under load most workers lose that race on most attempts.

Adding plain FOR UPDATE to the SELECT closes the gap but swaps one problem for another: every worker queues behind whoever locked the oldest row. Eight workers become one worker with seven spectators.

The claim statement with SKIP LOCKED

SKIP LOCKED tells PostgreSQL to pass over any row another transaction has already locked, instead of waiting for it. Put it in a CTE that picks one candidate, and update that candidate in the same statement:

WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status      = 'running',
    claim_token = gen_random_uuid(),
    lease_until = now() + interval '30 seconds',
    attempts    = j.attempts + 1
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.id, j.claim_token, j.attempts;

A single statement outside an explicit BEGIN is its own transaction, so the lock taken by the SELECT is held only for the instant it takes to write the row and commit. Walk through two workers arriving together. Worker A’s CTE finds job 41 and locks it. Worker B’s CTE also looks at job 41 first, sees the lock, skips it, and locks job 42. Each UPDATE writes its own row, each returns one id, and neither waited. If the queue is empty, or every pending row is currently locked, the statement returns zero rows and the worker sleeps or waits for a NOTIFY.

To claim a batch, change LIMIT 1 to LIMIT 10. Same reasoning, fewer round trips.

The queue also needs an index that matches the predicate and the order, or every claim scans the whole table, including the millions of done rows from last year:

CREATE INDEX jobs_claimable ON jobs (created_at, id) WHERE status = 'pending';

A partial index stays small because finished jobs drop out of it. Check with EXPLAIN that the claim uses it rather than assuming.

Note

No PostgreSQL server was available where this article was written, so the SQL in this section is shown as written for PostgreSQL 13 or later (gen_random_uuid() became built-in in version 13) and wasn’t executed here. The lease logic in the next section was run against SQLite, which lacks SKIP LOCKED but supports UPDATE ... RETURNING.

Claim, commit, then work

The shape of a worker loop matters as much as the SQL. A common mistake is wrapping everything in one transaction:

BEGIN
  claim job            (row locked)
  call payment API     (3 seconds, or 30 if it's having a bad day)
  mark job done
COMMIT

That design has one real advantage: if the worker dies, its connection drops, the transaction rolls back, and the job is pending again automatically. The costs are bigger. Each in-flight job pins a database connection for its whole duration, so ten workers with slow jobs can exhaust a pool sized for web traffic. Long transactions also hold back vacuum cleanup across the whole database. For anything that takes more than a moment, commit the claim immediately and do the work afterwards.

Once you commit early, a status of running no longer tells you whether anyone is actually running it. That’s what the lease is for.

Leases and claim tokens for crashed workers

lease_until says how long the claim is valid. A worker that’s still busy extends it periodically (a heartbeat). A worker that crashed stops extending it, and once it expires the job is fair game again. You can either let the claim query pick up expired rows directly, or run a small reaper on a schedule:

UPDATE jobs
SET status = 'pending', claim_token = NULL
WHERE status = 'running' AND lease_until < now();

Expiry introduces a new race. Worker A claims job 1, then stalls on a garbage collection pause or a slow network call longer than its lease. Worker C reclaims the job and starts on it. Then A wakes up and marks the job done. Without a guard, A has just overwritten C’s state. The claim token prevents that, because completion checks that the caller still holds the current claim:

UPDATE jobs
SET status = 'done', claim_token = NULL
WHERE id = $1 AND claim_token = $2 AND status = 'running';

Here is that timeline simulated in SQLite with 30-second leases. A claims job 1 at t=0, B claims job 2 at t=5, A stalls, and C claims at t=40 after A’s lease ran out:

A claims at t=0 -> [(1, 1)]
B claims at t=5 -> [(2, 1)]
C claims at t=40 -> [(1, 2)]
A completes job 1 rows updated: 0
C completes job 1 rows updated: 1
[(1, 'done', 2, None), (2, 'running', 1, 'tok-b'), (3, 'pending', 0, None)]

The tuples after each claim are (id, attempts). A’s late completion updates zero rows, which the worker must treat as “I lost this job” and stop, not as success. Job 1 shows attempts = 2, the count you’d use to send a job to a failed state after, say, five tries, so one poison job can’t cycle forever.

What SKIP LOCKED does not promise

Two claims interviewers probe:

Strict order. Skipping a locked row means a newer job can start before an older one that’s briefly locked, and with many workers, jobs finish in no particular order. If job 2 depends on job 1, the queue alone won’t enforce it. You’d need per-key serialisation, for example claiming only the oldest pending job per customer_id.

Exactly-once effects. The database guarantees only one worker holds the claim at a time. It knows nothing about the email server. If a worker sends the email and crashes before marking the job done, the lease expires and another worker sends it again. That’s at-least-once delivery, and the honest fix is on the receiving side: send an idempotency key (the job id works) so the second attempt is recognised as a duplicate. Payment providers such as Stripe accept exactly this kind of key.

SKIP LOCKED is also wrong outside queues. A report that silently ignores locked rows produces an incomplete answer with no error.

Practice question: the queue that does one job at a time

A team runs six workers against this claim query and notices throughput is no better than with one worker. Database monitoring shows five sessions waiting on locks most of the time.

BEGIN;
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 1 FOR UPDATE;
-- ... process the job for ~2 seconds ...
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

What’s wrong, and what would you change?

Answer. Two things combine. FOR UPDATE without SKIP LOCKED makes every other worker block on the same oldest pending row, and the transaction stays open for the whole two seconds of processing, so the wait lasts that long. When the first worker commits, its row is no longer pending; the waiting workers recheck it, find it doesn’t match, and that LIMIT 1 query can come back empty, so they loop again. The fix is to add SKIP LOCKED and split the transaction: claim with a single UPDATE ... RETURNING that sets running, a lease and a token, commit, process, then complete with a token check. Mention the partial index on pending rows, and the new failure mode you’ve introduced (crashed workers leaving running rows) along with its fix (lease expiry and an attempts limit). Naming the trade-off unprompted is what separates a good answer from a memorised one.

Related reading

• SQL interview questions and SQL practice MCQs
• PostgreSQL row locks: FOR UPDATE, NOWAIT and lost updates
• Partial indexes for keeping the claimable set small
• PostgreSQL SELECT reference: the locking clause