---
title: "Building a Job Queue in Postgres with SKIP LOCKED"
slug: building-a-job-queue-in-postgres-with-skip-locked
url: https://listedarticles.com/articles/building-a-job-queue-in-postgres-with-skip-locked
canonical_url: https://learn.michaelguay.dev/blog/building-a-job-queue-in-postgres-with-skip-locked
content_type: tutorial
language: en
published_at: 2026-10-04T00:00:00.000Z
updated_at: 2026-10-04T23:17:04.393Z
author: "Michael Guay"
author_url: https://learn.michaelguay.dev/
authored_by: human
publisher: "Michael Guay"
publisher_url: https://learn.michaelguay.dev/
topics: ["Databases", "Programming", "Software Engineering", "Tutorials", "Infrastructure"]
license: all-rights-reserved
word_count: 2138
reading_minutes: 9
citation: "Michael Guay, Michael Guay. \"Building a Job Queue in Postgres with SKIP LOCKED.\" 4 Oct 2026. https://learn.michaelguay.dev/blog/building-a-job-queue-in-postgres-with-skip-locked (all-rights-reserved)"
# The full text follows. The web page shows an extract and sends readers
# to the source above; quote the citation and link the canonical URL.
---

# Building a Job Queue in Postgres with SKIP LOCKED

> Michael Guay walks through running background jobs in Postgres instead of Redis: the jobs table, claiming work safely with SKIP LOCKED, leases for crashed workers, retries with backoff, dedup, LISTEN/NOTIFY wakeups, and when not to.

When you need background jobs, the usual answer is Redis plus a queue library. But if you
already run Postgres, you may not need another service at all. Postgres has everything a
reliable job queue needs: row locks, transactions, `SKIP LOCKED` and `LISTEN/NOTIFY`.

I use one on my course platform to send hundreds of videos through a rate-limited video API. It's one table and a small worker. This post covers the pattern in general: the table, how workers claim jobs safely, retries, crash recovery, deduplication, waking workers without polling, and when you should reach for something else.

## Why a queue in Postgres?

- **One less service.** No Redis to run, back up, secure and monitor.
- **Transactional enqueue.** Insert the job in the same transaction as the data it's about.
If the transaction rolls back, the job never existed. With a separate queue, you can save
an order and lose its "send receipt" job, or send a receipt for an order that rolled back.
- **You can query it.** It's a table. "Which jobs failed today, and why?" is a`SELECT` .
- **It's fast enough** for most apps: thousands of jobs a minute on ordinary hardware.

## The table

```
create table jobs (
  id          bigint generated always as identity primary key,
  kind        text        not null,               -- what to run: 'send_email', 'resize_image'...
  payload     jsonb       not null default '{}',  -- its arguments
  status      text        not null default 'queued'
              check (status in ('queued', 'running', 'done', 'failed')),
  run_at      timestamptz not null default now(), -- not before this
  attempts    int         not null default 0,
  max_attempts int        not null default 5,
  locked_by   uuid,                               -- the worker holding it
  last_error  text,
  created_at  timestamptz not null default now()
);
-- Workers only ever look for jobs that could run now.
create index jobs_ready_idx on jobs (run_at) where status in ('queued', 'running');
```
The column doing the most work is `run_at`. It means "not before this time", and it serves
three purposes:

- A **new job** runs at`now()` , or later if you schedule it.
- A **failed job** waiting to retry gets a`run_at` in the future (backoff).
- A **running job** gets a`run_at` at the end of its lease. If the worker dies, the job
becomes claimable again when the lease runs out (more on that below).

The index is partial: finished jobs don't bloat it, so it stays small however long the table gets.

## Claiming a job with `FOR UPDATE SKIP LOCKED`

The core problem: several workers poll the same table. Two of them must never run the same job, and they shouldn't wait on each other either.

`SELECT ... FOR UPDATE` locks the rows it returns. `SKIP LOCKED` (Postgres 9.5+) tells the
query to skip rows another transaction has already locked, instead of waiting for them. Put
that in a subquery, and claim the job in one statement:

```
update jobs
set status    = 'running',
    attempts  = attempts + 1,
    locked_by = $1,                          -- this worker's id
    run_at    = now() + interval '5 minutes' -- the lease
where id = (
  select id
  from jobs
  where status in ('queued', 'running')
    and run_at <= now()
  order by run_at
  limit 1
  for update skip locked
)
returning *;
```
One round trip, one atomic statement. If two workers run it at the same moment, the second one skips the row the first one locked and takes the next job. If nothing is due, it returns no rows.

To claim a batch, change `limit 1` to `limit 10` and `where id = (...)` to `where id in (...)`.

Why `status in ('queued', 'running')`? Because a running job whose lease has expired is
claimable again. That's the crash recovery, built into the same query.

## Leases: surviving crashed workers

A worker can die mid-job: a deploy, an out-of-memory kill, a lost connection. A queue that
only marks jobs `running` leaks those jobs forever. My own first version had exactly this
gap: a deploy in the middle of a job would have left it `running`, with nothing running it.

The lease fixes it. Claiming a job sets `run_at` to "now plus five minutes". If the worker
finishes, it marks the job done. If it dies, nothing marks it, the lease expires, and the
claim query picks the job up again.

Two rules make this safe:

1. **The lease must outlast the job.** If a job can take ten minutes, a five-minute lease
lets a second worker start it while the first is still going. For long jobs, have the
worker extend its lease as it goes (`update jobs set run_at = now() + interval '5 minutes' where id = $1 and locked_by = $2` ).
2. **Only the lease holder may finish the job.** That's what`locked_by` is for. If a slow
worker's lease expired and another worker took the job over, the slow one must not mark
it done:

```
update jobs
set status = 'done', locked_by = null
where id = $1 and locked_by = $2;  -- 0 rows: we lost the lease; someone else has it
```
## The worker loop

The worker is a loop: claim, run, record the outcome. Here it is in TypeScript with the
[`postgres`](https://github.com/porsager/postgres) driver. Any language looks the same.

```
import postgres from 'postgres';
const sql = postgres(process.env.DATABASE_URL!);
const workerId = crypto.randomUUID();
type Job = { id: number; kind: string; payload: unknown; attempts: number; max_attempts: number };
const handlers: Record<string, (payload: any) => Promise<void>> = {
  send_email: async (payload) => { /* ... */ },
};
async function claim(): Promise<Job | undefined> {
  const [job] = await sql<Job[]>`
    update jobs
    set status = 'running', attempts = attempts + 1, locked_by = ${workerId},
        run_at = now() + interval '5 minutes'
    where id = (
      select id from jobs
      where status in ('queued', 'running') and run_at <= now()
      order by run_at limit 1
      for update skip locked
    )
    returning *`;
  return job;
}
async function work() {
  for (;;) {
    const job = await claim();
    if (!job) {
      await Bun.sleep(1000); // nothing due: wait a moment (or LISTEN, below)
      continue;
    }
    try {
      await handlers[job.kind]!(job.payload);
      await sql`update jobs set status = 'done', locked_by = null
                where id = ${job.id} and locked_by = ${workerId}`;
    } catch (error) {
      await fail(job, error as Error);
    }
  }
}
```
Run as many of these as you like, in one process or many. `SKIP LOCKED` keeps them out of
each other's way.

## Retries with backoff (and when not to retry)

Not every failure deserves another try. A timeout, a 429 or a 5xx from an API will probably pass. A validation error won't, and retrying it only wastes time and rate limit. Decide which is which, and retry only the first kind:

```
function isRetryable(error: unknown): boolean {
  const status = (error as { status?: number }).status;
  if (status !== undefined) return status === 429 || status >= 500;
  return /timed? ?out|ECONNRESET|temporar/i.test(String(error));
}
async function fail(job: Job, error: Error) {
  const again = isRetryable(error) && job.attempts < job.max_attempts;
  // Exponential backoff with jitter: about 2s, 4s, 8s... capped at 10 minutes.
  const delay = Math.min(2 ** job.attempts, 600) * (0.5 + Math.random());
  await sql`
    update jobs
    set status     = ${again ? 'queued' : 'failed'},
        run_at     = now() + ${delay} * interval '1 second',
        last_error = ${error.message},
        locked_by  = null
    where id = ${job.id} and locked_by = ${workerId}`;
}
```
The jitter (`0.5 + Math.random()`) matters when many jobs fail at once, for example when an
API goes down. Without it they all retry at the same instant and knock it over again.

Jobs that run out of attempts stay in the table as `failed`, with their last error. That's
your dead-letter queue, and you can query it:

```
select kind, last_error, count(*)
from jobs
where status = 'failed' and created_at > now() - interval '1 day'
group by 1, 2
order by 3 desc;
```
To retry one by hand: `update jobs set status = 'queued', run_at = now(), attempts = 0 where id = $1`.

## Enqueueing, and not enqueueing twice

Enqueueing is an insert, ideally inside the transaction that made the job necessary:

```
await sql.begin(async (tx) => {
  const [order] = await tx`insert into orders ${tx(order)} returning id`;
  await tx`insert into jobs (kind, payload) values ('send_receipt', ${{ orderId: order.id }})`;
});
```
Often you also want **at most one pending job per thing**: one "reindex this course", not
five because someone clicked five times. Add a dedupe key and a partial unique index over
the jobs that are still pending:

```
alter table jobs add column dedupe_key text;
create unique index jobs_pending_dedupe_idx
  on jobs (kind, dedupe_key)
  where status in ('queued', 'running');
```
```
insert into jobs (kind, payload, dedupe_key)
values ('reindex_course', '{"courseId": 42}', 'course:42')
on conflict (kind, dedupe_key) where status in ('queued', 'running') do nothing;
```
The second click is a no-op while the first job is pending. Once it's done, a new one can be queued.

## Waking workers with `LISTEN/NOTIFY`

Polling every second is simple and fine for most apps, but it adds up to a second of delay and a steady trickle of queries. Postgres can tell workers when there's work instead:

```
create function jobs_notify() returns trigger as $$
begin
  perform pg_notify('jobs', new.kind);
  return new;
end;
$$ language plpgsql;
create trigger jobs_notify after insert on jobs
for each row execute function jobs_notify();
```
```
let wake = () => {};
await sql.listen('jobs', () => wake());
// In the worker loop, instead of a fixed sleep:
await Promise.race([
  new Promise<void>((resolve) => (wake = resolve)),
  Bun.sleep(30_000), // still poll now and then: retries and scheduled jobs don't notify
]);
```
Three things to know:

- **Notifications are sent on commit.** A job inserted in a transaction that rolls back
never wakes anyone.
- **Keep polling as a fallback.** Notifications aren't stored. A worker that was
reconnecting misses them, and jobs whose`run_at` arrives (retries, scheduled jobs) don't
send one.
- **`LISTEN` needs a dedicated connection.** It doesn't work through PgBouncer in
transaction pooling mode. Connect the listener directly to Postgres.

## Limiting concurrency and rate

Sometimes the limit isn't your workers, it's what they call. My video jobs call an API that allows about one request a second and gets overloaded with too many videos processing at once. Two cheap ways to respect limits like that:

- **Global concurrency:** count before claiming, and only claim while there's room:
`select count(*) from jobs where kind = 'process_video' and status = 'running' and run_atnow() `. With several workers, take a transaction-scoped advisory lock first   (` select pg_advisory_xact_lock(hashtext('process_video'))`) so they don't all see room at
once.
- **Rate:** for a single worker, sleep between calls. For several, store the time of the
last call in a one-row table and update it with`select ... for update` , so one worker at
a time decides who may go next.

And before an expensive call, check whether it's still needed. A job that waited ten minutes for a retry may find the work already done by something else in the meantime.

## Keeping the table healthy

A queue table sees a lot of updates, and every Postgres update writes a new row version. The old versions are dead tuples until vacuum cleans them up. Keep it in check:

- **Delete or archive finished jobs.** A nightly`delete from jobs where status = 'done' and created_at < now() - interval '7 days'` keeps the table small. Keep failed ones longer.
- **Let autovacuum keep up.** For a busy queue, make it run more often on this table:`alter table jobs set (autovacuum_vacuum_scale_factor = 0.01)` .
- **Keep the index partial,** as above. The workers' index then only holds pending jobs,
however many finished ones the table holds.

## Things to get right in your handlers

- **At least once, not exactly once.** A worker can finish the work and die before marking
the job done; the lease brings it back and it runs again. Write handlers that are safe to
run twice: check before acting, use idempotency keys with external APIs, upsert instead of
insert.
- **Keep jobs small.** One job per email, per image, per video. Big jobs hold leases for
long, retry expensively and make failures harder to read.
- **Store ids, not data,** in the payload. Load the current state when the job runs; the
data may have changed since it was queued.

## When not to use Postgres as a queue

Postgres is a good queue when:

- jobs number in the thousands a minute, not hundreds of thousands a second;
- each job does real work (an HTTP call, a file, an email), so the queue isn't the bottleneck;
- you already run Postgres, and want enqueueing to be part of your transactions.

Use a dedicated broker (Redis/BullMQ, RabbitMQ, SQS, Kafka) when you need very high throughput, fan-out to many consumers, or millisecond latency at scale, or when the queue's load would compete with your main database.

## Libraries that do this for you

If you'd rather not write it yourself, these use exactly this pattern:

- **Node.js:**[pg-boss](https://github.com/timgit/pg-boss) ,[Graphile Worker](https://worker.graphile.org/)
- **Go:**[River](https://riverqueue.com/)
- **Elixir:**[Oban](https://getoban.pro/)
- **Ruby on Rails:**[Solid Queue](https://github.com/rails/solid_queue)
- **Postgres extension:**[pgmq](https://github.com/pgmq/pgmq)

Writing your own is still worth it when your needs are small and specific, like mine: about
a hundred lines, no new dependency, and a table you can read in the admin page. Either way,
knowing how `SKIP LOCKED`, leases and backoff fit together makes any of them easier to run.