A Reliable Job Queue in PostgreSQL with SKIP LOCKED

Every backend I run eventually grows a queue: send this email, resize that image, sync this record to an external ERP. The reflex is to reach for Redis, RabbitMQ, or SQS. But if PostgreSQL is already the source of truth, adding a broker means one more service to deploy, monitor, and keep consistent with your database. For a huge range of workloads you do not need it. A single SQL clause, SKIP LOCKED, turns an ordinary table into a queue that safe, parallel workers can drain without stepping on each other.

SKIP LOCKED has been in PostgreSQL since version 9.5, and it is the same mechanism libraries like pg-boss and River build on. Understanding the raw pattern first means you can debug those libraries when they misbehave, and roll your own when a dependency is overkill. Here is how I build it, the mistakes that cost me hours, and the honest line where I stop and pay for a real broker instead.

Why the naive queue double-processes

The obvious approach is to read the oldest pending row, then mark it as processing. The problem is that reading and marking are two separate steps. Two workers polling at the same millisecond both read job 42 before either has marked it, and both go on to process it. You send the same invoice email twice. Plain FOR UPDATE fixes correctness but destroys throughput: the second worker blocks and waits for the first to finish its transaction, so your parallel workers degrade into a single-file line.

-- Naive queue: two workers can claim the same row
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1;              -- Worker A and Worker B both read job 42

UPDATE jobs SET status = 'processing' WHERE id = 42;
COMMIT;
-- Both workers now believe they own job 42 -> double processing

The SKIP LOCKED claim, in one statement

SKIP LOCKED does something subtle: when a worker asks to lock a row that another transaction already holds, it does not wait and it does not error. It pretends the row is not there and moves to the next candidate. Combine that with an UPDATE driven by a locking subquery, and each worker atomically finds an unclaimed job, locks it, marks it, and gets its payload back in a single round trip. No window exists for a second worker to see the same row.

-- Atomic claim: lock, mark, and return in one statement
UPDATE jobs
SET status = 'processing',
    locked_at = now()
WHERE id = (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING id, payload;

Index it, or watch it crawl

This is the mistake that cost me the most time. SKIP LOCKED still has to scan rows to find an unlocked one, and every locked or already-done row it walks past is wasted work. Without the right index, a busy queue table degrades toward a sequential scan and latency climbs as the table grows. A partial index that covers only pending rows keeps the scan tiny and, as a bonus, shrinks automatically as jobs complete.

-- Partial index: only pending rows are indexed
CREATE INDEX idx_jobs_pending
    ON jobs (created_at)
    WHERE status = 'pending';

Short transactions and crash recovery

There are two shapes for the worker. The simple one keeps a single transaction open for the whole job: the row lock releases automatically if the worker crashes, but a long-running job holds that lock and blocks vacuum the entire time. The scalable one commits the claim immediately, processes outside any transaction, then marks the job done in a second short transaction. That keeps locks brief, but a crashed worker leaves a job stranded in processing forever, so you need a reaper.

• Store a locked_at timestamp when you claim a job so a reaper can tell how long it has been running.
• Track an attempts counter and cap retries, routing repeated failures to a dead-letter status instead of looping forever.
• Make the job handler idempotent: at-least-once delivery means the same job can run twice after a crash, so writes must tolerate a replay.

// TypeScript worker with node-postgres: claim, run, mark done
async function claimAndRun(pool) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(`
      UPDATE jobs
      SET status = 'processing', locked_at = now()
      WHERE id = (
        SELECT id FROM jobs
        WHERE status = 'pending'
        ORDER BY created_at
        FOR UPDATE SKIP LOCKED
        LIMIT 1
      )
      RETURNING id, payload
    `);
    if (rows.length === 0) {
      await client.query('COMMIT');
      return null; // queue empty this tick
    }
    const job = rows[0];
    await handle(job); // your business logic (must be idempotent)
    await client.query(
      "UPDATE jobs SET status = 'done' WHERE id = $1",
      [job.id]
    );
    await client.query('COMMIT');
    return job.id;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

-- Reaper: requeue jobs stuck in 'processing' past a timeout
UPDATE jobs
SET status = 'pending',
    locked_at = NULL,
    attempts = attempts + 1
WHERE status = 'processing'
  AND locked_at < now() - interval '5 minutes';

One more refinement: constant polling wastes queries when the queue is empty. Pair the pattern with LISTEN and NOTIFY so producers wake workers the moment a job arrives, and fall back to a slow poll every few seconds as a safety net. That gives you near-instant pickup without hammering the database with empty SELECTs.

When to reach for a real broker instead

I run this pattern happily in production, but I stay honest about its ceiling. Postgres-as-a-queue is a pull model with one consumer per job. The moment you need fan-out to many independent consumers, multi-day replay, or sustained throughput far past ten thousand jobs per second, a purpose-built broker earns its keep. Here is the trade-off I weigh before adding infrastructure.

My rule of thumb is simple. If the work already touches Postgres and my throughput is in the hundreds-to-thousands of jobs per second, SKIP LOCKED is the boring, correct choice, and it deletes an entire moving part from my architecture. When the numbers or the delivery semantics outgrow that, I do not fight it; I add Kafka or SQS and move on. Start with the database you already have, and only pay for a broker when the workload actually demands one.