Here is one of the oldest bugs in distributed systems, still shipping to production every week: a service commits a row to its database, then tries to publish “row created!” to a message broker — and crashes between the two. The database says the order exists; Kafka never heard of it. Downstream consumers that send confirmation emails or update inventory silently miss the event, and no amount of retry logic in the publisher can fix it, because the publisher that knew what to publish no longer exists.
You cannot make “write to database” and “publish to broker” atomic across two systems. What you can do is refuse to need a cross-system transaction. The transactional outbox pattern does exactly that: instead of publishing the event directly, you insert it into an outbox table inside the same transaction as your business data. A separate process then relays outbox rows to the broker. Atomicity comes from the database; delivery becomes someone else’s job. This post builds the pattern end to end in Go and PostgreSQL — the table design, the relay loop, the exact role of SKIP LOCKED, and when to graduate from DIY to a tool like Debezium.
The dual-write problem, precisely
The failure isn’t exotic. Any sequence like this is broken:
// BROKEN: two systems, no shared atomicity
func CreateOrder(ctx context.Context, db *pgxpool.Pool, bus *Broker, o Order) error {
_, err := db.Exec(ctx,
"INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3)",
o.ID, o.CustomerID, o.Total)
if err != nil {
return err
}
// Crash here → order exists, event lost forever.
// Timeout here → maybe sent, maybe not, we can't tell.
return bus.Publish(ctx, "orders", o)
}Every window between the two calls is a correctness hole, and retrying doesn’t help: a retry after an ambiguous publish can duplicate the event, which is its own bug. Distributed transactions (two-phase commit spanning Postgres and Kafka) technically close the gap but at costs — coordinator availability, lock hold times, broker support — that few teams accept. The outbox pattern sidesteps the whole question by reducing the problem to a single-system transaction, where databases have been excellent for decades.
The outbox table
An outbox row is the event you want to publish, stored as data. A minimal, workable schema:
CREATE TABLE outbox (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
aggregate_type TEXT NOT NULL,
aggregate_id TEXT NOT NULL,
event_type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
processed_at TIMESTAMPTZ
);
CREATE INDEX idx_outbox_pending
ON outbox (id)
WHERE processed_at IS NULL;The writer-side change is one extra insert in the existing transaction:
func CreateOrder(ctx context.Context, tx pgx.Tx, o Order) error {
_, err := tx.Exec(ctx,
"INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3)",
o.ID, o.CustomerID, o.Total)
if err != nil {
return err
}
payload, _ := json.Marshal(o)
_, err = tx.Exec(ctx, `
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('Order', $1, 'OrderCreated', $2)`,
o.ID, payload)
return err
}Now the business row and the event live or die together. Commit succeeds: both exist. Crash before commit: neither does. The database’s own durability guarantees, which you already trust for your core data, now extend to event publication. That’s the entire insight — everything after this is engineering the relay.
The relay: polling with SKIP LOCKED
The relay reads pending rows, publishes them, and marks them processed. Two hard requirements: multiple relay instances must not fight over rows, and a crash mid-relay must not lose events. FOR UPDATE SKIP LOCKED, added to PostgreSQL’s row locking in version 9.5, was designed for exactly this shape of queue-in-a-table:
func RunRelay(ctx context.Context, pool *pgxpool.Pool, bus *Broker) error {
ticker := time.NewTicker(200 * time.Millisecond)
defer ticker.Stop()
for {
select {
case <-ctx.Done():
return ctx.Err()
case <-ticker.C:
if err := relayBatch(ctx, pool, bus); err != nil {
log.Printf("relay batch error: %v", err)
}
}
}
}
func relayBatch(ctx context.Context, pool *pgxpool.Pool, bus *Broker) error {
tx, err := pool.Begin(ctx)
if err != nil {
return err
}
defer tx.Rollback(ctx)
// Claim a batch. SKIP LOCKED means a second relay instance
// skips rows we hold locks on instead of blocking behind them.
rows, err := tx.Query(ctx, `
SELECT id, event_type, payload
FROM outbox
WHERE processed_at IS NULL
ORDER BY id
LIMIT 50
FOR UPDATE SKIP LOCKED`)
if err != nil {
return err
}
var events []OutboxEvent
for rows.Next() {
var e OutboxEvent
if err := rows.Scan(&e.ID, &e.Type, &e.Payload); err != nil {
rows.Close()
return err
}
events = append(events, e)
}
rows.Close()
if len(events) == 0 {
return nil
}
// Publish first, then mark. If we crash between the two,
// the row stays pending and gets published again — a duplicate,
// which consumers must tolerate (more on that below).
for _, e := range events {
if err := bus.Publish(ctx, "outbox."+e.Type, e.Payload); err != nil {
return err
}
}
_, err = tx.Exec(ctx,
"UPDATE outbox SET processed_at = now() WHERE id = ANY($1)",
eventIDs(events))
if err != nil {
return err
}
return tx.Commit(ctx)
}Walking through the properties: SKIP LOCKED makes multiple relays safe — each claims a disjoint set of rows without blocking or deadlock. Publishing before marking processed means a crash anywhere before commit leaves the row pending, so the event is redelivered. That deliberately trades “at least once” delivery for correctness: the outbox guarantees the event is published at least once, never zero times.
Note what this design does not require: no marking-then-publishing (crash between those leaves an event published but unmarked, which is fine — duplicate — but never lost), and no cross-system commit. The transaction boundary only wraps the database work; broker publication happens inside it for ordering convenience, and a broker failure simply rolls the claim back for the next tick to retry.
The deduplication corollary: at-least-once means consumers are idempotent
Because redelivery is a feature of the outbox (it’s the mechanism that prevents loss), every consumer must handle duplicates. The standard approach is deduplication on the event ID at the consumer side:
CREATE TABLE processed_events (
event_id BIGINT PRIMARY KEY,
processed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);func HandleEvent(ctx context.Context, tx pgx.Tx, e OutboxEvent) error {
// Claim the event; a duplicate insert loses the race and skips work.
tag, err := tx.Exec(ctx,
"INSERT INTO processed_events (event_id) VALUES ($1) ON CONFLICT DO NOTHING",
e.ID)
if err != nil {
return err
}
if tag.RowsAffected() == 0 {
return nil // already processed
}
return applyEvent(ctx, tx, e)
}This is why the outbox row’s id matters even after the message leaves the database — it rides in the message (a header, or part of the envelope) and becomes the consumer’s deduplication key. If you use UUIDs instead of bigserial, nothing changes; the ID just needs to be unique per event and travel with it.
Ordering: the caveat people discover in production
The outbox preserves write order in the table (ascending id), and a single relay instance publishes in that order. But with multiple relays, SKIP LOCKED batches can be published out of order: instance A claims rows 1–50, instance B claims 51–100, and B finishes first. If downstream consumers need strict per-entity ordering — “OrderUpdated must follow OrderCreated for order 42” — then route by aggregate ID. A Kafka partition keyed by aggregate_id guarantees order within a key; events for different orders interleave freely, which is almost always what you actually need. Alternatively, run a single relay per aggregate type and scale by partitioning the table range, trading throughput for simplicity.
Related trap: don’t let a single poisoned event block the pipe. If one row fails publication repeatedly (payload too large, schema the broker rejects), the ORDER BY id LIMIT 50 loop retries it forever and starves everything behind it. Add a retry counter or move failures to a dead-letter row after N attempts, and alert on that table’s depth.
Polling versus log tailing: when to bring in Debezium
The DIY relay polls every few hundred milliseconds, which adds that much latency and, more significantly, re-reads the pending index constantly. An alternative is to let change data capture (CDC) do the relaying: Debezium streams the database’s write-ahead log, and its outbox event router transformation converts inserts on your outbox table into broker messages automatically. With the router’s default configuration, Debezium expects the classic outbox shape — an id (UUID) for the event, an aggregatetype column used for routing (its value becomes part of the destination topic name, so “Order” lands on outbox.event.Order), an aggregateid used as the message key for partition ordering, a type for the event type, and the payload column itself.
The connector configuration is a single-message transform on any Debezium connector:
{
"transforms": "outbox",
"transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter"
}Trade-offs are worth stating plainly. CDC delivers events with much lower latency and without polling load, and it captures ordering from the WAL itself. But it’s a new distributed component (Kafka Connect, connectors to run, monitor, and upgrade), and its failure modes are cluster failure modes. The polling relay is a hundred lines in a process you already deploy. A reasonable progression: start with the DIY relay, and adopt Debezium when relay latency, throughput, or the number of services sharing the outbox stops being trivial. If your consumers can tolerate a few hundred milliseconds of latency — most can — the DIY version is often the permanent answer.
One middle path worth knowing for lower-stakes cases inside a single Postgres instance: NOTIFY can wake a relay the moment a transaction commits instead of waiting for the next poll tick. Notifications are only delivered if the sending transaction commits — an aborted transaction’s notifications are discarded — so the semantics compose cleanly with the outbox. But notification delivery isn’t durable or guaranteed; treat it as a latency optimization on top of polling, never as the transport.
Operational hygiene
- Prune processed rows. Without a cleanup job the outbox grows forever. A nightly
DELETE FROM outbox WHERE processed_at < now() - interval '7 days'in batches keeps the table and its index small. (Deleting processed rows also reclaims the partial index’s usefulness.) - Monitor two depths: pending rows (
WHERE processed_at IS NULL) and their age. Depth growing steadily means the relay is losing the race; a high minimum age means the relay is down entirely. - Keep payloads versioned. The payload is a durable artifact — consumers may process it minutes or (after an outage) days later. Include an event schema version field from day one.
- Don’t put binary blobs in the outbox. Large payloads bloat the table, the WAL, and every broker message. Store a reference and let consumers fetch.
- Watch lock contention on hot aggregates. Every insert into the outbox competes with the business-table writes in the same transaction; if outbox inserts become the tail latency driver, that’s the signal to move to CDC.
Wrapping up
The transactional outbox is the rare distributed-systems pattern that makes a system simpler: it deletes a cross-system consistency problem by refusing to create one, using only the transactional guarantees of the database you already run. The recipe is short — write events in the same transaction as state, relay them with SKIP LOCKED, deduplicate by event ID downstream, and route by aggregate ID when ordering matters. Start with the hundred-line relay; reach for Debezium when its limits become yours. And whatever the transport, keep the core invariant: an event either exists in the outbox with the state that caused it, or it doesn’t exist at all.