The dual-write problem has an odd shape: you can’t solve it, only avoid it. No protocol, retry strategy or error handler makes “write to Postgres AND publish to Kafka” atomic; the earlier article in this series walked through why every attempt fails.

The transactional outbox is the industry’s standard answer, and it works by refusing to play. Don’t write to two systems. Write twice to one system, atomically, and let something else carry the second write out later.

Your database already has the atomicity you need. It’s called a transaction. The outbox spends that guarantee to buy the one you’re missing.

The Pattern in Three Moves

Chris Richardson’s pattern catalog, AWS Prescriptive Guidance and Gunnar Morling’s Debezium post from 2019 all describe the same three moves:

  1. Write the event into an outbox table, inside the same transaction as the business change. One commit: both rows or neither. The dual write is gone by construction.
  2. A relay reads the outbox and publishes each event to the broker, marking it as delivered only after the broker confirms.
  3. Consumers dedupe, because the relay delivers at least once. AWS says it plainly: “The events processing service might send out duplicate messages or events, so we recommend that you make the consuming service idempotent by tracking the processed messages.”

The failure analysis that used to be scary becomes boring. Crash after the commit but before publishing? The event waits in the outbox and goes out on the relay’s next pass. Crash after publishing but before marking? It’s published again and consumers dedupe. Transaction rolls back? The event row rolls back with it, which is exactly AWS’s rule: “Do not send out an event notification if the transaction is rolled back.”

The Schema and the Writer

A minimal, production-shaped outbox table in PostgreSQL:

CREATE TABLE outbox (
    id           BIGSERIAL PRIMARY KEY,   -- relay order
    event_id     UUID NOT NULL,           -- consumers dedupe on this
    aggregate_id TEXT NOT NULL,           -- e.g. the order ID; broker partition key
    event_type   TEXT NOT NULL,           -- "OrderPlaced"
    payload      JSONB NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at TIMESTAMPTZ              -- NULL = pending
);

CREATE INDEX outbox_pending ON outbox (id) WHERE published_at IS NULL;

The partial index keeps the relay’s query fast no matter how much history piles up. The writer side is almost disappointing: from the producer’s point of view, the whole pattern is one more INSERT in the transaction.

func (s *OrderService) PlaceOrder(ctx context.Context, o Order) error {
    payload, err := json.Marshal(OrderPlaced{OrderID: o.ID, Total: o.Total})
    if err != nil {
        return err
    }

    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    if err := insertOrder(ctx, tx, o); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx,
        `INSERT INTO outbox (event_id, aggregate_id, event_type, payload)
         VALUES ($1, $2, $3, $4)`,
        uuid.NewString(), o.ID, "OrderPlaced", payload); err != nil {
        return err
    }

    return tx.Commit() // order + event: one atomic decision
}

The Relay: Where the Real Decisions Live

Everything interesting about an outbox lives in the relay, and the first decision is polling or change data capture.

Polling: the version to build first

A background worker reads pending rows, publishes them and marks them done. The code is short. The subtle part is ordering, and most examples you’ll find online get it wrong in one of two ways.

Mistake one: polling with a cursor. WHERE id > $last_seen_id looks efficient and silently loses events. IDs from a sequence are assigned at insert time, not at commit time. A transaction that grabbed id 1041 and commits a second after the one holding 1042 lands behind your cursor, and the relay skips it forever. Martin Kleppmann flagged this, Morling covers it in his 2024 retrospective, and Oskar Dudycz has a detailed write-up. Polling on published_at IS NULL sidesteps it: late commits are simply picked up on the next pass.

Mistake two: parallel relays with SKIP LOCKED. It’s the popular recipe for running several relay instances, and it breaks per-aggregate order. Relay A claims a batch containing OrderPaid for order 7, relay B claims the next batch containing OrderShipped for the same order, B is faster, and your consumers see the order shipped before it was paid. Per-aggregate order is the one ordering guarantee the business almost always needs.

The fix is boring: one active relay at a time, with standbys waiting on a Postgres advisory lock. The lock belongs to a database session, so if the leader’s process or connection dies, the lock is released and a standby takes over.

// Leader election: whoever holds this session-level lock is the relay.
// Standbys retry every few seconds. Pin the *sql.Conn: advisory locks
// belong to one session, not to the pool.
var leader bool
err := conn.QueryRowContext(ctx, `SELECT pg_try_advisory_lock(42001)`).Scan(&leader)

The leader’s drain loop, on that same pinned connection:

func (r *Relay) drainOnce(ctx context.Context, conn *sql.Conn) (int, error) {
    rows, err := conn.QueryContext(ctx, `
        SELECT id, event_id, aggregate_id, event_type, payload
        FROM outbox
        WHERE published_at IS NULL
        ORDER BY id
        LIMIT 100`)
    if err != nil {
        return 0, err
    }
    events, err := scanEvents(rows) // reads every row, closes rows, checks rows.Err()
    if err != nil {
        return 0, err
    }

    var done []int64
    var pubErr error
    for _, e := range events {
        // Aggregate ID as partition key: per-aggregate order survives the broker.
        if pubErr = r.broker.Publish(ctx, e.Type, e.AggregateID, e.EventID, e.Payload); pubErr != nil {
            break // stop here, so a later event for the same aggregate can't overtake this one
        }
        done = append(done, e.ID)
    }

    if len(done) > 0 {
        // pgx's stdlib driver encodes []int64 as an array; with lib/pq, wrap it in pq.Array.
        if _, err := conn.ExecContext(ctx,
            `UPDATE outbox SET published_at = now() WHERE id = ANY($1)`, done); err != nil {
            return len(done), err // they'll be republished next pass; consumers dedupe
        }
    }
    return len(done), pubErr
}

Two details worth copying. There’s no transaction wrapped around the publish, so you never hold row locks across network I/O. And publishing stops at the first failure instead of skipping ahead, which keeps order at the cost of briefly blocking everything behind it. You can get fancier and skip only the failing aggregate; start here.

This keeps per-aggregate order as long as writes to the same aggregate are serialized, which they should be anyway: that’s what the row lock on the order is for. Events for different aggregates may leave in a slightly different order than they committed. That’s almost never a business problem. If one relay becomes too slow, partition by a hash of the aggregate ID so that each aggregate belongs to exactly one relay.

Run drainOnce in a loop: go again immediately when it returned a full batch, sleep 200 ms–1 s with jitter when it’s idle (you know why). The cost of polling is a latency floor equal to the interval plus constant light query load. For most systems that’s invisible, and the entire relay is a hundred lines you fully understand.

CDC: the version you graduate to

The alternative reads events from the database’s write-ahead log instead of querying the table. Morling, who led the Debezium project, argues in the same retrospective that log-based CDC “should be the preferred option whenever possible”: the WAL hands you events in exact commit order, there’s no polling load, and he puts typical Postgres-to-Kafka latency in the “two-digit milliseconds” range. On Postgres he goes a step further and suggests skipping the table: pg_logical_emit_message() writes the event straight into the WAL inside your transaction, so there’s nothing to clean up afterwards.

I agree with him on the mechanics and disagree on the default for small systems. CDC means operating Kafka Connect or Debezium Server, connector configuration and replication slots, and an abandoned slot will keep WAL until your disk fills unless you cap it with max_slot_wal_keep_size. None of that exists in the polling design. My rule: poll until the interval or the query load actually hurts, then move to CDC with a team that’s ready to operate it. Adopting Debezium to publish forty events a minute is buying a freight train to deliver mail.

Cleanup and consumer lag

Delete published rows after a few days, or the table becomes an unbounded log. If you actually want an unbounded log, you want event sourcing, a different pattern with different bills, as the event-driven overview explains. And put a metric on the age of the oldest pending row. That’s your new lag signal, and an alert on it catches a dead relay before your users do.

The Objections

“Why not just LISTEN/NOTIFY?” It’s a good latency optimization on top of the outbox, not a replacement. Notifications are fire-and-forget: a listener that’s down misses them for good, so you still need the table and a catch-up pass. The table is the guarantee; everything else is delivery speed.

“Why not skip the broker?” This one deserves respect. If your events have exactly one consumer inside the same codebase, the outbox table is a perfectly good job queue: consume from it directly and don’t run Kafka at all. That’s the boring-technology answer for small systems. The full machinery earns its keep with multiple consumers, separate deployables or real fan-out, the same criteria that justified events in the first place.

“Won’t Kafka’s two-phase commit support make this obsolete?” Kafka has been working on participating in 2PC (KIP-939), and Morling’s answer is no: with 2PC your service needs both the database and the Kafka cluster up to accept a write; with the outbox it only needs its own database. Lower availability for a guarantee you can already get locally.

And one honest limit: the outbox guarantees your local transaction plus eventual publication. It doesn’t give you atomicity across several services’ databases. A checkout that reserves inventory in one service and charges payment in another is a distributed workflow, and no outbox turns it into a transaction. AWS’s own guidance points to sagas for that case, and so does a later article in this series.

When NOT to Use the Outbox

Shipping One This Week

  1. Pick one event: the most expensive dual write from your dual-write audit.
  2. Add the table and the INSERT to the producing transaction. An hour, migration included.
  3. Write the polling relay: published_at IS NULL, one leader via advisory lock, stop on first failure, jittered sleep. An afternoon.
  4. Make the consumer idempotent on event_id, with the handler pattern from earlier in the series.
  5. Add the ops trimmings: a metric on the oldest pending row, an alert when it grows, a cleanup job for published rows.
  6. Kill the old dual write, and delete the reconciliation cron that was papering over it. That deletion is the payoff.

Next in the series: what to do when the thing you call isn’t yours to fix. Circuit breakers, bulkheads and load shedding.

References