Postgres SKIP LOCKED for Work Queues

A work queue stores tasks until background processes, called workers, are ready to run them. In a Postgres-backed queue, each task is a row. Two workers must not claim the same row at the same time, and a task must become available again if its worker crashes.

SELECT ... FOR UPDATE locks a chosen row for the current transaction. SKIP LOCKED tells another worker to move past that row instead of waiting for it. Together they solve the short claim transaction. They do not keep a job owned after the transaction commits, so the row also needs a lease and a fencing token.

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE (status = 'pending' AND run_at <= now())
     OR (status = 'running' AND lease_until < now())
  ORDER BY run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs AS j
SET status = 'running',
    claimed_by = $1,
    claimed_at = now(),
    lease_until = now() + $2::interval,
    attempt = j.attempt + 1
FROM next_job
WHERE j.id = next_job.id
RETURNING j.*;

The common table expression selects and locks one candidate. Other claimers skip that row. The UPDATE records the owner, expiry, and a new attempt number before the same statement commits. The row lock then disappears; lease_until is the durable evidence that the worker may still be alive.

Two partial indexes keep the claim from scanning completed history:

CREATE INDEX jobs_pending_due_idx ON jobs (run_at, id)
WHERE status = 'pending';
 
CREATE INDEX jobs_running_lease_idx ON jobs (lease_until, id)
WHERE status = 'running';

The crash that the row lock cannot solve#

Worker A claims job 42 with attempt 7. The transaction commits, so Worker A no longer holds a row lock. It then pauses long enough for the lease to expire. Worker B reclaims the same row as attempt 8 and finishes first. If Worker A wakes up and writes without checking its attempt, its stale result can replace Worker B's result.

Completion therefore includes the returned attempt number:

UPDATE jobs
SET status = 'done',
    result = $3,
    completed_at = now(),
    lease_until = NULL
WHERE id = $1
  AND status = 'running'
  AND attempt = $2
RETURNING id;

No returned row means the worker lost ownership. It must discard its local result. Long jobs renew the lease with the same WHERE id = $1 AND attempt = $2 fence; renewal failure tells the worker to stop committing progress.

What the pattern guarantees#

The claim gives workers different rows without a central dispatcher. The lease makes a crashed worker's row eligible again. The attempt number fences stale database writes. RETURNING gives the worker the row and its fence in one statement.

The fence protects Postgres state only. If Worker A called an email or payment API before it lost the lease, Worker B cannot undo that call. Outside effects still need a stable idempotency key, provider lookup, or an explicit outcome_unknown state before retry.

Caveats#

• There is no built-in retry policy or dead-letter state. Classify errors, bound attempts, and keep the final reason on the row.
• SKIP LOCKED is a throughput tool, not a fairness guarantee. A row that is repeatedly locked can be skipped for many claim cycles; use a stable order, monitor oldest-ready age, and add explicit tenant fairness when needed.
• Status, heartbeat, and retry updates create dead tuples. Watch vacuum and index growth on a hot queue table.
• Polling adds database load even when no work is ready. Back off empty polls or pair the table with a notification that is only a wake-up hint.
• One hot table has a ceiling. Measure claim latency and database headroom for the real workload, then move delivery to a broker when the database is doing more queue work than business work.

Use this pattern for a modest queue when Postgres is already the durable source of truth and the team can own leases, retries, fairness, and cleanup. A broker is a better fit for high fan-out, large backlogs, or independent replay needs. Neither choice removes the outside-effect boundary.

Sources#

• PostgreSQL documentation: SELECT locking clauses
• PostgreSQL documentation: row-level locks
• PostgreSQL documentation: partial indexes