Postgres Job Queue with SKIP LOCKED: Leases and Reclaim

A Postgres job queue with SKIP LOCKED is enough for most products. Store jobs in a table, let workers claim them with SELECT ... FOR UPDATE SKIP LOCKED so no two take the same row, give each claim a lease that expires, and reclaim jobs whose worker died. You get transactions, backups, and one less service to run.

TL;DR: Use a jobs table, claim rows with FOR UPDATE SKIP LOCKED, and stamp each claim with a lease that a heartbeat extends. Reclaim expired leases only while attempts remain, and fail the rest. Make handlers idempotent, enqueue in the same transaction as the work, and switch to Redis or a broker only when throughput or fan-out demands it.

SigmaCPM runs its background work this way: a Postgres-backed job queue with leases, orphan reclaim, and skip-locked selection, with no extra broker to run. Its jobs include OCR on scanned tenders and LLM extraction. Below is the SQL for each piece, plus the mistakes that make a home-made queue loop or lose work.

Why run a job queue on Postgres instead of Redis?

Because your product already runs Postgres, and a second service costs hosting, monitoring, backups, and on-call attention. A Postgres queue also gives you transactional enqueueing and SQL over job state. Redis or a broker earns its place when throughput, fan-out, or load isolation demands it, not by default.

We run both kinds. Pullsy uses a Redis queue with an atomic move-with-ack and a delayed-retry set. Kraftr runs seven BullMQ queues on Redis, covering generation, model builds, scene builds, jewelry libraries, video, and batch polling. SigmaCPM uses Postgres. So what tips the choice?

For a product that already has Postgres, the table-based queue wins on four counts:

1. One less service to host, monitor, back up, and secure.
2. Transactions. A record and its follow-up job commit together, or neither does.
3. Queryable state. "Which jobs failed for this customer yesterday?" is one SQL query.
4. Backups for free. Jobs sit in the same backups as everything else.

What does the jobs table need?

A status, a retry counter with a ceiling, a time before which the job must not run, a lease expiry, and the last error. Add a priority from day one. Keep the payload small and put large inputs, such as PDFs, in object storage with a reference.

create table jobs (
  id            bigserial primary key,
  type          text not null,
  tenant_id     bigint,
  payload       jsonb not null,
  priority      smallint not null default 0,      -- higher runs first
  status        text not null default 'queued',   -- queued, running, done, failed
  attempts      int not null default 0,
  max_attempts  int not null default 5,
  run_after     timestamptz not null default now(),
  leased_until  timestamptz,
  last_error    text,
  created_at    timestamptz not null default now()
);

create index jobs_ready on jobs (priority desc, run_after)
  where status = 'queued';

The where clause makes this a partial index. The PostgreSQL docs on partial indexes describe exactly this use: leave out rows the query never asks for, which keeps the index small and spares many updates from touching it. Millions of done rows can pile up and the "what's ready?" lookup still reads only queued jobs.

How does FOR UPDATE SKIP LOCKED claim a job?

It locks the rows it can lock right now and silently skips rows another transaction holds. Several workers can run the same claim query at once, each gets a different job, and none waits on another. The PostgreSQL SELECT reference says skipping locked rows gives an inconsistent view, so it isn't for general reads, but it suits multiple consumers on a queue-like table.

with next as (
  select id from jobs
  where status = 'queued' and run_after <= now()
  order by priority desc, run_after
  limit 1
  for update skip locked
)
update jobs
set status = 'running',
    attempts = attempts + 1,
    leased_until = now() + interval '5 minutes'
from next
where jobs.id = next.id
returning jobs.id, jobs.type, jobs.payload, jobs.attempts;

The worker then does the job outside any transaction; holding one open through a ten-minute OCR run would pin a connection for no gain.

Keep the returned attempts value. It's the worker's proof that it still owns this claim, and every later write checks it.

How do leases keep a dead worker's job from getting stuck?

A lease is a deadline. While a worker is alive, it pushes leased_until forward on a heartbeat. If the worker crashes, gets killed in a deploy, or loses its connection, the heartbeats stop, the lease passes, and the job becomes an orphan that reclaim can hand to someone else.

The heartbeat, run every minute or so from the worker:

update jobs
set leased_until = now() + interval '5 minutes'
where id = $1 and status = 'running' and attempts = $2
returning id;

If no row comes back, the job was reclaimed and possibly claimed again by another worker. Stop work and write nothing. The same guard goes on the completion write:

update jobs set status = 'done', leased_until = null
where id = $1 and status = 'running' and attempts = $2;

How long should the lease be? Long enough that one missed heartbeat, a slow GC pause, or a brief database blip doesn't orphan a healthy job. Short enough that a dead worker's job restarts before anyone notices. We size it at a few heartbeat intervals, which is why the example pairs a one-minute beat with a five-minute lease. Without heartbeats, the lease must exceed your slowest job, and then every crash costs that long.

Orphan reclaim that respects max_attempts

A naive reclaim puts every expired lease back in the queue. That's a trap. A job whose input crashes the worker every time (a malformed PDF that takes the OCR process down, say) will be reclaimed, claimed, and crash forever, never reaching the failed state your retry logic promised. The fix is to check attempts in reclaim too.

-- Attempts left: back in the queue
update jobs
set status = 'queued', run_after = now(), leased_until = null,
    last_error = 'lease expired'
where status = 'running' and leased_until < now()
  and attempts < max_attempts;

-- Out of attempts: stop and alert
update jobs
set status = 'failed', leased_until = null,
    last_error = 'lease expired on final attempt'
where status = 'running' and leased_until < now()
  and attempts >= max_attempts;

Because attempts goes up on every claim, it counts runs started, crashed or not. Run both from a periodic task every minute.

Reclaim means a job can run more than once. Handlers must be idempotent: a second run must not double-charge, double-email, or duplicate records. Put unique keys on whatever the job creates, or check whether the work is already done before starting.

How should a failed job back off?

Not immediately. A failing upstream needs time, so push run_after further out on each attempt and add randomness so a batch of failures doesn't retry in the same second. Pullsy maps Meta error codes to backoff with jitter capped at 30 minutes; the post on API rate limits and backoff covers that strategy in full.

In SQL, with the same ownership guard as before:

update jobs
set status = case when attempts >= max_attempts then 'failed' else 'queued' end,
    run_after = now() + least(interval '30 minutes',
                interval '10 seconds' * power(2, attempts) * (0.5 + random())),
    leased_until = null,
    last_error = $3
where id = $1 and status = 'running' and attempts = $2;

A job that lands in failed keeps its last error. Alert on it; don't let it sit quietly.

Create the job in the same transaction as the work

This is the biggest practical win of a Postgres queue. Say a user uploads a tender and it needs an OCR job. With a separate broker, you save the upload and then publish a message. If the process dies between the two, you get an upload with no job, or a job pointing at an upload that rolled back.

With the queue in Postgres, both inserts share one transaction. Commit, and the upload and its job exist together. Roll back, and neither does. That removes a whole category of stuck records and the cleanup scripts that chase them.

SigmaCPM's slow work is exactly this shape: scanned tender pages go through OCR with retry handling before extraction, and that work runs on its Postgres queue rather than a broker. If a worker dies halfway through a large tender package, its lease runs out and another worker picks the job up. The tender OCR pipeline post follows that work end to end; this post is only about the queue under it.

Operating a Postgres job queue in production

The claim, lease, and reclaim SQL is the easy half. The rest is keeping one tenant from starving the others, running scheduled work, waking workers without hammering the database, and keeping a busy table healthy.

Priorities and fairness

The priority column already lets an on-screen request jump a nightly export. In a multi-tenant product, store tenant_id on each job and cap how many running jobs one tenant may hold, or give heavy job types their own workers, so one customer's bulk import can't delay everyone. The multi-tenant SaaS checklist lists the other places tenancy has to reach.

Scheduled and recurring jobs

A job with a future run_after is a scheduled job; nothing claims it early. For recurring work, have the handler insert the next run when it finishes, or run a small scheduler that inserts due jobs each minute. Add a unique index on job type and scheduled time, so a scheduler that fires twice can't create two copies of one nightly report.

Waking workers: polling and LISTEN/NOTIFY

Poll with a short sleep when the queue is empty and immediately again when a job was found. LISTEN and NOTIFY can wake idle workers sooner, with two caveats from the NOTIFY docs: a notification reaches only sessions listening at that moment, and one sent inside a transaction is delivered only on commit. A worker that was reconnecting misses it. Keep polling as the backstop and treat notifications as a hint.

Keeping the table healthy

Every claim, heartbeat, and completion is an update, and Postgres keeps old row versions until vacuum reclaims them. The routine vacuuming docs recommend more frequent vacuuming for heavily updated tables and allow per-table autovacuum settings. Use them on the jobs table, and delete or archive finished jobs in batches after a retention period.

What to monitor

Signal	What it tells you
Age of the oldest queued job	A backlog, before users notice
Failed jobs per hour, by type	A broken integration
Running jobs with expired leases	Workers dying
Attempts per completed job	A flaky upstream
Duration per job type	A slowdown

Kraftr's workers write heartbeats every 10 seconds into a liveness view in its admin, so a stuck queue shows up before a customer reports it. Build that view whatever queue you choose. Our reliability and monitoring page covers alerting around it.

When is a Postgres job queue the wrong choice?

When the queue would compete with the product for the database. Very high job throughput, fan-out to many consumers as in event streaming, or queue traffic slowing user-facing queries on a busy primary are all good reasons to move. So is a team that already runs a dedicated queue well.

Kraftr's generation work, with seven queues and many long-running image and video jobs, is that kind of workload, and it runs on Redis. You also don't have to write the SQL above yourself. Procrastinate is a Python task library that uses PostgreSQL as its queue and integrates with Django, and pg-boss is a Node.js job queue built on Postgres that relies on SKIP LOCKED.

FAQ

Will a job ever run twice on a Postgres queue?

Yes, it can. If a worker stalls past its lease, reclaim hands the job to another worker, and the first may still be running. The attempts guard stops the stale worker from writing its result, but any side effect it already caused stays. Make handlers idempotent with unique keys, so a second run does no harm.

How long should a job lease be?

Pick a few heartbeat intervals, such as a five-minute lease renewed every minute. That survives one missed heartbeat or a short database hiccup without orphaning a healthy job, yet restarts a dead worker's job within minutes. Without heartbeats, the lease must outlast your slowest job, so every crash costs that much delay.

Is there a library that does this already?

Yes. Procrastinate is a Python task library that stores and dispatches jobs in PostgreSQL and has Django integration. pg-boss does the same for Node.js and relies on SKIP LOCKED. Using one saves writing claim and retry SQL, but check how it handles expired leases and maximum attempts before trusting it.

How many workers can share one Postgres queue?

Many. SKIP LOCKED lets each worker claim a different row without waiting on the others, so locking is rarely the limit. Database load is: every claim, heartbeat, and completion is a write. Watch connection count and the jobs table's vacuum activity as you add workers, and pool connections rather than opening one per worker thread.

Running background jobs on Postgres, or planning to? See our PostgreSQL development work and ask for a review of your queue, its reclaim logic, and your slow queries.