Cheat sheet · Architecture
Transactional outbox
The problem
A shop saves a purchase in its database and then publishes an event so the loyalty service can credit points. Two writes to two systems with no shared transaction: now and then the commit succeeds and the publish does not, and the points are simply gone. The outbox writes the event into the same database in the same transaction, a relay delivers it at least once, the consumer drops duplicates, and keys, cleanup and log tailing deal with the rest.
What you learn
- Why "commit, then publish" loses events in either order, and how an outbox table in the same transaction closes the gap
- How a relay polls the outbox with
FOR UPDATE SKIP LOCKED, and why it can only promise at-least-once delivery - Consumer idempotency: a processed-events table committed together with the effect
- Order per aggregate with a message key, cleaning up sent rows, and polling versus reading the database log
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_app(["Shop app"])
n_api["Purchase service"]
n_db[("Purchases DB")]
n_broker[["Broker"]]
n_loyalty["Loyalty service"]
n_ldb[("Loyalty DB")]
n_relay("Outbox relay")
n_app -->|"call"| n_api
n_api --> n_db
n_broker -->|"async"| n_loyalty
n_loyalty --> n_ldb
n_relay --> n_db
n_relay -->|"async"| n_brokerMermaid: request sequence
sequenceDiagram
participant n_api as Purchase service
participant n_broker as Broker
participant n_db as Purchases DB
participant n_relay as Outbox relay
participant n_loyalty as Loyalty service
participant n_ldb as Loyalty DB
Note over n_api,n_broker: Save the purchase, then tell everyone
n_api->>n_broker: POST /v1/purchases -#62; purchase p_9051
Note over n_api,n_broker: The commit succeeds, the publish does not
n_api->>n_broker: 21:14:07.120 p_9188 COMMIT ok
Note over n_api,n_db: Write the event into the same transaction
n_api->>n_db: BEGIN#59;
n_db-->>n_api: COMMIT 3 ms (both rows or none)
Note over n_relay,n_db: A relay publishes what the outbox holds
n_relay->>n_db: SELECT id, aggregate_id, type, payload FROM outbox
Note over n_broker,n_loyalty: The relay restarts and credits points twice
n_broker->>n_loyalty: 21:30:01 loyalty evt_01HV9Q PurchaseCompleted p_9188 +249 points
Note over n_loyalty,n_ldb: The consumer remembers what it has applied
n_loyalty->>n_ldb: BEGIN#59;
n_ldb-->>n_loyalty: INSERT 0 0 -#62; duplicate, skipped
Note over n_broker,n_loyalty: The refund arrives before the purchase
n_broker->>n_loyalty: partition 3 offset 88120 PurchaseRefunded p_9188
Note over n_relay,n_broker: One key per purchase, one partition per key
n_relay->>n_broker: produce topic=purchase-events
n_broker-->>n_relay: ack partition 3 offset 88121
Note over n_relay,n_db: The outbox keeps every event forever
n_relay->>n_db: EXPLAIN ANALYZE SELECT ... FROM outbox WHERE sent_at IS NULL LIMIT 200#59;
n_db-->>n_relay: Index Scan using outbox_unsent (rows=200)
Note over n_relay,n_db: Delete sent rows in small batches
n_relay->>n_db: DELETE FROM outbox
n_db-->>n_relay: DELETE 5000
Note over n_relay,n_db: Or read the outbox from the database log
n_relay->>n_db: relay mode=log slot outbox_slotD2
direction: right
n_app: "Shop app" {
shape: rectangle
}
n_api: "Purchase service" {
shape: rectangle
}
n_db: "Purchases DB" {
shape: cylinder
}
n_broker: "Broker" {
shape: queue
}
n_loyalty: "Loyalty service" {
shape: rectangle
}
n_ldb: "Loyalty DB" {
shape: cylinder
}
n_relay: "Outbox relay" {
shape: rectangle
style.multiple: true
}
n_app -> n_api: "call"
n_api -> n_db
n_broker -> n_loyalty: "async"
n_loyalty -> n_ldb
n_relay -> n_db
n_relay -> n_broker: "async"PlantUML: request sequence
@startuml
participant "Purchase service" as n_api
queue "Broker" as n_broker
database "Purchases DB" as n_db
participant "Outbox relay" as n_relay
participant "Loyalty service" as n_loyalty
database "Loyalty DB" as n_ldb
note over n_api, n_broker : Save the purchase, then tell everyone
n_api ->> n_broker : POST /v1/purchases -> purchase p_9051
note over n_api, n_broker : The commit succeeds, the publish does not
n_api ->> n_broker : 21:14:07.120 p_9188 COMMIT ok
note over n_api, n_db : Write the event into the same transaction
n_api -> n_db : BEGIN;
n_db --> n_api : COMMIT 3 ms (both rows or none)
note over n_relay, n_db : A relay publishes what the outbox holds
n_relay -> n_db : SELECT id, aggregate_id, type, payload FROM outbox
note over n_broker, n_loyalty : The relay restarts and credits points twice
n_broker ->> n_loyalty : [duplicate] 21:30:01 loyalty evt_01HV9Q PurchaseCompleted p_9188 +249 points
note over n_loyalty, n_ldb : The consumer remembers what it has applied
n_loyalty -> n_ldb : BEGIN;
n_ldb --> n_loyalty : INSERT 0 0 -> duplicate, skipped
note over n_broker, n_loyalty : The refund arrives before the purchase
n_broker ->> n_loyalty : partition 3 offset 88120 PurchaseRefunded p_9188
note over n_relay, n_broker : One key per purchase, one partition per key
n_relay ->> n_broker : produce topic=purchase-events
n_broker -->> n_relay : ack partition 3 offset 88121
note over n_relay, n_db : The outbox keeps every event forever
n_relay -> n_db : EXPLAIN ANALYZE SELECT ... FROM outbox WHERE sent_at IS NULL LIMIT 200;
n_db --> n_relay : Index Scan using outbox_unsent (rows=200)
note over n_relay, n_db : Delete sent rows in small batches
n_relay -> n_db : DELETE FROM outbox
n_db --> n_relay : DELETE 5000
note over n_relay, n_db : Or read the outbox from the database log
n_relay -> n_db : relay mode=log slot outbox_slot
@endumlPlantUML: components
@startuml
left to right direction
agent "Shop app" as n_app
component "Purchase service" as n_api
database "Purchases DB" as n_db
queue "Broker" as n_broker
component "Loyalty service" as n_loyalty
database "Loyalty DB" as n_ldb
collections "Outbox relay" as n_relay
n_app --> n_api : call
n_api --> n_db
n_broker --> n_loyalty : async
n_loyalty --> n_ldb
n_relay --> n_db
n_relay --> n_broker : async
@endumlRecap
Six rules for the outbox.
1) Never write to the database and the broker as two steps: either order loses or invents events. 2) Insert the event into an outbox table in the same transaction as the data. 3) A relay publishes unsent rows and marks them after the ack; that is at-least-once, never exactly-once. 4) Consumers dedup by event id in the same transaction as the effect. 5) Key messages by aggregate id and split relays by key, so one aggregate's events stay in order. 6) Delete or drop sent rows, and choose polling for simplicity or log tailing for low delay.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Lost events | 0/day | 0/day |
| Duplicate credits | 0/day | 0/day |
| Out-of-order events | 0/day | 0/day |
| Commit to publish | 4 ms | 40 ms |
| Unsent outbox rows | 0 | 0 |
| Rows in the outbox | 0 | 900 000 |
Steps
- Case Save the purchase, then tell everyone
The purchase service commits a purchase to its own database.
- Problem The commit succeeds, the publish does not
At 21:14 the broker is slow.
- Fix Write the event into the same transaction
The service stops talking to the broker.
- Fix A relay publishes what the outbox holds
A small worker, the relay, reads unsent rows every 500 ms, publishes them, waits for the broker acks and marks the rows sent.
- Problem The relay restarts and credits points twice
The relay pod is killed between publishing and marking.
- Fix The consumer remembers what it has applied
The loyalty service records each
event_idinprocessed_eventsin the same transaction as the credit. - Problem The refund arrives before the purchase
A second relay pod is added for throughput.
- Fix One key per purchase, one partition per key
The relay sets the message key to the purchase id, and each relay pod takes only the keys whose hash falls into its share.
- Problem The outbox keeps every event forever
Nobody deletes sent rows.
- Fix Delete sent rows in small batches
A job deletes rows sent more than 3 days ago, 5 000 at a time, every minute.
- Fix Or read the outbox from the database log
Instead of polling, the relay can follow the database's write-ahead log through a replication slot, as CDC does.
- Recap Recap
Six rules for the outbox.
Terms
- Outbox relay
- A worker that reads unsent rows from the outbox table, publishes them to the broker and marks them sent after the ack. If it crashes before marking, the rows go out again, so delivery is at-least-once.
- Inbox (processed events) table
- A table where the consumer records every applied event id under a unique key, in the same transaction as the effect. A repeated delivery hits the key and is skipped.
- Message key (partition key)
- The value the broker uses to pick a partition for a message, usually the aggregate id. Messages with the same key land in one partition and keep their order; across different keys there is no order.
- Aggregate version
- An event's sequence number within its aggregate: 1, 2, 3. The consumer stores the last version it applied, drops older ones and waits for the missing one when there is a gap.