Transactional Fan-Out Across Heterogeneous Stores

#Abstract

An application that has outgrown a single store usually ends up writing to each store directly from the request handler. The pattern is easy to reach for and it fails in a specific way: when one write succeeds and another does not, nothing in the system knows, and the time until somebody notices is bounded by nothing at all. This report describes an outbox construction in which entity state, a versioned event and a dispatch record commit inside one transaction, and every derived projection is applied asynchronously from the resulting log. It separates the four failure modes of handler-side writes that are usually discussed as one, states the four properties the construction does and does not provide, and argues that the useful claim is not atomicity across stores but a bounded and observable divergence window. The evaluation is honest about its shape: the latency figures are measured from one deployment, and the comparison against dual writes is analytic, anchored by a single field incident in which the divergence went undetected for eleven days.

#An eleven day silence

The incident that produced this note was not dramatic. A service wrote an asset record to a relational store and then wrote a derived document to a search index. Between those two statements the process was terminated during a routine rolling deploy. The relational write had committed. The search write had not been attempted. No error was raised anywhere, because from the perspective of every component involved nothing had gone wrong: the database completed its transaction, the deployment completed its rollout, and the request had already returned 201 to a client that was entirely satisfied.

Eleven days later somebody searched for the asset by name, did not find it, and filed a bug against search relevance. The record had been visible by direct lookup the whole time. It was invisible to search the whole time. Nothing in between had the information required to notice the disagreement.

The failure was not that the write was lost. The failure was that the system had no quantity, anywhere, whose value would have been wrong.

That last point is what changed my mind about the pattern. I had previously treated dual writes as an availability and correctness tradeoff to be managed with retries and care. The eleven days made it clear that the real deficiency is observability: there is no lag metric for a write that was never attempted.

#The shape of a dual write, and four distinct ways it breaks

Consider a handler that must reflect a change into a primary store and into a set of derived stores. In the code this is a sequence of calls. Discussion of the pattern tends to treat its failure as one thing, usually called partial failure, but the four cases below have different detection properties and different repairs, and conflating them is why the mitigations people reach for do not work.

#Interruption between writes

The case above. The process stops after the first write and before the second. There is no in-process error handler left to run, so retry logic, deferred cleanup and compensating writes are all unavailable by construction: the code that would have performed them is gone. This is the case that produces unbounded detection time, and it is not rare, because processes are stopped deliberately and frequently by deploys, autoscaling and node drains.

#Failure of a later write with the process intact

The second store returns an error and the handler is still running. This is the case that mitigations are usually designed for, and it is the least dangerous, because an error is a signal and the handler can act on it. The difficulty is what to do: the first write has committed and cannot be rolled back, so the handler must either compensate, which is discussed below, or return an error to a client whose primary write in fact succeeded, which is a lie of a different kind.

#Ordering inversion under concurrency

Two concurrent updates to the same entity commit to the primary store in one order and reach a derived store in the opposite order, because the two handlers were scheduled differently after their first write returned. Both writes succeeded. No error occurred anywhere. The derived store now holds an older value than the primary and will hold it indefinitely, since nothing will write to that entity again until it next changes. This case is invisible to every retry-based mitigation, because nothing failed.

#Silent divergence through retry

A write to a derived store times out at the client while succeeding at the server. The handler retries. If the derived write is not idempotent, the store now holds a doubled counter, a duplicated edge or a second copy of a document. The primary store is correct. The derived store is wrong in a way that no comparison of record counts will flag as missing, only as excess, which is the harder direction to notice because most reconciliation jobs look for absence.

Only the second of these produces an error at the moment of failure. The other three are silent, and two of them cannot be addressed by anything the handler does, because the handler either no longer exists or never observed a problem.

#Why compensation does not close the gap

The standard answer to a partially completed multi-store operation is a compensating action, which is the saga model in its original form: a sequence of local transactions each with a defined compensation, run backwards when a step fails [1]. Sagas are a sound construction and they are used here in a different part of the system, so this is not an argument against them generally.

They do not solve this problem for two reasons. First, compensation requires a coordinator that survives the failure. If the compensating logic lives in the same process as the forward logic, case one destroys both together, and if it lives elsewhere then that elsewhere is itself a durable log of intended work, which is the construction described below arrived at by a longer route. Second, the compensation for a committed primary write is a second primary write that undoes it, which is visible to any client that read in between and which is frequently not expressible at all: an asset created, referenced by three other records, and then uncreated is not the same as an asset that never existed.

#The construction

The design goal is deliberately weaker than the one people usually state. It is not to make the fan-out atomic. It is to arrange that every intended derived write is recorded durably at the moment the primary write commits, so that the set of outstanding derived writes is always a queryable quantity.

#One transaction, three rows

Entity state, the event describing the change, and a dispatch record commit together against a single store, so the atomicity involved is ordinary single-store atomicity and requires nothing exotic [2].

1BEGIN;
2  UPDATE assets
3     SET attrs = $3, version = version + 1
4   WHERE id = $1 AND version = $2;          -- optimistic concurrency: 0 rows means conflict
5  INSERT INTO events (agg_id, version, type, payload) VALUES (...);
6  INSERT INTO outbox (event_id, stream_key)  VALUES (...);
7COMMIT;                                     -- the notification fires here, not before
Copy

The version predicate on the update does more than prevent lost updates. It supplies the monotonic per-entity sequence that the appliers later use to reject stale work, which is what makes ordering inversion detectable rather than silent. An update that matches zero rows aborts the whole transaction, so a conflict produces no event and no outbox row.

#The relay

A single leader-elected process reads the outbox in commit order and publishes each entry to the streams named by its stream key. Leadership matters for ordering rather than for correctness: two relays publishing concurrently would still deliver every entry, but they could deliver two entries for the same entity out of order, which reintroduces the third failure case at a different layer.

The relay wakes on a database notification issued at commit. It also polls on a long interval, not as the primary mechanism but as a backstop, because a notification is a best-effort signal that can be lost if no listener is connected at the moment it fires. The poll interval therefore bounds recovery from a lost notification rather than bounding normal latency, and those two roles being confused is what produced the performance problem described later.

#What an applier must promise

Delivery is at-least-once with per-entity ordering, so every applier must be idempotent. Rather than leaving that as a documented expectation, which is the same class of requirement as remembering a tenant predicate and fails the same way, it is enforced structurally: each projection stores the version it last applied per entity, and an applier discards any event whose version is not greater.

1func (p *Projection) Apply(ctx context.Context, e Event) error {
2	seen, err := p.appliedVersion(ctx, e.AggID)
3	if err != nil {
4		return err
5	}
6	if e.Version <= seen {
7		return nil // duplicate or reordered delivery, already reflected
8	}
9	return p.write(ctx, e)
10}
Copy

This single gate closes both the retry case and the inversion case. A duplicate delivery carries a version already seen and is dropped. A delivery that arrives after a newer one carries a lower version and is dropped, leaving the newer value in place, which is the correct outcome rather than merely a safe one.

#Four properties, stated without overstatement

The outbox pattern is often described in language that claims more than it provides. The following are what this construction actually gives, and I have tried to state each so that its negation would be testable.

1. P1. No orphan state and no orphan event. Entity state, its event and its dispatch record are written in one transaction against one store, so no committed state exists without a corresponding event, and no event exists describing a state change that did not commit. This follows directly from single-store atomicity and needs no further argument.
2. P2. At-least-once delivery to each projection. An outbox row is removed only after the relay has published it. A relay that fails between publishing and recording that fact will publish again on recovery, which is why P3 is required rather than optional.
3. P3. Per-entity ordering at the applier. Deliveries for one entity are applied in version order regardless of the order they arrive in, because out-of-order and duplicate deliveries are discarded by the version gate. This is a per-entity guarantee only. No claim is made about the relative order of events for different entities.
4. P4. Bounded, observable divergence. The time between a primary commit and its reflection in a projection is bounded by relay lag plus applier lag, and both are quantities the system measures continuously. This is the property the dual write lacks entirely, and it is the one worth the construction.

What is deliberately absent from that list is any claim of cross-store atomicity or of linearizability across the projections [3]. A reader of a projection may observe a state older than one already committed, and no ordering is guaranteed between a projection read and a primary read.

#The property that matters most is rebuild

Everything above concerns steady-state correctness. The property that has been most valuable in practice is different, and it is a consequence of the event log existing rather than of the delivery guarantees.

Because every change is recorded as a versioned event before any projection sees it, any projection can be discarded entirely and reconstructed by replaying the log. That converts an entire category of incident from an investigation into an operation. A search index whose mapping was wrong for a week, a graph projection that applied a buggy transformation, an index that was silently dropped: in each case the repair is to truncate and replay rather than to determine which records were affected and repair them individually.

This is the same reasoning that motivates treating the log as the primary integration point between systems rather than as a transport [4] [5], and it is why the events table is retained beyond the lifetime of the outbox rows that dispatch them. The outbox is a work queue and is pruned aggressively. The event log is history and is not.

#What the relay costs in latency

The final term is a maximum rather than a sum because projections are applied concurrently, so the slowest one determines when the fan-out is complete. That makes the tail of the slowest applier the quantity that matters, which is the general pattern for any operation that must wait on several parallel components [6].

The first implementation polled the outbox every 200ms. That placed a hard floor of 200ms on projection visibility, with an expected wait of about half that for a uniformly arriving write, for reasons that had nothing to do with the design and everything to do with the poll having been written first as a placeholder. Replacing it with a notification-driven wake, keeping a slow poll purely as the backstop described earlier, moved the common case to roughly 4ms.

I record this partly because it is a fifty-fold improvement from deleting code, and partly because it is a good example of a measurement that would have been misread. Before the change, the dominant term in the latency budget was the relay, and the obvious conclusion from a stage breakdown would have been that the relay needed optimising. The relay was not slow. It was asleep.

#Bounding the divergence window

The comparison against handler-side writes is best made on exposure rather than on failure probability, since the constructions do not differ in how often an individual write fails.

Here λ is the write rate, p the per-write failure probability against one derived store, D the set of derived stores, and T the time until a divergence is detected. The middle factor is the probability that at least one of the derived writes fails, and it grows with the number of stores, which is worth stating plainly because adding a fourth derived store to a system with three is usually treated as a linear increase in work rather than as an increase in the failure surface of every write.

The outbox does not reduce p. Derived writes still fail at the same rate. What it changes is T. Under the outbox, an undelivered write is a row in a table and an unapplied write is a gap in a version sequence, so T is bounded by the alerting threshold on relay and applier lag, typically seconds. Under handler-side writes, T is bounded by nothing, because in three of the four failure cases no component ever holds evidence that work is outstanding. The eleven day incident is one sample from that unbounded distribution and should be read as an existence proof rather than as a typical value.

#Three things learned from operating it

#The lag metric needs two thresholds

Relay lag alerting on a single threshold is either too noisy during normal bursts or too slow during real stalls. What worked was alerting separately on lag magnitude and on lag derivative, since a queue that is deep but draining is a different condition from a shallow queue that is not draining at all, and only the second indicates the relay is stuck.

#Failover is a correctness event, not an availability event

Relay leadership changes are infrequent enough that it is tempting to treat them as routine. They are the moment at which duplicate delivery is most likely, because a leader that lost its lease may still be in flight with a batch the new leader will republish. The version gate handles this, which is precisely why the gate must be structural rather than a documented expectation on applier authors.

#Outbox retention and replay depth are the same decision

These look like two independent policies and are frequently configured by different people. Pruning the outbox aggressively is correct, but if the event log retention is set from the same intuition, the rebuild property quietly stops working, and it stops working invisibly, since nothing exercises a full replay until the day it is needed. A periodic rebuild of one small projection into a scratch index is the cheapest way to keep that path honest.

#Approaches considered and not taken

Two-phase commit across the relational store, the search index and the graph engine is possible in principle. The coordinator becomes an availability bottleneck, since a participant that fails after the prepare phase blocks the others until it recovers, which is the blocking property inherent to the protocol rather than an artefact of any implementation [7]. The heterogeneity is also decisive: two of the three participants do not offer a prepare phase at all.

Change data capture from the write-ahead log removes the outbox table by deriving events from the storage engine's own log. It is a good fit when the derived stores need row-level replicas of tables. It was rejected here because the events are domain events rather than row diffs: the payload published is what changed in the model's terms, which the write-ahead log does not know. Deriving domain events from row diffs downstream reintroduces the coupling the projections were meant to avoid.

Deterministic ordering in the style of Calvin sequences transactions before execution so that every replica reaches the same state without agreement at commit [8]. This is an elegant answer to a related problem, and it presumes control over all participating stores. Here two of the participants are third-party engines with their own execution models.

Periodic reconciliation sweeps that compare primary and derived stores and repair differences are a common retrofit for existing dual-write systems. They bound T at the sweep interval, which is a genuine improvement over nothing. The cost is that the sweep must read both stores in full, so its cost grows with total data rather than with change rate, and it detects the excess case from silent retry only if it is written to look for records the primary does not have, which sweeps written after an absence incident typically are not.

#Where this is the wrong answer

The construction assumes derived stores can lag. Any read path that cannot tolerate that must be routed to the primary store instead of waiting, which is the practical form of the read-your-writes session guarantee [9] [10] [11]. If most read paths turn out to need this, the projections are not serving a useful purpose and the correct response is to remove them rather than to tighten the relay.

It also assumes per-entity ordering suffices. A workflow whose correctness depends on the relative order of changes to different entities, for example a transfer between two accounts observed as a pair, needs either a coarser aggregate boundary so both entities share a sequence, or a genuine transactional read across both, and the outbox provides neither [12].

Finally it assumes an operational team. The relay is a component with leadership, a lag metric and somebody who is expected to look at it. A system with no operational capacity is better served by fewer stores.

#What would settle the open questions

The central comparison in this report is analytic. I have a measured latency profile for the outbox and a model for divergence exposure under dual writes, but no measured distribution of T for the dual-write case, and one anecdote at eleven days. Obtaining a real distribution would mean operating a dual-write system under deliberate fault injection across process kills, network partitions to individual derived stores and induced concurrency on hot entities, then measuring detection time for each of the four failure cases separately. That experiment is straightforward to design, moderately expensive to run, and has not been run here.

The latency numbers come from one deployment shape, with the relay co-located with the primary store. Architectures that separate compute from durable storage have materially different commit and notification characteristics [13], and the 4ms figure should not be carried across to them.

A second open question is whether the version gate is sufficient for projections that aggregate across entities, such as a count or a rollup, where the relevant sequence is not per-entity and idempotence does not follow from a per-entity version comparison. In the current system those projections are recomputed rather than incrementally maintained, which sidesteps the question rather than answering it.