What is a Postgres job queue with SKIP LOCKED and how does it work?

A Postgres job queue is an ordinary table where each row is a unit of work, such as "send this email" or "resize this image". Workers pull rows from it concurrently. The mechanism that makes this work is the FOR UPDATE SKIP LOCKED clause on a SELECT.

Without it, the pattern breaks down. If ten workers run SELECT ... ORDER BY run_at LIMIT 1 FOR UPDATE, all ten target the same oldest row. One acquires the row lock, and the other nine block until that transaction finishes. SKIP LOCKED changes that behavior. When a candidate row is already locked by another transaction, Postgres passes over it and returns the next eligible row. Each worker claims a different job without waiting, and the table becomes a multi-consumer queue.

A job moves through four steps:

  1. Enqueue. The web tier inserts a row with a payload, a status such as pending, and a run_at timestamp.
  2. Claim. A worker in the pool opens a transaction and selects one or more pending rows with FOR UPDATE SKIP LOCKED, which locks them for that worker only.
  3. Execute. The worker performs the job's side effects.
  4. Complete. The worker deletes the row or marks it done, then commits. If the worker crashes before committing, the transaction aborts, the lock is released, and the row becomes claimable again.

This design does not guarantee ordering. ORDER BY is a best effort. Under concurrency, a newer job can start before an older one if the older one is locked, held by a slow worker, or retried. Nothing ensures that work is distributed fairly across tenants or job types. If you need strict FIFO or fairness, you have to build it yourself.

+--------------+
|   Web tier   |
+--------------+
       |
       | 1. Enqueue: INSERT (status='pending', run_at)
       v
+------------------------------------------------+
|                   jobs table                   |
+------------------------------------------------+
| id | status  | run_at | row lock               |
| 41 | pending | 10:00  | held by Worker A txn   |
| 42 | pending | 10:00  | held by Worker B txn   |
| 43 | pending | 10:01  | held by Worker C txn   |
| 44 | pending | 10:02  | free (next to claim)   |
+------------------------------------------------+
   ^    |          ^    |          ^    |
   |    v          |    v          |    v       2. Claim:
+-----------+   +-----------+   +-----------+   SELECT ... LIMIT 1
| Worker A  |   | Worker B  |   | Worker C  |   FOR UPDATE SKIP LOCKED
| 3. Execute|   | 3. Execute|   | 3. Execute|   (passes over rows
+-----------+   +-----------+   +-----------+    locked by others)
      |               |               |
      +---------------+---------------+
                      |
                      v
 4. Complete: DELETE row or mark done, then COMMIT -> lock released
    Crash before COMMIT: txn aborts, lock released, row claimable again

 Ordering is best effort: no strict FIFO or fairness under concurrency.

The web tier enqueues rows into the jobs table, and each worker uses SELECT ... FOR UPDATE SKIP LOCKED to claim a different unlocked row, executes it, and commits to complete it, with no strict FIFO or fairness guarantee.

Why enqueue jobs in the same transaction as your business write?

The strongest argument for a Postgres queue has nothing to do with throughput. When the INSERT into jobs runs in the same transaction as the business write, the two either commit together or roll back together. If an order insert fails a constraint check, the "send confirmation email" job disappears with it. If the order commits, its job is durable at that moment. No interleaving of crashes, deploys or network failures can separate them.

Move the queue to Redis and you take on a dual write. You commit to Postgres, then push to Redis, and the process can die in between. The order exists but no email is ever sent. Reverse the order and a worker can pick up a job for a row that later rolled back, or that isn't visible yet because the transaction hasn't committed. Teams usually fix this with an outbox table, a sweeper that re-enqueues missed work, or periodic reconciliation jobs. Each of these is code that has to be written, tested and monitored, and it tends to fail during incidents, when you can least afford it. A transactional enqueue removes this whole class of bug instead of patching it.

The guarantee has limits. It covers only writes to the same database. Once a job calls a payment API or sends an email, you are back in distributed territory. Delivery is still at-least-once: a worker can finish the side effect and crash before marking the job done, so the job runs again. Jobs must be idempotent under either design.

-- Business write and job enqueue share one transaction:
-- they commit together or roll back together.
BEGIN;

INSERT INTO orders (id, customer_id, total_cents)
VALUES ('9f1c2e4a-0000-4000-8000-000000000001', 42, 1999);

-- If the order insert above fails, this job never becomes visible.
-- The dedupe_key lets the email job check whether it already ran.
INSERT INTO jobs (kind, payload, dedupe_key)
VALUES (
  'send_order_confirmation',
  jsonb_build_object('order_id', '9f1c2e4a-0000-4000-8000-000000000001'),
  'order-confirmation:9f1c2e4a-0000-4000-8000-000000000001'
);

COMMIT;

-- Worker side: claim one job without blocking other workers.
-- Rows from uncommitted transactions are not visible here, so a worker
-- never sees a job whose order rolled back or hasn't committed yet.
BEGIN;

SELECT id, kind, payload
FROM jobs
WHERE done_at IS NULL
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- ... perform the side effect (send email) in application code ...
-- A crash here means the job runs again: delivery is at-least-once,
-- so the handler must be idempotent (e.g. keyed on dedupe_key).

UPDATE jobs SET done_at = now() WHERE id = $1;  -- $1 = id returned above

COMMIT;

The order row and its confirmation job are written in one transaction so neither can exist without the other, while the worker claims the job with FOR UPDATE SKIP LOCKED and still has to handle the job running more than once.

How do you implement a jobs table and dequeue query in Postgres?

A minimal jobs table needs id, queue, payload (jsonb), status (pending, running, done, dead), run_at, attempts, max_attempts, last_error, and a lease column such as locked_until. Add a partial index on (queue, run_at) with WHERE status = 'pending'. Finished rows drop out of that index, so the dequeue scan stays small even when millions of done rows remain in the table. The dequeue selects pending rows where run_at <= now(), orders by run_at, and uses FOR UPDATE SKIP LOCKED LIMIT n. Concurrent workers skip rows that another worker has already locked, so they do not block each other.

Long transaction Short claim + lease
Crash recovery Rollback releases the lock automatically Another worker reclaims the job after locked_until expires
Connection cost One connection held per running job Connection is released after the claim
Risk Long transactions hold back vacuum and bloat the table Lease must outlast the job, or the worker must extend it

On failure, increment attempts, store last_error, and push run_at forward exponentially, for example now() + 2^attempts seconds plus jitter. Once attempts reaches max_attempts, set status = 'dead' so the job stops retrying and remains available for inspection.

Use LISTEN/NOTIFY to wake idle workers right after an insert. Notifications are lost if no listener is connected, so keep a fallback poll every few seconds.

Per-key ordering, uniqueness, and rate limits are not provided by default. Implement them with a unique partial index on a dedupe key, or with advisory locks keyed by entity ID.

CREATE TABLE jobs (
  id           bigserial PRIMARY KEY,
  queue        text        NOT NULL DEFAULT 'default',
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'running', 'done', 'dead')),
  run_at       timestamptz NOT NULL DEFAULT now(),
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 5,
  last_error   text,
  locked_until timestamptz  -- lease: job may be reclaimed after this time
);
-- Only runnable rows are indexed, so done/dead rows don't slow the dequeue.
CREATE INDEX jobs_runnable ON jobs (queue, run_at) WHERE status = 'pending';

-- Short claim transaction: take a lease, commit, then run the job outside it.
-- Counting the attempt at claim time means crashed runs also count.
UPDATE jobs SET status = 'running', attempts = attempts + 1,
       locked_until = now() + interval '5 minutes'
WHERE id IN (SELECT id FROM jobs
             WHERE queue = 'emails' AND status = 'pending' AND run_at <= now()
             ORDER BY run_at LIMIT 10
             FOR UPDATE SKIP LOCKED)  -- concurrent workers skip each other's rows
RETURNING id, payload, attempts;

-- On failure ($1 = id, $2 = error): exponential backoff plus jitter, or dead.
UPDATE jobs SET last_error = $2, locked_until = NULL,
       status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
       run_at = now() + make_interval(secs => power(2, attempts) + random() * 5)
WHERE id = $1;

-- Reaper, run periodically: return jobs whose worker died and lease expired.
UPDATE jobs SET locked_until = NULL,
       status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END
WHERE status = 'running' AND locked_until < now();

-- Producer: wake listeners on commit; workers still poll because NOTIFY is not durable.
INSERT INTO jobs (queue, payload) VALUES ('emails', '{"user_id": 42}');
SELECT pg_notify('jobs', 'emails');

A jobs table with a partial index on runnable rows. The example covers a lease-based claim using FOR UPDATE SKIP LOCKED, exponential-backoff retries that end in a dead state, a reaper for expired leases, and a NOTIFY wakeup sent after insert.

When should you avoid a Postgres queue?

Table churn and vacuum

Postgres never updates a row in place. Each UPDATE writes a new tuple version and leaves the old one dead, and each DELETE leaves a dead tuple too. A jobs table has constant churn: rows are inserted, claimed, completed, then removed. Status and run_at columns are usually indexed, so these updates rarely qualify as HOT updates, and the indexes bloat along with the heap.

Illustrative estimate, not a benchmark. Assume 50 jobs per second, one row per job, and three row-modifying steps after insert: claim (UPDATE), complete (UPDATE), and purge (DELETE). Assume finished rows are kept for 24 hours.

  • Jobs per hour: 50 × 3,600 = 180,000
  • Dead tuples per hour: 180,000 × 3 = 540,000
  • Finished rows retained at steady state: 180,000 × 24 = 4.32 million

Autovacuum has to keep up with that rate. If it falls behind, dequeue scans get slower, which lengthens claim transactions, which lets dead tuples pile up faster. You will likely need per-table autovacuum settings, prompt purging or archiving, and possibly time-based partitions so that cleanup becomes dropping a partition.

Shared resources

Vacuum cannot remove tuples that are still visible to the oldest open transaction anywhere in the database. A long-running worker job or an analytics query can therefore stall cleanup of the queue table. Workers also use up connection slots that the web tier needs. Transaction-mode poolers break LISTEN/NOTIFY and session-level advisory locks, because both depend on staying on the same backend connection. Every enqueue and state change also generates WAL that replicas must replay, so queue throughput competes directly with application writes.

What are the alternatives: Graphile Worker and Redis queues like Sidekiq?

If you would rather not build this yourself, Graphile Worker is a Postgres-backed job runner for Node.js that already does it. It claims jobs with SKIP LOCKED, uses LISTEN/NOTIFY so workers pick up new jobs quickly without tight polling, and handles retries with backoff. You can enqueue by calling its SQL function (graphile_worker.add_job) inside your own transaction. This keeps the transactional guarantee, and you don't have to maintain the dequeue logic or the locking edge cases.

A Redis-backed queue such as Sidekiq works differently. Push and pop are in-memory list operations, so a single Redis instance can handle very high job rates without adding WAL to your primary database. The ecosystem is mature: retries, scheduled jobs and a web dashboard come with the tool. The cost is that Redis is a separate system and cannot join your database transaction. If you enqueue before commit, a worker can run a job for a row that was rolled back. If you enqueue after commit, a crash between the two steps loses the job.

How durable Redis is depends on how you configure persistence. RDB snapshots can lose everything written since the last snapshot. AOF with a looser fsync policy can lose roughly the last second of writes.

The usual fix for the consistency gap is the outbox pattern. You write an outbox row in the business transaction, and a relay process publishes it to Redis afterward. This restores atomicity, but it also brings back some of the Postgres insert, update and vacuum load you moved to Redis to avoid.

Option Consistency with business write Throughput Operational burden Tooling
Hand-rolled Postgres queue Atomic Bounded by primary's write capacity No new service; you own the code Whatever you build
Graphile Worker Atomic Bounded by primary's write capacity No new service; library upgrades Retries, cron, logging included
Sidekiq on Redis None without outbox High Separate Redis to run and persist Mature: retries, scheduling, web UI
Redis + outbox Atomic (at-least-once delivery) High dispatch; outbox writes hit Postgres Redis plus relay process Redis tooling plus custom relay

How does building your own queue affect development cost?

The dequeue query is the easy part; a working version takes an afternoon. The real cost is in everything around it, all of which mature libraries already ship:

  • Scheduling: delayed jobs and recurring jobs, with deduplication so two workers don't both fire the same cron entry.
  • Retries: backoff, attempt limits, and recovery for jobs whose worker crashed mid-run, which means lock timeouts or heartbeats.
  • Dead-job handling: a place for exhausted jobs to land and a safe way to inspect and replay them.
  • Observability: queue depth, oldest-job age, and failure rate exported to your metrics stack.
  • Admin tooling: pausing a queue, bulk-retrying, or deleting a poison job during an incident without hand-written SQL.
  • Table hygiene: pruning finished rows so the jobs table and its indexes don't bloat.

Teams usually scope the first bullet and discover the rest in production.

Illustrative estimate (assumed numbers, not a benchmark): First-year cost = build days + (maintenance days per month × 12)

  • Hand-rolled queue, assuming 15 build days and 2 maintenance days per month: 15 + 24 = 39 engineer-days.
  • Redis plus outbox, assuming 5 days to build the relay and 1.5 days per month on Redis operations and relay upkeep: 5 + 18 = 23 engineer-days. This option also brings a second stateful system to page on.

Substitute your own figures. The hand-rolled option rarely wins on cost alone.

The middle path avoids both bills. A Postgres-backed library such as Graphile Worker keeps the transactional enqueue and provides the tooling, so you are left maintaining only your job handlers.

Who should choose a Sidekiq alternative on Postgres? A decision checklist

Use the criteria below in order. The first two set the throughput ceiling. The rest decide which side of that ceiling you can comfortably live on.

Criterion Favours a Postgres queue Favours Redis (Sidekiq or similar)
Sustained job rate Moderate and steady, well within your primary's spare write capacity Very high and sustained, where queue churn competes with business writes
Job duration Seconds to minutes, so the claimed row isn't held for long Very short, high-volume jobs where per-job overhead dominates
Atomic with DB writes Required: enqueue must commit or roll back with the business change Not required, or you already run an outbox to bridge the gap
Operational expertise Team tunes Postgres, vacuum and indexes confidently Team already runs Redis with persistence and failover it trusts
Connection budget Worker pool fits under max_connections, with headroom or a pooler Postgres connections are already scarce

Every job is an insert, an update and a delete, so the line falls where queue traffic stops being background noise on the primary. You will see it before it hurts. Dead tuples on the jobs table outpace autovacuum, dequeue latency drifts upward, or worker connections crowd out web traffic. Below that point, a Postgres queue is the better default for a Postgres monolith. It removes a service to operate and makes "job enqueued but row never committed" impossible.

Keep or adopt Redis when you run sustained throughput that would make the jobs table one of your hottest write paths. Also choose it when your team already runs Redis well. Existing expertise is a real cost advantage, and there is little reason to give it up for a queue that is merely adequate.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres job queue skip locked, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.