Dual-Write Inconsistency Between Systems

When an application writes to two systems in sequence (database then search index, database then cache, database then analytics store) and the second write fails, the two systems permanently diverge. Without an automated reconciliation mechanism, the inconsistency is silent and persistent: queries to the primary store return the correct state while queries to the secondary store return stale or missing data indefinitely. No alert fires because no individual system reports an error: the inconsistency exists only in the gap between them.

Dual-write is the pattern of writing to two stores synchronously within the same application request: write to PostgreSQL, then write to Elasticsearch. The write is logically atomic from the application's perspective, but physically it consists of two separate operations with no shared transaction boundary. If the second write fails (network timeout, shard unavailability, index mapping conflict), only the first write is persisted. The two stores are now inconsistent.

The failure is silent by design. PostgreSQL reports the write as successful (it is, for PostgreSQL). The HTTP response to the client indicates success. The client's subsequent search query goes to Elasticsearch and does not find the record: or finds the old version. The user observes behavior that appears to be a search lag (the record "hasn't appeared yet") rather than a permanent inconsistency. In many cases the inconsistency persists indefinitely: the record will never appear in search results because there is no mechanism to detect and repair the divergence.

The failure frequency scales with the reliability gap between the two systems. If PostgreSQL has 99.99% write success rate and Elasticsearch has 99.9% write success rate, dual writes to both systems fail for the Elasticsearch step at 0.1% of writes. For a system handling 1,000 writes/second, this produces 1 inconsistency/second: 86,400 diverged records per day. Even at 0.01% failure rate, 864 records per day accumulate a permanent divergence with no automated repair.

Retry logic provides partial mitigation but introduces new risks. If the first write succeeded and the second write fails, retrying the entire operation must be idempotent with respect to the first write: otherwise the retry overwrites a newer version of the record with an older version. Applications that retry dual-writes without idempotency keys on the first write can produce data corruption worse than the original inconsistency: the newer record written between the first attempt and the retry is silently overwritten with the original version.

Dual-write is the same underlying problem two_phase_commit solves for databases that both support a prepare protocol: getting an atomic outcome across two independent systems with no shared transaction. It rarely applies here in practice, because the usual second system (Elasticsearch, Redis, an analytics store) does not implement 2PC or XA, so a synchronous atomic-commit fix is not available even in principle. The outbox pattern (write to PostgreSQL with a pending outbox record, deliver the secondary write from the outbox transactionally) eliminates this failure class instead by making the secondary delivery an eventually-consistent async operation that is retry-safe and persistent across process restarts: it trades synchronous atomicity, which is not achievable here, for asynchronous reliability, which is.