Save, Then Publish, Is Two Writes: The Transactional Outbox Pattern

The command handler in my CQRS and Event Sourcing post ends like this:

// Save events
if err := h.repo.Save(ctx, payment); err != nil {
    return fmt.Errorf("saving payment: %w", err)
}

// Publish for read model updates and integration
for _, event := range events {
    if err := h.eventBus.Publish(ctx, event); err != nil {
        // Log but don't fail - events are persisted
        log.Printf("failed to publish event: %v", err)
    }
}

The comment is true and it is also the bug. The events are persisted. Nothing ever reads them back to find out which ones were published, so a failed publish stays failed forever and the read model never learns the payment happened.

The follow-up on CQRS costs has a version that's worse in a quieter way:

go s.eventBus.Publish(context.Background(), OrderCreatedEvent{OrderID: order.ID})

A goroutine nobody waits for. A deploy sends SIGTERM, the process exits, and the publish never runs. I wrote a whole post on why graceful shutdown is harder than it looks and still shipped this.

Both are the same mistake, called a dual write: one operation that has to change two systems, with no transaction spanning both. This post is about the standard fix, the Transactional Outbox Pattern, and about what it costs, because it is not free.

Four ways two writes disagree

The database and the broker are separate systems. Each write can succeed or fail independently, and the process can die between them. Lay out the cases:

The hidden costs post talks about eventual consistency as the gap between a write and the read model catching up. The first three rows aren't that. They're a read model that never catches up, because the event that would have updated it doesn't exist anywhere a consumer can see. Eventual consistency assumes "eventually" arrives.

Why the obvious fixes don't work

Publish first. This swaps a lost event for a phantom one (the fourth row). A phantom is usually worse: a consumer that sends an email or captures a payment can't un-send it when the database write fails.

Retry the publish. Retries handle the broker being briefly unavailable. They don't handle the process dying, because the retry state lives in the memory of the process that just died. Same story for a background goroutine with a backoff loop.

A distributed transaction. Two-phase commit across Postgres and the broker would make the writes atomic, but SNS, SQS, EventBridge and Kafka don't take part in XA transactions, and when 2PC is available it holds locks across a network round trip to a coordinator. It isn't a realistic option for the brokers people actually use.

What all three have in common is that they try to coordinate two systems at write time. The outbox stops trying.

The Transactional Outbox Pattern

There is one system that can make several writes atomic: the database you're already writing to. So write the event there too, in the same transaction as the state change, and move it to the broker afterwards.

  command handler                          relay                     broker
 ┌────────────────────────────┐      ┌────────────────────┐      ┌─────────────┐
 │ BEGIN                      │      │ poll unpublished   │      │             │
 │   UPDATE payments ...      │      │ rows               │ ───► │ SNS / Kafka │ ──► consumers
 │   INSERT INTO outbox ...   │ ───► │ publish            │      │ / SQS       │
 │ COMMIT                     │      │ mark published     │      │             │
 └────────────────────────────┘      └────────────────────┘      └─────────────┘
       one atomic write                 at-least-once

Now the state change and the record of the event either both commit or both roll back. Publishing becomes a separate job that can fail, crash and retry as often as it needs to, because the thing it is retrying is durable.

The table:

CREATE TABLE outbox (
    id            uuid        PRIMARY KEY,
    aggregate_id  uuid        NOT NULL,
    event_type    text        NOT NULL,
    payload       jsonb       NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    published_at  timestamptz
);

CREATE INDEX outbox_unpublished
    ON outbox (created_at)
    WHERE published_at IS NULL;

The partial index matters. The relay only ever asks for unpublished rows, and they should be a tiny fraction of the table, so the index stays small however much history accumulates.

The handler from the first post, rewritten:

func (h *ProcessPaymentHandler) Handle(ctx context.Context, cmd ProcessPayment) error {
    tx, err := h.db.Begin(ctx)
    if err != nil {
        return fmt.Errorf("begin: %w", err)
    }
    defer tx.Rollback(ctx)

    payment, err := h.repo.Load(ctx, tx, cmd.PaymentID)
    if err != nil {
        return fmt.Errorf("loading payment: %w", err)
    }

    risk := h.riskEngine.Assess(cmd.CustomerID, cmd.Amount)
    events, err := payment.Process(cmd.Amount, cmd.Method, risk, cmd.IdempotencyKey)
    if err != nil {
        return fmt.Errorf("processing payment: %w", err)
    }

    if err := h.repo.Save(ctx, tx, payment); err != nil {
        return fmt.Errorf("saving payment: %w", err)
    }
    if err := outbox.Add(ctx, tx, events...); err != nil {
        return fmt.Errorf("recording events: %w", err)
    }

    return tx.Commit(ctx)
}

There is no event bus in the handler anymore. It can't publish, so it can't half-publish.

func Add(ctx context.Context, tx pgx.Tx, events ...Event) error {
    for _, e := range events {
        payload, err := json.Marshal(e)
        if err != nil {
            return fmt.Errorf("marshal %s: %w", e.EventType(), err)
        }
        _, err = tx.Exec(ctx, `
            INSERT INTO outbox (id, aggregate_id, event_type, payload)
            VALUES ($1, $2, $3, $4)`,
            e.EventID(), e.AggregateID(), e.EventType(), payload)
        if err != nil {
            return fmt.Errorf("insert outbox %s: %w", e.EventType(), err)
        }
    }
    return nil
}

The event ID is created by the domain when the event is created, not by the relay when it publishes. That detail comes back later: it's what lets consumers recognise a duplicate.

The relay

func (r *Relay) Run(ctx context.Context) error {
    for {
        n, err := r.publishBatch(ctx)
        if err != nil {
            r.log.Error("outbox relay", "err", err)
        }
        if n == r.batchSize {
            continue // a full batch means a backlog; drain it without waiting
        }
        select {
        case <-ctx.Done():
            return ctx.Err()
        case <-time.After(r.interval):
        }
    }
}

func (r *Relay) publishBatch(ctx context.Context) (int, error) {
    tx, err := r.db.Begin(ctx)
    if err != nil {
        return 0, err
    }
    defer tx.Rollback(ctx)

    rows, err := tx.Query(ctx, `
        SELECT id::text, event_type, aggregate_id::text, payload
        FROM outbox
        WHERE published_at IS NULL
        ORDER BY created_at
        LIMIT $1
        FOR UPDATE SKIP LOCKED`, r.batchSize)
    if err != nil {
        return 0, err
    }
    batch, err := pgx.CollectRows(rows, pgx.RowToStructByPos[Message])
    if err != nil {
        return 0, err
    }

    published := make([]string, 0, len(batch))
    for _, m := range batch {
        if err := r.publisher.Publish(ctx, m); err != nil {
            // Stop rather than skip: publishing later events past a failed one reorders them.
            r.log.Warn("publish failed, will retry", "event_id", m.EventID, "err", err)
            break
        }
        published = append(published, m.EventID)
    }

    if _, err := tx.Exec(ctx, `
        UPDATE outbox SET published_at = now()
        WHERE id = ANY($1::uuid[])`, published); err != nil {
        return 0, err
    }
    return len(published), tx.Commit(ctx)
}

publishBatch returns the number published, not the number fetched. If it returned the batch size while the broker was down, Run would see a full batch, skip the wait, and spin.

FOR UPDATE SKIP LOCKED lets you run more than one relay without them publishing the same rows. As I explain below, running more than one relay has a cost.

One trap is worth calling out because it looks like an optimisation. Don't have the relay remember "the last ID or timestamp I published" and query for everything after it. Sequence values and now() are assigned when the row is inserted, not when the transaction commits. Transaction A inserts row 10, transaction B inserts row 11, B commits first. A relay polling in that window sees 11, records 11 as its high-water mark, and row 10 commits behind it and is never published. Tracking published_at per row, as above, doesn't have this problem. Row 10 just turns up on the next poll.

What the outbox actually guarantees

It turns "maybe zero times" into "at least once". It doesn't give you exactly once, and the relay above shows why: if Publish succeeds and then the UPDATE or the COMMIT fails, or the relay dies between them, the row is still unpublished and goes out again on the next poll.

That's the right side to be wrong on, since a duplicate can be detected and a missing event can't. But it means every consumer has to be idempotent, and that's a real requirement, not a nice-to-have.

The approach I trust is the same one from idempotency belongs in the database: a constraint, with the collision treated as "already done".

func (p *PaymentProjection) Handle(ctx context.Context, msg Message) error {
    tx, err := p.db.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    tag, err := tx.Exec(ctx, `
        INSERT INTO processed_events (consumer, event_id)
        VALUES ('payment_summary', $1)
        ON CONFLICT DO NOTHING`, msg.EventID)
    if err != nil {
        return err
    }
    if tag.RowsAffected() == 0 {
        return nil // redelivery; the first copy already applied this
    }

    if err := p.apply(ctx, tx, msg); err != nil {
        return err
    }
    return tx.Commit(ctx)
}

This is sometimes called an inbox, and it mirrors the outbox: the dedup record and the effect commit together. It needs the read model to be in a transactional store. If the projection writes to Elasticsearch or a cache, use a version-guarded write instead (UPDATE ... WHERE version < $new_version, or the store's external versioning). That covers duplicates and reordering for projections that replace state. It doesn't work for projections that accumulate, like counters, since there's no version to compare an increment against.

Broker-side deduplication doesn't replace this. SNS FIFO and SQS FIFO deduplicate within a five-minute window, and a relay that is down for longer than that and then republishes is outside it.

The trade-offs

This section is the reason I wrote the post. The outbox is usually presented as the answer, full stop. It's a good answer, and here is what it costs.

Latency

Events now wait for the relay. With a polling interval T and an idle relay, an event waits somewhere between zero and T, so about T/2 on average. A 500 ms interval adds roughly 250 ms to the time between commit and publish. That's arithmetic rather than a measurement, but it shows how the cost scales: halving the interval halves the delay and doubles the empty polls.

Postgres can remove most of that. Call pg_notify('outbox', '') inside the command's transaction and have the relay LISTEN. Notifications are delivered only on commit, so the relay wakes when there's something committed to publish. Keep the poll anyway: notifications aren't durable, and one sent while the relay is reconnecting is simply gone. The poll becomes the safety net and NOTIFY becomes the fast path.

Load on the database you were trying to protect

Every command now does an extra insert, the relay does an update per event, and something has to delete old rows. On Postgres that's a table with constant inserts, updates and deletes, which is the workload that produces bloat and keeps autovacuum busy. At low volume you won't notice. At high volume the outbox can become one of the busiest tables in the database, and it sits inside the same transactions as your business writes.

Retention is a choice in its own right. Deleting rows as soon as they're published keeps the table small. Keeping them for a few days (DELETE ... WHERE published_at < now() - interval '7 days', in batches) lets you answer "did we ever publish event X, and when?", which is the question you'll actually be asking during an incident. I'd keep them for a while unless volume makes that painful.

A transaction held open across a network call

The relay above holds row locks while it calls the broker. If the broker is slow, those locks are held for the duration, and a long-running transaction also stops vacuum from cleaning up behind it. The alternative is to claim rows with a lease (SET locked_until = now() + interval '30 seconds'), commit, publish outside any transaction, and then mark them published. That removes the long transaction and adds lease-expiry logic and a second round trip. Start with the simple version and switch if broker latency shows up in your database metrics.

Ordering competes with throughput

A single relay publishing in created_at order gives you roughly commit order, and that's usually what consumers need. Not exactly commit order, for the same insert-time-versus-commit-time reason as the high-water-mark trap, so per-aggregate version numbers on the event are still worth having.

Add relays with SKIP LOCKED and you get throughput, but relay 1 can publish event 2 of an aggregate while relay 2 is still publishing event 1. If consumers care about per-aggregate order, partition: each relay owns hash(aggregate_id) % N and publishes to a partition key (a Kafka key, an SQS FIFO message group ID) so the broker preserves the order. That's more moving parts, and it's why "just add another relay" is a design decision rather than a scaling knob.

Another process to run and watch

The relay is a new failure mode: if it stops, nothing errors. Commands keep succeeding, the outbox keeps growing, and the read model goes stale. The upside is that the outbox now works as a buffer: when the broker is down for an hour, nothing is lost, it just queues. The downside is that you have to notice. One query covers most of it:

SELECT now() - min(created_at) AS oldest_unpublished
FROM outbox
WHERE published_at IS NULL;

That uses the partial index and is cheap to run every few seconds. Alert on its age, not on row count. A thousand unpublished rows from the last second is a busy minute. One row from twenty minutes ago is a stuck relay.

Alternatives, and what each one trades

Change data capture deserves a closer look because it removes polling and gives you true commit order from the log. The cost is operational: a Postgres logical replication slot that stops being consumed keeps WAL around until the disk fills, which turns a stalled connector into a database outage. Capturing business tables directly is tempting since it needs no outbox at all, but then every column rename is a breaking change to your event contract. CDC on an outbox table keeps the contract explicit and still gets rid of the relay.

The event-sourced handler in my first post was closer to correct than it looked. Its events table is already written in the same transaction as the state, because it is the state. It's already an outbox. What was missing was a reader that tracked which events had been delivered, and the high-water-mark trap applies to a global event sequence just as it does above.

On DynamoDB, the equivalent is TransactWriteItems putting the item and an outbox item together, or simply DynamoDB Streams on the table, which gives you CDC as a managed feature.

When you don't need it

If the read model lives in the same Postgres as the write model, the simplest fix isn't an outbox. It's updating the projection inside the command's transaction. No broker, no relay, no eventual consistency, no duplicates. You give up scaling reads independently of writes and keeping projection failures out of the command path, which were two of the reasons for CQRS in the first place. Plenty of systems don't need either yet, and the hidden costs post argued for starting there.

If the event is only a hint, such as a cache invalidation whose cache also has a TTL, losing one occasionally may be acceptable, and save-then-publish is an honest choice as long as it's a deliberate one.

And if a nightly reconciliation job already compares the two sides and repairs drift, you have a slow, coarse-grained outbox. It might be enough.

What I'd take from this

Writing to a database and then publishing to a broker is two writes, and no amount of ordering, retrying or logging makes them one. The comment "events are persisted" was exactly the right instinct pointed at the wrong question. Persisted isn't the issue. The issue is whether anything will come back for them.

The Transactional Outbox Pattern works by declining to coordinate two systems: it makes the event part of the one write the database can make atomic, and turns publishing into a retryable job. The price is at-least-once delivery, and that moves the duplicate problem to the consumers, where a constraint has to deal with it.

The costs are real and mostly operational: latency bounded by the poll interval, write load on a table that bloats, an ordering guarantee that weakens as you scale the relay, and a process whose failure looks exactly like success until someone checks how old the oldest unpublished row is. Before choosing it, ask whether the read model could just be updated in the same transaction. That's often the boring answer, and boring is fine.