
Your app needs to do things later: send a receipt email, resize an upload, call a flaky partner API, retry a webhook. The reflex is to add Redis (with Celery, Sidekiq, or BullMQ on top) or, if you're feeling ambitious, Kafka.
A week later you are running another stateful service, writing a runbook for it, and chasing a bug that only appears when your app crashes between two lines of code.
This post shows how to build a durable job queue with retries, delays, and dead-letter handling on the database you already run. The core is a one-table schema, one claim query, and a worker loop under 80 lines of Python, and it removes a class of bugs that a separate broker makes easy to introduce.
The bug a separate broker gives you
Look at what happens when an order is placed and you want to email a receipt:
sequenceDiagram
participant App
participant DB as Postgres
participant Q as Redis
App->>DB: COMMIT order 4211
Note over App,Q: crash, deploy, or network blip here
App->>Q: enqueue send_receiptThere are two systems and no shared transaction. If the app dies in the gap, the order exists and the receipt job doesn't. Flip the order of the two writes and you get the opposite bug: a job for an order that was rolled back.
This is the dual-write problem, and the usual fix is the transactional outbox pattern: write the job into a table in the same transaction as the business data, and let a worker process that table. If the jobs table is the queue, you don't need a relay and you don't need a second system.
WITH new_order AS (
INSERT INTO orders (customer_id, total) VALUES (42, 1999) RETURNING id
)
INSERT INTO jobs (kind, payload)
SELECT 'send_receipt', jsonb_build_object('order_id', id) FROM new_order;Both rows exist, or neither does. No cleanup code, no reconciliation script. (In application code you would normally run the two inserts inside one BEGIN ... COMMIT block; the CTE just shows the idea as a single runnable statement.)
Is a database queue fast enough?
For most apps, yes. A Postgres table claimed with FOR UPDATE SKIP LOCKED is commonly run at hundreds to thousands of jobs per second on ordinary hardware, and SQLite in WAL mode can handle a similar order of magnitude on a single machine. Those are rough figures, not benchmarks, so measure on your own setup. If you process a few thousand emails a day, a queue table is far more than you need.
We'll cover where this stops being the right tool near the end.
The design
The whole queue is one table and four ideas:
A job is a row. It has a
kind, a JSONpayload, and anattemptscounter.run_atsays when it may run. Delayed jobs and retries are just a futurerun_at.Claiming a job is a lease. A worker claims a job by pushing
run_atforward by a lease duration (the same idea as SQS's visibility timeout). If the worker crashes, the lease expires and the job reappears on its own.Finishing a job is an update. Success sets
done, exhausted retries setdead.
stateDiagram-v2
[*] --> queued: enqueue
queued --> leased: worker claims it
leased --> done: handler succeeds
leased --> queued: handler fails, retry after backoff
leased --> queued: worker crashes, lease expires
leased --> dead: attempts exhausted
done --> [*]
dead --> [*]leased is not a real status value. It is simply a queued row whose run_at is in the future. That keeps the claim query trivial and means crashed workers need no special recovery code.
Postgres implementation
Schema
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL,
payload JSONB NOT NULL DEFAULT '{}',
status TEXT NOT NULL DEFAULT 'queued'
CHECK (status IN ('queued', 'done', 'dead')),
attempts INT NOT NULL DEFAULT 0,
max_attempts INT NOT NULL DEFAULT 5,
run_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_error TEXT,
dedup_key TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ
);
-- Workers only ever look at queued rows, ordered by run_at.
CREATE INDEX jobs_ready_idx ON jobs (run_at) WHERE status = 'queued';
-- Optional: refuse duplicate pending jobs for the same key.
CREATE UNIQUE INDEX jobs_dedup_idx ON jobs (dedup_key)
WHERE dedup_key IS NOT NULL AND status = 'queued';The partial index stays small no matter how many finished jobs pile up, which is what keeps claiming fast.
Enqueue
Plain INSERT, inside whatever transaction already holds your business data. To make a job idempotent at enqueue time, give it a dedup_key:
INSERT INTO jobs (kind, payload, dedup_key)
VALUES ('sync_customer', '{"customer_id": 42}', 'sync_customer:42')
ON CONFLICT DO NOTHING;Want a delayed job? Set run_at = now() + interval '10 minutes'.
Claim
This is the heart of the queue:
WITH next AS (
SELECT id
FROM jobs
WHERE status = 'queued'
AND run_at <= now()
AND attempts < max_attempts
ORDER BY run_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET attempts = j.attempts + 1,
run_at = now() + make_interval(secs => 120) -- the lease
FROM next
WHERE j.id = next.id
RETURNING j.id, j.kind, j.payload, j.attempts, j.max_attempts;Line by line:
FOR UPDATElocks the chosen row so no one else can claim it.SKIP LOCKEDtells other workers to step over locked rows instead of waiting. Without it, ten workers would queue up behind the same row, and underREAD COMMITTEDyou can also get zero rows back when you shouldn't. This is the feature (Postgres 9.5+) that makes database queues practical.The
UPDATEbumpsattemptsand pushesrun_at120 seconds out. The row is now invisible to other workers, and the lock is released the moment this single statement commits.attempts < max_attemptsstops a job that keeps killing its worker from being claimed forever.
The worker
# worker.py (pip install "psycopg[binary]")
import random
import time
import traceback
import psycopg
from psycopg.rows import dict_row
DSN = "postgresql://app:secret@localhost:5432/app"
LEASE_SECONDS = 120 # must be longer than your slowest job, or extend it as you go
POLL_SECONDS = 1.0
CLAIM = """
WITH next AS (
SELECT id FROM jobs
WHERE status = 'queued' AND run_at <= now() AND attempts < max_attempts
ORDER BY run_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET attempts = j.attempts + 1,
run_at = now() + make_interval(secs => %(lease)s)
FROM next
WHERE j.id = next.id
RETURNING j.id, j.kind, j.payload, j.attempts, j.max_attempts
"""
HANDLERS = {}
def job(kind):
def register(fn):
HANDLERS[kind] = fn
return fn
return register
@job("send_receipt")
def send_receipt(payload):
print("emailing receipt for order", payload["order_id"])
def backoff(attempts):
delay = min(2 ** attempts, 900) # 2s, 4s, 8s ... capped at 15 minutes
return delay * random.uniform(0.5, 1.5) # jitter, so retries don't arrive in a herd
def run():
# autocommit: the claim commits immediately and no transaction stays open
# while the handler runs. The lease protects the job, not a database lock.
with psycopg.connect(DSN, autocommit=True, row_factory=dict_row) as conn:
while True:
row = conn.execute(CLAIM, {"lease": float(LEASE_SECONDS)}).fetchone()
if row is None:
time.sleep(POLL_SECONDS)
continue
try:
HANDLERS[row["kind"]](row["payload"])
except Exception:
err = traceback.format_exc()[-4000:]
if row["attempts"] >= row["max_attempts"]:
conn.execute(
"UPDATE jobs SET status = 'dead', last_error = %s, finished_at = now() "
"WHERE id = %s AND attempts = %s",
(err, row["id"], row["attempts"]),
)
else:
conn.execute(
"UPDATE jobs SET run_at = now() + make_interval(secs => %s), last_error = %s "
"WHERE id = %s AND attempts = %s",
(backoff(row["attempts"]), err, row["id"], row["attempts"]),
)
else:
conn.execute(
"UPDATE jobs SET status = 'done', finished_at = now() "
"WHERE id = %s AND attempts = %s",
(row["id"], row["attempts"]),
)
if __name__ == "__main__":
run()Start as many copies as you like. They coordinate purely through the database.
Two details worth pointing out:
AND attempts = %sis a fencing token. Suppose a job runs longer than its lease. Another worker claims it (attempts is now 2), and then the slow first worker finally finishes. Its update matches nothing becauseattemptsno longer equals 1, so it can't overwrite the newer attempt's outcome.Unknown job kinds retry and then die. A
KeyErrorfor a missing handler goes through the same failure path, so a typo in a deploy ends up in your dead jobs instead of vanishing.
Retries, dead jobs, and poison pills
Failures are handled by the handler's exception path above: exponential backoff with jitter, then dead after max_attempts. Dead rows keep last_error, so debugging is a SQL query, not a log hunt:
SELECT id, kind, attempts, left(last_error, 200) AS error
FROM jobs
WHERE status = 'dead'
ORDER BY finished_at DESC
LIMIT 20;To requeue one after you fix the bug:
UPDATE jobs
SET status = 'queued', attempts = 0, run_at = now(), finished_at = NULL
WHERE id = 1234;One more case: a poison pill is a job that crashes the whole worker process (out-of-memory, segfault) so the exception handler never runs. The claim query already refuses rows with attempts >= max_attempts, but those rows would sit as queued forever. A tiny periodic sweep finishes the job:
UPDATE jobs
SET status = 'dead', finished_at = now(),
last_error = coalesce(last_error, 'worker died repeatedly')
WHERE status = 'queued' AND attempts >= max_attempts AND run_at <= now();Run it from a cron job or at the top of each worker's loop every minute or so.
At-least-once means your handlers must be idempotent
This queue guarantees at-least-once delivery: a job can run twice if a worker dies after doing the work but before marking it done, or if a lease expires on a slow job. That is true of every practical queue, Redis and Kafka included. Exactly-once is something you build in the handler.
Practical ways to do that:
Use the job's own identity as an idempotency key when calling external APIs (most payment and email APIs accept one).
Make writes naturally repeatable:
INSERT ... ON CONFLICT DO NOTHING, orUPDATE ... SET state = 'sent'guarded byWHERE state <> 'sent'.Store the side effect and the "done" marker in the same transaction when the work is itself a database write.
If a handler can't be made idempotent, that is worth knowing early.
Waking workers without hammering the database
Polling once a second is cheap and often good enough. If you want near-instant pickup, Postgres has LISTEN/NOTIFY: run NOTIFY jobs inside the enqueue transaction (the notification is only delivered on commit), and have workers LISTEN jobs and attempt a claim when woken.
Keep the polling loop as a fallback. Notifications are not durable, so a worker that wasn't connected at the time simply misses them. Also note that LISTEN doesn't work through PgBouncer in transaction-pooling mode, so give listening workers a direct connection.
The SQLite version
For a single-machine app, a desktop tool, or a side project where adding Postgres is itself overkill, the same design works on SQLite. There is no SKIP LOCKED, but SQLite allows one writer at a time, and a single UPDATE ... RETURNING is atomic, so claiming is still safe. You need SQLite 3.35 or newer for RETURNING.
import random
import sqlite3
import time
import traceback
# isolation_level=None -> autocommit; timeout -> wait for the write lock instead of failing
db = sqlite3.connect("jobs.db", isolation_level=None, timeout=10)
db.row_factory = sqlite3.Row
db.execute("PRAGMA journal_mode = WAL") # readers don't block the writer
db.execute("""
CREATE TABLE IF NOT EXISTS jobs (
id INTEGER PRIMARY KEY,
kind TEXT NOT NULL,
payload TEXT NOT NULL DEFAULT '{}',
status TEXT NOT NULL DEFAULT 'queued',
attempts INTEGER NOT NULL DEFAULT 0,
max_attempts INTEGER NOT NULL DEFAULT 5,
run_at INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
last_error TEXT
)""")
db.execute("CREATE INDEX IF NOT EXISTS jobs_ready ON jobs (run_at) WHERE status = 'queued'")
NOW = "CAST(strftime('%s','now') AS INTEGER)"
CLAIM = f"""
UPDATE jobs
SET attempts = attempts + 1,
run_at = {NOW} + :lease
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued' AND run_at <= {NOW} AND attempts < max_attempts
ORDER BY run_at, id
LIMIT 1
)
RETURNING id, kind, payload, attempts, max_attempts
"""
def claim(lease=120):
# fetchall(), not fetchone(): the statement must run to completion
# so the write lock is released before the handler starts.
rows = db.execute(CLAIM, {"lease": lease}).fetchall()
return rows[0] if rows else None
def settle(row, error=None):
key = (row["id"], row["attempts"]) # same fencing idea as before
if error is None:
db.execute("UPDATE jobs SET status='done' WHERE id=? AND attempts=?", key)
elif row["attempts"] >= row["max_attempts"]:
db.execute("UPDATE jobs SET status='dead', last_error=? WHERE id=? AND attempts=?",
(error, *key))
else:
delay = int(min(2 ** row["attempts"], 900) * random.uniform(0.5, 1.5))
db.execute(f"UPDATE jobs SET run_at = {NOW} + ?, last_error=? WHERE id=? AND attempts=?",
(delay, error, *key))The worker loop is identical to the Postgres one: call claim(), run the handler, call settle(row) on success or settle(row, traceback.format_exc()) on failure, and sleep a second when the queue is empty.
Enqueueing atomically alongside your data works the same way, using one explicit transaction:
BEGIN IMMEDIATE;
INSERT INTO orders (customer_id, total) VALUES (42, 1999);
INSERT INTO jobs (kind, payload) VALUES ('send_receipt', '{"order_id": 1}');
COMMIT;SQLite caveats:
Keep the database on a local disk. WAL mode does not work reliably over network filesystems like NFS.
All writers share one lock. That is fine for a few workers on one machine and a poor fit for many machines.
Many processes on the same host can share the file safely, which covers most small deployments.
Running it in production
Watch two numbers. Queue health boils down to how long the oldest ready job has been waiting, and how many jobs are dead:
SELECT count(*) AS ready_jobs,
now() - min(run_at) AS oldest_wait
FROM jobs
WHERE status = 'queued' AND run_at <= now();
SELECT count(*) FROM jobs WHERE status = 'dead';Alert when oldest_wait grows beyond what your users would tolerate, and when dead jobs appear. Because leased jobs have a future run_at, they are excluded from ready_jobs, so the numbers reflect real waiting work.
Purge finished jobs. Every claim and every completion is an UPDATE, which leaves dead tuples behind in Postgres. Keep the table small and let autovacuum keep up:
DELETE FROM jobs
WHERE status = 'done' AND finished_at < now() - interval '7 days';For very high volume, delete in batches, or partition by day and drop old partitions.
Use the database clock. Every example above uses now() from the database, not timestamps generated by app servers. With several workers on several machines, clock skew would otherwise make leases and delays unreliable.
Keep payloads small. Store IDs and fetch the data inside the handler. Big blobs in a hot table make everything slower.
Common pitfalls
Holding a transaction open while the job runs. It pins a connection, blocks vacuum, and holds row locks. Claim in one short statement and let the lease do the protecting.
A lease shorter than the job. The job runs twice. Pick a lease longer than your slowest job, or extend it with a heartbeat:
UPDATE jobs SET run_at = now() + interval '2 minutes' WHERE id = $1 AND attempts = $2.No partial index. Without it, claiming degrades into scanning a table full of finished jobs.
Expecting strict ordering.
ORDER BY run_at, idgives roughly FIFO, but retries and concurrency mean strict ordering isn't guaranteed. If a job needs another to finish first, model that dependency explicitly.Forgetting to monitor dead jobs. A dead job is a failure nobody has seen yet.
When to graduate to Redis or Kafka
A database queue is a good default, not a universal answer. Look elsewhere when:
You sustain tens of thousands of jobs per second, or the jobs table has become your hottest table and is hurting the main workload.
Many independent consumers need to read the same stream of events, or you need to replay history. That is a log, which is what Kafka is built for. A log and a task queue are different tools.
You need low-latency fan-out to many subscribers (pub/sub).
Your services are written in several languages and don't share a database.
If you'd rather not own the code at all, several libraries implement this same pattern for you: pg-boss and Graphile Worker (Node), River (Go), Oban (Elixir), Procrastinate (Python), and Solid Queue (Rails).
Takeaways
A jobs table plus
FOR UPDATE SKIP LOCKEDis a real, durable queue.Enqueueing in the same transaction as your data removes the dual-write bug entirely.
Model in-flight work as a lease (
run_atin the future) so crashed workers recover automatically.Add backoff with jitter, a
deadstate, and a fencing check (attempts = ?) on every settle.Assume at-least-once delivery and make handlers idempotent.
Monitor the age of the oldest ready job and the count of dead ones, and purge old rows.
Start with the database you already have. You can always add a broker later, and by then you'll know exactly why you need it.
Comments(0)
Join the discussion by logging into your account.
No comments yet. Be the first to comment!