Building a Concurrent Job Queue with PostgreSQL `SKIP LOCKED`

A database-backed queue can be the right answer when jobs and application data must share a transaction, operational scale is moderate, and adding a broker would create more complexity than value. PostgreSQL gives you the concurrency primitive that makes multiple workers practical: SELECT ... FOR UPDATE SKIP LOCKED.

The primitive is useful, but it is not a complete queue. The robust design is a short claim transaction, a lease that makes abandoned work recoverable, explicit retry semantics, and idempotent job handling.

What `SKIP LOCKED` actually guarantees

FOR UPDATE locks rows against conflicting updates, deletes, and locking reads until the current transaction ends [1][2]. Without SKIP LOCKED, a worker that reaches an already-claimed row waits. With it, the worker skips rows it cannot lock immediately, so several workers can select different jobs without forming a convoy [1].

That behavior has two boundaries worth stating plainly. First, PostgreSQL describes the resulting view as inconsistent and recommends it for queue-like access, not general-purpose reads [1]. Second, skipping applies to row locks; the statement still takes its normal table-level ROW SHARE lock [1][2]. Schema changes that require stronger table locks can therefore still block workers.

Model jobs as a state machine

Start with explicit states and enough metadata to recover work:

CREATE TABLE jobs (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue_name    text        NOT NULL,
    payload       jsonb       NOT NULL,
    status        text        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'running', 'succeeded', 'failed')),
    priority      integer     NOT NULL DEFAULT 0,
    run_at        timestamptz NOT NULL DEFAULT now(),
    attempts      integer     NOT NULL DEFAULT 0,
    max_attempts  integer     NOT NULL DEFAULT 5,
    locked_by     text,
    locked_at     timestamptz,
    last_error    text,
    created_at    timestamptz NOT NULL DEFAULT now(),
    finished_at   timestamptz
);

CREATE INDEX jobs_claimable_idx
    ON jobs (queue_name, priority DESC, run_at, id)
    WHERE status = 'pending';

The partial index stores only rows matching its predicate [5]. PostgreSQL can use a partial index when it can recognize that the query condition implies that predicate, so keep the claim predicate structurally aligned with status = 'pending' [5]. Include id after the business ordering keys as a stable tie-breaker.

Claim jobs atomically, then commit

Use one statement inside a short transaction:

BEGIN;

WITH candidates AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
      AND queue_name = $1
      AND run_at <= now()
    ORDER BY priority DESC, run_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT $2
)
UPDATE jobs AS j
SET status    = 'running',
    locked_by = $3,
    locked_at = now(),
    attempts  = attempts + 1
FROM candidates AS c
WHERE j.id = c.id
RETURNING j.id, j.payload, j.attempts, j.max_attempts;

COMMIT;

The locking subquery selects claimable rows. Competing workers skip those locks and continue to other candidates [1]. The outer UPDATE changes the state and RETURNING yields the rows actually updated [4]. Because both operations are one statement in one transaction, no other worker can observe a selected job as still pending after the claim commits.

Keep this transaction short. Do not call remote services while holding it open. Row locks last until transaction end [2], so long transactions reduce throughput and increase interference with maintenance.

The ORDER BY clause defines preference among rows visible to a worker. PostgreSQL does not promise a stable subset for LIMIT without ordering [1]. Even with ordering, SKIP LOCKED deliberately sacrifices strict global order: a high-priority row already locked by one worker can be bypassed by another. That is the throughput trade-off, not a bug.

Process outside the claim transaction

After commit, execute the job. On success, finalize with ownership protection:

UPDATE jobs
SET status = 'succeeded', finished_at = now()
WHERE id = $1
  AND status = 'running'
  AND locked_by = $2;

On a retryable failure, reschedule with bounded exponential backoff and jitter:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    run_at = CASE
      WHEN attempts >= max_attempts THEN run_at
      ELSE now() + make_interval(secs => $3)
    END,
    locked_by = NULL,
    locked_at = NULL,
    last_error = $4,
    finished_at = CASE WHEN attempts >= max_attempts THEN now() END
WHERE id = $1
  AND status = 'running'
  AND locked_by = $2;

Classify errors rather than retrying everything. Timeouts and transient dependency failures are usually retryable; malformed payloads and permanent authorization failures usually are not. Cap attempts, record the last error, and move exhausted jobs to a terminal state that operators can inspect.

Recover abandoned leases

A process can crash after claiming a job. A periodic reaper should return expired leases to pending or mark them failed when the attempt budget is exhausted:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    locked_by = NULL,
    locked_at = NULL,
    run_at = CASE WHEN attempts >= max_attempts THEN run_at ELSE now() END,
    last_error = 'lease expired',
    finished_at = CASE WHEN attempts >= max_attempts THEN now() END
WHERE status = 'running'
  AND locked_at < now() - interval '5 minutes';

Choose the lease duration above normal execution time, or implement lease renewal for long jobs. The ownership predicate on completion prevents an old worker from marking a job successful after a reaper has reassigned it.

This design is at-least-once, not exactly-once. A worker may complete an external side effect and crash before recording success, after which the lease expires and another worker runs the job again. Make handlers idempotent with a business idempotency key, a uniqueness constraint, or a destination API that accepts idempotency keys. If the job writes only PostgreSQL data, place the business write and success transition in one database transaction.

Isolation and contention details

PostgreSQL defaults to Read Committed, where each command starts with a new snapshot [3]. A locking command can encounter a row changed since its snapshot, wait if needed, and then re-evaluate whether the updated row still matches [2][3]. Keep all eligibility conditions inside the locking query; do not select IDs in one unlocked statement and claim them later.

Batch size is a tuning knob. Larger batches reduce round trips but hold more work per worker and can worsen fairness. Start small, measure claim latency and end-to-end age, then tune. Add workers only while throughput improves; the database, downstream service, or hot index can become the bottleneck.

Watch at least:

queue depth and age of the oldest eligible job;
claim rate, completion rate, retry rate, and terminal failures;
running jobs older than the lease threshold;
transaction duration and lock waits;
per-queue latency and attempts per job.

When to use something else

This pattern is strong when transactional enqueueing with relational data matters more than extreme throughput. A dedicated broker is usually a better fit when you need very high fan-out, native consumer groups across services, long retention and replay, cross-region delivery, or queue isolation from database load.

The implementation test is simple: prove concurrent workers do not claim the same live lease; kill workers after the external side effect and before completion; verify expired leases recover; and confirm duplicates are harmless. SKIP LOCKED solves lock contention during selection. The rest of correctness comes from the state machine around it.

Sources

PostgreSQL, “SELECT — The Locking Clause”: https://www.postgresql.org/docs/current/sql-select.html
PostgreSQL, “Explicit Locking”: https://www.postgresql.org/docs/current/explicit-locking.html
PostgreSQL, “Transaction Isolation”: https://www.postgresql.org/docs/current/transaction-iso.html
PostgreSQL, “UPDATE”: https://www.postgresql.org/docs/current/sql-update.html
PostgreSQL, “Partial Indexes”: https://www.postgresql.org/docs/current/indexes-partial.html

Building a Concurrent Job Queue with PostgreSQL SKIP LOCKED

What SKIP LOCKED actually guarantees