SELECT FOR UPDATE SKIP LOCKED (Postgres Job Queue).

🧰Build it first, explain after

You need a worker queue. Before you reach for Redis/SQS, your Postgres can already do it — safely, with 20 workers pulling at once.

BEGIN;
SELECT id, payload FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED LIMIT 1;
UPDATE jobs SET status='done' WHERE id = $1;
COMMIT;

That's the whole thing. Now what each piece actually buys you.

🔒FOR UPDATE

Takes a row lock on whatever the SELECT returns, held until COMMIT. Without it, two workers read the same row and you send the same email twice.

⏭️SKIP LOCKED

The part people don't know exists. Normally a second transaction hitting a locked row blocks — it sits there waiting. SKIP LOCKED says: pretend those rows don't exist, give me the next free one.

Without it, 20 workers = 1 working + 19 queued behind the same row. Your "concurrency" is 1.

With it, 20 workers grab 20 different rows on the first try.

📉The number that matters

A naive SELECT ... FOR UPDATE queue doesn't get slower gracefully — it serializes. Throughput stays roughly flat as you add workers, because every worker is fighting over the head of the queue. SKIP LOCKED scales close to linearly until you hit disk or the job itself.

🧨The gotchas that bite intermediates

1. SKIP LOCKED only skips locked rows — not rows that are simply stale. If a worker crashes mid-job, its lock dies with the connection and the row returns to the pool. Good. But if you instead mark rows status='running' and COMMIT before doing the work, a crash leaves it 'running' forever. You now need a reaper:

UPDATE jobs SET status='pending'
  WHERE status='running' AND locked_at < now() - interval '5 minutes';

2. Holding the lock for the whole job means holding a transaction open for the whole job. Fine for 200ms jobs. Terrible for a 10-minute video encode — long transactions block autovacuum and bloat the table. For long jobs: claim fast (mark running + locked_at, commit), work outside the transaction, reap stragglers.
3. LIMIT 1 with ORDER BY is fine; batch claims (LIMIT 50) are much faster per-job but if the worker dies you re-do 50.
4. SKIP LOCKED makes ordering approximate. You get "roughly FIFO", never strict FIFO. If you need strict per-key ordering, partition by key and let one worker own a key.
5. Index it. CREATE INDEX ON jobs (created_at) WHERE status='pending'; — a partial index, so the index stays tiny even when the table has 10M finished rows.

⚖️When NOT to do this

Under ~a few thousand jobs/sec, Postgres-as-a-queue is genuinely great: transactional with your business data (enqueue a job in the same transaction as the row that caused it — no dual-write problem), one less system to operate, queryable with SQL. Past that, or if you need fan-out to many consumers, go get a real broker.

🧪Your exercise

Open two psql sessions. In both, run BEGIN; then the SELECT ... FOR UPDATE LIMIT 1 — without SKIP LOCKED. Watch session 2 hang. Roll both back, add SKIP LOCKED, run again: session 2 instantly returns a different row (or zero rows if the table has one). Then answer: with SKIP LOCKED and only ONE pending row, what does session 2 get back — and why is that different from an error?