Save and announce together (outbox, CDC)

Many designs say: "The order service saves the order, then sends a message so the kitchen can start." The interviewer asks: "What if the server crashes between the save and the send?"

A weak answer says "we retry the send" and stops. The retry lives in the memory of a server that has crashed, so nothing remembers it. A strong answer says that the database and the message system cannot commit together. It then saves the message in the same database transaction as the order, and lets a second program send it afterwards. It also says that a message may then be sent more than once, so the receiver must skip repeats.

At SDE-1 level you should see the problem. At SDE-2 level you should explain how the message is saved with the order, and what that costs. At SDE-3 level you should compare the ways to send the saved messages, and say what can go wrong with each.

Quick recap (lessons 58 to 61). A broker is the server that stores messages. A producer sends a message to the broker. The broker sends the producer a confirm when it has stored the message. A consumer reads a message and sends an ack, which means "I finished". If the ack is lost, the broker sends the message again. So most systems deliver at-least-once: a message arrives one time or more. An event is a message that says something already happened, such as "order placed". The receiver must be idempotent: handling a message many times leaves the same result as handling it once.

Think about a shopkeeper. When a customer buys, she writes the sale in her book. Then she phones the warehouse to send the goods. If the shop closes in between, the warehouse never hears. If she phones first and then finds the book is locked, the warehouse sends goods for a sale that was never written. Two separate acts cannot be made one act.

The dual-write problem

A dual write is one action that writes to two places that cannot commit together. The common case is a database and a broker. In lesson 21 you saw that a transaction covers one database. It cannot include a call to the broker. So there are only two orders, and both can fail:

1. Save, then send. If the server crashes after the save and before the send, the order exists but nobody is told. This is a lost event. The same happens if the broker is down for a moment.
2. Send, then save. If the save fails after the send, other services act on an order that does not exist. This is a ghost event.

Sending inside the open transaction, just before the commit, does not fix it. The send can succeed and then the commit can fail. That gives a ghost event again.

A retry does not fix it either. A retry helps when the process is alive. In a crash, the process is gone, and nothing remembers that a message is still to be sent. The memory of "I must still send" must live in the database.

The outbox

An outbox is a table in the same database, where the service writes its messages before they are sent. The service now does one transaction (lesson 21):

BEGIN;
INSERT INTO orders (id, customer_id, total) VALUES (981, 42, 400);
INSERT INTO outbox (id, type, payload, sent)
VALUES (5001, 'OrderPlaced', '{"order_id": 981}', false);
COMMIT;

The payload column holds the body of the message. The sent column says if the message has gone out.

Both rows are saved, or neither is. If the server crashes before the commit, both are undone. If it crashes after the commit, the outbox row waits in the database. Nothing is lost.

A separate small program, the relay, sends the messages. It repeats these steps in a loop:

1. Read the rows where sent is false, from the smallest ID up, a few at a time.
2. Publish each message to the broker, and wait for the broker's confirm (lesson 60).
3. Mark the row as sent.

Three details matter:

• A repeat is possible. If the relay crashes after step 2 and before step 3, it sends the same row again after it restarts. So the outbox gives at-least-once delivery (lesson 60), not exactly-once. The row ID goes in the message as the event ID, and the consumer skips IDs it has seen (lesson 61). If the relay marked the row first, a crash would lose the message. That is at-most-once, and it brings the lost event back.
• Use a flag, not "the last ID I read". A row with a smaller ID can commit later than a row with a bigger ID. A relay that remembers only the last ID would skip it forever. The sent flag finds it on the next loop.
• Order. One relay sends the rows by ID. This is almost the order of the commits, but not exactly, because of the point above. If you run two relays, they must not take the same row. Postgres has FOR UPDATE SKIP LOCKED for this, but then the order is lost. Use the order ID as the message key (lesson 59). Then all messages of one order go to one partition and keep their order.

The outbox has a cost. Every business write has one more insert. The table grows, so a cleanup job must delete old sent rows. A message is sent a little later, after the next loop of the relay.

Change data capture

Polling the outbox table adds load and delay. Change data capture (CDC) is another way to find new rows. In lesson 21 you saw that the database writes every change to a log on disk before it confirms a commit. This is the write-ahead log. A CDC program reads that log and turns each committed change into a message. This program is a connector. For example, Debezium is a connector. It reads Postgres through a feature called logical decoding, and it reads MySQL through the binary log. This is the file where MySQL records its changes. Logical decoding gives the connector only changes that are committed.

CDC is used in two ways:

• With an outbox table. The connector watches only the outbox table. When the service inserts a row, the connector sends it. There is no polling loop and no sent flag.
• On the business tables. The connector sends a message for every row change in the orders table. This also announces changes made by other programs or by hand. But the message is a copy of the row, so if you rename a column, the consumers break.

CDC has its own costs:

• Setup. The database needs special settings, and someone must run and watch the connector.
• Repeats. The connector also gives at-least-once delivery. The consumer must still skip repeats.
• Disk. In Postgres, the connector holds a place in the log called a replication slot. The database keeps all log files after that place. If the connector stops, the log grows until the disk is full. The Postgres manual warns about this.

Polling or CDC

A food app. The order service saves each order and sends an "order placed" event to the kitchen.

Assumptions (made up to teach with, not real data):

• 200,000 orders per day.
• One order in 2,000 is hit by a crash or a broker error between the save and the send.
• The peak is 10 times the average rate.
• An outbox row is 500 bytes. The relay needs 5 ms to publish a row and 1 ms to mark it. It keeps rows for 3 days.

Step 1: the two broken orders. One in 2,000 of 200,000 orders: 200,000 / 2,000 = 100 orders per day. For the second case we use the same rate for a failed save.

• Save, then send: 100 orders are saved and the kitchen never hears. The customer waits for food that nobody cooks. These are 100 lost events. This answers the first half of the predict box.
• Send, then save: 100 times the kitchen hears of an order that was never saved. It cooks, but no order exists to deliver or to pay for. These are 100 ghost events. This answers the second half.

Step 2: the outbox. The service saves the order row and the outbox row in one transaction. A crash before the commit undoes both: the customer sees an error and tries again. A crash after the commit leaves both rows. Lost events per day: 0. Ghost events per day: 0.

Step 3: can the relay keep up?

• Average rate: 200,000 / 86,400 seconds = 2.3 orders per second.
• Peak rate: 10 x 2.3 = 23 orders per second.
• Relay speed: 5 ms + 1 ms = 6 ms per row. 1,000 ms / 6 ms = 166.7, about 167 rows per second.
• 167 / 23 = 7.2 times the peak. One relay is enough.
• Delay: if the relay loops every 200 ms, a message waits about 0.2 seconds.

Step 4: the broker is down for 10 minutes. The orders still work, because the rows wait in the database. Waiting rows: 600 seconds x 2.31 rows per second (the unrounded rate) = about 1,390 rows. The relay sends 167 rows per second and 2.31 new rows arrive, so it removes 167 - 2.31 = about 164 rows per second. Catch-up time: 1,390 / 164 = about 8.5 seconds.

Step 5: repeats. Assumption: the relay crashes 3 times a day. It handles one row at a time and marks it right after it publishes, so a crash can repeat at most 1 row. That is up to 3 repeats a day. The kitchen saves each event ID in a table with a unique constraint, in the same transaction as its own work (lesson 61). A repeat is skipped.

Step 6: size. 200,000 rows x 500 bytes = 100,000,000 bytes = 100 MB per day. For 3 days: 3 x 100 MB = 300 MB.

Step 7: the same design with CDC. A connector reads the log, so the polling loop is gone. Assumption: the whole database writes 2 MB of log per second, because it also serves many other tables. If the connector is down for 1 hour, the database must keep 2 x 3,600 = 7,200 MB = 7.2 GB of log. We need an alarm for a connector that falls behind.

• The outbox solves lost and ghost events. It costs one more insert for each write, a growing table, a small delay, and repeats. I would use it whenever a state change and a message must agree, and I would make the consumer idempotent.
• A polling relay solves the problem with a small program. It costs repeated reads and a delay of one loop. I would use it at small volume.
• CDC solves the polling load and the delay. It costs database settings, a connector to run, and a risk of a full disk when the connector stops. I would change to CDC when polling becomes slow or heavy, and I would watch the lag of the connector.

"I send the message inside the transaction, just before the commit, so both happen together." The broker is not part of the transaction. The send can work and the commit can fail. Then the message is a ghost event.

"The outbox gives exactly-once delivery." It gives at-least-once. The relay can crash after it publishes and before it marks the row. The consumer must skip repeats.

"If the send fails, my code retries, so nothing is lost." The retry is in the memory of the server. If the server crashes, the retry is gone. Only a row in the database survives a crash.

1. Why can one transaction not cover a database save and a broker send? Name the failure of each of the two orders.
2. A shop has 500,000 orders per day. One in 5,000 is hit between the save and the send. An outbox row is 400 bytes and rows are kept for 7 days. (a) How many lost events per day without an outbox? (b) How much storage does the outbox need? (c) The broker is down for 15 minutes. How many rows wait, at the average rate?
3. A teammate builds an outbox. The relay marks each row as sent first and then publishes, "to avoid repeats". What goes wrong? What do you change?