Postgres as a job queue: SKIP LOCKED, leases and fencing tokens

If your app already runs on Postgres, you may not need a separate queue for background jobs. A table, one index and three or four queries get you a queue that is transactional with the rest of your data, easy to inspect with SQL, and fast enough for most workloads.

Claiming a job is easy. The hard part is a worker that freezes halfway through. For that you need leases, so stuck jobs come back, and fencing tokens, so a worker that wakes up late can't overwrite the one that took over.

The table

CREATE TABLE jobs (
  id           bigserial   PRIMARY KEY,
  queue        text        NOT NULL,
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'ready', -- ready | running | done | dead
  run_at       timestamptz NOT NULL DEFAULT now(),
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 5,
  lease_until  timestamptz,
  fence        bigint      NOT NULL DEFAULT 0,
  last_error   text
);
 
CREATE INDEX jobs_ready_idx ON jobs (queue, run_at) WHERE status = 'ready';
CREATE INDEX jobs_lease_idx ON jobs (lease_until) WHERE status = 'running';

Both indexes are partial. One covers ready jobs for claiming, the other covers running jobs for the lease check below. They stay small even when the table holds millions of finished jobs. fence is a counter that goes up every time someone claims the job. More on that below.

Claiming jobs with SKIP LOCKED

FOR UPDATE SKIP LOCKED (in Postgres since 9.5, docs) locks the rows it selects and skips any row another transaction already has locked. Ten workers running the same query each get different jobs, with no blocking and no double claims.

UPDATE jobs
SET status      = 'running',
    attempts    = attempts + 1,
    fence       = fence + 1,
    lease_until = now() + interval '30 seconds'
WHERE id IN (
  SELECT id FROM jobs
  WHERE queue = $1 AND status = 'ready' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload, fence;

This runs as one short transaction. The row locks are released as soon as it commits. The worker then does the actual work outside any transaction, holding only the job id and the fence value it got back.

That's a deliberate choice. The other common pattern keeps the transaction open for the whole job, so the row lock itself marks the job as taken. It's simpler, but a long job then means a long-open transaction, which holds a connection, blocks vacuum from cleaning up dead rows, and leaves no record of the work if the connection drops. Leases avoid all three.

Leases: getting stuck jobs back

A lease is a promise with an expiry: "I'm working on this until lease_until." If the worker crashes, the lease runs out and the job becomes claimable again.

Long jobs extend the lease with a heartbeat:

UPDATE jobs
SET lease_until = now() + interval '30 seconds'
WHERE id = $1 AND fence = $2 AND status = 'running';

If this updates zero rows, the worker has lost the job. It should stop and throw away its work.

A reaper puts expired jobs back in the queue. Run it every few seconds from any worker; it's safe to run concurrently.

UPDATE jobs
SET status      = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'ready' END,
    run_at      = now(),
    lease_until = NULL
WHERE status = 'running' AND lease_until < now();

Jobs that keep crashing their worker end up dead instead of looping forever.

The problem leases don't solve

Here's the failure that bites people. Worker A claims a job, then pauses: a long GC pause, a VM migration, a slow network call. Its lease expires. The reaper puts the job back, and worker B claims it. Then A wakes up, unaware any time has passed, and writes its result.

Now two workers both think they own the job. Clocks and timeouts can't prevent this. A paused process doesn't know it was paused. Martin Kleppmann's How to do distributed locking walks through the same problem for lock services.

Fencing tokens

The fix is the fence column. Every claim increments it, and every write a worker makes carries the fence it was given. Writes check it.

Completing a job:

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

Failing with exponential backoff:

UPDATE jobs
SET status      = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'ready' END,
    run_at      = now() + interval '1 second' * power(2, attempts),
    lease_until = NULL,
    last_error  = $3
WHERE id = $1 AND fence = $2 AND status = 'running';

When worker A wakes up with fence 1 and B has already claimed with fence 2, A's update matches nothing. The worker checks the row count and gives up.

The same idea extends past the jobs table. If the job writes to another table, store the fence there too and only accept newer ones:

UPDATE reports
SET body = $2, fence = $3
WHERE id = $1 AND fence < $3;

A fence only protects systems that check it. If the job sends an email or calls a payment API, the fence can't stop a duplicate. That needs idempotency keys, which is a topic of its own.

The whole lifecycle

Things to watch in production

• Polling adds up. Ten workers polling every 100 ms is a hundred queries a second against an empty queue. Poll less often and use LISTEN/NOTIFY to wake workers early when a job is inserted. Treat the notification as a hint, never the only trigger, since a worker that isn't listening at that moment never sees it.
• Every claim and completion is an UPDATE, which leaves a dead row behind. Keep autovacuum healthy, and delete or archive done jobs on a schedule.
• One idle-in-transaction connection holds back vacuum for the whole database, and the queue table feels it first.
• Pick the lease length on purpose. Too short and healthy jobs get stolen mid-run. Too long and a crashed job sits idle. Heartbeat at about a third of the lease.
• If you need very high throughput, fan-out to many consumers, or replay of old messages, a log-based system such as Kafka fits better. Below that, Postgres is usually enough.

Takeaway

• FOR UPDATE SKIP LOCKED lets many workers claim jobs from one table without blocking each other.
• Claim in a short transaction and record a lease. Don't hold a transaction open while the job runs.
• Leases bring back jobs from dead workers, but they can't stop a paused worker from waking up and writing late.
• A fencing token on every write closes that gap. Check the row count, and stop when it's zero.