Building a job queue with Postgres

Draft

Emails, webhooks, PDFs, image resizing, delayed reminders: work that shouldn't block a request goes to a job queue. A table is enough, building on SKIP LOCKED from the locks lesson. It's what Solid Queue, Oban, River, graphile-worker, pg-boss and pgmq do.

Delivery guarantees

Each job goes to one of N workers. What happens when that worker crashes?

Guarantee	How	Worker crashes mid-job
At most once	Mark done, then work	Job lost
At least once	Work, then mark done	Job runs again
Exactly once	Impossible	—

Nobody can do exactly-once delivery: not Postgres, Kafka, or SQS. If a worker's "done" never arrives, the queue can't tell "done, then crashed" from "crashed first". That's the Two Generals' Problem (a simpler cousin of the Byzantine Generals Problem; see also You Cannot Have Exactly-Once Delivery). You get at-least-once plus idempotent work, or exactly-once processing inside Postgres, shown below.

The migration

Loaded Database:postgres-job-queue.database-init

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  total_cents INTEGER NOT NULL
);

CREATE TABLE jobs (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'running', 'done', 'failed')),
  attempts INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,
  run_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  locked_until TIMESTAMPTZ,
  last_error TEXT
);

-- Workers only ever look for pending jobs that are due, oldest first.
CREATE INDEX jobs_pending_idx ON jobs (run_at, id) WHERE status = 'pending';

CREATE TABLE invoices (
  order_id INTEGER PRIMARY KEY REFERENCES orders (id),
  total_cents INTEGER NOT NULL
);

The seed

INSERT INTO orders (id, customer, total_cents)
VALUES
  (1, 'alice', 4200),
  (2, 'bob', 1500),
  (3, 'carol', 9900);

-- Three jobs waiting to run.
INSERT INTO jobs (kind, payload)
VALUES
  ('send_receipt', '{"order_id": 1}'),
  ('send_receipt', '{"order_id": 2}'),
  ('create_invoice', '{"order_id": 3}');

-- Two jobs whose workers died ten minutes ago, so their leases have expired.
INSERT INTO jobs (kind, payload, status, attempts, run_at, locked_until)
VALUES
  ('send_receipt', '{"order_id": 3}', 'running', 1,
   now() - interval '10 minutes', now() - interval '5 minutes'),
  ('sync_crm', '{"customer": "carol"}', 'running', 3,
   now() - interval '10 minutes', now() - interval '5 minutes');

The naive queue

Each session is a real, separate worker connection.

Session AStep 1Worker A looks for the next job

SELECT id, kind, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1;

id	kind	payload
1	send_receipt	[object Object]

Session BStep 2Worker B looks for the next job

SELECT id, kind, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1;

id	kind	payload
1	send_receipt	[object Object]

Session AStep 3Worker A claims job 1

UPDATE jobs SET status = 'running' WHERE id = 1
RETURNING id, status;

id	status
1	running

Session BStep 4Worker B claims job 1 too

UPDATE jobs SET status = 'running' WHERE id = 1
RETURNING id, status;

id	status
1	running

Both workers own job 1. Alice gets two receipts.

Claiming with SKIP LOCKED

Session AStep 1Worker A claims a job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
1	send_receipt	[object Object]	1

Session BStep 2Worker B claims a job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
2	send_receipt	[object Object]	1

Job 1 is locked, so SKIP LOCKED takes job 2 instead of waiting.

Session CStep 3Worker C claims a job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
3	create_invoice	[object Object]	1

Session DStep 4Worker D claims a job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

No rows

Everything is locked: no job, no waiting.

Session AStep 5Worker A finishes job 1

UPDATE jobs SET status = 'done' WHERE id = 1;

COMMIT;

No rows

Session BStep 6Worker B crashes

ROLLBACK;

No rows

A crash never commits: Postgres rolls the transaction back, like this ROLLBACK.

Session DStep 7Worker D tries again

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
2	send_receipt	[object Object]	1

Job 2 is back, so nothing is lost. But attempts is 1 again: the crash rolled it back too.

Correct and at-least-once, but a job that always crashes retries forever, and long jobs hold transactions open.

Leases: claim, commit, then work

Like SQS's visibility timeout:

UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    locked_until = now() + interval '5 minutes'
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

Reclaiming expired leases:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    locked_until = NULL,
    last_error = 'lease expired'
WHERE status = 'running'
  AND locked_until < now()
RETURNING id, kind, status, attempts;

Session AStep 1Worker A claims a job with a 5-minute lease

UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    locked_until = now() + interval '5 minutes'
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
1	send_receipt	[object Object]	1

The claim commits immediately. No transaction stays open while Worker A works.

Session BStep 2Which running jobs have an expired lease?

SELECT id, kind, attempts, locked_until < now() AS lease_expired
FROM jobs
WHERE status = 'running'
ORDER BY id;

id	kind	attempts	lease_expired
1	send_receipt	1	false
4	send_receipt	1	true
5	sync_crm	3	true

Session BStep 3Reclaim the expired leases

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    locked_until = NULL,
    last_error = 'lease expired'
WHERE status = 'running'
  AND locked_until < now()
RETURNING id, kind, status, attempts;

id	kind	status	attempts
4	send_receipt	pending	1
5	sync_crm	failed	3

Job 4 is retried. Job 5 is out of attempts: it goes to 'failed', the dead-letter queue.

Session CStep 4Worker C claims a job

UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    locked_until = now() + interval '5 minutes'
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
4	send_receipt	[object Object]	2

Attempt 2 of job 4: the counter survived the crash.

Session DStep 5Job 4's original worker wakes up and reports it done

UPDATE jobs SET status = 'done', locked_until = NULL
WHERE id = 4
  AND attempts = 1
RETURNING id, status;

No rows

Job 4's first worker was only slow. attempts = 1 no longer matches, so it can't overwrite Worker C's claim.

Session AStep 6Worker A reports job 1 done

UPDATE jobs SET status = 'done', locked_until = NULL
WHERE id = 1
  AND attempts = 1
RETURNING id, status;

id	status
1	done

On an error, retry with exponential backoff:

-- Worker A's attempt at job 1 failed. Try again later, waiting
-- 2, 4, 8... seconds, or give up once attempts reaches max_attempts.
UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    run_at = now() + interval '1 second' * power(2, attempts),
    locked_until = NULL,
    last_error = 'SMTP timeout'
WHERE id = 1
  AND attempts = 1;

Exactly-once processing

When the work is a write to the same database:

Session AStep 1Worker A claims the invoice job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
    AND kind = 'create_invoice'
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
3	create_invoice	[object Object]	1

Session AStep 2Worker A writes the invoice

INSERT INTO invoices (order_id, total_cents)
SELECT id, total_cents FROM orders WHERE id = 3
RETURNING order_id, total_cents;

order_id	total_cents
3	9900

Session AStep 3Worker A crashes before committing

ROLLBACK;

No rows

The invoice is rolled back together with the claim.

Session BStep 4Worker B claims the invoice job

BEGIN;

UPDATE jobs
SET attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
    AND kind = 'create_invoice'
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload, attempts;

id	kind	payload	attempts
3	create_invoice	[object Object]	1

Session BStep 5Worker B writes the invoice

INSERT INTO invoices (order_id, total_cents)
SELECT id, total_cents FROM orders WHERE id = 3
RETURNING order_id, total_cents;

order_id	total_cents
3	9900

Session BStep 6Worker B marks the job done and commits

UPDATE jobs SET status = 'done' WHERE id = 3;

COMMIT;

No rows

Session CStep 7How many invoices for order 3?

SELECT invoices.order_id, invoices.total_cents, jobs.status AS job_status
FROM invoices
JOIN jobs ON jobs.id = 3
WHERE invoices.order_id = 3;

order_id	total_cents	job_status
3	9900	done

Delivered twice, invoiced once.

For emails or payments, pass the job id as an idempotency key.

Enqueue with your data

BEGIN;

INSERT INTO orders (id, customer, total_cents)
VALUES (4, 'dave', 2500);

INSERT INTO jobs (kind, payload)
VALUES ('send_receipt', '{"order_id": 4}');

COMMIT;

No order without its job, no job without its order. With a separate broker you need an outbox table for this, which is a Postgres queue anyway.

LISTEN / NOTIFY

-- Each worker, once, on its own connection:
LISTEN new_job;

-- The producer, in the same transaction as the INSERT:
BEGIN;

INSERT INTO jobs (kind, payload)
VALUES ('send_receipt', '{"order_id": 4}');

NOTIFY new_job;

COMMIT; -- listeners are woken up now, and only if it commits

Only a hint: keep polling slowly as a safety net.

One event, many consumers

-- One event, one job per consumer. Each copy is claimed,
-- retried, and failed independently of the others.
INSERT INTO jobs (kind, payload)
SELECT kind, '{"order_id": 4}'
FROM unnest(ARRAY['send_receipt', 'create_invoice', 'sync_crm']) AS kind;

Postgres or a broker?

Not the goal of this lesson, just the high-level idea:

Postgres	Transactional with your data, nothing new to run. Thousands of jobs/s.
Redis	Very fast; Streams add consumer groups. Memory-bound, weaker durability.
RabbitMQ	Routing, acks, dead-letter exchanges, push delivery. One more cluster.
Kafka	Replayable log, huge throughput, many consumer groups. Heavy to operate.
SQS / SNS	Managed; visibility timeouts, DLQs, SNS fan-out. AWS only.

Rules of thumb

Claim with FOR UPDATE SKIP LOCKED.
Assume every job runs twice: make it idempotent.
Short in-database work: one transaction. Long work: a lease.
Limit attempts, back off, keep a dead-letter status.
Delete finished jobs so vacuum keeps up.

What We Learned

FOR UPDATE SKIP LOCKEDMany workers, one table, no job handed out twice.
Delivery guaranteesAt-most-once or at-least-once. Exactly-once is impossible.
Leases and fencinglocked_until recovers dead workers' jobs; the attempt number rejects stale reports.
Exactly-once processingWork and "done" in one transaction.
Transactional enqueue (outbox)Enqueue in the same transaction as your data.
LISTEN / NOTIFYWake workers on commit instead of polling.