1. The Dual-Write Problem

Imagine a service that creates an order and publishes an OrderCreated event to Kafka so downstream services (inventory, notifications, analytics) can react. There are two operations:

  1. INSERT order into orders table
  2. Publish OrderCreated event to Kafka topic

These are two separate operations that can fail independently. If you write to the DB first and the Kafka publish fails (broker down, network issue), the event is silently lost — downstream services never know the order was created. If you publish to Kafka first and the DB insert fails, you have an event for an order that does not exist.

  NAIVE DUAL-WRITE (broken):

  Service:
    1. INSERT INTO orders ...        ← DB write succeeds
    2. kafka.publish("OrderCreated") ← network blip!  FAILS
       Event lost. Inventory never notified.
       Order stuck in PENDING state forever.

  OR:
    1. kafka.publish("OrderCreated") ← published
    2. INSERT INTO orders ...        ← DB write fails
       Event exists, order does not exist.
       Inventory reserves stock for ghost order.

2. The Outbox Pattern Solution

The Outbox pattern introduces an outbox table in the same database as the business data. Instead of publishing directly to Kafka, the service inserts the event into the outbox table within the same database transaction as the business write. A separate relay process then reads from the outbox and publishes to Kafka.

  OUTBOX PATTERN FLOW:

  Service (within a single DB transaction):
    ┌─────────────────────────────────────────┐
    │  BEGIN TRANSACTION                      │
    │  INSERT INTO orders (...)               │  ← business write
    │  INSERT INTO outbox                     │  ← event write
    │    (event_type='OrderCreated',          │
    │     payload='{"order_id":42,...}',      │
    │     status='PENDING')                   │
    │  COMMIT                                 │  ← atomic: both or neither
    └─────────────────────────────────────────┘

  Outbox Relay (separate process):
    POLL: SELECT * FROM outbox WHERE status='PENDING' LIMIT 100
       → Publish each event to Kafka
       → UPDATE outbox SET status='SENT' WHERE id=...

3. Outbox Table Schema

SQL — outbox table schema CREATE TABLE outbox ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, aggregate_id VARCHAR(64) NOT NULL, -- e.g. order_id aggregate_type VARCHAR(50) NOT NULL, -- e.g. 'Order' event_type VARCHAR(100) NOT NULL, -- e.g. 'OrderCreated' payload JSON NOT NULL, -- event body status ENUM('PENDING','SENT','FAILED') NOT NULL DEFAULT 'PENDING', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME NULL, PRIMARY KEY (id), KEY idx_status_created (status, created_at) -- for relay polling query ) ENGINE=InnoDB; -- Application code (within same transaction as order insert): INSERT INTO outbox (aggregate_id, aggregate_type, event_type, payload) VALUES (42, 'Order', 'OrderCreated', '{"order_id":42,"user_id":7,"total":9999}');

4. Outbox Relay: Polling vs CDC

There are two ways to implement the outbox relay that reads from the outbox table and publishes to the message broker:

Option A: Polling

A background job (cron or loop) periodically queries the outbox for PENDING rows, publishes them to Kafka, and marks them as SENT. Simple to implement, but adds SELECT query load to the database and has inherent latency equal to the polling interval.

PHP — simple polling-based outbox relay // Run as a background worker (e.g. every 1 second via supervisor) while (true) { $pdo = new PDO($dsn, $user, $pass); $rows = $pdo->query( "SELECT * FROM outbox WHERE status='PENDING' ORDER BY created_at LIMIT 100" )->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $published = publishToKafka($row['event_type'], $row['payload']); if ($published) { $pdo->prepare( "UPDATE outbox SET status='SENT', sent_at=NOW() WHERE id=?" )->execute([$row['id']]); } } sleep(1); }

Option B: Change Data Capture (Debezium)

Debezium is an open-source CDC platform that reads the database transaction log (MySQL binlog, PostgreSQL WAL) and streams changes to Kafka. When a row is inserted into the outbox table, Debezium immediately produces a Kafka message — with near-zero latency and no polling load on the database. This is the recommended approach for production systems.

Debezium Outbox Event Router

Debezium has a built-in Outbox Event Router SMT (Single Message Transform) that routes outbox CDC events to the correct Kafka topic based on the aggregate_type and event_type fields. This means you get automatic topic routing without writing a custom relay. Configure it in your Debezium connector JSON with "transforms": "outbox", "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter".

5. Relay vs CDC Comparison

PropertyPolling RelayCDC (Debezium)
LatencyPolling interval (seconds)Near-realtime (milliseconds)
DB loadAdds SELECT queriesReads log, no extra queries
ComplexityLow (just a loop)Medium (Debezium + Kafka connector setup)
ThroughputLimited by poll frequencyHigh (log-based streaming)
DB supportAnyMySQL, PostgreSQL, MongoDB, SQL Server, etc.
Best forLow throughput, simple setupsHigh throughput, production microservices

6. The Inbox Pattern — Idempotent Consumers

The Outbox pattern guarantees at-least-once delivery — an event may be published more than once if the relay crashes after publishing but before marking the row as SENT. Consumer services must handle duplicate events. The Inbox pattern provides this guarantee:

  • The consumer service maintains an inbox table with a UNIQUE constraint on event_id
  • When processing an event, the service inserts the event_id into the inbox table within the same transaction as the business logic
  • If the same event arrives again, the INSERT fails with a UNIQUE violation — the event is skipped
  • This achieves exactly-once processing semantics at the application level, even with at-least-once message delivery

Outbox Table Cleanup

The outbox table grows indefinitely if you never delete processed rows. Run a periodic cleanup job to delete rows older than your retention window (e.g. DELETE FROM outbox WHERE status='SENT' AND sent_at < NOW() - INTERVAL 7 DAY LIMIT 10000). Use batched deletes to avoid locking. Alternatively, partition the outbox table by month so old partitions can be dropped instantly without row-level deletes.

How We Research and Update This Guide

We test the underlying formula or workflow, compare outputs with reliable references, and revise examples whenever the page content changes.

  • The workflow or formula is tested directly in the tool and compared against independent reference examples.
  • Examples are kept practical so readers can verify the result without hidden assumptions.
  • Pages are revised whenever the interface, calculation flow, or surrounding guidance materially changes.

Frequently Asked Questions — Outbox Pattern