Postgres as a Job Queue: FOR UPDATE SKIP LOCKED Done Properly

Adding a message broker for background jobs means another service to run, secure, back up, and keep consistent with your database. PostgreSQL can serve as a correct, concurrent job queue for a large range of workloads — the core primitive is SELECT … FOR UPDATE SKIP LOCKED, available since 9.5. Mature libraries build on it (pg-boss and Graphile Worker for Node, River for Go, Oban for Elixir, Solid Queue for Rails, Procrastinate for Python); this article covers the mechanics so you can evaluate or build one. (Your App Needs Background Jobs covers the why.)

The core idea

Many workers poll the same table. Without coordination, they'd all grab the same job. FOR UPDATE locks the rows a worker selects; SKIP LOCKED makes other workers skip rows that are already locked instead of waiting for them. Each worker gets a disjoint set of jobs, with no blocking.

Schema

CREATE TABLE jobs (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue        text        NOT NULL DEFAULT 'default',
  kind         text        NOT NULL,
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'ready'
               CHECK (status IN ('ready','running','done','failed')),
  priority     smallint    NOT NULL DEFAULT 0,
  run_at       timestamptz NOT NULL DEFAULT now(),
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 20,
  locked_by    text,
  locked_until timestamptz,
  last_error   text,
  unique_key   text,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- The claim query's index: only rows that could be claimed
CREATE INDEX jobs_claim_idx ON jobs (queue, priority DESC, run_at, id)
  WHERE status = 'ready';

-- Lease recovery
CREATE INDEX jobs_running_idx ON jobs (locked_until) WHERE status = 'running';

-- Optional de-duplication of pending work
CREATE UNIQUE INDEX jobs_unique_pending ON jobs (unique_key)
  WHERE unique_key IS NOT NULL AND status IN ('ready','running');

Claim with a lease, not a long transaction

The naive pattern holds a transaction (and the row lock) open for the whole job. That ties up a connection per in-flight job, and a long-running transaction holds back vacuum across the whole database. Prefer short transactions that claim a lease:

WITH next AS (
  SELECT id FROM jobs
  WHERE queue = $1 AND status = 'ready' AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  LIMIT $2
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running',
    attempts = j.attempts + 1,
    locked_by = $3,
    locked_until = now() + make_interval(secs => $4)
FROM next
WHERE j.id = next.id
RETURNING j.*;

The row locks exist only for this statement's transaction. The lease (locked_until) is what protects the job afterwards.

Ack, retry, or fail

-- success
UPDATE jobs SET status = 'done', locked_by = NULL, locked_until = NULL
WHERE id = $1 AND locked_by = $2;

-- failure: exponential backoff with jitter, or give up
UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'ready' END,
    run_at = now() + make_interval(secs => least(3600, power(2, attempts)) * (0.5 + random())),
    last_error = $3, locked_by = NULL, locked_until = NULL
WHERE id = $1 AND locked_by = $2;

The locked_by = $2 guard is a fencing check: if this worker's lease expired and another worker reclaimed the job, the stale worker's ack updates zero rows and it knows its result is void.

Recover crashed workers

UPDATE jobs SET status = 'ready', locked_by = NULL, locked_until = NULL
WHERE status = 'running' AND locked_until < now();

Run it periodically (or fold the condition into the claim query). Long jobs heartbeat by extending locked_until. Choose lease durations well above normal job duration, and accept the consequence: this is at-least-once delivery. A worker can finish the work, then die before acking, and the job runs again. Handlers must be idempotent. (Handling Webhooks Reliably covers idempotency techniques.)

Wake workers with LISTEN/NOTIFY

Polling every few hundred milliseconds adds latency and load. Notify on enqueue:

-- enqueue
INSERT INTO jobs (queue, kind, payload) VALUES ('email', 'welcome', $1);
SELECT pg_notify('jobs_email', '');

Workers LISTEN jobs_email and claim on notification, plus a slower fallback poll (notifications are lost if nobody is listening, and they're delivered only at commit). Notes: LISTEN needs a dedicated session connection — it doesn't work through a transaction-mode pooler; payloads are limited to 8000 bytes (send nothing, or an ID); and a very high notify rate has its own overhead, so coalesce.

The killer feature: transactional enqueue

Because the queue is in the same database, you can enqueue atomically with your business write:

BEGIN;
INSERT INTO orders (...) VALUES (...) RETURNING id;
INSERT INTO jobs (kind, payload) VALUES ('send_receipt', jsonb_build_object('order_id', ...));
COMMIT;

Either both happen or neither. With an external broker, you'd face the dual-write problem — commit succeeds but the publish fails, or vice versa. (The Transactional Outbox Pattern generalises this.)

Operational concerns

Bloat. A queue table is high-churn: every job is inserted, updated several times, and eventually removed. Dead tuples accumulate; the claim index degrades. Mitigations:

Delete or archive done jobs promptly (or move them to a partitioned history table and drop partitions).
Tune autovacuum per table (ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01, …)).
Watch n_dead_tup in pg_stat_user_tables.
Avoid long-running transactions anywhere in the database — they prevent cleanup of dead rows for every table, and the queue suffers first.

Contention. SKIP LOCKED scans past locked rows; with very many workers claiming from the head of the same queue, they can burn CPU skipping each other. Claim in batches (LIMIT 10), shard by queue, and keep the partial index tight.

Ordering. Priority and run_at order is best-effort under concurrency and retries. If you need strict per-entity ordering (all jobs for account 42 in sequence), add a per-key lock (e.g. pg_try_advisory_xact_lock(hashtext(key))) or a "one running job per key" constraint.

Observability. Queue depth per queue, oldest ready job age, failure rate, retry counts, and lease expirations. Alert on age, not just depth.

Where it stops being the right tool

Postgres queues comfortably handle many small-to-medium workloads — commonly into the hundreds or low thousands of jobs per second on decent hardware, depending on job size and tuning. Consider a dedicated broker when you need:

Sustained very high throughput where queue churn competes with your primary workload.
Fan-out pub/sub to many independent consumers, or replayable event streams (Kafka-style).
Cross-service messaging where services don't share a database.

Until then, one database with transactional enqueue is simpler, and simplicity is operational reliability.

EasySpawn runs your app, its workers, and a managed Postgres in one persistent workspace — so a Postgres-backed queue needs no extra infrastructure, and Claude Code can load-test claim throughput against the real database. See how it works or join the waitlist.