The Dual-Write Problem and the Transactional Outbox

A service that changes its own state and then tells the rest of the system about the change performs two commits against two systems. The database commit and the broker append are independent, and no amount of ordering inside the application makes them one operation. Every design that ignores this produces either lost notifications or phantom ones, and the choice between them is usually made by accident.

Two systems, one intent

The intent is single: “this order was placed”. The representations are two: a row in the order table and a message on a topic. Each is durable in its own store, with its own failure modes, and each can succeed while the other fails.

The failure is easiest to see as a timeline. A request handler opens a transaction, inserts the order, commits. The commit succeeds and the database is durable. The process then builds a message and calls the broker. If the process is killed between the two, the order exists and no one is told. If the broker is unreachable, the same outcome, except the handler may now return an error to a client whose order was in fact created, which is the ambiguous-outcome problem in another form.

The reverse order is worse. Publishing before committing means a consumer can act on an event whose corresponding state was rolled back. A fulfilment service dispatches a parcel for an order that does not exist, and the correction requires a compensating action rather than a retry.

There is also a third window that gets less attention: two events can be published in the opposite order to the one in which their state was committed. Thread A commits order creation and is descheduled before publishing; thread B commits a cancellation and publishes immediately. Consumers see the cancellation first and have to handle an event for an entity they have never seen. Ordering is not part of what a broker promises across keys, and it is not preserved across concurrent publishers even on a single topic.

Why the obvious fixes do not close it

Publish-after-commit is the most common arrangement, and it converts both failure modes into a lost event. That is a defensible bias when the event is advisory, and it is not defensible when a downstream service owns data that must reflect the change.

Publish-before-commit moves the ambiguity to the consumer, which must now verify that the event corresponds to real state before acting. That works, at the cost of a read against the producer for every event, which reintroduces temporal coupling into a path that was meant to be asynchronous.

Two-phase commit over the database and the broker is the theoretically correct answer and rarely the practical one. It requires both resource managers to participate in XA, a coordinator that is itself highly available, and prepared transactions that hold locks from prepare until commit. A prepared transaction that is never resolved keeps its locks and prevents vacuum from reclaiming anything it touched, which is why PostgreSQL ships with prepared transactions disabled and warns about them in its documentation. A coordinator failure stalls every participant, so the mechanism trades a rare inconsistency for a correlated availability risk.

Retrying the publish from an in-process queue survives only until the process exits. A durable queue of pending publishes is, in effect, the beginning of the outbox, just placed outside the transaction that needed it.

The outbox table

The transactional outbox inverts the problem. Instead of writing to two systems, the service writes two rows to one system: the business row and an event row describing what happened. Both commit or neither does, because they are in the same local transaction. A separate relay reads the event rows and publishes them.

The event row is an ordinary table row, so the commit that makes the business change durable makes the intent to publish durable at the same instant. There is no window in which one exists without the other.

BEGIN;

INSERT INTO orders (id, customer_id, total_cents, status)
VALUES ($1, $2, $3, 'placed');

INSERT INTO outbox (aggregate_id, aggregate_type, event_type, payload)
VALUES ($1, 'order', 'order.placed', $4::jsonb);

COMMIT;

The cost is on the write path. The transaction now touches one more table and writes a payload, which lengthens the transaction slightly and adds WAL volume. A large payload in the outbox makes every business write carry it, so events usually carry identifiers and the fields consumers need, not a full snapshot of the aggregate. A payload of a few kilobytes is unremarkable; a payload that embeds an entire document is not.

The relay has a second dual write of its own: publish, then mark published. If it crashes between them, it publishes again after restart. That is the correct direction of failure, because at-least-once delivery plus a deduplicating consumer is a well-understood arrangement, while at-most-once delivery plus a gap detector is not. Marking rows before publishing would turn a crash into silent loss.

Polling and log tailing as relay strategies

A polling relay reads unpublished rows in batches and publishes them. The query shape matters more than the framework:

SELECT id, aggregate_id, event_type, payload
  FROM outbox
 WHERE published_at IS NULL
 ORDER BY id
 LIMIT 100
   FOR UPDATE SKIP LOCKED;

SKIP LOCKED, available in PostgreSQL since 9.5 and in MySQL since 8.0, lets several relay instances work the same table without blocking each other or publishing the same batch twice. Without it, concurrent relays serialise on the same rows and the throughput of the relay becomes the throughput of the table’s oldest unpublished rows.

Polling latency is bounded below by the poll interval, so a 100 ms interval gives an average delay of tens of milliseconds and an idle database query every 100 ms. That idle cost is the main argument against polling on a busy primary. A partial index on the unpublished predicate keeps the scan cheap as the table grows, and that index is the difference between a relay that scales and one that degrades over months.

Log tailing removes the polling. A change data capture connector reads the database’s write-ahead log through logical replication and emits committed changes, so the outbox table is read from the log rather than by query. Latency drops to the replication lag, usually well under a second, and there is no idle query cost. The price is operational: a replication slot that is not consumed fills the primary’s disk with retained WAL, schema changes to the outbox must be understood by the connector, and the connector’s own offsets become state that has to be backed up and restored consistently with the database.

Ordering differs between the two. A polling relay that orders by the outbox primary key preserves insertion order, which for a single-writer aggregate is commit order. A log-based relay sees commit order, and concurrent transactions become visible in log sequence order but may have committed at different times, so two events for the same aggregate from concurrent transactions can appear in an order that neither transaction observed. Keying the topic by aggregate id keeps each aggregate’s events on one partition, which restores per-aggregate order at the consumer.

Approach	Loss window	Duplicate risk	Latency	Operational cost
Publish after commit	Process death	None	Low	Silent divergence
Two-phase commit	Coordinator failure	Low	High	Locks, XA, coordinator
Outbox with polling	None	Relay crash	Poll interval	Index bloat, idle queries
Outbox with log tailing	None	Connector restart	Replication lag	Slot retention, schema coupling

Cleanup, retention, and the shape of the table

An outbox grows at the write rate of the service. A table that receives a thousand rows per second accumulates tens of millions of rows per day, and deleting them one by one costs more than publishing them did.

The workable pattern is time partitioning: partition the outbox by day, keep the partitions that unpublished rows can still occupy, and drop older partitions once their rows are marked published. Deletion by DELETE leaves dead tuples that vacuum has to reclaim, and on a table that is written continuously the bloat can outrun vacuum.

Retention is set by the slowest consumer that might need to replay from the outbox rather than from the topic. If a consumer rebuilds a projection, it usually replays from the topic or from a snapshot, not from the producer’s outbox, because the outbox is an implementation detail of the producer. Treating the outbox as a shared log couples every consumer to the producer’s schema, which is a coupling the outbox was supposed to avoid.

Where the guarantee actually ends

The outbox guarantees that an event is eventually published if the business transaction committed. It does not guarantee that the event is published once, in global order, or before a consumer that reads the database directly sees the state.

NOTIFY in PostgreSQL is a tempting shortcut and is not a durable queue: notifications are delivered only to sessions listening at the time and are lost if no session is listening. It is useful as a wake-up signal that lets a polling relay react immediately instead of waiting for its next interval, and it is not a substitute for the table.

Consumers must therefore be written for at-least-once delivery and for out-of-order arrival within a key. Idempotency Keys and Duplicate Suppression at Service Boundaries covers the duplicate side at the HTTP boundary; the same reasoning applies to message consumers, with the business key and version replacing the client-supplied key.

The final limitation is that the outbox makes one service’s local commit atomic with its own notification, and nothing more. A workflow that spans three services still has three independent commits, and the coordination between them is a saga rather than a transaction. Compensating Transactions in Multi-Service Workflows deals with that layer.