Postgres SKIP LOCKED as a Job Queue: When You Don't Need Redis

Build a transactional job queue on Postgres with FOR UPDATE SKIP LOCKED, keep it healthy under MVCC, and know the numbers that say it is time for Redis.

TL;DR

  • A Postgres queue lets you enqueue a job in the same transaction as the data it depends on, so the two commit or roll back together.
  • Claim jobs with one statement: a CTE with FOR UPDATE SKIP LOCKED feeding an UPDATE.
  • Don't hold a transaction open while a job runs. Use a lease (started_at) and a janitor query, and make handlers safe to run twice.
  • Stay on Postgres until you pass roughly 1,000 jobs a minute or need p99 latency under 50ms.

The transactional advantage you are missing

Most engineers reach for Redis when they need a background job queue. That convenience has a cost: your job state and your business data now live in two systems that cannot share a transaction.

Say a signup should send a welcome email. Write the user to Postgres, then push the job to Redis, and a crash between the two leaves a lost job: the user exists, the email never goes out, and nothing in the database looks wrong. Push the job first instead, and a failed database write leaves a ghost job: a worker wakes up to process a user who does not exist.

Closing that gap across two systems takes an outbox table or a distributed transaction. Both are more machinery than most teams want for a welcome email.

In Postgres, the job is just another row. Your handler inserts the user and the job in one transaction, and they commit or roll back together. You get that guarantee without adding infrastructure.

The right way to claim a job

Here is a schema that the rest of this post runs against. The payload column is JSONB, so each job type can carry its own arguments without a migration.

CREATE TABLE jobs (
  id           bigserial   PRIMARY KEY,
  status       text        NOT NULL DEFAULT 'pending',  -- pending, running, done, failed
  priority     int         NOT NULL DEFAULT 0,
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 5,
  scheduled_at timestamptz NOT NULL DEFAULT now(),
  started_at   timestamptz,
  payload      jsonb       NOT NULL DEFAULT '{}'
);

The naive claim is SELECT... FOR UPDATE followed by a separate UPDATE. It works at first. Then you add workers, they all try to lock the same first row, and each waits for the one ahead of it. Your worker pool is now serialized through a single row. Adding NOWAIT swaps the waiting for errors your application has to catch and retry.

SKIP LOCKED fixes this. A worker that meets a locked row skips it and takes the next unlocked one, so each worker gets a different job and nobody waits on anybody.

Do the whole claim in one statement. The CTE picks and locks the next due job, and the UPDATE marks it as running in the same atomic statement. Whatever comes back belongs to this worker alone.

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND attempts < max_attempts
    AND scheduled_at <= now()
  ORDER BY priority DESC, scheduled_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status     = 'running',
    started_at = now(),
    attempts   = jobs.attempts + 1
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.attempts, jobs.payload;

Keep the id and attempts it returns. You need both to mark the job done safely later.

Optimizing the dequeue path

A queue table churns. Most of its rows are finished jobs, and an index that covers all of them makes both the dequeue query and autovacuum work harder than they need to.

Index only what workers look for. A partial index covers only pending rows, so the claim query never wades through completed jobs and the index stays small. Put the claim query's sort order into the index too, so Postgres can read rows already in order instead of sorting on every poll.

-- Matches the claim query's WHERE and ORDER BY
CREATE INDEX jobs_pending_idx
  ON jobs (priority DESC, scheduled_at)
  WHERE status = 'pending';
 
-- Running rows are outside the index above, so the janitor gets its own
CREATE INDEX jobs_running_idx
  ON jobs (started_at)
  WHERE status = 'running';

Keep the indexed column bare in the query. Wrapping it in a function, as in WHERE lower(status) = 'pending', stops the planner from using a plain index on it.

Managing the MVCC bloat tax

Postgres never updates a row in place. Every status change on a job writes a new tuple version and leaves the old one dead, and the B-tree index keeps pointing at those dead tuples until vacuum clears them. The pending-only partial index gets hit hardest, because rows enter and leave it all the time.

Autovacuum usually keeps up, with one hard limit: it will not remove a dead tuple that might still be visible to an active transaction. The oldest open transaction sets that cutoff, the MVCC horizon. One long transaction, like a forgotten psql session or a slow report, holds the horizon back for the whole database, and dead tuples pile up behind it.

So never keep a transaction open for the length of a job. The claim query commits straight away, which makes it a lease: started_at records when a worker took the job. The worker does its work, then marks the job done in a second short transaction, guarded so that it only succeeds if it still owns the job:

-- id and attempts are the values the claim query returned
UPDATE jobs
SET status = 'done'
WHERE id = 1
  AND status = 'running'
  AND attempts = 1;

If that update touches no rows, the job was taken back from this worker, so log it and drop the result.

If the worker dies, the job sits in running forever unless something notices. That is the janitor's job. Run it every minute or so from cron, a scheduler or pg_cron:

UPDATE jobs
SET status     = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    started_at = NULL
WHERE status = 'running'
  AND started_at < now() - interval '5 minutes';

It does not touch attempts, because the claim already counted this one. A job that keeps killing its worker ends in failed instead of looping forever.

Make the timeout a setting (the 5 minutes above is a placeholder), and set it well above your slowest normal job, not your average one. Too short, and a slow but healthy worker loses its job to a second worker while still running it. The guard stops the first worker's late done from overwriting the second worker's run. It cannot stop the work happening twice. This queue delivers at least once, so write handlers that are safe to run twice.

When row locks hit the ceiling

SKIP LOCKED does not make locking free. With many workers racing for the same rows, there are brief moments when more than one transaction holds a lock on a row. Postgres then replaces the row's transaction ID with a MultiXact ID, a list of lockers kept outside the row. Any FOR SHARE or FOR KEY SHARE lock on the same rows makes it worse, and foreign key checks take FOR KEY SHARE locks without asking, so a jobs table that other tables reference builds up MultiXacts quickly.

MultiXact data lives in a small, fixed-size cache in shared memory. When the MultiXacts you are looking up don't fit, Postgres evicts them and reads them back from disk over and over. You will see it as MultiXactMemberSLRU and MultiXactOffsetSLRU wait events piling up across many backends in pg_stat_activity.

At that point, move coordination off the rows. Advisory locks don't touch the heap, don't create MultiXact entries and don't generate dead tuples. They live entirely in shared memory. The price is that you give up the atomicity of FOR UPDATE SKIP LOCKED, so a worker that takes an advisory lock still has to handle the job failing partway through.

Advisory locks work well for coordinating whole job types, for example allowing only one worker at a time on invoice generation. Use the transaction-level functions, the ones with _xact_ in the name. They release on commit or rollback. A session-level lock survives the transaction, and with a connection pool that means an unrelated worker can inherit the connection and its lock. The flip side is that a transaction-level lock lasts only as long as its transaction, and the MVCC rule still holds: guard a quick step with it, not a job that runs for minutes.

BEGIN;
-- One key per job type. hashtext turns a readable name into the integer key,
-- so nobody has to keep a list of hand-picked numbers.
SELECT pg_try_advisory_xact_lock(hashtext('invoice_generation')) AS got_lock;
-- got_lock = true:  do the short, guarded step in this transaction
-- got_lock = false: another worker holds this job type, so move on
COMMIT;  -- the lock is released here, or on ROLLBACK

When to reach for Redis instead

The question isn't which tool is better. It's where your bottleneck is: throughput, latency or operations.

On throughput, one benchmark found Postgres within 8% of Redis at under 1,000 jobs a minute. That is where one benchmark saw the gap open, not a Postgres limit: the author of Que, a Ruby gem built on advisory locks, wrote that it could pass 10,000 jobs per second on the right hardware. Below the line, keeping your jobs next to your data means one less system to monitor, patch, back up and scale.

On latency, the gap is real. The same benchmark measured p99 at 85ms for Postgres against 34ms for Redis. For email sends, report generation and webhook delivery, nobody notices. For work where a user is waiting on the result, they might.

Here is what to do:

  1. Start on Postgres with the schema, claim query, indexes and janitor above.
  2. Watch jobs per minute, p99 latency from enqueue to claim, and the MultiXact wait events.
  3. Consider moving a job type to Redis when it passes about 1,000 jobs a minute and your own numbers show Postgres struggling, or when it needs p99 latency under 50ms, which the benchmark puts out of Postgres's reach. That job type loses the transactional enqueue, so give it an outbox or idempotent handlers first.

If what you need is millions of cache reads a second, that was never a queue problem, and Redis is the right tool for it.

Sources

postgresarchitecturebackenddatabase

All writing