Marlow Books is an online bookshop run by four people, and it exists only in this course. On the first Wednesday in June, about three weeks after the Thursday lesson 039 opens with, the founder did everything right and eleven customers paid for books and heard nothing back.
The job was operating system updates on the box that runs the broker, the separate piece of software lesson 017 introduced as the thing that holds messages. The founder picked 11:20 on purpose. Lesson 038 had already published why that is the quiet moment: at lesson 027's rate of an order every fifty seconds, a figure nobody at the shop has ever checked, four email workers leave that queue empty by late morning. So the founder checked the queue was empty, checked the dead letter queue was empty too, and stopped the broker at 11:18.
The box came back at 11:27. Nine minutes, against the three the founder had planned for, because a disk check ran that nobody had asked for.
Nothing else stopped. Box A, which has run Marlow's Postgres and its application since lesson 005, took orders the whole time, each one running the shape lesson 035 shipped in February and lesson 039 walked step by step: the pending row and the stock - 1 in one transaction, the card with nothing open, a second short transaction moving the row to paid. Then the checkout published order_placed, the message lesson 038 renamed in April, into a broker that was not there.
That publish raised a connection error, and the code around it logged a warning and carried on. Not laziness. An exception there would show a customer an error page for an order the shop had already charged them for, and the founder had thought about that and picked the quieter failure on purpose.
Nine minutes at an order every fifty seconds is just under eleven orders. It was eleven. Every one of them paid, every one of them logged, not one of them announced.
Four days later, on the Sunday, a customer emailed to ask whether their order had gone through. The book had arrived on the Friday. The shop had charged the card, picked the copy, packed it and posted it, because every one of those steps reads the orders table and none of them reads the queue. The only thing it failed to do was tell anybody.
Finding the rest took a record from outside the shop. The founder exported the email provider's send log for the previous thirty days and joined it against the orders table on the order id. Eleven orders with no send, all between 11:18 and 11:27 on the Wednesday.
One batch, which answers a question the founder had not thought to ask. The broker has carried this message since December. Why had six months produced one batch of losses and no stragglers?
Because the window is tiny and the failure is correlated. The publish call takes a couple of milliseconds, which is what lesson 017 priced a broker's accept at. A process dying at a random instant has about one chance in twenty five thousand of dying inside a two millisecond window in a fifty second gap between orders, so the shop could restart the application badly every day for years and never lose an order that way. Take the broker away for nine minutes and the window is nine minutes wide and everything inside it is lost.
This failure does not arrive in ones. It arrives in a batch, on the day something downstream is unavailable, which is exactly the day everybody is looking at the thing that is unavailable.
You cannot alert on a message that was never written
Every other failure in this course left evidence. A slow consumer leaves depth on a graph, and lesson 017 taught you to judge it by the age of the oldest message rather than by the size of the pile. A poison message leaves a row in a drawer. Lesson 036's 108 messages sat in a dead letter queue for a month, invisible but present, and the moment somebody built the alert it fired.
A lost publish leaves a log line nobody reads and nothing else at all.
No depth, because the message never reached a queue to add depth to. No dead letter, because dead lettering is something a broker does to a message it has. No age, no delivery count, no offset that stops moving. You cannot alert on a message that was never written, and every instrument lessons 017, 018, 036 and 037 handed you watches a message that got written.
What is left is reconciliation: comparing two sets that should match. The founder's thirty day join is that done by hand, and it only worked because the email provider keeps a log of what it sent with the order id in it. Take that away and the eleven are gone from the record entirely.
The shop got lucky, and the pattern in the luck is a design rule. Two consumers read order_placed in June, and the third got lucky by not being one any more: the Wednesday at the end of lesson 038 deleted order_count and left the orders a minute graph running a count over the orders table, so the graph came through the nine minutes right. reorder_watch is still on the queue, and it missed eleven chances to notice a shelf running low, which was recoverable in one query, since what it does is read a title's current stock and compare it to three. Both of those are functions of the database. Run them again and you get the right answer.
The confirmation email is not a function of anything. It happened or it did not.
The consumers that survive a lost message are the ones whose output you can recompute. Lesson 038 found the same shape from the other end, where a counter that is recomputed cannot be double counted. The ones you cannot recompute are lesson 019's third pile, the work that leaves your building and is not idempotent at any price, and a lost message destroys those permanently.
Three orderings, and none of them work
Two writes. A row in Postgres, the fact; a message in the broker, the announcement. Three orderings are available, and each failure is worth walking.
Publish first, then commit. The broker has the message, and then the commit fails: a dropped connection to box A, a serialisation failure, a box that dies. At Marlow the paid transaction fails, the row stays pending, and the customer is holding an email saying their order is confirmed while the shop's own record says it is not. The checkout does not get to decide how that ends. Lesson 019's sweeper arrives within about four minutes, asks the provider what happened to that key, and either completes the order or refunds the charge, and only the first of those makes the email true. Take the other branch, the one lesson 039 walked, and the customer is holding a confirmation for an order that was unwound: a refund nobody asked for, and no book. You have published a fact that may never become true, and lesson 038 priced that: a producer that stops knowing who is listening also stops knowing who acted on the message you now wish you could take back.
Commit first, then publish. June.
Both in one transaction. You cannot, and lesson 039 spent its opening section on why: a commit is an entry in a log one machine owns, and the broker is not in that log and has never heard of your transaction id.
People then reach for something that is not an ordering at all, and it is where the pattern comes from. Reconcile afterwards: have something sweep up and republish whatever did not get announced. Marlow already owns a sweeper, so this is not hypothetical.
Write that query. Find every order that should have had a message published and did not.
The first half is easy and the second half does not exist. The orders table knows an order is paid and has no column anywhere that separates an announced order from an unannounced one, because the announcement happened on another machine and left nothing behind. Reconciling needs a record of what was supposed to be published, somewhere you can query.
A record of intent is an outbox, whatever you decide to call it. That derivation is worth more than being handed the table, because the next time somebody says to reconcile it later, you will know what they are asking you to build.
One table, one transaction
Add a table in the same database as the business data.
create table outbox (
id uuid primary key,
topic text not null,
payload jsonb not null,
created_at timestamptz not null default now(),
published_at timestamptz
);
Then write the fact and the intention in one transaction.
begin;
update orders set status = 'paid'
where id = $1 and status = 'pending';
insert into outbox (id, topic, payload)
values ($2, 'order_placed', $3);
commit;
A separate process, the drainer, reads rows where published_at is null, publishes each to the broker and stamps it. The broker stays lesson 017's broker, the consumers never know anything happened, and the publish call leaves the request path.
What that buys is one thing, and it is the thing June needed. The order being paid for and the shop's intention to announce it are two rows in one write ahead log on one machine, so lesson 015's atomicity covers both, and there is no longer any state in which the shop holds money and holds no record that somebody is owed an email.
Be equally clear about what it does not buy, because this is where the pattern gets oversold. The drainer publishes and then stamps: two machines, in order, with a gap between them. Same problem, one level down, and a drainer that dies in that gap republishes on its next pass. The outbox is at-least-once and lessons 019 and 036 apply to it unchanged.
Which sounds like a circle until you price the two failures against each other. Before: a message that does not exist, with no record and no name, findable only in somebody else's send log. After: a message that exists twice carrying the same id both times, which is a duplicate with a name on it, and Marlow's email consumer has been deciding what to do about those since March, when it started claiming a row on the order id before it sends.
Notice what has and has not changed. This is still commit first and publish second, the ordering that lost June's eleven orders. What moved is the contents of the commit, and therefore what a failed publish leaves behind.
The outbox does not close the gap. It moves the gap to the side where repeating is cheap. You cannot deduplicate a message that does not exist, and you can always deduplicate one that arrives twice. Which puts one rule on the drainer: the id goes into the outbox row when the business transaction writes it, and a republish carries that same id rather than a fresh one. Lesson 038 showed the standard has a slot for it, since CloudEvents requires an id on every event so the receiver can tell a repeat from a new fact.
What a poll actually costs at a shop this size
Most writing about the outbox warns you about the cost of polling a table every second. At a shop this size that warning is backwards.
One tick a second is 86,400 queries a day. Each asks for unsent rows, and the index that answers is partial, covering only rows where published_at is null:
create index outbox_unsent on outbox (created_at)
where published_at is null;
An index over the backlog alone is normally empty, or holds the one row this tick is about to clear, which is a handful of pages that never leave Postgres's buffer cache. Cheaper than the single row read lesson 010 priced at two tenths of a millisecond, so call it a tenth. That is 8.6 seconds of database work a day.
Now the day it sits in. Lesson 010 measured Marlow's whole read load at 1.04 seconds of database work a second, and that is the Christmas peak of four hundred requests a second; an ordinary day's forty is a tenth of it on the same mix, so about nine thousand seconds of database work between one midnight and the next. The poll is a thousandth of that, and lesson 010's sixteen cores never notice it.
The real cost of polling is latency. A tick every second adds between zero and one second, so half a second on average, to every message. For a confirmation email that is nothing. For Galewatch, the telemetry service that collects a reading from each of nine hundred wind turbines every two seconds and sells dashboards that lag by seconds, half a second of avoidable delay would be a real slice of the product's promise.
There is a cheap fix with a sharp edge on it. NOTIFY inside the transaction is delivered to listeners at commit and not before, so the drainer wakes on the commit rather than on its tick, and latency drops from half a second to a millisecond.
The edge is that a notification is not durable. If nothing is listening when the commit lands it is gone, and nobody anywhere remembers it was sent, so a drainer that happened to be restarting finds that row on its next poll instead.
So keep the poll. A notification is an optimisation and the poll is the guarantee, which is the right way round whenever one of the two is lossy. Lesson 010 listed LISTEN and NOTIFY among the things that break behind a connection pooler in transaction mode, and lesson 035 noted Marlow has no pooler, which is the only reason this is on the table here. Install one and the acceleration stops quietly while the poll carries on, and the symptom is half a second of latency coming back. For an optimisation that is the best failure mode you can buy.
One more trap in the drain, and I have watched people build it twice.
The obvious optimisation is to add a bigserial, an auto-incrementing number, and keep a high water mark: remember the highest number you sent, and next tick ask for anything above it. No update on the hot path, no index on a null column, and the rows come out in order.
It loses messages. Postgres hands out sequence values outside your transaction, before the commit, so two transactions that overlap at all can commit in the opposite order to the numbers they took: 101 commits while 100 is still a millisecond away from committing. Tick in that millisecond and the drainer sees 101, publishes it, stores 101 as its mark, and row 100 becomes visible straight afterwards and is never asked for again. Permanently unpublished, with no backlog anywhere to show it, which is June's failure rebuilt inside June's fix.
Two things have to coincide, the out of order commit and a tick landing between the two, and that is what makes this a trap rather than a daily annoyance. At Marlow the insert rides in the paid transaction, a couple of milliseconds long at one order every fifty seconds, so the first of them is the hook's one in twenty five thousand all over again. At a few hundred writes a second transactions overlap constantly and only the tick is rare, so the loss rate is small, never zero, and nowhere on any graph.
| Drain query | Order | Can lose a row |
|---|---|---|
where published_at is null |
not guaranteed | no |
where seq > watermark |
by sequence | yes, permanently |
A sequence number is handed out before the commit, so it is not a commit order. The honest choice at Marlow is the left column, which never loses a row and gives up strict ordering, and lesson 038 already told the shop what that costs: with no partitions and four workers, two events about the same title can be handled in either order anyway, and at one order every fifty seconds that will happen rarely enough that no test finds it and a customer does. If order matters, lesson 037's answer is the only one that works, which is to partition by the key whose order you need. One drainer publishing serially gets you close and is not a guarantee.
Reading the database's own log instead
The other way to drain an outbox is to stop reading the table and read the record of changes to it. That is change data capture, and this course has almost built it twice.
Lesson 011 defined logical replication: Postgres decodes the write ahead log back into row changes and hands them to a client as ordinary inserts and updates. Lesson 012 printed the positions those bytes pass through, sent_lsn to flush_lsn to replay_lsn, and an LSN is an address in that log. A change data capture reader is a logical replication client that publishes those row changes instead of applying them to a second database. The one you will meet is Debezium, which writes what it reads into a Kafka topic and ships a transform built for exactly this table, routing outbox rows to the topic named in the row.
Three things it buys: no queries against your tables, no latency floor beyond replication, and ordering that really is commit order, because logical decoding holds a transaction's changes until its commit record turns up and hands them over in that order. That last one is the problem a sequence could not solve.
Then the bill, which is not the one the sales pitch prepares you for. A logical replication client holds a replication slot: a named position in the write ahead log the primary promises not to discard until that client confirms it has got past it. The promise is the point, and it is what makes the reader safe to restart without missing anything. It also means a slot whose consumer is dead makes the primary keep every segment written since that position, until the disk fills and the database stops.
Polling puts your backlog in a table and change data capture puts it on your primary's disk. A table full of unsent rows is a graph you can alert on and a problem you can fix on Monday. A primary at one hundred percent disk is lesson 001's single server, down.
Which brings in the one machine in this course nobody is allowed to add load to. Stagefront, an event ticketing service that exists only in this course, puts a stadium show on sale at exactly ten in the morning for two hundred thousand people pressing the same button, and keeps the seats on one machine on purpose. Lesson 024 chose that so there would be no second copy of a seat to have an opinion, and capacity is what it pays for the choice. A poller is reads against that machine, which is the one thing you may not spend there, so change data capture is the right tool and this is the clearest case for it in the course.
Price its risk honestly, because the arithmetic does not land where the fear does. Lesson 017 sized the stadium at forty thousand seats and had the sale over in eighty seconds. Lesson 015's shape writes each seat twice, a claim and then a confirmation when the card comes back, with a row per purchase on top, so the whole event is something like a hundred thousand row writes. At a few hundred bytes of log each, which is the right order for a small insert and occasionally much more when Postgres writes out a whole page, the busiest eighty seconds of Stagefront's year is about thirty megabytes of write ahead log. Lesson 003 published about forty on-sales a year, so every show the business runs, all year, comes to a gigabyte or so.
An on-sale cannot fill a disk, then, and that is the uncomfortable half rather than the reassuring one. A slot retains whatever the database writes, and between on-sales this is still a database with a business on it, and nobody in this course has ever measured what it writes on an ordinary Tuesday. A slot forgotten for a few months has kept every byte of it. The quantity that gets you is the one you cannot estimate from any figure here, which is the argument for an alarm rather than a calculation.
Two things make that survivable and both are one line. Alert on the slot, which is a query against pg_replication_slots, where since version 13 Postgres reports a wal_status and how much room is left. And set max_slot_wal_keep_size, which caps what a slot may hold and invalidates the slot rather than the database when it goes past. Its default, since that same version 13, is unlimited: wrong for anybody who has not thought about it, right for the people who wrote it, because dropping a slot quietly loses data.
One more decision hides in here. If you are reading the log anyway, why keep the table at all, rather than publishing the orders row changes directly?
Because a row change is not an event. A diff showing status going from pending to paid is a fact about your schema, and turning it back into "an order was placed" means the consumer knows your column names, your status vocabulary and which transitions mean anything. Lesson 038 named that coupling and lesson 052 owns what happens when you rename the column. Change data capture without an outbox publishes your schema. With one, it publishes your events, in the shape you chose, written by the service that knows what the fact means.
Galewatch is where the whole lesson gets refused. Lesson 037 put its readings into a log first and made the sharded Postgres a consumer of that log, so there is no outbox to build and no second place to write: the append is the record and everything anybody reads is derived from it. Arrange for the first write to be the thing you would have published and this lesson's problem does not arise.
The inbox, and the half of a problem that gets built
The mirror image belongs to the consumer. An inbox is a table of message identifiers a consumer has already handled, written in the same transaction as the effect of handling them, so a redelivery finds the id and does nothing.
Marlow has had one since March and nobody called it that either. Lesson 036 shipped it: the email consumer claims an order with a row carrying a null sent_at before calling the provider, and stamps that row once the provider answers. Lesson 038 confirmed it has worked on every order since.
It is also the weakest version available, and lesson 019 says why. A real inbox puts the id and the effect in one transaction, which only works when the effect is a write to the same database. Sending an email is lesson 019's third pile, so the shop claims the row before the send and stamps it after, and then has to guess what an unstamped claim means: lesson 036's rule is that a claim younger than two minutes is still in flight and an older one is dead, and nothing anywhere makes that rule true. The outbox fixes the producer's half of this problem completely, the inbox fixes the consumer's half when the effect is a database write, and nothing fixes it when the effect leaves the building.
Which leaves the question this lesson turns on. The shop built the inbox in March, the week after the Thursday that sent 324 emails for 108 orders, and by June it had still not built the outbox, with the same two-machine gap sitting in the same handler the whole time.
The difference is not difficulty. Both are one table. Three emails about one book produce a customer asking whether they have been charged three times, and a founder giving up a Friday morning to prove they have not. Zero emails produce a customer wondering, four days later, whether they imagined buying something, and ten others who never say a word. The half of a problem that generates complaints is the half that gets built, and the other half waits for the day something downstream goes away for nine minutes.
Recap
You cannot alert on a message that was never written. Depth, age, delivery counts, dead letter queues and offsets are all instruments for messages that exist, which is how Marlow ran six months of this failure and measured it as zero. Finding such a loss means reconciling against a second record, and the shop's only second record belonged to somebody else.
A record of intent is an outbox, whatever you call it. Reconciling needs to know what should have been published, and writing that down is the table. Put it in the same transaction as the business change and lesson 015's atomicity covers the fact and the intention together.
The outbox moves the gap to the side where repeating is cheap. It does not remove it: the drainer still publishes and then stamps, so this is at-least-once and lessons 019 and 036 apply unchanged. What changes is the currency. A lost message has no name and no record, and a message published twice carries the same id both times.
A notification is an optimisation and the poll is the guarantee. At Marlow a one second poll is about a thousandth of the database's day, so the honest objection to polling is the half second it adds rather than the load. NOTIFY removes that wait and is lossy, so it goes on top of the poll and never in place of it.
A sequence number is handed out before the commit, so it is not a commit order. A drainer with a high water mark skips any row whose transaction commits late, permanently and silently. Draining on published_at is null can publish out of order and cannot lose a row, which is the right trade unless you have lesson 037's partitions giving you real ordering.
Polling puts your backlog in a table and change data capture puts it on your primary's disk. A replication slot is a position the primary will not discard, which is what makes the reader restartable and what fills the disk when the reader dies. At Stagefront that is still the right trade, because reads against the seat machine are what lesson 024 refuses to spend, and it survives on an alert on the slot and a cap on what it may keep. Read the log without an outbox and what you publish is your schema.
The cheapest answer is one place to write. Galewatch has no outbox because its first write is the log everything else derives from, and lesson 039 found the same about compensations. Arrange for a fact to be an append that other things read, and most of this lesson stops applying.
Check your understanding
Your service writes a row and then calls a search indexer's API to index it. A colleague says the outbox pattern does not apply, because there is no broker anywhere in the design. Say whether they are right, and what you would put in the table if you built one.
Marlow's founder proposes skipping the outbox in favour of a reconciliation job: every ten minutes, compare paid orders against the email provider's send log and republish the gaps. Argue both sides with today's figures, then say what you would ship and what the reconciliation job is still good for afterwards.
Your outbox drainer has been down for six hours and the table holds 400,000 unsent rows. Say what you would check before starting it again, and which earlier lessons own the two failures you are trying to avoid.
A team proposes draining the outbox with eight workers using
FOR UPDATE SKIP LOCKED, which lesson 035 named for queue-shaped work. Say what this fixes, what it breaks, and what you would need to know about the consumers before agreeing.Stagefront's change data capture reader is run by a different team than the one that owns the seat database. Say what you would require of that team before the next on-sale, and what you would monitor on the database side regardless of what they promise.
A colleague argues that an outbox is pointless for Galewatch because readings are already appends, and therefore pointless for the alarms Galewatch derives from those readings. Say where you agree, where the argument stops holding, and which lesson establishes the difference.
Next lesson
041 Backpressure: When the Consumer Cannot Keep Up. Today's drainer publishes as fast as it can read a table, which is fine until the thing on the other end cannot take it that fast; next lesson is what a system should do when the only honest answer is to slow down.