A job queue in Postgres, when the worker can't be reached

If you already have Postgres, you already have a job queue. One table, one SELECT ... FOR UPDATE SKIP LOCKED query, and workers that pull instead of being pushed to. That last part is what makes it work when the machine doing the job sits on someone's desk behind a home router.

The problem

Our image generator runs on a public server. The thing that actually generates images needs a logged-in desktop session, so it runs on a laptop — behind NAT, on a residential connection, sometimes closed.

So the server cannot call the worker. There is no address to call.

The web request also cannot wait. Generation takes 30–90 seconds; an HTTP request that hangs that long dies to some proxy timeout you do not control.

Two constraints, one shape: the server writes work down, the worker comes and asks for it.

Why the obvious version breaks

The naive queue is a table and this:

SELECT id FROM jobs WHERE status = 'queued' ORDER BY created_at LIMIT 1;
UPDATE jobs SET status = 'claimed' WHERE id = $1;

It works perfectly with one worker and falls apart the moment there are two. Both run the SELECT in the same millisecond, both get job 1, both run it.

That is not theoretical. Three workers, four queued jobs, a 300 ms gap between the select and the update — the table afterwards:

 id | status  | worker
----+---------+--------
  1 | claimed | B
  2 | queued  |
  3 | queued  |
  4 | queued  |

All three ran job 1. Two of them wrote their claim over each other, so the table does not even record that it happened — you find out from the bill. Meanwhile jobs 2, 3 and 4 sat there untouched.

The obvious fix is FOR UPDATE, and it is genuinely correct: the second worker blocks until the first commits, then re-checks and takes a different job. The problem is the word blocks. Measured on the same setup, the second worker sat waiting 4.01 seconds — exactly as long as the first one held its transaction. Add a third worker and it waits behind both. You have bought correctness by turning your workers back into one worker.

What you actually want is for the second worker to skip the row the first one is holding and take the next one. Postgres has had exactly that since 9.5.

The fix

WITH claimed AS (
  UPDATE generation_jobs
     SET status = 'claimed',
         worker_id = $1,
         claimed_at = now(),
         attempts = attempts + 1
   WHERE id = (
     SELECT id FROM generation_jobs
      WHERE status = 'queued'
      ORDER BY created_at
      FOR UPDATE SKIP LOCKED
      LIMIT 1
   )
   RETURNING id, kind, payload
)
SELECT * FROM claimed;

Read it inside out:

• FOR UPDATE takes a row lock on the one job it picked.
• SKIP LOCKED tells any other transaction doing this right now to pretend locked rows do not exist and keep scanning.
• The whole thing is one statement, so select-and-claim cannot interleave.

Ten workers can run this in a tight loop and each gets a different job. No coordination, no lock table, no Redis.

The table it runs against:

CREATE TABLE generation_jobs (
  id               BIGSERIAL PRIMARY KEY,
  user_id          BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind             TEXT NOT NULL,
  status           TEXT NOT NULL DEFAULT 'queued',   -- queued | claimed | done | error
  payload          JSONB NOT NULL,
  result           JSONB,
  error            TEXT,
  credits_reserved INTEGER NOT NULL DEFAULT 0,
  attempts         INTEGER NOT NULL DEFAULT 0,
  worker_id        TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  claimed_at       TIMESTAMPTZ,
  finished_at      TIMESTAMPTZ
);

-- The worker's "what's next" scan, and nothing else, uses this.
CREATE INDEX generation_jobs_queue_idx
  ON generation_jobs (created_at) WHERE status = 'queued';

That partial index matters more than it looks. Without the WHERE status = 'queued' clause the index carries every job you have ever run; with it, the index only ever holds the backlog — which is usually near zero. The queue scan stays the same speed on day 400 as on day 1.

Check it yourself

One container, sixty seconds. Every number above came from running this.

docker run --rm -d --name qdemo -e POSTGRES_PASSWORD=demo -p 55432:5432 postgres:16
until docker exec qdemo pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done

DB=postgresql://postgres:demo@localhost:55432/postgres
psql $DB -q <<'SQL'
CREATE TABLE jobs (id SERIAL PRIMARY KEY, status TEXT NOT NULL DEFAULT 'queued', worker TEXT);
INSERT INTO jobs (status) SELECT 'queued' FROM generate_series(1, 4);
SQL

Worker A claims a job and holds the transaction open for five seconds:

psql $DB -q -c "
BEGIN;
UPDATE jobs SET status='claimed', worker='A'
 WHERE id = (SELECT id FROM jobs WHERE status='queued'
             ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1);
SELECT pg_sleep(5);
COMMIT;" &
sleep 2

Worker B tries, two seconds in, while A is still holding:

time psql $DB -q -c "
UPDATE jobs SET status='claimed', worker='B'
 WHERE id = (SELECT id FROM jobs WHERE status='queued'
             ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
 RETURNING id;"

B gets job 2 in 0.05 s. Delete the two words SKIP LOCKED from B's query and run it again: B gets the right answer, but only after 4.01 s of waiting for A. That gap is your throughput ceiling, and it is the whole reason the two words exist.

docker rm -f qdemo

The two things that bite later

A claimed job is not a finished job. A worker can be closed mid-run, and that job stays claimed forever. You need a sweep:

UPDATE generation_jobs
   SET status = 'queued', worker_id = NULL
 WHERE status = 'claimed'
   AND claimed_at < now() - ($1 * interval '1 second');

Run it on a timer. Requeue anything claimed longer than a generous timeout, and give up — mark it error — past a few attempts, so a job that crashes its worker cannot crash every worker in turn.

If work costs money, reserve it on enqueue and refund it on failure. We hold the credits when the job is written, in the same transaction. If the job fails or gets reaped, the refund happens in the transaction that marks it failed. The credits_reserved column exists so that "what do we owe this user back" is a fact stored on the job and not something you recompute from logs at 3 a.m.

When this is the wrong tool

Postgres queues top out somewhere in the low thousands of jobs per second, and every worker polling is a query. If you are pushing tens of thousands per second or fanning one event out to many consumers, use a broker built for it.

Below that — and "below that" covers most products — a table you can SELECT from is worth more than a broker you cannot inspect. When a customer asks why their job never finished, the answer is a WHERE id = 41, not a stack of correlated log lines.

The code

Runnable, and CI keeps it that way: CSTSolution/examples/postgres-job-queue — the schema, the claim query and the reaper.

git clone https://github.com/CSTSolution/examples
cd examples/postgres-job-queue

Where this goes next

This queue runs in production behind the Game Asset Generator — the browser studio enqueues, a worker on a machine with the generation login pulls, and the image comes back. The same shape is what Workflow Builder uses to run user-defined flows.

Next in this series: how the worker proves it is allowed to claim jobs — a shared-token problem with more sharp edges than it looks.