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

A Postgres job queue is a jobs table with a status column (queued, running, done, failed), a run_at timestamp for scheduling and retry backoff, an attempts counter, and a payload column. Workers claim work by opening a transaction and running SELECT ... FOR UPDATE SKIP LOCKED against rows that are queued and due. They then mark the claimed row running and increment attempts.

Without SKIP LOCKED, a second worker that reaches a row the first worker has locked waits for that lock to be released. Under load, workers would queue behind each other on the oldest job. With SKIP LOCKED, the second worker passes over locked rows and takes the next unlocked one. Concurrent workers spread across different rows instead of contending for the same row.

The claim query filters on status = 'queued' AND run_at <= now(), orders by run_at, and uses LIMIT to take one row or a small batch. A partial index on run_at WHERE status = 'queued' supports it. The index covers only pending rows, so it stays small even when the table holds millions of finished jobs.

Polling adds latency up to the poll interval. Libraries such as Graphile Worker reduce that delay with LISTEN/NOTIFY: the enqueue issues a NOTIFY, and Postgres delivers it to listening workers only when the transaction commits. Notifications are not stored, though. A worker that is disconnected or restarting misses them, so these libraries keep polling as a fallback.

Flow: app transaction inserts job → commit makes row visible in jobs → NOTIFY fires → worker wakes and claims with SKIP LOCKED → job runs → worker marks row done and commits.

-- Jobs table: status, scheduling time, retry counter, payload
CREATE TABLE jobs (
  id       bigserial PRIMARY KEY,
  status   text        NOT NULL DEFAULT 'queued',  -- queued | running | done | failed
  run_at   timestamptz NOT NULL DEFAULT now(),
  attempts int         NOT NULL DEFAULT 0,
  payload  jsonb       NOT NULL
);

-- Partial index covers only pending rows, so it stays small as done jobs pile up
CREATE INDEX jobs_pending_run_at ON jobs (run_at) WHERE status = 'queued';

-- Enqueue inside the app's own transaction; the NOTIFY is delivered only on COMMIT
BEGIN;
INSERT INTO jobs (payload) VALUES ('{"type": "send_email", "user_id": 42}');
SELECT pg_notify('jobs_new', '');
COMMIT;

-- Worker session runs: LISTEN jobs_new;
-- It runs the claim on each notification AND on a poll timer (notifications are not durable)
BEGIN;
UPDATE jobs
SET status = 'running', attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED   -- pass over rows other workers hold instead of waiting
)
RETURNING id, payload;
COMMIT;

-- After the job's work succeeds, mark it finished
UPDATE jobs SET status = 'done' WHERE id = $1;

A minimal Postgres queue: the jobs table with a partial index on pending rows, an enqueue that fires NOTIFY on commit, a FOR UPDATE SKIP LOCKED claim run on notification or poll, and the completion update.

Why use transactional enqueue instead of enqueuing to Redis after commit?

The core advantage of a Postgres job queue is that the job row and the business row share a transaction, so they commit or roll back together. When you enqueue to Redis, you have two separate systems to write to and no transaction that spans both. Each ordering of the writes has its own failure mode.

Enqueue inside the transaction, before commit. If the transaction later rolls back, the job already sits in Redis and points at an order that does not exist. A fast worker can also pick up the job before the commit lands and find no row, even when the commit eventually succeeds. Teams often patch this with retries, which hides the race without removing it.

Enqueue after commit. The process can crash, get OOM-killed, or lose its connection to Redis between COMMIT and the push. The order row is durable, but no confirmation email is sent and nothing records that it was skipped.

With Postgres, the call looks like INSERT INTO orders ...; INSERT INTO jobs ...; COMMIT;. There is no gap between the two writes.

On the Redis side, the standard fix is the transactional outbox. You write an outbox row in the business transaction, and a separate relay process reads unsent rows, pushes them to Redis, and marks them sent. That works, but you now own:

  • an extra table
  • a long-running relay with its own deployment and alerting
  • outbox lag as a new metric to watch
  • cleanup of sent rows
  • idempotent handlers, because the relay delivers at-least-once

At that point you have already built most of a Postgres queue, and Redis is just the last step in the chain.

import json
import psycopg
import redis

DSN = "postgresql://app:example_password@localhost:5432/shop"
r = redis.Redis(host="localhost", port=6379)

INSERT_ORDER = "INSERT INTO orders (customer_id, total_cents) VALUES (%s, %s) RETURNING id"

def place_order_redis(customer_id: int, total_cents: int) -> None:
    with psycopg.connect(DSN) as conn:
        with conn.transaction():  # COMMIT happens when this block exits
            order_id = conn.execute(INSERT_ORDER, (customer_id, total_cents)).fetchone()[0]
        # Dual-write gap: the order is durable, but a crash, OOM kill or Redis
        # timeout right here means the confirmation job is never enqueued.
        r.lpush("jobs:send_confirmation", json.dumps({"order_id": order_id}))

def place_order_postgres(customer_id: int, total_cents: int) -> None:
    with psycopg.connect(DSN) as conn:
        with conn.transaction():
            order_id = conn.execute(INSERT_ORDER, (customer_id, total_cents)).fetchone()[0]
            # The job row is part of the same transaction as the order row.
            conn.execute(
                "INSERT INTO jobs (kind, payload) VALUES (%s, %s::jsonb)",
                ("send_confirmation", json.dumps({"order_id": order_id})),
            )
        # Both rows committed together, or a rollback discarded both.
        # Workers cannot see the job until the order is visible too.

The Redis version leaves a window between COMMIT and LPUSH where the job can be lost, while the Postgres version writes the order and its job in one transaction so they commit or roll back together.

How do Redis-backed workers like Sidekiq work?

Sidekiq's model is simple. When you enqueue a job, the client serializes the job class name and arguments to JSON and pushes the payload onto a Redis list named after the queue. Worker processes run many threads that block on those lists and pop payloads as they arrive. Each thread deserializes a payload and calls your code. Because Redis serves these operations from memory, enqueueing and dequeueing put almost no load on your primary database.

Time-based work uses sorted sets instead of lists. Scheduled jobs and failed jobs awaiting retry sit in sorted sets scored by their due timestamp. A poller periodically moves due entries back onto the regular queue lists. Jobs that exhaust their retries go to a dead set, where you can inspect them and requeue them by hand.

The ecosystem is the main reason to choose this approach. Mature Redis worker libraries commonly ship with:

  • a web dashboard showing queue depth, busy workers, retries and dead jobs
  • automatic retries with exponential backoff
  • scheduled and recurring jobs
  • concurrency limits and rate limiting, though some of these features sit in commercial tiers

A hand-rolled Postgres queue built on SKIP LOCKED has none of these. Each one becomes a table column, an index, a cron task or an admin page that you write and maintain. Postgres-backed libraries narrow the gap, but how much they cover varies. Graphile Worker, for example, handles retries with backoff and scheduled jobs well. Before you commit to any library, check how it handles dashboards and rate limiting, because those are the features where Postgres-backed options differ most.

What happens when a worker crashes mid-job?

Delivery semantics differ by backend, but no backend gives you exactly-once execution. A crash, a network partition, or a timeout can cause any of them to run a job twice. The real choice is how you detect abandoned work.

Postgres: open transaction vs. lease column

If a worker claims a row with SELECT ... FOR UPDATE SKIP LOCKED and keeps that transaction open while it works, a crash is simple to handle. The connection drops, Postgres rolls back, the lock is released, and the row becomes visible to other workers again. The cost shows up with long jobs. Each in-flight job holds a connection for its full duration, and a long-open transaction stops vacuum from cleaning up dead tuples across the database.

A lease column avoids that cost. The worker sets locked_until = now() + interval and commits right away. Other workers reclaim rows whose lease has expired. This introduces two edge cases. A crashed job stays stuck until its lease runs out. A slow but healthy job whose lease expires gets picked up a second time while the first run is still going.

Redis: reservation and recovery

A plain blocking pop removes the job from Redis, so a crash after the pop loses the job. Reliable-fetch strategies avoid this by atomically moving the job into a per-worker in-progress list. A recovery process then re-queues entries left behind by dead workers. That recovery path is also at-least-once.

Make handlers idempotent

Pass an idempotency key, such as order_id plus the action, and write it to a table with a unique constraint in the same transaction as the side effect. If the job runs a second time, it hits the constraint violation and exits without repeating the work.

-- One row per completed unit of work. The primary key is the idempotency key.
CREATE TABLE processed_jobs (
    idempotency_key text PRIMARY KEY,
    processed_at    timestamptz NOT NULL DEFAULT now()
);

-- Handler for a 'charge order 4217' job. The key and the side effect
-- commit together or not at all.
BEGIN;

-- Claim the key first. If an earlier run already committed it, this raises
-- unique_violation (SQLSTATE 23505) and the transaction aborts before any
-- work is repeated. If another run is still in flight, this INSERT waits
-- until that run commits (then fails) or rolls back (then succeeds).
INSERT INTO processed_jobs (idempotency_key)
VALUES ('order:4217:charge');

-- The side effect, in the same transaction as the key.
INSERT INTO ledger_entries (order_id, kind, amount_cents)
VALUES (4217, 'charge', 1999);

UPDATE orders SET status = 'charged' WHERE id = 4217;

COMMIT;

-- Worker logic around this transaction:
--   success          -> acknowledge or delete the job
--   SQLSTATE 23505   -> ROLLBACK, treat as already done, acknowledge the job
--   any other error  -> ROLLBACK and let the job be retried
-- This protects only writes made in this database. External calls such as
-- payment APIs or email need their own idempotency key passed downstream.

An idempotent job handler that inserts an idempotency key under a primary-key constraint in the same transaction as its database writes, so a duplicate run fails on the constraint and repeats no work.

What does a Postgres job queue cost your database and your ops team?

Every job is at least one insert, a status update on claim, and a delete or update on completion. Under MVCC each update or delete leaves a dead tuple behind. A busy jobs table can therefore produce dead rows faster than default autovacuum thresholds trigger, because those thresholds scale with table size. Set per-table storage parameters such as a low autovacuum_vacuum_scale_factor on the jobs table so it gets vacuumed often.

The claim query also needs a partial index covering only runnable rows, for example on run_at where status is queued. Without it, every poll scans past finished and dead entries.

Clear out finished jobs on a schedule. Batched periodic deletes work at moderate volume. Time-based partitioning lets you drop an entire old partition instead of creating more dead tuples.

Long-running transactions are the hidden risk. Any open transaction holds back the oldest visible snapshot, so vacuum cannot remove dead rows anywhere in the database, not only in the jobs table. A forgotten BEGIN in a reporting session lets queue bloat build up and slows claim queries and unrelated queries alike.

Illustrative estimate (assumed numbers, not a benchmark). Connections = worker processes × concurrency + web processes × pool size + admin headroom.

  • Workers: 4 × 10 = 40
  • Web: 6 × 5 = 30
  • Admin headroom: 5
  • Total: 75 against a max_connections of 100

A rolling deploy that briefly runs old and new processes side by side can push this past the limit. Use a connection pooler and cap worker concurrency.

Concern Redis workers Postgres queue
Durability Choose RDB snapshots (can lose recent jobs) or AOF Existing WAL and backups
Memory Size for peak backlog Disk-backed
Eviction Must use noeviction so jobs are never dropped Not applicable
Backups New pipeline to build Reuse existing
Failover Separate replication and failover setup Reuse existing
Security New auth and TLS to configure Reuse existing
Monitoring New dashboards Add queue depth and bloat to existing monitoring

Postgres queue vs Sidekiq-style Redis workers: side-by-side comparison

Dimension Postgres job queue Sidekiq-style Redis workers
Enqueue consistency Atomic with business writes Separate write; needs an outbox for atomicity
Throughput / per-job overhead Row insert, update, delete, plus WAL and vacuum In-memory list operations, low overhead
Latency Polling interval or LISTEN/NOTIFY Blocking pop, near-immediate
Durability configuration Inherits database settings AOF/RDB persistence must be tuned
Crash behavior Lock released, job reappears In-flight job can be lost without reliable fetch
Feature ecosystem Smaller, library-dependent Mature UI, middleware, plugins
Load on primary database Direct and continuous None
Stateful services One Two
Migration cost Low to adopt Low to adopt, outbox adds work

The Postgres option saves engineering time in three places. First, you skip building an outbox. Reliable handoff to Redis requires an outbox table, a relay process, and deduplication on the consumer side. Each of those needs its own tests and monitoring. Second, you run one fewer stateful system, so there is no second backup policy, no second failover plan, and no extra capacity planning. Third, the tooling is shared. You inspect jobs with SQL, test them against the same database fixtures as the rest of the app, and debug them with the query tools you already use.

Do not assume you can switch backends later at low cost. Libraries like Graphile Worker and Sidekiq differ in retry backoff, uniqueness guarantees, scheduling semantics, and how arguments are serialized. Every enqueue call site also changes behavior. A call that was transactional becomes fire-and-forget, so any code that relied on rollback discarding the job needs to be rewritten. Expect to audit every enqueue call site and every job handler, not just change a config setting.

When should you avoid a Postgres job queue and move to Redis workers?

That migration cost is why the default for a monolith already running Postgres is to start with a Postgres job queue and stay there until you can name the limit you hit, for example "enqueue-to-start latency exceeds our SLO at peak" or "job-table vacuum is starving our OLTP tables." "Redis is faster" is not a reason to move. A specific, measured limit is.

Signal Favors Postgres Favors Redis workers
Job volume Low to moderate, bursty Sustained high rate
Job duration Seconds or longer Very short, where the per-job insert, update and delete cost dominates
Consistency needs Jobs must commit or roll back with business data Jobs tolerate at-least-once delivery after commit
Database health Queue traffic is a small share of primary load Queue load measurably slows user-facing queries
Team Small ops team, one datastore to run Team already operates Redis with persistence and failover

Before deciding, measure these:

  • Queue query cost. In pg_stat_statements, check what share of total execution time goes to the dequeue and completion statements.
  • Bloat and vacuum. Watch dead tuples and autovacuum frequency on the job table in pg_stat_user_tables. Churn that vacuum cannot keep up with slows the SKIP LOCKED scan.
  • WAL and replication lag. Every job generates several writes. If replicas fall behind during job bursts, the queue is costing you more than CPU.
  • Enqueue-to-start latency at peak, compared against what the jobs actually require.

If none of these numbers is a problem, keep the job queue in Postgres and spend the effort elsewhere.

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, 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 shared variable is one edit and one review instead of editing every service's .env by hand.

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.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • 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.