Retry Failed Jobs in Postgres Without a Queue Server

For: A backend engineer at a seed-stage SaaS startup who owns a monolith backed by Postgres and keeps finding failed background jobs — email sends, PDF exports, webhook dispatches — silently dropped in a cron table because nobody wired up retry logic, and the team cannot justify adding Redis and a queue server to the stack before the next funding milestone

You don't need Redis, RabbitMQ, or a separate queue server to reliably retry failed background jobs. Postgres 9.5+ ships with SELECT ... FOR UPDATE SKIP LOCKED, which is enough to turn an ordinary table into a safe, concurrent job queue with atomic claim semantics, exponential backoff, and a dead-letter state. This tutorial shows you exactly how to build it — schema, worker loop, and the failure-mode edge cases nobody warns you about.

Target reader: you own a Postgres-backed monolith, you have a hand-rolled jobs table that a cron job scans every minute, and jobs that fail (SMTP hiccup, third-party 502) vanish silently because no one wrote retry logic. Adding Redis + a worker fleet is not on the roadmap. Good — you don't need it yet.

Why SKIP LOCKED changes the math

The classic problem with a Postgres-as-queue setup is the race: two workers both run SELECT * FROM jobs WHERE status = 'pending' LIMIT 1, both get the same row, both process it. People solve this with advisory locks, table locks, or UPDATE ... RETURNING tricks — all of which either serialize workers or are subtly wrong under load.

FOR UPDATE SKIP LOCKED makes Postgres skip any row another transaction already has locked, and returns the next available one. Two workers running the same query at the same instant get different rows. No table-level lock, no advisory-lock bookkeeping, no serialization. This is exactly what a queue's dequeue operation needs to be.

The tradeoffs, honestly: this pattern is bad at very high throughput (past roughly a few thousand jobs/second you'll want a real broker), it does not give you fan-out or pub/sub semantics, and long-running transactions on the same database will bloat your table. For a seed-stage SaaS moving hundreds to low tens of thousands of jobs a day, none of that matters.

Prerequisites

• Postgres 9.5 or newer (check with SELECT version();)
• A language with a decent Postgres driver — examples below are Python + psycopg2, but the SQL is what matters
• Ability to run one or more long-lived worker processes (systemd, Docker, PM2, whatever)
• Ten minutes

Step 1: Design the jobs table

The schema needs to encode job identity, payload, state machine, retry count, and the next time the job is eligible to run. That last field is what makes exponential backoff possible.

CREATE TYPE job_status AS ENUM ('pending', 'running', 'succeeded', 'failed', 'dead');

CREATE TABLE jobs (
  id           BIGSERIAL PRIMARY KEY,
  kind         TEXT NOT NULL,
  payload      JSONB NOT NULL DEFAULT '{}'::jsonb,
  status       job_status NOT NULL DEFAULT 'pending',
  attempts     INT NOT NULL DEFAULT 0,
  max_attempts INT NOT NULL DEFAULT 5,
  run_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  locked_at    TIMESTAMPTZ,
  locked_by    TEXT,
  last_error   TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX jobs_dequeue_idx
  ON jobs (run_at)
  WHERE status = 'pending';

Two things worth calling out. The partial index on status = 'pending' keeps the dequeue query fast even when the table has millions of historical rows. And run_at is the field the worker filters on — future-dated rows are simply invisible to dequeue until their time comes, which is how we get backoff for free.

Expected output after running the migration:

CREATE TYPE
CREATE TABLE
CREATE INDEX

Step 2: The atomic claim query

This is the whole trick. One CTE that picks a candidate row with SKIP LOCKED, one UPDATE that marks it as running and bumps the attempt counter, returning the claimed row.

WITH claimed AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= NOW()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs j
SET status = 'running',
    attempts = j.attempts + 1,
    locked_at = NOW(),
    locked_by = %s,
    updated_at = NOW()
FROM claimed
WHERE j.id = claimed.id
RETURNING j.id, j.kind, j.payload, j.attempts, j.max_attempts;

Run this from two psql sessions at the same time with two pending rows in the table. Each session gets a different row. Run it with only one pending row and two sessions — one gets the row, the other gets zero rows. That's the guarantee.

Step 3: The worker loop

The worker is intentionally boring. Claim, run the handler, mark success or schedule a retry. Nothing exotic.

import os, time, socket, json, traceback
import psycopg2
import psycopg2.extras

WORKER_ID = f"{socket.gethostname()}:{os.getpid()}"
BACKOFF_BASE = 10  # seconds

CLAIM_SQL = """
WITH claimed AS (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= NOW()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs j
SET status='running', attempts=j.attempts+1,
    locked_at=NOW(), locked_by=%s, updated_at=NOW()
FROM claimed WHERE j.id=claimed.id
RETURNING j.id, j.kind, j.payload, j.attempts, j.max_attempts;
"""

HANDLERS = {}
def handler(kind):
    def wrap(fn): HANDLERS[kind] = fn; return fn
    return wrap

@handler('send_email')
def send_email(payload):
    # your SMTP call here
    ...

def backoff_seconds(attempts):
    # 10s, 40s, 90s, 160s, 250s — quadratic-ish, jittered
    import random
    return BACKOFF_BASE * (attempts ** 2) + random.randint(0, 5)

def run_once(conn):
    with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
        cur.execute(CLAIM_SQL, (WORKER_ID,))
        row = cur.fetchone()
        if not row:
            conn.commit()
            return False
        conn.commit()  # release the row lock; job is now 'running'

    job_id = row['id']
    try:
        HANDLERS[row['kind']](row['payload'])
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE jobs SET status='succeeded', updated_at=NOW() WHERE id=%s",
                (job_id,))
        conn.commit()
    except Exception as e:
        err = traceback.format_exc()[-2000:]
        with conn.cursor() as cur:
            if row['attempts'] >= row['max_attempts']:
                cur.execute("""UPDATE jobs SET status='dead', last_error=%s,
                               updated_at=NOW() WHERE id=%s""", (err, job_id))
            else:
                delay = backoff_seconds(row['attempts'])
                cur.execute("""UPDATE jobs SET status='pending', last_error=%s,
                               run_at=NOW() + (%s || ' seconds')::interval,
                               updated_at=NOW() WHERE id=%s""",
                            (err, str(delay), job_id))
        conn.commit()
    return True

def main():
    conn = psycopg2.connect(os.environ['DATABASE_URL'])
    while True:
        did_work = run_once(conn)
        if not did_work:
            time.sleep(1)

if __name__ == '__main__':
    main()

Notice what's happening on failure: the row goes back to pending, but run_at is pushed into the future. The dequeue query filters on run_at <= NOW(), so the job is invisible until the backoff expires. No separate scheduler process, no cron job for retries.

Step 4: Enqueue a job and watch it retry

Insert a job whose handler is guaranteed to fail on the first two attempts:

INSERT INTO jobs (kind, payload, max_attempts)
VALUES ('send_email', '{"to":"user@example.com"}'::jsonb, 5);

Start the worker. Tail the table:

SELECT id, status, attempts, run_at, last_error FROM jobs WHERE id = 1;

Expected sequence, roughly:

 id | status    | attempts | run_at                        | last_error
----+-----------+----------+-------------------------------+-----------
  1 | pending   |        1 | 2024-... (now + 15s)          | SMTPError...
  1 | pending   |        2 | 2024-... (now + 45s)          | SMTPError...
  1 | succeeded |        3 | 2024-...                      | SMTPError...

The last_error stays populated even after success, which is deliberate — it's your only forensic trail when a customer says "my export was slow."

Step 5: Handle stuck jobs (the crash-mid-run problem)

What happens if a worker claims a job, sets it to running, and then the process is killed? The row is stuck in running forever. Nothing will ever pick it up.

The fix is a reaper query that runs on a schedule — from cron, from a separate goroutine, or as the first thing each worker does on startup. Any job that has been running for longer than your maximum job duration gets kicked back to pending.

UPDATE jobs
SET status = 'pending',
    last_error = COALESCE(last_error, '') || E'\n[reaper] reclaimed stuck job',
    updated_at = NOW()
WHERE status = 'running'
  AND locked_at < NOW() - INTERVAL '10 minutes';

Pick the interval based on your slowest legitimate job. If your PDF export can take eight minutes, set it to fifteen. If nothing should ever run longer than thirty seconds, set it to two minutes and get alerted sooner.

Step 6: Dead-letter visibility

Jobs in dead state are the ones that exhausted retries. They need to be visible — not in a log file, in a dashboard the on-call engineer actually looks at. A single query is enough:

SELECT kind, COUNT(*) AS dead_count,
       MAX(updated_at) AS most_recent
FROM jobs
WHERE status = 'dead'
  AND updated_at > NOW() - INTERVAL '24 hours'
GROUP BY kind
ORDER BY dead_count DESC;

Wire this into whatever you already use — a Grafana panel against Postgres, a daily Slack digest, a small internal admin page. The point is that dead means "a human needs to look at this," and if no human ever does, you're back where you started.

For requeuing after a fix (say, you patched a bug in the handler):

UPDATE jobs
SET status = 'pending', attempts = 0, run_at = NOW(),
    last_error = NULL, updated_at = NOW()
WHERE status = 'dead' AND kind = 'send_email';

Step 7: Run multiple workers safely

Start two, five, twenty workers. The SKIP LOCKED claim query guarantees no two workers ever process the same job, and there's no configuration to coordinate — each worker just polls independently. This is the property that most hand-rolled queue tables get wrong.

One thing to watch: if you're running dozens of workers all polling every second and the table is mostly empty, you're generating unnecessary load. Two mitigations. First, back off polling when a claim returns nothing — sleep 1s, then 2s, then 5s, capped. Second, use LISTEN/NOTIFY: have the enqueue call pg_notify('jobs', ''), and have workers LISTEN on that channel so they wake up on new work instead of polling. That's a 20-line change and drops idle DB load to near zero.

Common errors and how to fix them

"duplicate key value violates unique constraint" on retry

Your handler is doing an INSERT without an idempotency key. Every retry re-runs the whole handler. Use INSERT ... ON CONFLICT DO NOTHING, or better, generate an idempotency key at enqueue time and store it in the payload so downstream APIs can dedupe.

Jobs stuck in 'running' forever

You didn't wire up the reaper from Step 5, or its interval is longer than your worker's actual max runtime. Also check: is your worker committing after claim? If the claim transaction is still open when the handler runs, the row lock is held for the entire handler duration and nothing else can process anything else on that worker's connection.

Workers processing the same job twice

Almost always because someone "optimized" the claim query and dropped either FOR UPDATE or SKIP LOCKED. Both are required. A plain SELECT followed by an UPDATE in the same transaction is not equivalent — the initial SELECT takes no lock, so two workers see the same row.

Table bloat after a few weeks

Succeeded jobs pile up. Either add a nightly DELETE FROM jobs WHERE status = 'succeeded' AND updated_at < NOW() - INTERVAL '7 days', or move completed rows to a jobs_archive table. Also make sure autovacuum is actually running on this table — the churn rate is high and default autovacuum settings are often too conservative.

"canceling statement due to lock timeout"

Something else is holding a long transaction on the jobs table — usually a migration or a report query. The claim query waits behind it. Set a short lock_timeout on the worker's session (SET lock_timeout = '2s') so it fails fast and retries rather than blocking indefinitely.

When to graduate to a real queue

This pattern holds up well into the low tens of thousands of jobs per day per worker. Signs you've outgrown it: dequeue latency creeping up (add LISTEN/NOTIFY first, then reconsider), needing fan-out to multiple consumers, needing priority queues with strict SLAs across tiers, or your Postgres primary is CPU-bound and half the load is the jobs table. At that point, look at a broker — but don't preempt the decision. Most seed-stage SaaS never hit that ceiling, and the operational cost of Redis + a queue worker fleet is real.

Teams we've worked with at CodeNicely — including accounting SaaS platforms processing invoice generation and logistics marketplaces handling dispatch webhooks — ran on exactly this pattern for years before ever adding a dedicated broker. Boring, cheap, correct.

Frequently Asked Questions

Is Postgres actually reliable enough to use as a job queue in production?

Yes, for most SaaS workloads. Companies process millions of jobs a day on Postgres-backed queues (Sidekiq's own docs acknowledge this pattern, and libraries like Oban for Elixir and Que for Ruby are built on it). The ceiling is throughput and fan-out semantics, not reliability.

Do I need to use advisory locks instead of SKIP LOCKED?

No. Advisory locks were the pre-9.5 workaround and required careful bookkeeping to release. SELECT ... FOR UPDATE SKIP LOCKED is simpler, gives you the same concurrency guarantee, and doesn't require you to track which lock IDs are in use.

How do I schedule delayed or recurring jobs with this pattern?

Delayed jobs: set run_at to the future timestamp at insert time. The dequeue query already filters on run_at <= NOW(), so it just works. Recurring jobs: on successful completion of a recurring job, insert the next occurrence with the appropriate run_at. Or use pg_cron as the trigger to insert new job rows on a schedule.

What's the difference between this and using pg_cron?

pg_cron runs SQL on a schedule inside Postgres. It's a scheduler, not a queue — no retries, no worker concurrency, no dead-lettering. The two compose well: use pg_cron to enqueue rows into your jobs table, and let this worker pattern handle execution and retries.

Can I use this pattern with an ORM like Django or Rails ActiveRecord?

Yes, but you'll want to drop to raw SQL for the claim query specifically. ORMs generally don't emit FOR UPDATE SKIP LOCKED correctly through their query builders, and the claim CTE is the one place correctness matters. The rest — inserting jobs, updating status — is fine through the ORM.