I Replaced Redis and RabbitMQ With 15 Lines of Postgres

#database #backend #postgres #tutorial

Launch Workspace — "Just Use Postgres" Week 1

Route: TBD (A = canonical build on omenabyte.com → dev.to API · B = dev.to-first · C = draft-only manual publish)
Decision made: pending Boss's pick

ARTICLE 1 — dev.to-optimized draft

Title: I Replaced Redis and RabbitMQ With 15 Lines of Postgres
Tags: postgres, database, backend, tutorial
Cover image: Just-Use-Postgres-Field-Manual-cover.png (blueprint elephant, navy/amber — matches dev.to dark theme)
Canonical URL (route A): https://omenabyte.com/blog/replace-redis-rabbitmq-with-postgres

Every background job system I've built started the same way: "we'll just add Redis for the queue." Then six months later there's a broker to patch, secure, monitor, and pay for — doing a job Postgres can do with one SQL clause.

That clause is FOR UPDATE SKIP LOCKED, and once you've used it, a message broker feels like overkill for most job-queue workloads.

The problem with a naive SQL queue

The reason people avoid building a queue directly on a table is real: two workers can grab the same "pending" row at the same time, one locks it, and the other sits there waiting. That's a legitimate deadlock risk — if you build it naively.

The fix: SKIP LOCKED

SKIP LOCKED tells Postgres: if a row is already locked by another transaction, don't wait for it — skip straight to the next one. That single behavior turns an ordinary table into a safe, concurrent queue.

CREATE TABLE jobs (
  id          bigserial PRIMARY KEY,
  payload     jsonb NOT NULL,
  status      text NOT NULL DEFAULT 'pending',
  locked_at   timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Without this, the query below does a full table scan on every poll
CREATE INDEX idx_jobs_status_created ON jobs (status, created_at)
  WHERE status IN ('pending', 'processing');

Here's the actual dequeue query every worker runs:

WITH next_job AS (
  SELECT id FROM jobs
  WHERE status = 'pending'
     OR (status = 'processing' AND locked_at < now() - interval '5 minutes')
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs
SET status = 'processing', locked_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;

That locked_at check matters more than it looks. It's the equivalent of a message queue's visibility timeout — if a worker crashes mid-job, the job doesn't stay stuck in processing forever. Another worker reclaims it after five minutes.

I actually tested this for duplicates

I ran this exact query five times in a row against a seeded table: three fresh jobs and one simulated crashed job. Every job came back exactly once, in order, and the crashed job was correctly reclaimed on the fourth call. Zero duplicates, zero races.

Why this beats adding Redis for most teams

No broker to operate. Nothing new to patch, secure, or monitor.
Job history lives with your data. You can join a job row straight to the order or user it belongs to — try doing that across two databases.
Crashes don't lose work. The reclaim logic above handles it natively.

Where Redis still wins

If you need sub-millisecond pub/sub fan-out to thousands of concurrent WebSocket clients, or a pure in-memory cache absorbing extreme read traffic, that's a different problem — Redis is still the right tool there. But "give my background jobs somewhere safe to live" almost never needs a separate service.

This is one of eight infrastructure swaps in Just Use Postgres, a 24-page field manual on replacing MongoDB, Redis, Elasticsearch, Pinecone, and more with the database you're probably already running. Every recipe in it — including this one — was run against a live Postgres instance before it went in the book.

👉 Get the field manual — launch price $14 instead of walking into a $50/month managed-Redis invoice: https://payhip.com/omenabyte
🐳 Or take the $24 bundle with the full docker-compose up starter repo — all 8 modules as tested, runnable migrations + seed data: https://4693433176360.gumroad.com/

Read this on omenabyte.com → https://omenabyte.com/blog/replace-redis-rabbitmq-with-postgres

DEV Community