mufassall

Cheat sheet · Architecture

Transactional outbox

14 min · Advanced · mufassall.dtwin.uz/en/l/outbox-pattern/

← Open the lesson

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
Final scene
70%

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_broker

Mermaid: 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_slot

D2

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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Lost events0/day0/day
Duplicate credits0/day0/day
Out-of-order events0/day0/day
Commit to publish4 ms40 ms
Unsent outbox rows00
Rows in the outbox0900 000

Steps

  1. Case Save the purchase, then tell everyone

    The purchase service commits a purchase to its own database.

  2. Problem The commit succeeds, the publish does not

    At 21:14 the broker is slow.

  3. Fix Write the event into the same transaction

    The service stops talking to the broker.

  4. 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.

  5. Problem The relay restarts and credits points twice

    The relay pod is killed between publishing and marking.

  6. Fix The consumer remembers what it has applied

    The loyalty service records each event_id in processed_events in the same transaction as the credit.

  7. Problem The refund arrives before the purchase

    A second relay pod is added for throughput.

  8. 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.

  9. Problem The outbox keeps every event forever

    Nobody deletes sent rows.

  10. Fix Delete sent rows in small batches

    A job deletes rows sent more than 3 days ago, 5 000 at a time, every minute.

  11. 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.

  12. 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.