Your global SaaS runs an active-active multi-master relational topology (PostgreSQL BDR / MySQL Group Replication style async bi-directional replication) between `us-east-1` and `eu-west-1` serving 6,000 writes/sec. A 9-minute transatlantic fiber cut partitions the regions while both continue accepting writes. Both regions update the same `account_balances` and `subscription_entitlements` rows. On recovery, the replication engine applies Last-Write-Wins (LWW) using `updated_at` wall-clock timestamps. Due to a 180ms NTP drift in `eu-west-1`, 1,400 valid `us-east-1` payments and plan upgrades are silently overwritten without error logs. Redesign the replication conflict-detection, ordering, and recovery pipeline to eliminate silent data loss.
From patterns to decisions.
Your global SaaS runs an active-active multi-master relational topology (PostgreSQL BDR / MySQL Group Replication style async bi-directional replication) between `us-east-1` and `eu-west-1` serving 6,000 writes/sec. A 9-minute transatlantic fiber cut partitions the regions while both continue accepting writes. Both regions update the same `account_balances` and `subscription_entitlements` rows. On recovery, the replication engine applies Last-Write-Wins (LWW) using `updated_at` wall-clock timestamps. Due to a 180ms NTP drift in `eu-west-1`, 1,400 valid `us-east-1` payments and plan upgrades are silently overwritten without error logs. Redesign the replication conflict-detection, ordering, and recovery pipeline to eliminate silent data loss.
Wall clocks across regions drift.
Wall clocks across regions drift by tens or hundreds of milliseconds under NTP, so a causally older write in a fast-clock region receives a higher timestamp and overwrites a newer write in a slower-clock region.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Wall-clock LWW is simple to implement but fundamentally unsafe for financial or entitlement state. |
Step 1
Observe that NTP only guarantees ~10-250ms accuracy over WAN, not strict real-time ordering.
Step 2
Trace how LWW discards the losing write completely with no error or audit trail.
Step 3
Identify that even with zero clock skew, concurrent writes during a partition have no total causal order.
Alternative 1
Single-writer primary per tenant.
Failure Mode 1
Silent data loss of upgrades, payments, and revocations.
# Why LWW fails: EU clock is +180ms ahead of US
us_write = {"tier": "ENTERPRISE", "wall_ts": 1731450000100} # True time: .100s
eu_write = {"tier": "STARTER", "wall_ts": 1731450000130} # True time: .950s (earlier!)
# Naive LWW picks eu_write (130 > 100) and silently downgrades the customer!
Maintain a 64-bit HLC (pt_ms.
Maintain a 64-bit HLC (pt_ms << 16) | logical_counter and require WHERE hlc_ts = :base_hlc on replicated updates, routing mismatches to replication_conflicts.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Requires adding `hlc_ts` and `origin_region` columns to replicated tables and tracking `base_hlc` in CDC events. |
Step 1
Implement HLC.now() and HLC.update(remote_hlc) to preserve happens-before ordering.
Step 2
Send (base_hlc, new_hlc, payload) in every replication event.
Step 3
Execute conditional UPDATE ... WHERE id = %s AND hlc_ts = %s; if rowcount == 0, insert into replication_conflicts.
Alternative 1
CockroachDB / Spanner synchronous Raft per range (trades 85ms WAN latency for serializability).
Failure Mode 1
If base_hlc is omitted from the CDC payload, concurrent writes cannot be distinguished from sequential ones.
import time
class HybridLogicalClock:
def __init__(self):
self.pt = int(time.time() * 1000)
self.lc = 0
def now(self) -> int:
phys = int(time.time() * 1000)
if phys > self.pt:
self.pt, self.lc = phys, 0
else:
self.lc += 1
return (self.pt << 16) | (self.lc & 0xFFFF)
def receive(self, remote_hlc: int) -> int:
r_pt, r_lc = remote_hlc >> 16, remote_hlc & 0xFFFF
phys = int(time.time() * 1000)
if phys > self.pt and phys > r_pt:
self.pt, self.lc = phys, 0
elif self.pt == r_pt:
self.lc = max(self.lc, r_lc) + 1
elif r_pt > self.pt:
self.pt, self.lc = r_pt, r_lc + 1
else:
self.lc += 1
return (self.pt << 16) | (self.lc & 0xFFFF)
Replace mutable balance updates with.
Replace mutable balance updates with an append-only balance_ledger table keyed by (origin_region, tx_id) so all regional deltas merge commutatively.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Eliminates lost-update conflicts completely, though overdrafts during a partition require async reconciliation or credit limits. |
Step 1
Store every credit/debit as an immutable INSERT with delta_cents.
Step 2
Replicate INSERTs bi-directionally with ON CONFLICT (origin_region, tx_id) DO NOTHING.
Step 3
Compute current balance via a periodic materialized rollup + recent deltas.
Alternative 1
PN-Counter CRDT per region (us_delta, eu_delta columns).
Failure Mode 1
Allowing UPDATE or DELETE on balance_ledger breaks commutativity.
CREATE TABLE balance_ledger (
origin_region SMALLINT NOT NULL,
tx_id UUID NOT NULL,
account_id BIGINT NOT NULL,
delta_cents BIGINT NOT NULL,
hlc_ts BIGINT NOT NULL,
PRIMARY KEY (origin_region, tx_id)
);
-- Concurrent US (-500) and EU (+1000) both survive merge:
-- SUM(delta_cents) converges identically in both regions.
Tag every replicated transaction with.
Tag every replicated transaction with origin_region (or PostgreSQL replication origin session state) and configure the CDC publisher to stream only locally originated changes.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Designating one tie-breaker region for conflict resolution is simple and deterministic, but pauses auto-resolution if that region is down. |
Step 1
Set pg_replication_origin_session_setup('eu_origin') in the applier connection.
Step 2
Configure logical replication / Debezium to ignore transactions with a non-local origin_id.
Step 3
Run the deterministic conflict resolver in exactly one designated tie-breaker region (e.g., us-east-1).
Alternative 1
Deterministic tie-breaker rule executed identically by both regions (e.g., SUSPENDED > ACTIVE).
Failure Mode 1
Forgetting to set the replication origin in an admin/backfill script triggers a feedback storm across the WAN.
-- PostgreSQL applier marks incoming changes with remote origin
SELECT pg_replication_origin_session_setup('origin_eu_west');
-- Only local writes (origin = NULL) are published back to EU-West
Abandon async multi-master when strict.
Abandon async multi-master when strict global uniqueness or zero-overdraft invariants cannot tolerate 85ms partitions, and instead use single-writer per shard/tenant or global Raft (CockroachDB/Spanner).
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Single-writer per tenant eliminates split-brain completely, at the cost of 85ms write latency if a user travels out-of-region. |
Step 1
Measure true cross-region conflict rate and business cost of post-partition overdrafts.
Step 2
If strict serializability is mandatory, shard by tenant_id with a single authoritative Raft leaseholder per shard.
Step 3
Keep read replicas local for < 5ms reads while routing writes to the tenant's leaseholder.
Alternative 1
Multi-region CockroachDB with REGIONAL BY ROW locality.
Failure Mode 1
Misconfigured GeoDNS routing a tenant's traffic to the wrong region adds 85ms latency per hop.
# Envoy / Gateway tenant-affinity routing rule
routes:
- match:
prefix: "/v1/ledger"
headers:
- name: "x-tenant-region"
exact_match: "eu-west-1"
route:
cluster: eu_west_primary
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Never use raw wall-clock LWW for multi-master financial or inventory state; pair Hybrid Logical Clocks (HLC) and row-level origin tracking with domain-specific semantic merges (or delta/CRDT columns) and quarantine unresolvable conflicts.
- 02Trade-off tip: Eliminates lost-update conflicts completely, though overdrafts during a partition require async reconciliation or credit limits.
- 03Watch out for: Forgetting to set the replication origin in an admin/backfill script triggers a feedback storm across the WAN.
Active Active Split Brain Recovery — Normal → Load / Fault → Recovery
Never use raw wall-clock LWW for multi-master financial or inventory state; pair Hybrid Logical Clocks (HLC) and row-level origin tracking with domain-specific semantic merges (or delta/CRDT columns) and quarantine unresolvable conflicts.
Putting Active-Active Multi-Master Split-Brain Recovery Together
Never use raw wall-clock LWW for multi-master financial or inventory state; pair Hybrid Logical Clocks (HLC) and row-level origin tracking with domain-specific semantic merges (or delta/CRDT columns) and quarantine unresolvable conflicts.
Putting Active-Active Multi-Master Split-Brain Recovery Together
- Implement
HLC.now()andHLC.update(remote_hlc)to preserve happens-before ordering. - Send
(base_hlc, new_hlc, payload)in every replication event. - Execute conditional
UPDATE ... WHERE id = %s AND hlc_ts = %s; ifrowcount == 0, insert intoreplication_conflicts.
Interview Readiness Check
Tag every replicated transaction with origin_region (or PostgreSQL replication origin session state) and configure the CDC publisher to stream only locally originated changes.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Active-Active Multi-Master Split-Brain Recovery
Never use raw wall-clock LWW for multi-master financial or inventory state; pair Hybrid Logical Clocks (HLC) and row-level origin tracking with domain-specific semantic merges (or delta/CRDT columns) and quarantine unresolvable conflicts.
