Postgres job queues with SKIP LOCKED, in practice

A jobs table, one claiming query and a lease column give a small team a reliable work queue on the Postgres it already runs. What to build, and where it stops.

Published

Updated

Reading time

11 min

The first version of a Postgres queue is usually two statements: select the oldest unprocessed row, then update it to mark it taken. It works on a laptop and fails the first time two workers run at once, because both selects return the same row before either update lands. Locking the row with FOR UPDATE fixes the duplicate but creates a new problem: the second worker now waits for the first, so a pool of workers serialises behind one row.

FOR UPDATE SKIP LOCKED is the missing piece. A worker that cannot lock a row immediately skips it and takes the next one. Around that clause you need a small amount of design: a state column, a lease so a crashed worker does not strand its job, a guard so a late worker cannot overwrite its successor, and a reaper. This walkthrough builds all of it in order, ends with a TypeScript worker, and adds LISTEN and NOTIFY so jobs start in milliseconds instead of waiting for the next poll.

What the locking clause promises#

The behaviour below is from the PostgreSQL manual on the SELECT locking clause, explicit locking and UPDATE. Check the page for the version you run.

  • SKIP LOCKED skips rows that cannot be locked immediately. The manual says this gives an inconsistent view of the data, so it is unsuitable for general purpose work, but it can be used to avoid lock contention with multiple consumers accessing a queue-like table. NOWAIT, the alternative, raises an error instead.
  • Locks last until the transaction ends. A crashed connection ends its transaction, which releases its locks.
  • A row lock writes to the row. Locking can cause a disk write, because the selected rows are modified to mark them locked. A claim is not a free read.
  • LIMIT stops the locking. With a LIMIT, locking stops once enough rows have been returned. Rows skipped by OFFSET still get locked, so do not use OFFSET.
  • ORDER BY plus a lock can return rows slightly out of order at READ COMMITTED. The sort happens first, then rows are locked, and a row updated in between can come out of order. For a work queue this is fine. If it is not fine for you, you are asking for a log with strict ordering, which is a different tool.

Together these give the pattern: select candidate ids with FOR UPDATE SKIP LOCKED LIMIT n in a CTE, update them to a claimed state in the same statement, and return what you claimed.

A table that stays fast as it fills#

State is a column with a check constraint, and every state change is an update to a row you can query:

jobs.sqlsql
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')),
  attempts      integer     NOT NULL DEFAULT 0,
  max_attempts  integer     NOT NULL DEFAULT 5,
  run_at        timestamptz NOT NULL DEFAULT now(),
  locked_until  timestamptz,
  last_error    text,
  created_at    timestamptz NOT NULL DEFAULT now(),
  finished_at   timestamptz
);
 
CREATE INDEX jobs_claim_idx   ON jobs (queue, run_at) WHERE status = 'queued';
CREATE INDEX jobs_expiry_idx  ON jobs (locked_until)  WHERE status = 'running';

The partial indexes keep the claim fast as finished rows accumulate: only queued rows are in the first index and only running rows in the second. Without them, the claim scans a growing table, which is the usual way these queues slow down over months.

Claim a batch, and hold a lease instead of a lock#

The claiming transaction commits immediately. The work then runs outside any transaction, protected by a lease: a locked_until timestamp. That keeps connections free and lets a job run longer than any sensible transaction.

claim.sqlsql
WITH next AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued' AND queue = $1 AND run_at <= now()
  ORDER BY run_at
  LIMIT $2
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status       = 'running',
    attempts     = jobs.attempts + 1,
    locked_until = now() + make_interval(secs => $3)
FROM next
WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload, jobs.attempts;

The CTE picks up to $2 rows nobody else has locked and locks them, and the UPDATE flips them to running in the same statement. Two workers running this at the same moment get disjoint sets. $3 is the lease length in seconds. The order of the RETURNING rows is not guaranteed, so order in the worker if handlers care.

Concurrent claims never collide: rows that are locked are skipped, so each worker gets a different row.

Finish, fail and reap with the attempt number as a fence#

Because the lease can expire while a slow worker is still running, a second worker may claim the same job. The first worker must not be able to overwrite the second's result. Every completion and failure update therefore checks attempts, which the claim increments: a worker holding an older attempt number matches no row.

finish.sqlsql
-- complete
UPDATE jobs
SET status = 'done', finished_at = now(), locked_until = NULL
WHERE id = $1 AND status = 'running' AND attempts = $2;
 
-- fail: back to the queue with exponential backoff, or dead after the last attempt
UPDATE jobs
SET status       = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'queued' END,
    run_at       = now() + make_interval(secs => LEAST(3600, 30 * power(2, attempts))),
    last_error   = $3,
    locked_until = NULL,
    finished_at  = CASE WHEN attempts >= max_attempts THEN now() ELSE NULL END
WHERE id = $1 AND status = 'running' AND attempts = $2;
 
-- reap: leases that expired because the worker died
UPDATE jobs
SET status       = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'queued' END,
    last_error   = COALESCE(last_error, 'lease expired'),
    locked_until = NULL,
    finished_at  = CASE WHEN attempts >= max_attempts THEN now() ELSE NULL END
WHERE status = 'running' AND locked_until < now();

The backoff is 30 seconds doubling with each attempt, capped at an hour; pick numbers for the failure you expect. Since the claim has already incremented attempts, power(2, attempts) makes the first retry wait 60 seconds. A job that fails max_attempts times becomes dead, which is a row someone has to read.

Keep the lease longer than your slowest honest job. If a job can legitimately run for ten minutes, extend its lease with a heartbeat that moves locked_until forward for the same id and attempt, or the reaper will hand it to a second worker.

The worker loop#

worker.tsts
import { Pool } from "pg";
 
const pool = new Pool({ max: 3 }); // connection settings come from the standard PG* environment variables
const QUEUE = "default";
const BATCH = 5;
const LEASE_SECONDS = 120;
 
interface Claimed { id: string; payload: unknown; attempts: number }
 
async function claim(): Promise<Claimed[]> {
  const { rows } = await pool.query<Claimed>(
    `WITH next AS (
       SELECT id FROM jobs
       WHERE status = 'queued' AND queue = $1 AND run_at <= now()
       ORDER BY run_at
       LIMIT $2
       FOR UPDATE SKIP LOCKED
     )
     UPDATE jobs
     SET status = 'running',
         attempts = jobs.attempts + 1,
         locked_until = now() + make_interval(secs => $3)
     FROM next
     WHERE jobs.id = next.id
     RETURNING jobs.id, jobs.payload, jobs.attempts`,
    [QUEUE, BATCH, LEASE_SECONDS],
  );
  return rows;
}
 
async function handle(job: Claimed): Promise<void> {
  // Replace with the real work. It must be safe to run more than once.
}
 
async function run(job: Claimed): Promise<void> {
  try {
    await handle(job);
    await pool.query(
      `UPDATE jobs SET status = 'done', finished_at = now(), locked_until = NULL
       WHERE id = $1 AND status = 'running' AND attempts = $2`,
      [job.id, job.attempts],
    );
  } catch (err) {
    await pool.query(
      `UPDATE jobs
       SET status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'queued' END,
           run_at = now() + make_interval(secs => LEAST(3600, 30 * power(2, attempts))),
           last_error = $3, locked_until = NULL,
           finished_at = CASE WHEN attempts >= max_attempts THEN now() ELSE NULL END
       WHERE id = $1 AND status = 'running' AND attempts = $2`,
      [job.id, job.attempts, String(err).slice(0, 1000)],
    );
  }
}
 
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
let stopping = false;
process.on("SIGTERM", () => { stopping = true; });
 
async function main() {
  while (!stopping) {
    const jobs = await claim();
    if (jobs.length === 0) {
      await sleep(1000 + Math.random() * 1000); // idle poll with jitter
      continue;
    }
    await Promise.all(jobs.map(run));
  }
  await pool.end();
}
 
main().catch((err) => { console.error(err); process.exit(1); });

Run the reaper statement as a separate loop in one worker, or as a scheduled job every minute. A job that finishes its work and dies before writing done is re-queued by the reaper and runs again, so the queue delivers at least once and handlers must tolerate repeats. The pattern for that, a stable key passed to every side effect, is the same one described in idempotent Pub/Sub consumers; the job id is the natural key here.

Enqueue in the same transaction as the business change#

Enqueueing is a plain insert, and this is where the table beats an external queue. Do it in the transaction that makes the business change, so either both exist or neither does, and no outbox table is needed:

create-order.tsts
import type { Pool } from "pg";
 
export async function createOrder(pool: Pool, customerId: string, totalCents: number) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const { rows } = await client.query(
      "INSERT INTO orders (customer_id, total_cents) VALUES ($1, $2) RETURNING id",
      [customerId, totalCents],
    );
    await client.query("INSERT INTO jobs (queue, payload) VALUES ('default', $1)", [
      { type: "send_receipt", orderId: rows[0].id },
    ]);
    await client.query("SELECT pg_notify('jobs', 'default')"); // wake-up hint, see below
    await client.query("COMMIT");
    return rows[0].id as string;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Wake workers with LISTEN and NOTIFY#

With a one to two second idle poll, a job can wait that long before it starts. Polling faster costs queries on a mostly empty table, multiplied by the number of workers. LISTEN and NOTIFY remove the trade-off, as long as they are treated as a hint and not as the queue.

Per the NOTIFY documentation, a notification sent inside a transaction is not delivered until and unless that transaction commits, so a worker is never woken for a job it cannot yet see. Payloads must be shorter than 8,000 bytes by default, and notifications go to the sessions listening when they are delivered. A worker that was disconnected misses them. That is why the poll stays.

wake.tsts
import { Client } from "pg";
 
// A dedicated, direct connection: not a pooled one (see the note below).
export async function listenForJobs(onWake: () => void) {
  const client = new Client(); // PG* environment variables
  await client.connect();
  client.on("notification", () => onWake());
  client.on("error", () => process.exit(1)); // let the platform restart us; we re-claim on start
  await client.query("LISTEN jobs");
}

The worker's loop then waits for either a notification or a long timer, whichever comes first, and claims on both. In worker.ts, the idle sleep becomes:

worker.ts (idle wait)ts
let wake: (() => void) | null = null;
 
function waitForWakeOrTimeout(ms: number): Promise<void> {
  return new Promise((resolve) => {
    const timer = setTimeout(resolve, ms);
    wake = () => { clearTimeout(timer); resolve(); };
  });
}
 
await listenForJobs(() => wake?.());
// in main(), when claim() returned nothing:
await waitForWakeOrTimeout(30_000 + Math.random() * 5_000);

After a restart the worker always claims first, so anything enqueued while it was deaf is picked up.

Three properties keep this correct:

  • Polling stays the source of truth. Drop the idle poll to 30 seconds or so as a safety net. If every notification were lost, the queue would still drain, only slower.
  • Delayed and retried jobs have no notification. A job whose run_at is in the future becomes due when the clock passes it, not when someone commits. Only the poll finds it, which is another reason to keep it.
  • Every listener wakes at once. Ten workers each run the claim, and SKIP LOCKED hands the single new row to one of them. That is harmless at this scale, but the claim query is the only coordination, so never assume a notification means "you got the job".

LISTEN needs a session, so it does not work through a pooler in transaction mode (see connection pooling when every service is tiny). Give the listener its own direct connection and keep it out of the claim pool.

Operating the table#

Bloat. Every state change updates a row, which leaves dead row versions for vacuum. Delete or archive finished rows on a schedule and watch table size and autovacuum. Partitioning by time is the next step at high volume.

Long transactions. A worker that claims inside a transaction and processes inside it holds locks and blocks cleanup. The lease design exists to avoid this.

Skipped rows are an inconsistent view. The claim query must never answer "how many jobs are pending". Use a separate count, and alert on the count of dead jobs and the age of the oldest queued one.

Fairness. One queue with cheap and expensive jobs lets the expensive ones occupy every worker. Use separate queue names and dedicated workers for slow work.

CostPolling keeps a scale-to-zero database awake

There is no extra service to pay for, and the price shows up in the database: each claim writes rows, and polling workers hold connections and CPU. A managed database that scales or pauses when idle will not do so while workers poll it, so a queue on such a database turns the saving off. The mechanisms are described in when your managed database goes to sleep. Keep the worker pool small, poll no faster than the latency you need, and let NOTIFY carry the latency.

Where the table stops#

A table queue is the right tool when the jobs are created next to your data, volume is thousands per day rather than thousands per second, and a team of any size would rather query a row than operate a broker. It stops being the right tool in three places.

If you need fan-out to many independent consumers, replay of history or strict ordering across jobs, you want a log or a broker, not a work queue in a table. If you expect sustained thousands of jobs per second, the write load competes with application traffic and a dedicated system is safer. And if the work is triggered by events that already live in a managed messaging service, a table in the middle only duplicates state. Finally, if the database has little headroom, polling workers are an unkind new tenant: fix the capacity first.

Share
All articles →