Databases & System Design•Part 5: Real-Time Architecture, Replication & Scale (51 - 60)
The Transactional Outbox Pattern:
In Simple Words:
Instead of writing to the database and sending an event to Kafka in two separate operations (which risks partial failure), write the event into an outbox table in the same atomic database transaction. A background worker or CDC process tails the table and publishes to Kafka.
sql
BEGIN;
-- 1. Mutate business state:
INSERT INTO orders (id, user_id, amount) VALUES ('ord_55', 'user_1', 199.99);
-- 2. Insert event in the SAME transaction:
INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload)
VALUES ('ORDER', 'ord_55', 'ORDER_CREATED', '{"amount": 199.99}');
COMMIT;