The Transactional Outbox Pattern: Saving Your Data and Your Message Atomically

Every developer who has wired a database to a message broker has eventually written this bug: save the order, then publish an “order.created” event. The database commit succeeds, the broker call times out, and your downstream services — inventory, email, analytics — never learn the order exists. Retry the publish and you risk the mirror image: the broker call succeeds, the commit rolls back, and downstream services process an order that was never saved. This is the dual-write problem, and no amount of try/catch around two independent systems fully fixes it, because the failure can strike between the two operations no matter how you order them.

The transactional outbox pattern resolves this by refusing to make two systems agree at once. Instead of publishing to the broker directly, the application writes the event into an outbox table inside the same database transaction as the business data. Either both the order and the event exist, or neither does — atomicity is inherited from the database you already trust. A separate relay process then reads the outbox and delivers the events to the broker. This post builds the pattern end to end in Go and PostgreSQL, then covers the operational details that separate a working implementation from a fragile one.

The outbox table

The outbox is deliberately boring: an append-only table whose rows are messages waiting for delivery. A published_at column tracks what has been handed off, and everything else is your event payload:

CREATE TABLE outbox (
    id             BIGSERIAL 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(),
    published_at   TIMESTAMPTZ
);

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

The partial index matters more than it looks. Published rows stay in the table until a cleanup job deletes them, so without the partial index the relay’s scan of unpublished messages would wade through an ever-growing pile of delivered history. The id column gives you a monotonic ordering, which matters for consumers that care about event sequence.

Writing business data and events atomically

On the write side, the pattern changes only one thing: the event insert joins the same transaction as the domain change. Here is a Go handler that creates an order and its corresponding event in one commit:

package orderservice

import (
	"context"
	"database/sql"
	"encoding/json"
)

type Order struct {
	ID         string
	CustomerID string
	Total      int64 // cents
}

type OrderStore struct {
	db *sql.DB
}

func (s *OrderStore) CreateOrder(ctx context.Context, order Order) error {
	payload, err := json.Marshal(map[string]any{
		"order_id":    order.ID,
		"customer_id": order.CustomerID,
		"total":       order.Total,
	})
	if err != nil {
		return err
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op if Commit succeeds

	if _, err := tx.ExecContext(ctx,
		`INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3)`,
		order.ID, order.CustomerID, order.Total,
	); err != nil {
		return err
	}

	if _, err := tx.ExecContext(ctx,
		`INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
		 VALUES ($1, $2, $3, $4)`,
		"order", order.ID, "order.created", payload,
	); err != nil {
		return err
	}

	return tx.Commit()
}

If the broker were involved here instead of the outbox insert, the second operation would be a network call with its own failure domain, and the transaction would either hold locks open across that call or give up atomicity entirely. With the outbox, both writes hit the same database, and the commit either covers both or neither. The code your application writes stays simple — no retries, no compensation, no saga machinery at this layer.

The relay: moving events to the broker

The relay is a background worker that polls the outbox, publishes each pending event, and marks it delivered. The subtle part is running multiple relay instances safely. Two relays polling at once must never publish the same event twice concurrently, and the answer is FOR UPDATE SKIP LOCKED — each relay claims only rows no other relay has locked, following the same recipe database-backed job queues use:

package orderservice

import (
	"context"
	"database/sql"
	"encoding/json"
	"log"
	"time"
)

type Relay struct {
	db      *sql.DB
	publish func(ctx context.Context, eventType string, key string, payload json.RawMessage) error
}

func (r *Relay) Run(ctx context.Context) {
	ticker := time.NewTicker(500 * time.Millisecond)
	defer ticker.Stop()

	for {
		select {
		case <-ctx.Done():
			return
		case <-ticker.C:
			if err := r.publishBatch(ctx); err != nil {
				log.Printf("relay batch failed: %v", err)
			}
		}
	}
}

func (r *Relay) publishBatch(ctx context.Context) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op if Commit succeeds

	rows, err := tx.QueryContext(ctx, `
		SELECT id, event_type, aggregate_id, payload
		FROM outbox
		WHERE published_at IS NULL
		ORDER BY id
		LIMIT 100
		FOR UPDATE SKIP LOCKED`)
	if err != nil {
		return err
	}

	type pendingEvent struct {
		id          int64
		eventType   string
		aggregateID string
		payload     json.RawMessage
	}
	var events []pendingEvent
	for rows.Next() {
		var e pendingEvent
		if err := rows.Scan(&e.id, &e.eventType, &e.aggregateID, &e.payload); err != nil {
			rows.Close()
			return err
		}
		events = append(events, e)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return err
	}
	if len(events) == 0 {
		return nil
	}

	for _, e := range events {
		if err := r.publish(ctx, e.eventType, e.aggregateID, e.payload); err != nil {
			return err // tx rolls back; rows stay pending for the next tick
		}
		if _, err := tx.ExecContext(ctx,
			`UPDATE outbox SET published_at = now() WHERE id = $1`, e.id); err != nil {
			return err
		}
	}

	return tx.Commit()
}

If the broker is unreachable, publish returns an error, the transaction rolls back, and the claimed rows return to the pool for the next tick — no lost events, no busy-waiting on a dead broker. Holding the row locks across the publish calls is a deliberate trade: it keeps the "published" bookkeeping inside the same atomic boundary as the sends themselves, at the cost of keeping a short-lived transaction open across network I/O. Keep batches small (the LIMIT 100 above) so a slow broker cannot pile up long-running transactions, and consider marking rows only after the batch's sends all succeed if your broker client supports batching natively.

Delivery guarantees and what consumers must do

The pattern's contract is at-least-once delivery, and it is worth seeing exactly where the duplicate window opens. If the relay publishes to the broker and then crashes before committing, the rolled-back transaction leaves the row unpublished — the event will be sent again. The event is safe (it reached the broker at least once) but it can arrive twice. That is the best guarantee any pattern of this shape can offer: exactly-once delivery across an independent broker is not achievable without transactional coordination on the broker side too.

So consumers carry the deduplication burden, and it is lighter than it sounds. Give each outbox row's id (or a UUID generated at write time) to the broker as the message key or a header, and have consumers track processed IDs — a table keyed on message ID, or an idempotent state transition that simply no-ops when the event has already been applied. Most consumer codebases need only a small "have I seen this ID" check around their handler.

Ordering has a similar caveat. Within a single relay running a single poll loop, rows publish in id order for one aggregate, which is usually what you want. But the moment you scale to multiple relays or a broker with per-partition ordering, events for the same aggregate can interleave across workers. If strict per-aggregate ordering matters, partition the broker topic by aggregate_id — the code above already passes it as the message key for exactly this reason — and keep relay batch sizes modest so one stuck row cannot stall unrelated aggregates for long.

Housekeeping and the CDC alternative

Two operational chores keep the pattern healthy. First, cleanup: published rows are pure history, so a scheduled job deletes them on a retention window — DELETE FROM outbox WHERE id IN (SELECT id FROM outbox WHERE published_at < now() - interval '7 days' LIMIT 10000) in a loop keeps the table small without long-running deletions. Second, throughput: polling every 500 milliseconds adds up to half a second of latency to every event, which many systems tolerate and some cannot.

When polling latency or write amplification becomes a problem, change-data-capture tools can read the outbox table from the database's replication log instead, streaming new rows as they commit with no polling loop at all. Debezium ships an outbox event router for exactly this: it tails the outbox table's change events and unwraps them into clean domain messages on Kafka, routing by aggregate type. The application code does not change — the same transaction inserts the same row — but the delivery path moves from your relay process to the replication stream. The trade is operational: you now run a Kafka Connect cluster, which is a meaningful piece of infrastructure to own.

The outbox pattern is not exotic — it is the recognition that your database transaction is the only atomic operation you actually have, so the event must be created inside it. Keep the write side dumb (one extra INSERT), put the delivery responsibility in a single well-understood relay, and make consumers idempotent. The dual-write bug stops being a class of incidents in your system and becomes a solved problem you never think about.

Leave a Reply

Your email address will not be published. Required fields are marked *