{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"skip-redis-build-a-job-queue-on-postgres-or-sqlite-nupli","url":"https://zyvop.com/skip-redis-build-a-job-queue-on-postgres-or-sqlite-nupli","title":"Skip Redis: Build a Job Queue on Postgres or SQLite","subtitle":"Retries, delays, and dead-letter handling in one table and one SQL statement, using the database you already run.","tldr":"Adding Redis or Kafka just to send emails and process webhooks means another stateful service and a dual-write bug waiting to happen. Here is a durable job queue built on Postgres (SKIP LOCKED) or SQLite, with retries, backoff, leases, and dead jobs.","keywords":["backend","job queue","postgres","System Design","SQLite"],"entities":["Anshu Pathak","backend","job queue","postgres","System Design","SQLite","ZyVOP"],"keyTakeaways":["A jobs table plus FOR UPDATE SKIP LOCKED is 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_at in the future) so crashed workers recover automatically.","Add backoff with jitter, a dead state, 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."],"headings":["The bug a separate broker gives you","Is a database queue fast enough?","The design","Postgres implementation","Schema","Enqueue","Claim","The worker","Retries, dead jobs, and poison pills","At-least-once means your handlers must be idempotent","Waking workers without hammering the database","The SQLite version","Running it in production","Common pitfalls","When to graduate to Redis or Kafka","Takeaways"],"outboundLinks":[],"contentText":"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-&gt;&gt;DB: COMMIT order 4211 Note over App,Q: crash, deploy, or network blip here App-&gt;&gt;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 JSON payload, and an attempts counter. run_at says when it may run. Delayed jobs and retries are just a future run_at. Claiming a job is a lease. A worker claims a job by pushing run_at forward 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 set dead. stateDiagram-v2 [*] --&gt; queued: enqueue queued --&gt; leased: worker claims it leased --&gt; done: handler succeeds leased --&gt; queued: handler fails, retry after backoff leased --&gt; queued: worker crashes, lease expires leased --&gt; dead: attempts exhausted done --&gt; [*] dead --&gt; [*]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 &lt;= now() AND attempts &lt; 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 =&gt; 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 UPDATE locks the chosen row so no one else can claim it. SKIP LOCKED tells other workers to step over locked rows instead of waiting. Without it, ten workers would queue up behind the same row, and under READ COMMITTED you can also get zero rows back when you shouldn't. This is the feature (Postgres 9.5+) that makes database queues practical. The UPDATE bumps attempts and pushes run_at 120 seconds out. The row is now invisible to other workers, and the lock is released the moment this single statement commits. attempts &lt; max_attempts stops 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 &lt;= now() AND attempts &lt; 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 =&gt; %(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\"] &gt;= 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 =&gt; %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 = %s is 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 because attempts no longer equals 1, so it can't overwrite the newer attempt's outcome. Unknown job kinds retry and then die. A KeyError for 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 &gt;= 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 &gt;= max_attempts AND run_at &lt;= 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, or UPDATE ... SET state = 'sent' guarded by WHERE state &lt;&gt; '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 -&gt; autocommit; timeout -&gt; 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 &lt;= {NOW} AND attempts &lt; 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\"] &gt;= 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 &lt;= 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 &lt; 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, id gives 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 LOCKED is 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_at in the future) so crashed workers recover automatically. Add backoff with jitter, a dead state, 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.","contentHash":"sha256:f0a00907f0c9df278e83e4d101bca00bcc70a2df7d85463b880ac9a0af6ee47a","authorName":"Anshu Pathak","authorUrl":"https://zyvop.com/author/anshu","authorSameAs":["https://www.linkedin.com/in/anshu-pathak-zyvop/"],"category":"System Design","tags":["backend","job queue","postgres","System Design","SQLite"],"audience":"Software engineers and developers building applications with System Design","tone":"Instructional, practical, code-first","readingTimeMinutes":11,"wordCount":2683,"faqs":null,"primaryTopic":"System Design","publishedAt":"2026-11-16T05:00:39.460Z","updatedAt":"2026-11-16T05:00:39.460Z","canonicalUrl":"https://zyvop.com/skip-redis-build-a-job-queue-on-postgres-or-sqlite-nupli"}