Design a Multi-Region Active-Active Database

Multi-region deployment: deploy database replicas across 3–5 geographically distributed regions (e.g., US-East, EU-West, AP-Southeast); each region has a full copy of the data; any region can serve reads AND writes independently — true active-active (not active-passive)

Local reads and writes: users routed to the nearest region; reads and writes serviced locally with single-digit millisecond latency; no cross-region round trip for normal operations; the database 'feels local' everywhere in the world

Cross-region replication: changes written in one region asynchronously replicated to all other regions; replication lag: typically 50–300ms (cross-region network latency); data eventually consistent across regions; replication handles network partitions and temporary outages gracefully

Conflict resolution: concurrent writes to the same record in different regions → conflict; system must detect and resolve conflicts automatically; strategies: last-write-wins (LWW by timestamp), conflict-free replicated data types (CRDTs), or application-level resolution; conflicts should be rare in well-designed systems (< 0.001% of writes)

Global strong consistency (optional mode): for specific high-value operations (financial transactions, inventory deductions), support globally strong consistency — a write in one region is visible to reads in ALL regions before the write returns; requires synchronous cross-region coordination (higher latency: 200–500ms)

Region failover: if an entire region goes down → traffic automatically rerouted to the next-nearest region; no data loss (already replicated); failover transparent to the application; RPO ≈ 0 (replication lag), RTO < 60 seconds (DNS/routing switch)

Data sovereignty and geo-fencing: some data must remain within a specific geographic boundary (GDPR — EU user data in EU only); support per-table or per-row region pinning; pinned data writable only in the designated region; reads globally available (or restricted based on policy)

Schema management: schema changes (ALTER TABLE, ADD INDEX) must propagate consistently across all regions; online schema change — no downtime during DDL; schema version tracked globally; all regions must apply schema changes in the same order

Global secondary indexes: support secondary indexes that span all regions; queries on secondary indexes return globally consistent results (or best-effort with staleness bound); index updates replicated alongside data; trade-off: global indexes add cross-region latency on writes that update indexed columns

Observability and conflict auditing: expose per-region replication lag metrics; conflict rate per table; cross-region bandwidth usage; write amplification factor; provide conflict audit log (conflicting versions, resolution outcome, timestamp) for debugging and compliance