Transactional Fan-Out Across Heterogeneous Stores

#Abstract

Applications that outgrow a single store commonly write to each store from the request handler, which produces divergence with an unbounded detection time. This note describes an outbox construction in which state, a versioned event and a dispatch record commit in one transaction and derived projections are applied asynchronously from the resulting log. It states precisely what is atomic and what is not, and compares bounded divergence detection against the alternatives analytically, without a measured failure distribution.

#What is and is not atomic

Being precise about this matters, because the pattern is frequently described in terms that overstate it.

• Atomic: entity state, the versioned event describing the change, and the outbox row. These commit together in one transaction against one store. There is no committed state without its event and no event without its state.
• Not atomic: the derived projections. Graph and search indexes are eventually consistent with the source of truth, at single-digit milliseconds when the relay is warm.
• Guaranteed: at-least-once delivery per projection with per-aggregate ordering. Appliers must therefore be idempotent, enforced here by version gating.

The claim is not that the fan-out is transactional. It is that the fan-out is recoverable: any projection can be dropped entirely and rebuilt from the log without touching the source of truth.

#Construction

1BEGIN;
2  UPDATE assets SET ... WHERE id = $1 AND version = $2;   -- optimistic concurrency
3  INSERT INTO events (agg_id, version, type, payload) VALUES (...);
4  INSERT INTO outbox (event_id, stream_key) VALUES (...);
5COMMIT;                                                   -- notification fires here
Copy

A leader-elected relay wakes on the notification, reads the outbox in commit order and publishes. Consumer groups apply to each projection, with pending entries reclaimed when a consumer dies.

#Why not distributed commit

Two-phase commit across a relational store, a search index and a graph engine is possible in principle. The coordinator becomes an availability bottleneck: a participant failing after prepare blocks the others until it recovers, which is the well known blocking property of the protocol [2].

The outbox accepts eventual consistency in the projections and keeps the source of truth strongly consistent, which is the trade Helland describes for systems whose data no longer fits one transaction [1] and which the log-oriented view of data integration makes explicit [3] [4].

#Latency

An early implementation polled the outbox on a 200ms interval, placing a floor of 200ms on projection visibility for reasons of implementation rather than design. Replacing the poll with a notification-driven wake, retaining a slow poll purely as a backstop against lost notifications, moved the common case to approximately 4ms.

#Comparison with handler-side dual writes

λ is write rate, p per-write failure probability, D the set of derived stores and T the detection time. The outbox does not reduce p. It bounds T by relay lag, which is a monitored quantity, where handler-side writes leave T unbounded because the failure is silent when the process terminates between two writes.

In one observed case, T was eleven days.

#Costs

• Read after write. A client may not observe its own write through a projection. The mitigation is routing the query to the source of truth rather than introducing a delay [5] [6].
• Per-aggregate ordering only. A global order would require serialising every write.
• An operational component. The relay requires leader election, a lag metric and somebody observing it.
• Outbox growth. Published rows require pruning, and the retention policy interacts with how far back a rebuild may need to replay.

#Limitations

The comparison above is analytical. No measured distribution of T under handler-side writes with injected failures is reported, and obtaining one would require deliberately operating a dual-write system under fault injection. The single observed eleven-day case is an anecdote rather than a distribution.

The latency figures come from one deployment shape with a co-located relay. Systems separating compute from durable storage have different characteristics [7] and were not measured.