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-12Field Note · currentZFN-12 — Queues, topics, and journals are different tools — don't conflate themQueues (competing consumers), topics (fan-out), and journals (ordered, replayable logs) give different guarantees. Don't conflate them; a pipeline often uses several. Prefer journals over topics, but not where head-of-line blocking hurts. With queues, bound the concurrency.Open 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-2Field Note · currentZFN-2 — Engineering priority orderingWhen concerns conflict, prioritize security > correctness > availability > performance — and never trade a higher-ranked concern for a lower one. The rule binds the moment you must choose. Cite it instead of re-arguing it.Open 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-19Field Note · currentZFN-19 — Annotate read-only and idempotent endpoints; make every mutation idempotentAnnotate every endpoint as read-only (safe) or idempotent, in the schema, so infrastructure can retry, route to replicas, and cache safely. Make every state-changing endpoint idempotent (idempotency keys for create/charge/send); a non-idempotent retry double-applies.Open 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-12Field Note · currentZFN-12 — Queues, topics, and journals are different tools — don't conflate themQueues (competing consumers), topics (fan-out), and journals (ordered, replayable logs) give different guarantees. Don't conflate them; a pipeline often uses several. Prefer journals over topics, but not where head-of-line blocking hurts. With queues, bound the concurrency.Open 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-21Field Note · currentZFN-21 — Cache only immutable objects; treat caches as tech debtUse caches sparingly, only for immutable addressed objects — never for mutable DB results, where invalidation bugs and stale reads live; use projections instead. A cache in the data path is usually a patch over an architectural gap that trades correctness for performance.Open 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-25Field Note · currentZFN-25 — Track the version a client has seen for read-your-writesFor read-your-writes across backends, track the latest version a client has seen — a token or vector clock. Return it on write; reads then go to a backend at least that fresh. Hold it client-side (a token they present) or server-side (a gateway tracks the session and routes).Open 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-12Field Note · currentZFN-12 — Queues, topics, and journals are different tools — don't conflate themQueues (competing consumers), topics (fan-out), and journals (ordered, replayable logs) give different guarantees. Don't conflate them; a pipeline often uses several. Prefer journals over topics, but not where head-of-line blocking hurts. With queues, bound the concurrency.Open ZFN-12 → — the WAL is a journal; derived updates flow through ordered streams and queues.
• ZFN-19Field Note · currentZFN-19 — Annotate read-only and idempotent endpoints; make every mutation idempotentAnnotate every endpoint as read-only (safe) or idempotent, in the schema, so infrastructure can retry, route to replicas, and cache safely. Make every state-changing endpoint idempotent (idempotency keys for create/charge/send); a non-idempotent retry double-applies.Open ZFN-19 → — at-least-once event delivery means consumers must be idempotent.
• ZFN-21Field Note · currentZFN-21 — Cache only immutable objects; treat caches as tech debtUse caches sparingly, only for immutable addressed objects — never for mutable DB results, where invalidation bugs and stale reads live; use projections instead. A cache in the data path is usually a patch over an architectural gap that trades correctness for performance.Open ZFN-21 → — the same WAL-as-source feeds in-memory views instead of caches.
• ZFN-2Field Note · currentZFN-2 — Engineering priority orderingWhen concerns conflict, prioritize security > correctness > availability > performance — and never trade a higher-ranked concern for a lower one. The rule binds the moment you must choose. Cite it instead of re-arguing it.Open ZFN-2 → — keep the source of truth correct; accept eventual consistency for derived copies.
• ZFN-25Field Note · currentZFN-25 — Track the version a client has seen for read-your-writesFor read-your-writes across backends, track the latest version a client has seen — a token or vector clock. Return it on write; reads then go to a backend at least that fresh. Hold it client-side (a token they present) or server-side (a gateway tracks the session and routes).Open 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.