Postgres Job Queue: SKIP LOCKED, Leases, Retries and When to Switch

The same job runs twice. In a naive Postgres job queue, a customer gets two receipt emails because two workers read the same row before either marked it as taken.

Picture a common setup. A small online store in Pune saves each order, then a background worker sends the receipt email and builds an invoice PDF. The setup is made up, but the double-send bug is real.

The store already runs PostgreSQL, so the team keeps background jobs in a jobs table instead of adding a separate broker. That is a sound choice, and this guide shows how to make the Postgres job queue correct, then how to keep it fast.

When the store adds a second worker to keep up with a sale, receipts start going out twice. We reproduced this: eight workers draining 400 jobs with a plain read-then-update claimed 244 to 260 of them more than once.

You need six words. Each one is tied to the store's receipt emails.

• Job: one row that describes one piece of background work, such as sending the receipt for order 101.
• Worker: a process that loops forever, claiming a job, running it and marking it done. The store runs several.
• Transaction: a group of SQL statements that succeed or fail together. Nothing it changes is visible to others until it commits.
• Row lock: a mark a transaction puts on one row. Other transactions that try to change or lock that row must wait, but plain reads are not blocked.
• Lease: a deadline written on a claimed job. Until it passes, other workers leave the job alone. The hosted queue SQS calls the same idea a visibility timeout.
• Dead job: a job that failed too many times. It is parked for a human to inspect instead of being retried forever.

A Postgres job queue is a table where each row is a job with a status, and workers claim rows with SELECT ... FOR UPDATE SKIP LOCKED. The lock stops two workers taking the same row, and SKIP LOCKED stops them queueing behind each other.

Here is the table the rest of this guide uses. We ran it, and every other SQL sample here, on PostgreSQL 16.15 and 18.4. PostgreSQL 18 is the current major release as of 26 October 2026.

CREATE TABLE jobs (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind         text        NOT NULL,
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'ready'
               CHECK (status IN ('ready', 'running', 'done', 'dead')),
  run_at       timestamptz NOT NULL DEFAULT now(),
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 5,
  leased_until timestamptz,
  last_error   text
);

CREATE INDEX jobs_ready_idx ON jobs (run_at) WHERE status = 'ready';
CREATE INDEX jobs_running_idx ON jobs (leased_until) WHERE status = 'running';

The status moves from ready to running to done, or to dead. The run_at column says when a job may run, which is how retries wait. The two partial indexes are explained in the index section.

Adding a job is a plain INSERT. The big win over a separate broker is that it can sit in the same transaction as the order itself.

BEGIN;
INSERT INTO orders (email, total) VALUES ('[email protected]', 1499.00);
INSERT INTO jobs (kind, payload)
VALUES ('send_receipt', jsonb_build_object('order_id', currval('orders_id_seq')));
SELECT pg_notify('jobs_ready', 'send_receipt');
COMMIT;

In our test, a rolled-back order left no job behind, and a committed order always had its job. With a separate broker, the app must handle the gap where the order commits but the message send fails.

An everyday picture helps. Think of a bank counter where tokens hang on a board. A clerk who takes a token flips it over, so nobody else serves that customer.

FOR UPDATE is flipping the token. Without SKIP LOCKED, the next clerk reaches for the same flipped token and stands waiting. With SKIP LOCKED, they take the next unflipped one. Where the picture breaks: a row lock vanishes the moment its transaction ends, even if the clerk fainted mid-task.

Because two workers can read the same ready row before either one writes running. The read and the write are separate statements, and nothing stops another worker slipping in between them.

-- Naive claim: shown because it is wrong. Do not use it.
SELECT id FROM jobs WHERE status = 'ready' ORDER BY run_at LIMIT 1;
-- the app reads id 1, then runs:
UPDATE jobs SET status = 'running' WHERE id = 1;

This is the naive version, and it is wrong. Under READ COMMITTED, the default rule for what one transaction sees of others, both UPDATEs succeed, because the second one still finds the row and simply sets running again.

Pause and predict: with eight workers and 400 jobs that each take 10 ms, how many jobs run twice? Many people guess a handful.

In three runs on PostgreSQL 16.15, 244, 251 and 260 jobs were claimed more than once. That is 61 to 65 percent. On 18.4 it was 212 to 238. The scripts are in our test folder, and your counts will vary with timing.

It makes the second worker wait. Plain FOR UPDATE locks the row the first worker picked, and any other worker that wants the same row blocks until the first transaction ends.

The PostgreSQL docs say other transactions trying UPDATE, DELETE or SELECT FOR UPDATE on that row will be blocked until the current transaction ends. We watched it happen with two sessions on both versions.

• Worker A locked job 1 and kept its transaction open for 2 seconds.
• Worker B ran the same query. After 1 second, pg_stat_activity showed it waiting on a Lock, with wait event transactionid.
• When A committed, B finished after 2.01 seconds and returned job 2, not job 1. It rechecked job 1, saw it was no longer ready, and moved on.
• With NOWAIT instead, B failed at once with error 55P03, could not obtain lock on row.
• With SKIP LOCKED, B returned job 2 in under 10 milliseconds.

So plain FOR UPDATE is correct but slow: every worker queues behind the first one. NOWAIT is fast but makes the app catch an error and try again.

-- Run this in two psql windows at the same time.
BEGIN;
SELECT id FROM jobs
WHERE status = 'ready' AND run_at BETWEEN '-infinity' AND now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- With two ready jobs, window 1 prints id 1 and window 2 prints id 2 at once.
-- Delete the words SKIP LOCKED and window 2 waits instead.
ROLLBACK;

The docs give the reason it suits queues. Skipping locked rows gives an inconsistent view of the data, so it is not for general work, but it avoids lock contention for many consumers of a queue-like table.

Claim and mark the job in one short statement. A WITH query picks and locks the next due row, and the UPDATE marks it running, adds one to attempts and sets a lease five minutes ahead.

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'ready' AND run_at BETWEEN '-infinity' AND now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running',
    attempts = jobs.attempts + 1,
    leased_until = now() + interval '5 minutes'
FROM next
WHERE jobs.id = next.id
RETURNING jobs.id, jobs.kind, jobs.payload, jobs.attempts;

Read it in parts. The WITH query finds the oldest ready job that is due and locks it, skipping rows other workers hold. The UPDATE flips it to running, and RETURNING hands the worker the job.

BETWEEN '-infinity' AND now() simply means run_at is now or earlier. The SELECT docs say a locking clause must sit inside the WITH query to lock rows there, which is why FOR UPDATE is written inside it.

Change LIMIT 1 to LIMIT 100 to claim a batch. Our drain test claimed 1,00,000 (100,000) jobs in 1,000 batches of 100, and no batch ever returned more than 100 rows.

We then ran the same 400-job, eight-worker test five ways, three rounds each. Ideal times are 4.0 seconds for one worker at a time and 0.5 seconds for perfect sharing.

What just happened? Without SKIP LOCKED, the hold-the-transaction workers formed a single line, so 400 jobs took longer than the serial ideal. With leases, the lock lasts only for the claim, so plain FOR UPDATE hurt little at this load.

SKIP LOCKED still helped when claims were the whole cost. With zero work per job and 3,000 jobs, lease claims took 1.90 to 2.07 seconds with SKIP LOCKED and 2.06 to 2.47 without.

Beginners can stop here: you have a correct, parallel queue. The rest covers crashed workers, retries, wake-ups, indexes, bloat and when to leave Postgres.

Take a lease for anything slower than a moment. Holding the transaction open is simpler, but it keeps a lock and a transaction ID alive for the whole job, and that blocks cleanup for the whole database.

In the hold style, the worker runs BEGIN, claims with SKIP LOCKED, does the work, marks the job done and commits. If its database session ends, Postgres rolls the transaction back and the row lock disappears.

We ended both kinds of worker's sessions with pg_terminate_backend. The held job was back to ready and claimable at once. The leased job stayed running until its lease ran out, as designed.

The cost shows up in cleanup. A transaction that has locked a row has a transaction ID, and VACUUM, the command that removes old row versions, must keep anything that ID might still need.

With one hold-style worker sitting on one job, we updated 1,000 other jobs and ran VACUUM (VERBOSE). It reported 0 removed and 1,000 dead but not yet removable. After the worker committed, all 1,000 were removed.

A job that calls a slow payment API for 30 seconds holds back cleanup for those 30 seconds, every time. The lease style commits the claim at once and runs the work with no transaction open.

A lease is a deadline on the claimed row. If the worker finishes in time, it marks the job done. If it crashes, a reaper query finds running jobs whose deadline has passed and makes them ready again.

UPDATE jobs
SET status = CASE WHEN attempts = greatest(attempts, max_attempts)
                  THEN 'dead' ELSE 'ready' END,
    leased_until = NULL,
    last_error = 'lease expired'
WHERE status = 'running'
  AND leased_until BETWEEN '-infinity' AND now();

Run the reaper every minute from a scheduler, or from each worker before it claims. On both versions, EXPLAIN showed it using the partial index on leased_until, so it stays cheap.

Why completion must check attempts

A slow worker is not a dead one. If worker A is still busy when its lease expires, worker B can claim the same job, and now two workers are running it.

So the completion statement checks attempts, which works as a fencing token. A fencing token is a number that proves you still hold the claim, because it changes on every new claim.

UPDATE jobs
SET status = 'done', leased_until = NULL
WHERE id = 1 AND status = 'running' AND attempts = 1;

We tested this with a 1-second lease. A claimed the job with attempts 1 and then stalled. After the reaper ran, B claimed it with attempts 2. A's completion then updated 0 rows, and B's updated 1.

The check stops a stale worker from overwriting the record, but it cannot undo work already done. If A sent the email before stalling, the customer still gets two.

This is why queues like this are called at least once. Make each job idempotent, meaning running it twice has the same effect as running it once. For example, record that receipt 101 was sent, and check that record first.

When a try fails, set the job back to ready with a later run_at, and mark it dead once it has used all its attempts. The claim query already skips jobs whose run_at is in the future.

UPDATE jobs
SET status = CASE WHEN attempts = greatest(attempts, max_attempts)
                  THEN 'dead' ELSE 'ready' END,
    run_at = now() + least(power(2, attempts) * interval '10 seconds',
                           interval '1 hour'),
    leased_until = NULL,
    last_error = 'HTTP 503 from webhook'
WHERE id = 2 AND status = 'running' AND attempts = 1;

The wait doubles with each try: 20, 40, 80 and 160 seconds, capped at one hour. With max_attempts at 5, the fifth failure sets the job to dead. We stepped one job through all five failures to confirm this.

The greatest() test is attempts at or above max_attempts, written so it still works if someone lowers max_attempts later. Backoff matters because the usual cause of failure, such as a partner API being down, needs time to clear.

Dead jobs keep their last_error, so a person can read why they failed. Once the cause is fixed, put them back by hand.

UPDATE jobs
SET status = 'ready', attempts = 0, run_at = now(), last_error = NULL
WHERE status = 'dead' AND kind = 'call_webhook';

The enqueuing transaction calls pg_notify, and idle workers wait on LISTEN instead of querying in a tight loop. Keep a slow fallback poll, because a notification is a hint, not a stored message.

A worker that finds nothing due waits for a notification or 5 seconds, whichever comes first. This sample uses psycopg 3.2, a Python driver; the timeout and stop_after options were added in that version.

import psycopg

DSN = "dbname=shop user=queue_worker"  # your connection string
CLAIM = """
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'ready' AND run_at BETWEEN '-infinity' AND now()
  ORDER BY run_at LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running', attempts = jobs.attempts + 1,
    leased_until = now() + interval '5 minutes'
FROM next WHERE jobs.id = next.id
RETURNING jobs.id, jobs.kind, jobs.payload, jobs.attempts"""


def handle(conn, job):
    job_id, kind, payload, attempts = job
    # send the email or build the PDF here, then mark it done
    conn.execute(
        "UPDATE jobs SET status = 'done', leased_until = NULL "
        "WHERE id = %s AND status = 'running' AND attempts = %s",
        (job_id, attempts),
    )


listen = psycopg.connect(DSN, autocommit=True)
listen.execute("LISTEN jobs_ready")
work = psycopg.connect(DSN, autocommit=True)

while True:
    job = work.execute(CLAIM).fetchone()
    if job:
        handle(work, job)
        continue
    # Nothing due: wait for a NOTIFY, or 5 seconds at most
    for _ in listen.notifies(timeout=5.0, stop_after=1):
        pass

Note the two connections. The listening one stays idle and outside any transaction, which matters because of the rules we tested on both versions.

• A NOTIFY inside a transaction that rolled back was never delivered. One inside a committed transaction arrived only after COMMIT.
• Three identical notifications in one transaction arrived as one. Three with different payloads arrived as three.
• A listener busy inside its own transaction got nothing until that transaction ended.
• A notification sent while no session was listening was simply gone. A worker that reconnected saw nothing.

The last point is why the fallback poll exists. In our run, a job enqueued with a NOTIFY was picked up within 10 milliseconds, and one enqueued without it waited for the 5-second poll.

The NOTIFY docs add limits. The payload must be under 8,000 bytes by default, so send an ID, not data. If the notification queue, 8 GB in a standard installation, fills up, transactions calling NOTIFY fail at commit.

LISTEN registrations belong to one database session and end with it. So keep the listener on its own direct connection, not one that a connection pooler, a proxy that shares a few database connections among many clients, hands to others.

Index only what the hot queries search: ready jobs by run_at, and running jobs by leased_until. Partial indexes, which cover only rows matching a WHERE condition, keep both tiny while done rows pile up.

We filled the table with 1,00,000 done jobs and 100 ready ones, then rebuilt the indexes. The partial ready index was 16 kB. A full index on status and run_at was 3,104 kB, about 194 times larger. The index file does not shrink by itself: after 1,00,000 queued jobs drained, ours stayed 2,208 kB until rebuilt.

EXPLAIN (COSTS OFF)
SELECT id FROM jobs
WHERE status = 'ready' AND run_at BETWEEN '-infinity' AND now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Look for: Index Scan using jobs_ready_idx on jobs

Run EXPLAIN on your own data. On both versions the plan showed an Index Scan using jobs_ready_idx with a LockRows step above it, which is the row locking at work.

One catch from the partial index docs: the planner uses the index only if the query's WHERE clearly implies status = 'ready'. The docs say parameterised clauses do not work here, so write 'ready' as a literal.

Every status change writes a new row version and leaves the old one dead, so each job leaves dead versions behind. Autovacuum, the background cleaner, normally keeps up, but one long transaction anywhere in the database stops it.

The SevyDevy guide on Postgres MVCC explains row versions in depth. Here, each job is one INSERT and two UPDATEs, so it leaves two dead versions.

We drained 1,00,000 jobs through the claim and done steps, with no other sessions open. The table file reached 29 MB on 16.15. Right after the drain, one empty claim read 275 buffers, the 8 kB pages Postgres caches in memory. After VACUUM it read 1. On 18.4 the figures were 196 and 1.

The idle transaction that slows every claim

Then we repeated the drain while one REPEATABLE READ transaction sat idle after a single SELECT. Such a transaction keeps one snapshot, its frozen view of committed data, until it ends. VACUUM (VERBOSE) reported 0 removed and 2,00,000 dead but not yet removable.

Every claim now had to read past index entries for drained jobs, because none could be cleaned up. An empty claim read 1,609 buffers and took 8 to 11 milliseconds across our runs. Once the transaction ended and VACUUM ran, it read 1 buffer and took about 0.2 milliseconds.

Those timings are from a small test machine; the buffer counts are the steadier signal. The cause matches the MVCC rule: dead versions cannot be removed while any open snapshot might still need them.

Keeping cleanup ahead

Four habits keep a busy jobs table healthy. The queries below show what to watch and what to set.

SELECT status, count(*),
       max(now() - run_at) FILTER (
         WHERE status = 'ready' AND run_at BETWEEN '-infinity' AND now()
       ) AS oldest_due_wait
FROM jobs GROUP BY status ORDER BY status;

SELECT pid, state, now() - xact_start AS xact_age, backend_xid, backend_xmin
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY xact_start;

SELECT n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'jobs';

• Watch n_dead_tup and last_autovacuum on the jobs table, and any session with an old xact_age.
• Delete done jobs in batches, or copy them to an archive table first. Small batches keep each transaction short.
• Lower the autovacuum scale factor on the jobs table if it keeps many rows. The defaults wait for 50 dead rows plus 20 percent of the table's rows.
• Set idle_in_transaction_session_timeout for worker roles. Its default is 0, which means off.

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

DELETE FROM jobs
WHERE id IN (
  SELECT id FROM jobs
  WHERE status = 'done'
  LIMIT 5000
);

ALTER ROLE queue_worker SET idle_in_transaction_session_timeout = '1min';

The table settings above are a starting point, not a rule; tune them by watching n_dead_tup. For very large job history, the partitioning docs note that dropping a whole partition avoids the VACUUM cost of a bulk DELETE.

Move when the queue's needs stop matching what a table does well. Staying is right while jobs are tied to your database writes, volumes fit comfortably, and one team owns both producer and workers.

There is no single jobs-per-second line. Our 2-CPU test drained 1,00,000 batched jobs in about 4 seconds, but that is one machine with empty jobs, not a benchmark. Measure your own claim time and dead tuples.

• Many consumers need the same message: one event goes to billing, email and analytics. A table queue hands each job to one worker.
• Consumers need to replay history from an offset, which is a position in a log. That is a log-based broker's core feature.
• Producers and consumers belong to different teams or services that should not share a database.
• Queue churn makes autovacuum fall behind, or claims slow down, even after you fix long transactions and indexing.
• Workers hold so many database connections that the queue competes with your main app for them.

The SevyDevy guide on message queues compares Kafka, RabbitMQ and SQS if you reach that point. You can also keep the transactional enqueue in Postgres and have a relay process forward jobs to the broker.

Lead with why: jobs commit in the same transaction as the business data. Then show you know the three failure modes, which are double claims, crashed workers and table bloat.

1. 1

   State the need

   Background work, such as receipts and PDFs, must run for every order, survive worker crashes and not slow checkout.

2. 2

   Describe the table

   A jobs table with status, run_at, attempts and a lease column. Enqueue in the same transaction as the order.

3. 3

   Explain the claim

   One statement selects the next due job FOR UPDATE SKIP LOCKED and marks it running with a lease. Workers never wait on each other's row locks.

4. 4

   Handle failure

   A reaper resets expired leases. Failures retry with exponential backoff, then go dead. Jobs are idempotent because delivery is at least once.

5. 5

   Cover operations

   Partial indexes on ready and running rows, LISTEN/NOTIFY with a fallback poll, batch deletes and no long transactions.

6. 6

   Name the exit

   Move to a broker for fan-out, replay, cross-team boundaries or when vacuum cannot keep up.