What is the dual write problem and why can't retries fix it?

A service that handles an order typically does two things: it inserts a row into its database and publishes an OrderCreated event to a broker such as Kafka. These are two separate systems with no shared transaction, so the service performs a dual write. Any crash, timeout, or rollback between the two steps can leave them disagreeing.

There are three failure cases:

  • Commit succeeds, publish fails. The order exists, but no downstream service ever learns about it. Inventory isn't reserved and no confirmation email goes out.
  • Publish succeeds, transaction rolls back. Consumers act on an order that doesn't exist. This happens when you publish before committing, or when the commit itself fails on a constraint violation or deadlock.
  • Publish times out. The broker may or may not have written the message. Retrying risks a duplicate. Not retrying risks a loss. The service can't tell which.

Retries only help while the process is alive. If the pod is killed after the commit but before the publish succeeds, the pending event exists only in memory and disappears. Reordering the steps just exchanges lost events for phantom ones.

Two-phase commit across the database and the broker would solve this in theory. In practice, most brokers don't take part in XA transactions. Where 2PC is available, it adds blocking coordination, a coordinator that can fail, and lower throughput.

The outbox pattern takes a different approach. It removes the second write from the request path entirely.

How does the transactional outbox pattern with Debezium work?

When the service handles a request, it opens one local database transaction. Inside it, the service updates its business tables (for example, inserting an orders row) and inserts a row into an outbox table that describes the event, such as OrderCreated with its payload. The database's atomicity guarantee covers both writes, so either both commit or neither does. An order can no longer exist without its event, and an event can no longer exist for an order that was rolled back.

Getting that event row to Kafka is where change data capture comes in. Debezium does not poll the outbox table with SELECT queries. Polling adds latency, loads the database, and makes it easy to miss rows. Instead, Debezium reads the database's transaction log: the PostgreSQL write-ahead log through logical decoding, or the MySQL binlog. The log contains only committed changes, in commit order, so Debezium never sees a row from a transaction that rolled back. Debezium runs as a Kafka Connect source connector, and Kafka Connect publishes the captured rows to Kafka topics.

 Service ──(one transaction)──► Database
                                orders + outbox
                                      │
                                      ▼
                       Transaction log (WAL / binlog)
                                      │
                                      ▼
                       Debezium on Kafka Connect
                                      │
                                      ▼
                            Kafka topics ──► Consumers

The trade-off is that the database becomes the single source of truth, but delivery is asynchronous and at-least-once. Consumers see events after a delay. After a connector restart, they may receive events that were already delivered.

+-----------------+
|  Order service  |
+-----------------+
         |
         | one local transaction (both rows or neither)
         v
+----------------------------------------+
| Database (PostgreSQL / MySQL)          |
|  +-------------+   +--------------+    |
|  | orders      |   | outbox       |    |
|  +-------------+   +--------------+    |
|  only committed changes, commit order  |
|                  v                     |
|  Transaction log: WAL / binlog         |
+----------------------------------------+
         |
         | reads the log (no SELECT polling)
         v
+----------------------------------------+
| Kafka Connect                          |
|  +--------------------------------+    |
|  | Debezium source connector      |    |
|  +--------------------------------+    |
+----------------------------------------+
         |
         | publishes captured outbox rows
         v
+----------------+     +----------------------------+
|  Kafka topics  |---->|  Consumers: at-least-once  |
+----------------+     +----------------------------+

The service writes the business row and the outbox row in one transaction, and Debezium on Kafka Connect reads the committed changes from the WAL or binlog and publishes them to Kafka topics, from which consumers receive events asynchronously and at least once.

How do you design the outbox table and event payload?

A minimal outbox table needs six columns:

  • id: a UUID generated by the application. Consumers use it to deduplicate redeliveries, so it must be unique and stable across retries.
  • aggregatetype: the kind of entity, such as order. The event router uses it to choose the destination topic.
  • aggregateid: the entity's identifier. It becomes the Kafka message key, so all events for one order land in the same partition and stay in order.
  • type: the event name, such as OrderPlaced.
  • payload: the event body, usually JSON or JSONB.
  • created_at: when the event was written. It is useful for debugging and for cleanup.

The application writes both rows in one database transaction. It inserts or updates the orders row, inserts the matching outbox row, and commits. If either statement fails, both roll back. No code path can update the business data without also recording the event.

Treat the payload as a public contract, not a serialized copy of your ORM entity. If you dump internal state, every column rename or refactor becomes a breaking change for consumers you may not know about. Include only the fields that consumers need, and put a schemaVersion or versioned event type in every message.

This design has costs:

  • Coupling to the write path: building the payload runs inside the write transaction, so slow serialization holds locks longer.
  • Versioning discipline: schema changes need additive evolution or a new version, and you need a way to retire old versions.
  • Write amplification: a large payload is written to the table and to the write-ahead log, which adds I/O on the primary for every business write.
-- PostgreSQL: outbox table using column names the Debezium outbox event router expects by default
CREATE TABLE outbox (
  id            uuid        PRIMARY KEY,           -- generated by the app; consumers dedupe on it
  aggregatetype text        NOT NULL,              -- routes to a topic, e.g. 'order'
  aggregateid   text        NOT NULL,              -- becomes the Kafka message key (per-order ordering)
  type          text        NOT NULL,              -- event name, e.g. 'OrderPlaced'
  payload       jsonb       NOT NULL,              -- public, versioned contract, not entity state
  created_at    timestamptz NOT NULL DEFAULT now() -- debugging and cleanup
);

-- Business row and event row commit or roll back together
BEGIN;

INSERT INTO orders (id, customer_id, status, total_cents)
VALUES ('ord-1042', 'cust-77', 'PLACED', 4999);

INSERT INTO outbox (id, aggregatetype, aggregateid, type, payload)
VALUES (
  '3f2b8c1e-9a4d-4e6f-b1c2-7d8e9f0a1b2c',  -- UUID from the application, reused on retry
  'order',
  'ord-1042',
  'OrderPlaced',
  -- Only fields consumers need, plus an explicit schema version
  '{"schemaVersion": 1, "orderId": "ord-1042", "customerId": "cust-77", "totalCents": 4999, "currency": "USD"}'
);

COMMIT;

A PostgreSQL outbox table and a single transaction that writes the order row and its versioned OrderPlaced event together, so the business change and the event commit or roll back as one unit.

How do you configure Debezium and the outbox event router?

Database prerequisites

Debezium reads the database's change log, so the log must carry full row data. On PostgreSQL, set wal_level = logical and leave headroom in max_replication_slots and max_wal_senders. Changing wal_level requires a restart. The connector uses the built-in pgoutput plugin and creates a replication slot plus a publication. The slot is not free: if the connector stops, the primary retains WAL until the slot is consumed or dropped, and disk usage grows without bound. On MySQL, enable the binlog with binlog_format = ROW and binlog_row_image = FULL, and give the connector a unique server ID.

CDC needs more privileges than an application account. PostgreSQL requires the REPLICATION attribute. MySQL requires REPLICATION SLAVE, REPLICATION CLIENT, SELECT, and usually RELOAD. Managed database services may lock these settings behind parameter groups or vendor-specific roles, or not support them at all. Have your DBA and security owners review the change before you build on it.

Connector configuration

Point the connector at only the outbox table with table.include.list, for example public.outbox. This keeps business tables out of Kafka and keeps your internal schema private. On PostgreSQL, publication.autocreate.mode = filtered limits the publication to that table.

Then apply the outbox SMT. Set transforms = outbox and set transforms.outbox.type to io.debezium.transforms.outbox.EventRouter. Configure these properties:

  • route.by.field = aggregatetype: selects a destination topic for each event based on its aggregate type.
  • route.topic.replacement (for example outbox.event.${routedByValue}): sets the topic naming scheme.
  • table.field.event.key = aggregateid: uses the aggregate ID as the Kafka message key. All events for one aggregate land on the same partition, which gives you per-aggregate ordering.
# Debezium PostgreSQL connector that captures only the outbox table
name=orders-outbox-connector
connector.class=io.debezium.connector.postgresql.PostgresConnector
tasks.max=1

# Connection; the user needs the REPLICATION attribute
database.hostname=orders-db.internal
database.port=5432
database.user=debezium_cdc
database.password=change-me
database.dbname=orders
topic.prefix=orders

# Built-in logical decoding plugin, a named slot, and a publication
# limited to the captured table
plugin.name=pgoutput
slot.name=orders_outbox_slot
publication.name=orders_outbox_pub
publication.autocreate.mode=filtered

# Capture only the outbox table so business tables stay out of Kafka
table.include.list=public.outbox

# Outbox rows are inserted then deleted, so skip delete tombstones
tombstones.on.delete=false

# Outbox event router SMT
transforms=outbox
transforms.outbox.type=io.debezium.transforms.outbox.EventRouter
# Pick the destination topic from the aggregatetype column
transforms.outbox.route.by.field=aggregatetype
# For example, aggregatetype 'order' is routed to outbox.event.order
transforms.outbox.route.topic.replacement=outbox.event.${routedByValue}
# Use the aggregate ID as the Kafka message key so each aggregate's events stay ordered
transforms.outbox.table.field.event.key=aggregateid

This Kafka Connect configuration captures only public.outbox through a filtered pgoutput publication. Debezium's EventRouter then sends each event to a topic named after its aggregate type and keys the message by aggregate ID.

What ordering and delivery guarantees should consumers expect?

Per-aggregate ordering, and nothing more. Kafka guarantees order only within a partition. Events for order-42 arrive in commit order, but there is no global ordering across aggregates. A consumer can see order-43 created before order-42 shipped even if the database committed them the other way round. The per-aggregate guarantee breaks if you add partitions to the topic or change the key strategy. Either change can route the same aggregate ID to a different partition while older events are still unconsumed, so new events may be processed before old ones. Plan those changes as migrations, not config tweaks. Workflows that span aggregates, such as reserving stock and then charging a payment, cannot rely on topic ordering. They need a saga or an explicit correlation ID that consumers use to join related events.

Delivery is at-least-once. Debezium records its position in the database log through Kafka Connect offsets, which are committed periodically. After a connector restart or a worker rebalance, it resumes from the last committed offset and re-emits anything published after that point. Consumers have the same window between processing a message and committing their own offset. Duplicates are normal behavior, not a sign of a fault.

Make consumers idempotent. In the same local transaction that applies the side effect, insert the event's id into a processed_events table that has a unique constraint on that column. If the insert hits a conflict, the event was already handled, so roll back or skip it and commit the offset.

Finally, downstream services are eventually consistent. A client that writes through one service and immediately reads from another may not see its own write. Design APIs and UIs to tolerate that gap.

import json
import psycopg2
from confluent_kafka import Consumer

# Table (created once):
#   CREATE TABLE processed_events (event_id UUID PRIMARY KEY,
#     processed_at TIMESTAMPTZ NOT NULL DEFAULT now());

consumer = Consumer({
    "bootstrap.servers": "kafka:9092",
    "group.id": "shipping-service",
    "enable.auto.commit": False,  # commit only after the DB transaction succeeds
    "auto.offset.reset": "earliest",
})
consumer.subscribe(["outbox.event.order"])
db = psycopg2.connect("host=db dbname=shipping user=shipping password=change-me")

def apply_side_effect(cur, event):
    cur.execute("INSERT INTO shipments (order_id, status) VALUES (%s, 'pending')",
                (event["orderId"],))

while True:
    msg = consumer.poll(1.0)
    if msg is None:
        continue
    if msg.error():
        raise RuntimeError(msg.error())

    # The outbox event router puts the event id in the "id" header by default.
    # Strip quotes in case the header converter serialized it as a JSON string.
    event_id = dict(msg.headers() or [])["id"].decode().strip('"')
    event = json.loads(msg.value())

    with db:  # one local transaction: commit on success, rollback on exception
        with db.cursor() as cur:
            cur.execute("INSERT INTO processed_events (event_id) VALUES (%s) "
                        "ON CONFLICT (event_id) DO NOTHING", (event_id,))
            if cur.rowcount == 1:  # first time we see this id
                apply_side_effect(cur, event)
            # rowcount == 0 means a duplicate redelivery: skip the side effect

    consumer.commit(message=msg, asynchronous=False)  # safe to advance either way

An idempotent Kafka consumer that records each outbox event id in a processed_events table in the same transaction as its side effect, skipping redelivered duplicates and committing the offset only after the database commit.

How do you keep the outbox table small and operate the pipeline?

Cleanup comes in two forms, and the choice affects auditability, replay, and database load.

Strategy Auditability Replay from database Database load
Delete in the same transaction None in the table. History lives only in the WAL and Kafka. Not possible. Replay depends on topic retention. Lowest. No bloat and no batch deletes.
Scheduled cleanup job Rows remain queryable until purged. Possible within the retention window. Table growth, plus periodic delete and vacuum work.

Deleting immediately works because Debezium reads the insert from the log, not from the table. The connector also emits the delete, and the event router acts only on inserts, so the delete produces no extra event. A scheduled job gives you a debugging window, but you need an index on the creation timestamp and small batches to avoid long locks and vacuum pressure.

Operationally, monitor three things:

  • Connector and task state. Check it through the Kafka Connect REST API.
  • Replication lag. Compare the slot's confirmed position in pg_replication_slots with the current WAL position.
  • Inactive slots. An inactive slot with growing retained WAL is the most dangerous signal. PostgreSQL keeps WAL for that slot indefinitely, which can fill the primary's disk. Alert on it, and drop slots for decommissioned connectors.

Kafka Connect stores offsets under the connector name. If you redeploy with the same name, the connector resumes from those offsets. If you rename it or delete its offsets, the snapshot.mode setting decides whether it re-snapshots.

Failover is where CDC breaks. If the logical slot does not exist on the promoted standby, or MySQL binlog positions differ across hosts without GTIDs, the connector cannot resume where it stopped. It either re-snapshots, skips events (gaps), or re-reads them (duplicates). Test failover before you rely on it.

When should you avoid the outbox pattern, and what are the alternatives?

The pattern pays off when several services publish events from a relational database and your team already runs Kafka Connect, or can staff it. It is a poor fit in four cases:

  • A single service with no downstream consumers.
  • Low-stakes notifications where an occasional lost message is acceptable.
  • Managed databases that restrict replication slots or binlog access.
  • Teams that cannot take on another on-call surface.
Alternative How it works Main trade-off
Direct publish after commit Commit, then send to the broker A crash between the two steps loses the event
Polling outbox relay A worker queries the outbox table on an interval Adds latency and database load, but needs no log access
Event sourcing The event store is the source of truth Forces a deep redesign of how state is modelled
Listen-to-yourself Publish first, then consume your own event to update the database Reads lag behind writes
Broker transactions Atomic writes across topics Does not cover your database unless it shares the transaction

Development cost. Debezium is not free to adopt. You must configure connectors and choose where offsets are stored. You must also handle schemas, plan upgrades for both the connector and the database, and assign someone to own failures at 3 a.m.

Where time is saved. The cost buys back three things:

  • Fewer bespoke retry and reconciliation jobs.
  • Fewer incidents caused by data drift between services.
  • One publishing path that every service reuses.

Illustrative estimate: assume the platform setup takes three engineer-weeks, and each service would otherwise need one week to build its own retry logic. Under those assumptions, the shared pipeline breaks even at the third or fourth service.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to transactional outbox pattern debezium, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.