Three services died in that pull request.
- notification-svc 1,850 lines
- scheduler-svc 1,340 lines
- retry-svc 910 lines
+ job table 41 lines of SQL
+ one worker 560 lines
Nothing was wrong with any of them. That is the part that took eighteen months to see, and it is the only reason this post is worth your time — because if they had been badly built, the lesson would just be "we wrote bad services and then wrote better ones," which is not a lesson.
They were well built. They were well built around a seam that did not need to exist.
What the three of them actually did
A user places an order. Some time later, they get a receipt. Between those two facts sat:
-
notification-svc— owned templates and delivery. Talked to the email provider, the push provider, the SMS provider. -
scheduler-svc— owned when. Send now, send in 20 minutes, send at 9am in the user's timezone. -
retry-svc— owned what happens when the provider is down. Backoff, jitter, dead-letter, the alert when the dead-letter queue got deep.
Between them: two RabbitMQ queues, a Redis for scheduled sets, three deployments, three sets of dashboards, three on-call runbooks. Roughly 4,100 lines, not counting infrastructure.
Write down what all three do in one sentence and the problem becomes visible:
All three existed to move a row from one place to another, later.
That is not a domain. That is a queue. We had built three domain services around a data structure.
The bug that was actually driving all of it
Here is the thing that made me stop and rewrite instead of refactor.
Every path into that system looked like this:
await db.insert(orders, order) // system 1
await queue.publish('send_receipt', { orderId }) // system 2
Two systems. One of them can fail.
If the publish fails after the insert commits, you now have an order in your database that nothing in the world knows about. No exception in your logs, because you caught it and retried, and the retry also failed, and eventually you gave up and logged a warning nobody reads. No alert. Just a customer who never got their receipt, and a row that looks completely fine.
Do it the other way round and you get the mirror image: a receipt for an order that does not exist.
This has a name — the transactional outbox problem — and if you have ever shipped a queue, you have shipped this bug. You may not have found it yet.
The standard fix is to add an outbox table, a relay process that reads it and publishes, and dedupe on the consumer because that relay is at-least-once. That works. It is also three more moving parts to paper over one seam, and it was on our roadmap.
We built the other thing instead.
The table
This is the entire queue.
create table job (
id bigint generated always as identity primary key,
kind text not null,
payload jsonb not null,
run_at timestamptz not null default now(),
attempts int not null default 0,
max_attempts int not null default 8,
status text not null default 'ready',
locked_until timestamptz,
last_error text
);
-- the only index that matters. Partial, so it stays small:
-- the ready set is a few thousand rows, the table is millions.
create index job_ready_idx on job (run_at)
where status = 'ready';
run_at is scheduler-svc. attempts and max_attempts are retry-svc. kind and payload are notification-svc. Nine columns.
How a worker takes a job
with claimed as (
select id
from job
where status = 'ready'
and run_at <= now()
order by run_at
for update skip locked -- ← this line is the whole post
limit 20
)
update job j
set status = 'running',
locked_until = now() + interval '5 minutes',
attempts = attempts + 1
from claimed c
where j.id = c.id
returning j.*;
FOR UPDATE SKIP LOCKED tells Postgres: lock these rows, and if another transaction already has one, don't wait — skip it and take the next.
Twenty workers can run that statement at the same time and no two of them will ever get the same row. No coordination, no leader, no lease service, no Redis. Postgres has shipped this since 9.5, in 2016. Most teams buy a broker to get a behaviour their database has had for a decade.
Two details that are not optional:
Claim, then commit, then work. The transaction above ends the moment the rows are claimed. The worker does the actual sending outside it. Holding a transaction open for the duration of a job is the mistake that gives "Postgres as a queue" its bad reputation — it pins a connection and blocks vacuum for as long as the job runs.
Something has to reap. locked_until is a lease. A worker that dies mid-job leaves its rows in running forever, so a small periodic statement moves expired leases back:
update job set status = 'ready', locked_until = null
where status = 'running' and locked_until < now();
That is your entire failure-recovery story. It replaced retry-svc.
And then the enqueue
This is the part I actually care about, and it is one line:
begin;
insert into orders (...) values (...);
insert into job (kind, payload)
values ('send_receipt', jsonb_build_object('order_id', ...));
commit;
The order and the job that sends its receipt now commit together, or neither does.
Not "usually." Not "we retry the publish three times." There is no publish. There is no second system. The class of bug where the row exists and the notification does not is no longer unlikely — it is unrepresentable.
We did not solve the outbox problem. We deleted the seam it lives in.
What got deleted along with the services
Things that had been real work, real tickets, real pages, and stopped existing:
- Idempotency keys on the consumer. At-least-once delivery meant every handler had to be safe to run twice. Now a job is claimed by exactly one worker in one transaction.
-
The dead-letter queue and its dashboard.
attempts >= max_attemptsis aWHEREclause. Inspecting failures isselect * from job where status = 'failed'. Retrying them is anupdate. - Queue-vs-database drift. There is no longer a state of the world where the queue believes one thing and the database believes another, because there is one place.
- Two RabbitMQ nodes, a Redis, and three deploy pipelines.
- "Which service is this in?" — the question that ate the most engineer-hours of anything on this list, and does not appear in any postmortem.
Ninety days later
| Before | After | |
|---|---|---|
| Services | 3 | 1 |
| Lines of code | 4,100 | 600 |
| p99 enqueue → execute | 4.2s | 380ms |
| Pages / month | 9 | 1 |
| Places a message can be lost | 1 | 0 |
The p99 number surprised people, so: it is not that Postgres is faster than RabbitMQ. It is that we deleted two network hops and a poll interval. The old p99 was mostly scheduling latency and queue-hop overhead, not broker throughput. Nothing here makes Postgres a faster message bus. It makes the path shorter.
The last row is the only one I would have done this for.
The five objections, scored honestly
Every one of these was said to me, in a review, by someone competent.
1. "A database is not a queue."
It is a durable, transactional, ordered, indexed store with row-level locking and a purpose-built primitive for concurrent consumers. If that is not a queue, the word has stopped meaning anything. Cargo cult.
2. "It won't scale."
It will not scale forever, which is a different sentence. On one unremarkable Postgres box this pattern handles a few thousand jobs a second before you have to think hard. Ask what your actual number is. Ours was 40 jobs a second at peak, and I would bet real money yours is closer to 40 than to 5,000. Real, but check your number first.
3. "You'll blow up the table with dead tuples."
This one is correct and it is the one nobody warns you about loudly enough. A queue table is the highest-churn table you will ever own — every job is an insert, two updates and a delete. Autovacuum's defaults are tuned for tables that do not behave like that. Set them per-table:
alter table job set (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_cost_delay = 0,
autovacuum_analyze_scale_factor = 0.05
);
And delete completed rows on a schedule rather than keeping them forever — move them to a job_archive table if you need the history. Real. Budget for it on day one.
4. "You're coupling your services to one database."
We had one database before. The services shared it. What we removed was the pretence that they did not. Cargo cult, in our case — check whether it is in yours.
5. "This doesn't work for fan-out."
Correct, and it is the real limit. One row goes to exactly one worker; that is the entire design. If you need one event delivered independently to six consumers who each track their own position, you want a log, and you want Kafka, and no amount of SQL is going to be nicer. Real. Different problem.
When this is the wrong call
I would not do this if:
- You are above a few thousand jobs a second. Buy the broker. The vacuum tuning stops being a config change and starts being a full-time interest.
- You need fan-out or replay. See above. Different data structure, different tool.
- Your teams work in four languages. A broker is a lingua franca. SQL and a shared schema are a coupling you have to socialise, and that cost is organisational, not technical, which makes it harder, not easier.
- The queue must outlive the database. This is the real trade and it deserves saying plainly: your queue is now exactly as available as your primary. For most teams that is an upgrade, because the queue was never actually more available than the database it fed. But you should choose it on purpose rather than discover it at 3am.
What I would tell myself eighteen months earlier
Check one number before you build three services.
Not "how many messages a second do we expect at scale" — the honest one: how many a second do we do today, and what is the multiple we would need before this hurts? Ours was 40, and the answer was about 100×. We spent eighteen months and three services buying headroom for a load that has still not arrived.
The queue is a solved problem that a lot of us keep re-buying, one service at a time. Postgres solved it in 2016 and did not put out a press release.
Four questions I would genuinely like answered in the comments:
- What is your actual jobs-per-second at peak — not your projection, the number in your dashboard right now?
- Has anyone here found the autovacuum tuning insufficient at high churn, and what did you move to?
- If you moved the other way — Postgres → broker — what number finally forced it?
- Who has shipped the transactional outbox and would do it again, versus collapsing the seam instead?
Top comments (0)