Database-Backed Job Queues

A job queue stored in the application's own relational database removes an entire piece of infrastructure and fixes the dual-write problem at its root, and this guide covers the approach as part of Backend Frameworks & Worker Scaling. Rails ships Solid Queue as its default backend, Elixir has Oban, Go has River, Python has Procrastinate, and plain SELECT ... FOR UPDATE SKIP LOCKED makes a hand-rolled queue on Postgres a few dozen lines of SQL. The trade-off is that the database now absorbs a write-heavy, churn-heavy workload it was not tuned for, and the ceiling arrives sooner than with a dedicated broker.

The decision is rarely "Postgres or Redis" in the abstract. It is whether your job volume, latency needs, and operational appetite fit inside the database you already run — and, if they do, how to keep the queue table from degrading the rest of that database.

The Scenario: Two Systems That Disagree

A SaaS application creates an invoice in Postgres and enqueues a "send invoice email" job to Redis for Sidekiq. Once or twice a week, support finds an invoice that was never emailed. The cause is the classic dual write: the Postgres transaction committed, then the process crashed before perform_async reached Redis. The reverse also happens — the job is enqueued, the transaction rolls back, and a worker emails an invoice that does not exist, then fails with RecordNotFound.

The team considered the transactional outbox pattern, which fixes this by writing an outbox row in the same transaction and relaying it to Redis. Then someone asked the obvious follow-up: if the outbox row is already a durable, transactional record of the job, why relay it at all? A worker could claim the row directly. That is a database-backed queue.

Architectural Overview: How a Table Becomes a Queue

A database queue is a table of jobs plus a claim query that lets many workers take different rows without blocking each other. Before Postgres 9.5 this was hard: workers either serialized on a table lock or used advisory locks with subtle bugs. FOR UPDATE SKIP LOCKED changed that. A worker selects the oldest available rows, locks them, and any row already locked by another worker is simply skipped rather than waited on.

-- The minimal schema
CREATE TABLE jobs (
  id           bigserial PRIMARY KEY,
  queue        text        NOT NULL DEFAULT 'default',
  kind         text        NOT NULL,
  args         jsonb       NOT NULL,
  priority     smallint    NOT NULL DEFAULT 0,
  run_at       timestamptz NOT NULL DEFAULT now(),
  attempts     int         NOT NULL DEFAULT 0,
  max_attempts int         NOT NULL DEFAULT 20,
  state        text        NOT NULL DEFAULT 'available',  -- available|running|completed|discarded
  locked_by    text,
  locked_at    timestamptz,
  last_error   text,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- The index the claim query needs: only rows that are candidates
CREATE INDEX jobs_claim_idx ON jobs (queue, priority DESC, run_at, id)
  WHERE state = 'available';

-- The claim: take up to 10 due jobs, skipping rows other workers hold
WITH next AS (
  SELECT id FROM jobs
  WHERE state = 'available' AND queue = $1 AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET state = 'running', locked_by = $2, locked_at = now(), attempts = j.attempts + 1
FROM next WHERE j.id = next.id
RETURNING j.*;

The partial index is the single most important performance detail. Completed and discarded rows never appear in it, so the claim query stays fast even when the table holds millions of finished jobs — as long as those rows are eventually deleted, which is where vacuum enters the picture below. The full, production-grade version of this is built step by step in building a Postgres job queue with SKIP LOCKED.

Implementation 1: Transactional Enqueue with a Library

In most stacks you should not hand-roll the queue; mature libraries implement claiming, retries, scheduling, uniqueness, and cleanup. The pay-off of all of them is the same: enqueue inside the business transaction.

# Rails 8 with Solid Queue: the job row commits with the invoice
class InvoicesController < ApplicationController
  def create
    Invoice.transaction do
      @invoice = Invoice.create!(invoice_params)
      SendInvoiceEmailJob.perform_later(@invoice)   # INSERT into solid_queue_jobs, same txn
    end
    redirect_to @invoice
  end
end

// Go with River: InsertTx uses the caller's transaction
tx, err := dbPool.Begin(ctx)
if err != nil { return err }
defer tx.Rollback(ctx)

invoiceID, err := insertInvoice(ctx, tx, req)
if err != nil { return err }
if _, err := riverClient.InsertTx(ctx, tx, SendInvoiceArgs{InvoiceID: invoiceID}, nil); err != nil {
    return err
}
return tx.Commit(ctx)       // invoice and job become visible together

Solid Queue needs one caveat: it defaults to a separate database connection (connects_to a queue database) in many setups, and a job enqueued on a different connection is not part of the invoice's transaction. To get the transactional guarantee, put the queue tables in the same database and connection, or use Rails' after_commit enqueueing and accept the small window it leaves. The production setup is detailed in running Rails Solid Queue in production; the Go side is in River: a Postgres job queue for Go.

Implementation 2: Low-Latency Wake-Ups and Message-Queue Semantics

Polling is the default claim strategy: each worker runs the claim query every second or so. It is simple and robust, but it adds up to a second of latency and generates constant query load even when the queue is empty. Postgres LISTEN/NOTIFY removes both: the enqueue transaction sends a notification on commit, and idle workers wake immediately.

-- Trigger that notifies on insert; delivered only if the transaction commits
CREATE OR REPLACE FUNCTION notify_job_insert() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('jobs_' || NEW.queue, '');   -- payload unused: workers re-query
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER jobs_notify AFTER INSERT ON jobs
FOR EACH ROW WHEN (NEW.run_at <= now()) EXECUTE FUNCTION notify_job_insert();

Notifications are a latency optimization, never the source of truth — a worker that was disconnected misses them, so it must still poll on a slow interval (every 5–30 seconds) as a safety net. Postgres LISTEN/NOTIFY for job wake-ups covers connection pooling pitfalls (PgBouncer in transaction mode breaks LISTEN) and notification storms.

For teams that want message-queue semantics — visibility timeouts, read counts, archive tables — rather than a job framework, the pgmq extension implements an SQS-like API as SQL functions inside Postgres; using pgmq for Postgres message queues walks through it.

Retries, Scheduling, and Uniqueness Are Just Columns

One quiet advantage of a table-backed queue is that features which need special machinery on a broker are ordinary SQL here. Three examples show the pattern.

Retries with backoff are an update of run_at. When a job fails, the worker returns it to available with a future run_at computed from the attempt number; the claim query's run_at <= now() condition does the rest. There is no separate retry set, no scheduler process to move jobs between structures, and the retry schedule for any job is visible with a SELECT.

-- Fail with exponential backoff and jitter; discard after max_attempts
UPDATE jobs
SET state      = CASE WHEN attempts >= max_attempts THEN 'discarded' ELSE 'available' END,
    run_at     = now() + (LEAST(power(2, attempts), 3600) * (0.5 + random()/2)) * interval '1 second',
    last_error = $2,
    locked_by  = NULL,
    locked_at  = NULL
WHERE id = $1;

Scheduled jobs are inserts with a future run_at. A job due in three days is a row that the partial index ignores for three days. Compare this with the sorted-set bookkeeping in implementing delayed jobs with Redis sorted sets: the database version needs no poller moving jobs from a delayed structure into a ready one.

Uniqueness is a unique index. "At most one pending sync job per account" becomes a partial unique index on the arguments for non-finished rows, and an INSERT ... ON CONFLICT DO NOTHING turns duplicate enqueues into no-ops atomically — no lock keys with TTLs, no race between check and insert.

CREATE UNIQUE INDEX jobs_unique_sync ON jobs ((args->>'account_id'))
  WHERE kind = 'sync_account' AND state IN ('available', 'running');

INSERT INTO jobs (kind, args) VALUES ('sync_account', '{"account_id": "a-42"}')
ON CONFLICT DO NOTHING;      -- second enqueue while one is pending: silently skipped

The flip side is that every one of these features adds write load to the same table, and a unique index is one more index updated on every insert. Add them because you need them, not because they are easy.

Observability Straight from SQL

Broker-based queues need exporters to expose depth and latency. A database queue can answer every operational question with a query, which makes both dashboards and incident debugging faster. The core health view is one statement:

-- Depth, oldest waiting job, and in-flight count per queue
SELECT queue,
       count(*) FILTER (WHERE state = 'available' AND run_at <= now())       AS ready,
       count(*) FILTER (WHERE state = 'available' AND run_at >  now())       AS scheduled,
       count(*) FILTER (WHERE state = 'running')                             AS running,
       count(*) FILTER (WHERE state = 'discarded')                           AS dead,
       extract(epoch FROM now() - min(run_at) FILTER (
         WHERE state = 'available' AND run_at <= now()))                     AS oldest_ready_s
FROM jobs
GROUP BY queue;

Expose it through postgres_exporter's custom-query support and alert on oldest_ready_s, which is the queue-time signal an SLO should be built on — see defining SLOs for job latency. Run the query against a replica if the jobs table is large, or maintain the counts in a small summary table updated by triggers when the count(*) becomes expensive. During an incident, the same table answers questions no broker dashboard can: which accounts' jobs are failing, what the last error was for each, and whether failures started at a deploy boundary.

Trade-off Analysis: Database Queue vs Dedicated Broker

The throughput row is the honest limit. Each job is at least an insert, an update to claim, and an update or delete to finish — three writes and three index updates on the primary, plus the dead tuples each leaves behind. A well-tuned Postgres handles a few thousand of those per second alongside normal traffic; a Redis list handles an order of magnitude more with no impact on the application database. Below roughly 500 jobs per second the database queue is usually the simpler system overall. Above a few thousand, it competes with your application for the most expensive resource you run.

Failure Modes & Recovery

Table and index bloat. Every claim and completion leaves a dead tuple. If autovacuum cannot keep up — or is blocked by a long-running transaction elsewhere in the database — the jobs table and its indexes grow, and the claim query slows from milliseconds to hundreds of milliseconds. Remediation: tune autovacuum per table (autovacuum_vacuum_scale_factor = 0.01, a low autovacuum_vacuum_cost_delay), delete or partition finished jobs aggressively, and alert on n_dead_tup for the jobs table.

Long transactions pin the horizon. A reporting query or an idle-in-transaction session that runs for an hour prevents vacuum from removing any tuple that died after it started, across the whole database. Queue tables are the first to suffer because they churn fastest. Remediation: idle_in_transaction_session_timeout, a statement timeout for analytics users, and running long reports on a replica.

Stuck running jobs. A worker that crashes after claiming leaves rows in running forever. Remediation: a rescuer that returns rows whose locked_at is older than the job's timeout to available — the database equivalent of a visibility timeout.

Connection exhaustion. Each worker process holds connections for claiming, heartbeating, and listening. Fifty worker pods with ten threads each can exceed max_connections on their own. Remediation: size worker concurrency against the connection budget, use a pooler for claim and job queries, and keep LISTEN on a dedicated direct connection per process.

Performance Tuning

• Claim in batches. Claiming ten rows per query instead of one cuts claim overhead roughly tenfold; hand them to local threads.
• Delete, don't update, on completion — or partition by day and drop old partitions. An UPDATE state='completed' keeps the row and its bloat; a partition drop reclaims space instantly with no vacuum work.
• Keep payloads small. Large jsonb arguments are TOASTed and rewritten on every update of the row. Store references, not documents.
• Separate hot columns. Frequently updated fields (heartbeat, attempts) cause a new row version each time; heartbeating a long job every second multiplies bloat. Heartbeat every 15–30 seconds, or move heartbeats to a separate narrow table.
• Use fillfactor. Setting fillfactor = 70 on the jobs table leaves room for HOT (heap-only tuple) updates, which avoid index writes when the updated columns are not indexed.

ALTER TABLE jobs SET (
  fillfactor = 70,
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum after 1% of rows are dead, not 20%
  autovacuum_vacuum_cost_delay = 1,          -- let vacuum work faster on this table
  autovacuum_analyze_scale_factor = 0.02
);

# Queue health from postgres_exporter and a custom query
pg_stat_user_tables_n_dead_tup{relname="jobs"} > 500000
max(pg_stat_activity_max_tx_duration{state="idle in transaction"}) > 300

FAQ

Can a database queue handle millions of jobs per day? Yes — a million jobs per day is about 12 per second on average, comfortably within reach. The concern is peak rate and the effect on the primary, not daily totals. A few thousand per second sustained is where careful tuning becomes mandatory.

Should the queue live in the same database as the application? For transactional enqueue, it must share the transaction, so yes. If you do not need that guarantee and the queue is busy, a separate database isolates its vacuum and I/O load from the application.

Will a database queue slow down my application queries? It can, in three ways: extra write I/O and WAL volume on the primary, autovacuum work competing for I/O, and connections held by workers. On a primary with headroom and a queue of a few hundred jobs per second, the effect is usually within noise. Watch replication lag and checkpoint frequency after enabling it — both rise with WAL volume — and move the queue to its own database if either becomes a problem.

How do I handle a job that runs longer than the rescue timeout? Heartbeat: the worker updates locked_at every 15–30 seconds while it runs, and the rescuer only reclaims rows whose heartbeat is stale. Set per-kind timeouts so a report that legitimately runs for twenty minutes is not rescued at five.

Does this work on MySQL? MySQL 8.0 supports FOR UPDATE SKIP LOCKED with the same semantics, and libraries such as Solid Queue support it. There is no LISTEN/NOTIFY equivalent, so workers poll.

When should I move off a database queue? When queue writes are a meaningful share of primary load, when vacuum cannot keep up despite tuning, or when you need sub-millisecond latency at high rates. Migrating from Redis to a Postgres job queue covers the move in the other direction, and the same dual-run technique works both ways.

Related

• Building a Postgres Job Queue with SKIP LOCKED — the full claim, retry, and rescue implementation.
• Postgres LISTEN/NOTIFY for Job Wake-Ups — low-latency dispatch without constant polling.
• Running Rails Solid Queue in Production — the Rails default backend, configured for real traffic.
• Using pgmq for Postgres Message Queues — SQS-style semantics inside Postgres.
• In-Memory vs Persistent Queue Storage — the durability spectrum a database queue sits at one end of.