One transactional store per write; propagate changes asynchronously

TL;DR

A single logical write operation commits to exactly one transactional data store. Don’t try to synchronously write the same operation across two transactional stores and keep them atomically consistent. Update every other system asynchronously, from a reliable, ordered event stream derived from that one committed write. And don’t reach for two-phase commit (2PC) to make the multi-store write atomic — distributed transactions across heterogeneous stores are a hornet’s nest of coordinator failures, held locks, blocking, and operational pain; the cure is worse than the disease.

If your primary store is a relational database (e.g. PostgreSQL), use the transaction to your advantage: its write-ahead log (WAL) is already a reliable, ordered, replayable journal (ZFN-12). Stream changes from it to update everything else — and you can write your own domain events into the same transaction so they commit atomically with the data and inherit the log’s ordering.

Context

The moment a single operation needs to land in two places — the primary database and a search index, a cache, an analytics store, another service’s database — people reach for one of two bad options:

• Write to both synchronously and hope. The two writes aren’t atomic: a crash or error between them leaves the stores disagreeing, with no clean recovery, and you spend forever writing reconciliation jobs to paper over the gaps.
• Use 2PC / a distributed transaction to make them atomic. This is where the pain lives. The coordinator becomes a critical, stateful single point; participants hold locks across the network while they wait; an in-doubt transaction after a coordinator failure blocks resources until a human intervenes; availability drops to the product of all participants’; and many real stores don’t support it well or at all. 2PC trades a data-integrity problem you can manage for an availability-and-operations problem you mostly can’t.

The way out is to stop pretending the write is atomic across systems and instead make one store the source of truth, commit there transactionally, and treat every other system as a derived consumer fed by an ordered event stream. You accept eventual consistency for the derived systems — which is almost always fine, and is the correct trade (ZFN-2): the source of truth stays strongly consistent; the copies converge.

Recommendation

One transactional commit per logical write; everything else is downstream of an ordered log.

• Pick the single source of truth for each piece of state and commit the logical write there, in one transaction. Other systems do not participate in that transaction.
• Derive an ordered, reliable event stream from the commit, and drive all other updates from it. Downstream consumers (search index, cache, other services, analytics) react to those events. Because delivery is at-least-once, consumers must be idempotent (ZFN-19); because the stream is ordered, they apply changes in the right sequence.
• Use the database’s own log as that stream. A relational WAL is a durable, ordered, replayable journal — exactly ZFN-12’s journal primitive, already built. Stream it via logical replication / change-data-capture; it’s also the same mechanism behind keeping an in-memory view fresh (ZFN-21).
• Emit domain events atomically with the data. When the change you want to publish is richer than a row diff, write the event in the same transaction: a transactional outbox table the CDC stream picks up, or a custom WAL message (e.g. Postgres pg_logical_emit_message). Either way the event commits if and only if the data does — no dual-write gap — and rides the same ordered log.
• Never 2PC across heterogeneous stores. If a flow genuinely spans multiple owners and needs coordination, model it as a saga — a sequence of local transactions with compensating actions — driven by the event stream, not a distributed lock.

Scope. This is about a single logical write needing to affect multiple stores/systems. Multiple tables in one relational database in one transaction is fine — that’s a single store. The rule is one transactional store per operation, not one table.

Consequences

Easier:

• No dual-write inconsistency and no 2PC: the write either commits to the one store or it doesn’t, and everything else is reliably caught up from the ordered log.
• High availability — derived systems can be down or slow without blocking the primary write; they catch up by replaying the log.
• Replayability falls out: rebuild a derived store, add a new consumer, or reprocess by re-reading the journal from a point in time.
• The source of truth stays strongly consistent; you only spend eventual-consistency complexity where it’s cheap to accept.

Harder:

• The rest of the system is now eventually consistent, which you must design for — including read-your-writes, which needs its own handling (ZFN-25).
• You operate a CDC/outbox pipeline and idempotent consumers — real infrastructure, and ordering/lag to monitor.
• Cross-system invariants that truly need to hold synchronously are genuinely hard without a shared transaction; usually the right answer is to redraw the boundary so the invariant lives inside one store, not to add 2PC.

References

• ZFN-12 — the WAL is a journal; derived updates flow through ordered streams and queues.
• ZFN-19 — at-least-once event delivery means consumers must be idempotent.
• ZFN-21 — the same WAL-as-source feeds in-memory views instead of caches.
• ZFN-2 — keep the source of truth correct; accept eventual consistency for derived copies.
• ZFN-25 — handle the read-your-writes gap that async propagation creates.
• The transactional outbox and log-based change-data-capture patterns; sagas as the alternative to distributed transactions.

Changelog

• 2026-06-12: First published as a Field Note.