Skip to content
Tutorials Center
My learning

Topics

Lesson 86 of 90 ยท Projects

Project: Job Queue with SKIP LOCKED

Build a reliable job queue in PostgreSQL: a jobs table, safe claiming with FOR UPDATE SKIP LOCKED, retries with backoff, dead letters and lease timeouts.

Almost every application needs background work: send the welcome email, resize the uploaded image, call the payment provider again in a minute. The usual answer is to add a message broker. But if you already run PostgreSQL, a table can be a perfectly good queue, with real advantages: jobs can be enqueued in the same transaction as the data they belong to (no "the row was saved but the message was lost" bug), you can query and fix the queue with SQL, and there is one less system to operate.

The hard part is letting many workers pull jobs at the same time without two of them taking the same job and without waiting on each other. PostgreSQL's answer is SELECT ... FOR UPDATE SKIP LOCKED, introduced in Locks, Row Locking and Deadlocks. In this project you design the table, claim jobs safely, handle failures with retries and a dead-letter state, recover from crashed workers, and keep the queue fast.

What runs live. FOR UPDATE SKIP LOCKED needs several concurrent sessions and exists only in PostgreSQL, so it is shown in read-only blocks. SQLite has a single writer, which makes a one-statement claim atomic by itself, so the schema, the claim logic, retries, dead letters and lease recovery all run live. A small clock table replaces now() so every run gives identical output.

The shape of a queue

A job has a life

queued Waiting for its run_at time.
running A worker holds a lease on it.
done Finished successfully.
dead Out of attempts. A human looks.
1 / 2
stateDiagram-v2
    [*] --> queued: enqueue
    queued --> running: claim
    running --> done: success
    running --> queued: failure with attempts left
    running --> dead: failure, no attempts left
    running --> queued: lease expired

Every job moves through these states; the interesting transitions are the failure paths.

The PostgreSQL table

SQL
-- PostgreSQL (read-only)
CREATE TABLE jobs (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue        text        NOT NULL DEFAULT 'default',
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'queued'
               CHECK (status IN ('queued', 'running', 'done', 'dead')),
  priority     integer     NOT NULL DEFAULT 0,
  attempts     integer     NOT NULL DEFAULT 0,
  max_attempts integer     NOT NULL DEFAULT 5,
  run_at       timestamptz NOT NULL DEFAULT now(),
  locked_at    timestamptz,
  locked_by    text,
  last_error   text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  finished_at  timestamptz
);

-- only the waiting jobs are indexed, so the index stays tiny
CREATE INDEX jobs_ready_idx ON jobs (queue, priority DESC, run_at, id)
  WHERE status = 'queued';

The payload holds whatever the worker needs, usually IDs rather than data. The index is a partial index on status = 'queued': finished jobs (the vast majority, over time) are not in it, so it stays small and workers find the next job in a few page reads. Because run_at is part of the key, the "not before this time" filter is cheap, and scheduling a job for later is just a different run_at.

Enqueuing is an ordinary insert, and it can share a transaction with the business change:

SQL
-- PostgreSQL (read-only)
BEGIN;
INSERT INTO orders (customer_id, total_cents) VALUES (42, 5900);
INSERT INTO jobs (queue, payload) VALUES ('email', '{"template": "order_confirmation", "order_id": 1001}');
COMMIT;   -- both rows appear together, or neither does

Claiming a job safely

The claim must find the best waiting job, lock it so nobody else takes it, and mark it running, all in one step. PostgreSQL does it in one statement:

SQL
-- PostgreSQL (read-only)
WITH next AS (
  SELECT id
  FROM jobs
  WHERE queue = 'email' AND status = 'queued' AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', attempts = j.attempts + 1,
    locked_at = now(), locked_by = 'worker-7'
FROM next
WHERE j.id = next.id
RETURNING j.id, j.payload, j.attempts;

Read it from the inside out. The SELECT picks the first ready row in priority order. FOR UPDATE takes a row lock on it. SKIP LOCKED says: if a row is already locked by another transaction, do not wait, pretend it is not there and look at the next one. So when ten workers run this statement at the same moment, each gets a different job and none blocks. The UPDATE ... RETURNING flips the row to running and hands the job to the worker in the same round trip. If no job is ready, zero rows come back and the worker sleeps.

Without SKIP LOCKED, the other nine workers would queue up behind the first worker's row lock, and after it commits they would re-check the row, find it no longer matches, and many would return nothing. That is the throughput cliff the clause removes.

One honest caveat: SKIP LOCKED gives each worker "some available job", not a strict global order. Under heavy concurrency, a job later in the order may be taken before an earlier one that was locked at that instant. For queues that must be strictly FIFO per customer or per entity, use one queue partition per key or advisory locks. For ordinary background work, approximate priority order is fine.

A very important design choice is not holding the claim transaction open while the job runs. A transaction that lasts minutes keeps its row lock and a connection, holds back autovacuum for the whole database, and disappears if the connection drops. Instead, commit the claim immediately (the status is now running), do the work with no transaction open, and report the result with a second short statement. The status and locked_at columns are the lease that other workers respect.

The same logic, live

In SQLite one UPDATE with a subquery is atomic, because only one writer runs at a time, so it can stand in for the claim. The clock table holds the current time. Three workers ask for work; the high-priority job goes first, then the two normal jobs in the order they became due. The job scheduled for the future is not eligible, and the fourth request finds nothing:

SQL Claiming jobs by priority and due time

Press Run (or Ctrl + Enter) to see the result here.

The email job (priority 5) goes to the first worker, then the two resize jobs go to the next two workers in order of their run_at. The report job is scheduled for noon, so the fourth claim returns no row and the job stays queued. The PostgreSQL claim behaves the same, with the extra guarantee that it stays correct when the four statements run at the same instant on four connections.

Failures, retries and dead letters

Jobs fail: the email server times out, the image is corrupt. Retrying immediately usually fails again, so each retry waits longer (exponential backoff). After max_attempts the job moves to dead, the dead-letter state, where it stops consuming resources and waits for a human. A single UPDATE can express both outcomes. In PostgreSQL:

SQL
-- PostgreSQL (read-only): report a failure for job :id
UPDATE jobs
SET status     = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'queued' END,
    run_at     = CASE WHEN attempts >= max_attempts THEN run_at
                      ELSE now() + make_interval(secs => 10 * power(2, attempts)) END,
    last_error = :error,
    locked_at  = NULL, locked_by = NULL,
    finished_at = CASE WHEN attempts >= max_attempts THEN now() END
WHERE id = :id AND status = 'running' AND locked_by = :worker;

-- and for success
UPDATE jobs SET status = 'done', finished_at = now(), locked_at = NULL, locked_by = NULL
WHERE id = :id AND status = 'running' AND locked_by = :worker;

The AND status = 'running' AND locked_by = :worker condition is a safety check: if a worker is so slow that its lease expired and another worker took the job, the slow worker's late report matches zero rows instead of overwriting the new owner's work. Add a small random amount to the delay (jitter) in real systems so thousands of failing jobs do not all come back at the same second.

The live simulation walks one job (max_attempts of 2) through the whole story: claim, fail, a premature claim that finds nothing because the job is waiting out its backoff, the clock moving forward, a second claim and a second failure that kills the job. Backoff is 10 * 2^attempts seconds, written with a bit shift in SQLite:

SQL Retry with backoff, then dead letter

Press Run (or Ctrl + Enter) to see the result here.

After the first failure the job is queued again with run_at set to 10:00:20 (attempt 1 gives 10 * 2^1 = 20 seconds). The premature claim returns nothing, so no job is running. At 10:00:30 the job is due; its second failure exhausts the two attempts and the status becomes dead, with the last error kept for whoever investigates. The usual operational routine is a dashboard or alert on status = 'dead', and a manual "retry" that sets the status back to queued with attempts = 0.

Crashed workers: leases and visibility timeouts

A worker that crashes mid-job leaves its row stuck in running forever. The fix is a visibility timeout (also called a lease): a running job whose locked_at is older than a limit is presumed abandoned and goes back to the queue. Run a reaper statement every minute or so from one worker, or from pg_cron:

SQL
-- PostgreSQL (read-only)
UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'queued' END,
    locked_at = NULL, locked_by = NULL,
    last_error = 'lease expired'
WHERE status = 'running' AND locked_at < now() - interval '5 minutes';

Pick the timeout longer than your slowest legitimate job, or let long jobs extend their lease by updating locked_at as a heartbeat. Since a reclaimed job may in fact still be running on a slow worker, jobs must be idempotent: running one twice must not charge a customer twice. Use an idempotency key in the payload and check it before the side effect.

SQL Reclaiming expired leases and monitoring the queue

Press Run (or Ctrl + Enter) to see the result here.

The reaper returns two rows: job 1 goes back to queued, and job 3 (already on its third and final attempt) becomes dead instead of looping forever. The healthy worker's job is untouched. The monitoring queries then show the new totals (three queued, one running, one done, one dead), and the oldest ready job has been waiting 30 minutes: it is the reclaimed job 1, whose run_at is 10:00. In production, the age of the oldest ready job is the most useful single alert for a queue: it grows whenever workers are too few, too slow or down.

Keeping the queue fast

A queue table is the opposite of a normal table: rows are inserted, updated a few times and finished, all within seconds. That churn creates dead tuples quickly, so a few habits matter.

  • Keep the table small. Delete finished jobs after a day or move them to a history table in batches. A queue of a few thousand live rows is fast; a queue that keeps ten million finished rows in the same table and index is not.
  • Tune autovacuum for this table. Lower the scale factor, for example ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 0), so dead rows are cleaned continuously. See VACUUM, Autovacuum and Bloat.
  • Avoid long transactions anywhere in the database. One idle-in-transaction session blocks cleanup of dead jobs and slowly makes every claim scan more dead index entries.
  • Claim in batches (LIMIT 10) when jobs are tiny, to cut round trips; process them one by one but report them in one statement.
  • Do not poll in a tight loop. Sleep a second or two when the queue is empty, or let NOTIFY wake workers immediately (see Event Triggers, LISTEN and NOTIFY). Treat the notification as a hint and still poll occasionally, because notifications are lost if nobody is listening.
  • Measure before worrying. A single modest server can usually handle thousands of job claims per second with this design, which is more than most applications ever need. If you outgrow it, a dedicated broker is a reasonable next step, and libraries and extensions that package this pattern exist for most languages.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SKIP LOCKED do when ten workers claim jobs at the same moment?

    Rows locked by another transaction are treated as invisible for this query, so concurrent workers spread over different jobs instead of queuing behind a single lock.
  2. 2. Why should the worker commit the claim right away instead of keeping the transaction open while the job runs?

    Use a status column and a lease instead of a held lock. The work then runs with no transaction open and the result is reported with a short second statement.
  3. 3. Why does the failure UPDATE include AND status = 'running' AND locked_by = :worker?

    If the job was reclaimed and now belongs to another worker, the late report matches no rows and changes nothing.
  4. 4. A job's lease expired but the original worker was merely slow and is still running it. What protects you from running its side effect twice?

    Queues like this deliver at least once. Making each job safe to run twice is the standard defense.

Summary

  • A jobs table with status, run_at, attempts, a lease (locked_at, locked_by) and a partial index on queued rows is a complete queue.
  • Claim with one statement: SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1 inside a CTE, then UPDATE ... RETURNING. Concurrent workers get different jobs and never block each other.
  • Commit the claim immediately and do the work outside any transaction; the lease, not a held lock, marks ownership.
  • Retry failures with exponential backoff (and jitter), and move jobs to a dead state after max_attempts.
  • Reclaim expired leases with a reaper statement, and write idempotent jobs because delivery is at least once.
  • Keep the table small, tune autovacuum, avoid long transactions, and alert on the age of the oldest ready job.

Next: Project: Multi-Tenant SaaS Schema.