Multi-Region Database Design

In one line

Multi-region designs place data near users and survive regional outages. Options: single-writer with cross-region read replicas, active-passive, geo-partitioning (each user's data lives in a home region), or multi-writer active-active with conflict resolution or consensus. Every choice trades write latency, consistency, data residency and complexity.

0/5 · 0%

Think of it like this

A company with offices in Mumbai, Frankfurt and Virginia. Either all decisions go through head office (slow for far offices), each office owns its own customers (fast, but cross-office deals need coordination), or every office decides independently and reconciles later (fast, but conflicts).

Key ideas

01
Single primary + remote read replicas: simple, and reads are local, but remote writes pay cross-region latency (~100–250 ms round trips) and reads are stale by the lag.
02
Geo-partitioning: rows carry a home region (user's country) and are stored and written there; global tables (product catalogue) are replicated everywhere. Supports data residency (e.g. EU data stays in the EU). CockroachDB REGIONAL BY ROW and Spanner placement do this natively; with PostgreSQL, you shard by region.
03
Active-active multi-writer: any region accepts writes; conflicts resolved by LWW, CRDTs or application logic (DynamoDB global tables use LWW; Cassandra is multi-DC). Only safe for data that tolerates it, such as carts, preferences or counters.
04
Consensus across regions (Spanner, CockroachDB with 3–5 regions): strongly consistent global writes at the cost of cross-region latency per commit; survives region loss with a majority quorum.
05
Design questions: where are users; which data must be strongly consistent globally (usernames, balances); residency laws; acceptable write latency; failure domain (zone vs region).

Code & diagrams

geo-partitioning.sqlsql

-- CockroachDB multi-region
ALTER DATABASE shop SET PRIMARY REGION "ap-south-1";
ALTER DATABASE shop ADD REGION "eu-central-1";
ALTER DATABASE shop ADD REGION "us-east-1";
ALTER DATABASE shop SURVIVE REGION FAILURE;

ALTER TABLE users SET LOCALITY REGIONAL BY ROW;      -- each row stored/written in its crdb_region
ALTER TABLE product SET LOCALITY GLOBAL;             -- fast reads everywhere, slower writes

-- PostgreSQL equivalent: one cluster per region, route by users.home_region,
-- replicate global reference data outward (logical replication), CDC for analytics.

Interview problem

The problem

Global SaaS with EU data residency

A B2B SaaS has customers in India, the EU and the US. EU customer data must stay in the EU. Users expect < 100 ms API latency. Billing and authentication are global. Design the database topology.

When it breaks

Active-active writes with last-write-wins on account balances

What you see

Concurrent debits in two regions both succeed; LWW keeps one balance update and loses the other, silently creating money.

Fix & prevent

Give balances a single home region (or consensus-backed writes); use active-active only for conflict-tolerant data.

Explain it without notes

What is geo-partitioning and why is it popular?

Practice

Estimate commit latency for a consensus write with replicas in Mumbai, Singapore and Frankfurt, leader in Mumbai.

Trade-offs

↔
Local latency and residency versus global consistency and operational complexity.

Done when you can

I can choose a multi-region topology from latency, consistency and residency requirements.

Command Palette