Database Backed Job Queue vs Redis: Choosing for Your Monolith

How a database backed job queue works with SKIP LOCKED

A database-backed job queue is an ordinary table in the database your application already uses. Enqueueing a job is an INSERT, and workers poll the table for work. A minimal Postgres schema needs id, queue, payload (jsonb), run_at, attempts, locked_at and status. The status values are pending, running, done and failed.

Concurrency is the hard part. A worker claims work inside a short transaction. It runs SELECT id ... WHERE queue = $1 AND status = 'pending' AND run_at <= now() ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED, then sets status = 'running', locked_at = now() and attempts = attempts + 1, then commits. SKIP LOCKED makes Postgres pass over rows another transaction has locked instead of waiting on them. Ten workers running the same query therefore get ten different rows, and none of them blocks.

The claim transaction commits before the job runs. Holding a row lock for the full job would keep a connection and a transaction open for minutes, and long-running transactions stop vacuum from cleaning up dead rows. Instead, locked_at acts as a lease:

• Success: set status = 'done', or delete the row.
• Failure: reset the row to pending, clear locked_at, and push run_at forward by a backoff interval. Once attempts exceeds a limit, mark the row failed.
• Crashed worker: a periodic sweep returns any running row whose locked_at is older than the lease timeout to pending.

The two architectures compare like this:

CopyDatabase-backed                 Redis-backed

 [web] ──┐                       [web] ──► [Postgres]
         ▼                         │
     [Postgres]                    └──► [Redis] ──► [worker]
         ▲                                            │
 [worker]┘                              [Postgres] ◄──┘

 one stateful system             two stateful systems

CopyCREATE TABLE jobs (
  id        bigserial PRIMARY KEY,
  queue     text        NOT NULL DEFAULT 'default',
  payload   jsonb       NOT NULL,
  run_at    timestamptz NOT NULL DEFAULT now(),
  attempts  int         NOT NULL DEFAULT 0,
  locked_at timestamptz,
  status    text        NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'running', 'done', 'failed'))
);
-- Partial index so the claim query only scans claimable rows.
CREATE INDEX jobs_claimable ON jobs (queue, run_at) WHERE status = 'pending';

-- Claim: one short statement and transaction. SKIP LOCKED passes over rows
-- another worker is claiming right now instead of waiting on them.
UPDATE jobs
SET status = 'running', locked_at = now(), attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE queue = $1 AND status = 'pending' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload, attempts;

-- Success: the job ran after the claim committed; record the outcome.
UPDATE jobs SET status = 'done', locked_at = NULL WHERE id = $1;

-- Failure: retry with exponential backoff, or give up after 5 attempts.
UPDATE jobs
SET status = CASE WHEN attempts >= 5 THEN 'failed' ELSE 'pending' END,
    locked_at = NULL,
    run_at = now() + interval '1 second' * power(2, attempts)
WHERE id = $1;

-- Sweep (run periodically): an expired lease means the worker likely crashed.
UPDATE jobs SET status = 'pending', locked_at = NULL
WHERE status = 'running' AND locked_at < now() - interval '15 minutes';

A minimal Postgres job table with a SKIP LOCKED claim query, success and backoff-on-failure updates, and a sweep that uses locked_at as a lease to reclaim jobs held by crashed workers.

Why transactional enqueueing is the main reason to use it

When the job lives in the same database as your business data, enqueueing becomes an ordinary INSERT. Put it inside the transaction that creates the order, and the job inherits that transaction's atomicity. If the transaction rolls back, the job row disappears with the order. If it commits, the job is durably there. No state exists in which one is true and the other isn't.

A Redis-backed queue can't offer this, because Postgres and Redis don't share a transaction. Every enqueue is a dual write, and the ordering determines which failure you get:

• Enqueue before commit. A worker can pick up the job while the transaction is still open, or after it rolls back. The handler looks up order_id = 4182 and finds nothing, or it emails a customer about an order that never existed.
• Enqueue after commit. The row is committed, but the enqueue can fail. Redis may be unreachable, the network call may time out, or the process may be killed. The order exists and its job never runs.

After-commit hooks, which many frameworks provide, solve the first problem and narrow the second. They still leave a window between COMMIT returning and the push to Redis completing. A deploy that sends SIGKILL, an OOM kill, or a crashed pod inside that window silently drops the job. That gap is why the transactional outbox pattern exists. You write an outbox row in the business transaction, and a separate relay moves it to the broker. A database-backed queue makes the outbox and the queue the same table.

The teams that benefit most are monoliths already running Postgres whose jobs read data written moments earlier, such as confirmation emails, webhook delivery, search indexing, and billing follow-ups.

Copy-- Minimal job table: the queue lives next to the business data.
CREATE TABLE jobs (
  id         bigserial PRIMARY KEY,
  kind       text        NOT NULL,
  payload    jsonb       NOT NULL,
  run_at     timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Enqueue inside the same transaction as the business write.
BEGIN;

WITH new_order AS (
  INSERT INTO orders (customer_id, total_cents)
  VALUES (77, 4999)
  RETURNING id
)
-- The job references the order id generated in the same transaction.
-- No worker can see this row until COMMIT makes both rows visible.
INSERT INTO jobs (kind, payload)
SELECT 'send_order_confirmation',
       jsonb_build_object('order_id', id)
FROM new_order;

-- If anything fails before this point, ROLLBACK removes the order
-- and the job together. There is no window where only one exists.
COMMIT;

A Postgres transaction that inserts an order and its confirmation job together, so both commit or both roll back.

How Redis-backed queues work and where they lose or repeat jobs

The simplest Redis queue is a list: producers LPUSH a serialized job, and workers block on BRPOP. The pop removes the job from Redis, so if the worker crashes mid-job, the job is gone. Reliable-fetch patterns fix this by atomically moving the job into a per-worker processing list with LMOVE (or the older RPOPLPUSH). The worker deletes the entry when it finishes. A reaper process returns entries from dead workers' lists to the main queue. Redis Streams build this in. XREADGROUP hands entries to a consumer, unacknowledged entries stay in the group's pending entries list until XACK, and XAUTOCLAIM lets another consumer take over entries that have been idle too long. Either way, a job that was half-finished when its worker died runs again.

Losing jobs is a separate risk, and it comes from Redis's own durability settings:

• RDB snapshots alone: a crash loses everything written since the last snapshot.
• AOF with appendfsync everysec: a crash can lose roughly the last second of writes.
• AOF with appendfsync always: closes that gap at a large cost in write latency.

Replication is asynchronous. When a primary fails, it may have acknowledged an enqueue that never reached the replica that gets promoted. WAIT reduces this window but does not remove it. Many deployments use Redis as configured for caching, with eviction policies that can silently drop keys. Teams then assume it is durable, and the gap shows up as missing jobs after an incident.

With either a database or Redis, the realistic guarantee is at-least-once delivery. A database queue's durability follows from Postgres's own write and replication settings. A Redis queue's durability depends on the persistence and replication settings described above.

At-least-once delivery: idempotent handlers and lease tuning

Whichever backend you choose, a worker can crash after committing its side effect but before acknowledging the job. The job then runs again. Retrying is the only safe recovery, so every handler has to tolerate running twice.

Idempotency keys and unique constraints

Give each job a stable idempotency key, such as the job ID or a business key like invoice:4821:receipt-email. Record that key in a processed_jobs table, or directly on the side-effect table, behind a unique constraint. The handler performs INSERT ... ON CONFLICT DO NOTHING in the same transaction as its writes. If no row was inserted, the work already happened, and the handler returns success without repeating it.

This works because the uniqueness check runs inside the database. A read-then-write check in application code can race when two workers handle the same job at once. For external calls such as payment APIs, pass the same key to the provider's idempotency mechanism if it has one.

Choosing lease length

A lock lease or visibility timeout trades duplicates against recovery time:

• Too short: a slow but healthy job loses its lease and a second worker starts it.
• Too long: a job held by a crashed worker sits invisible until the lease expires.

Set the lease to a small multiple of a typical job's runtime, not its worst case. Long jobs should heartbeat instead: they periodically update locked_until, and if a worker dies, its heartbeat stops and the lease expires on schedule.

Retries

Track attempts, max_attempts, and run_at columns on each job. On failure, increment attempts and push run_at forward with exponential backoff plus jitter. Once a job reaches max_attempts, move it to a dead state so it doesn't retry forever.

Copy-- PostgreSQL. Unique key makes the side effect safe to run twice.
CREATE TABLE processed_jobs (
  idempotency_key text PRIMARY KEY,
  job_id          bigint NOT NULL,
  processed_at    timestamptz NOT NULL DEFAULT now()
);

-- Handler body, run in ONE transaction with the job's other writes.
BEGIN;
WITH claimed AS (
  INSERT INTO processed_jobs (idempotency_key, job_id)
  VALUES ('invoice:4821:receipt-email', 9137)
  ON CONFLICT (idempotency_key) DO NOTHING
  RETURNING idempotency_key
)
-- Inserts nothing if the key already existed, so a rerun is a no-op.
INSERT INTO email_outbox (idempotency_key, recipient, template)
SELECT idempotency_key, 'billing@example.com', 'receipt' FROM claimed;
UPDATE jobs SET status = 'done', locked_by = NULL, locked_until = NULL
WHERE id = 9137 AND locked_by = 'worker-3';
COMMIT;

-- Heartbeat for long jobs: extend the lease only if this worker still holds it.
-- Zero rows updated means the lease was lost, so the worker should stop.
UPDATE jobs SET locked_until = now() + interval '2 minutes'
WHERE id = 9137 AND locked_by = 'worker-3';

-- On failure: count the attempt, back off exponentially with jitter, cap at 1 hour,
-- and mark the job dead once max_attempts is reached. SET sees pre-update values.
UPDATE jobs SET
  attempts     = attempts + 1,
  run_at       = now() + least(interval '1 hour',
                   interval '10 seconds' * power(2, attempts)) * (0.5 + random() / 2),
  status       = CASE WHEN attempts + 1 >= max_attempts THEN 'dead' ELSE 'pending' END,
  locked_by    = NULL,
  locked_until = NULL
WHERE id = 9137 AND locked_by = 'worker-3';

A PostgreSQL sketch of an idempotent handler using INSERT ... ON CONFLICT DO NOTHING in the same transaction as its side effect, a lease-extending heartbeat, and a failure path that tracks attempts, applies jittered exponential backoff, and dead-letters exhausted jobs.

Sidekiq vs good_job and BullMQ alternatives: an architectural comparison

…stops consuming worker time and stays visible for manual inspection. Both Ruby and Node have libraries that follow each model, and they differ less in features than in where state lives and what breaks when a component fails.

The table shows a difference in failure coupling. A Postgres queue fails together with the data it acts on. A Redis queue adds a second system that can fail separately from your database.

The established Redis libraries have an ecosystem advantage. They often ship rate limiting, batches, unique jobs, and mature dashboards, sometimes only in paid tiers. Postgres-backed libraries vary widely here. Check each feature you rely on before you choose a library. Don't assume parity.

Running a Postgres queue in production: bloat, polling and connections

A queue table churns constantly: every job is inserted, updated, and eventually deleted. That makes dead tuples, not row count, the main operational cost. Index only claimable rows with a partial index, for example on (priority, run_at) WHERE status = 'pending', so the claim query stays small as history grows. Move finished rows out on a schedule, either by deleting in batches or by moving them to an archive table, so they don't accumulate in the hot table.

Watch n_dead_tup, last_autovacuum, and autovacuum_count for the queue table in pg_stat_user_tables. Check pg_stat_activity for old xact_start values. A transaction left open for hours, such as an idle-in-transaction session or a long report, stops vacuum from removing any tuple that died after it began. Nothing fails. Claim queries just slow down over days as they scan more dead rows.

Use LISTEN/NOTIFY to wake idle workers instead of polling every few milliseconds. Keep a slow fallback poll anyway, because notifications sent while a listener is disconnected are lost and payloads are small.

Every worker thread that holds a connection takes one away from web requests. Budget the total explicitly: web pool plus worker concurrency plus headroom must fit under max_connections. When it doesn't, add a pooler. Transaction-mode pooling breaks LISTEN and session-level advisory locks, so give listeners a dedicated direct connection.

Finally, the queue now fails whenever your primary does. A failover or a heavy migration pauses background work too.

Copy-- 1. Index only claimable rows; finished history never enters this index.
CREATE INDEX CONCURRENTLY jobs_claimable_idx
  ON jobs (priority, run_at)
  WHERE status = 'pending';

-- 2. Scheduled archival: move one bounded batch of finished rows per run.
--    Small batches keep each transaction short and let vacuum keep up.
WITH moved AS (
  DELETE FROM jobs
  WHERE id IN (
    SELECT id FROM jobs
    WHERE status IN ('done', 'failed')
      AND finished_at < now() - interval '7 days'
    LIMIT 5000
  )
  RETURNING *
)
INSERT INTO jobs_archive SELECT * FROM moved;

-- 3. Is autovacuum keeping up with churn on the queue table?
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'jobs';

-- 4. Oldest open transactions: these stop vacuum from removing
--    any tuple that died after they started.
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

A partial index on pending jobs, a batched archival query for finished rows, and two monitoring queries that show dead-tuple buildup on the queue table and the long-running transactions that keep vacuum from cleaning it up.

When to use each, when to avoid a database queue, and how to migrate

That coupling is the first thing to weigh. Base the decision on signals you can measure, not on a fixed jobs-per-second number.

Illustrative estimate. Assume these inputs: 50 jobs/s, about 4 queue statements and 10 application queries per job, and a primary serving 3,000 queries/s at 40% CPU. That works out to 700 extra queries/s. If the job queries cost roughly the same as your existing ones, CPU rises to about 49%. That fits. The same arithmetic against a primary already at 75% does not.

Avoid a database queue in three cases: high-volume, low-value events such as clickstream data, latency targets tighter than polling allows, or a primary already near capacity. Otherwise the savings are real. You run one fewer stateful system to back up, monitor and fail over, and you don't need an outbox. In exchange you accept a lower throughput ceiling and ongoing vacuum tuning.

To migrate in either direction, route every enqueue call through a job interface that hides the backend. Run both queues side by side, send new jobs to the target, and let workers drain the old queue until it is empty. Usually you only need to move the high-volume classes, such as analytics events, to Redis. Jobs that must commit together with business data stay in the database.

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 database backed 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.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• 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.