Set up multi-region for a Postgres app

Most 'we need multi-region' problems are solved by a CDN, edge caching, and a well-designed read path. Multi-region Postgres adds operational complexity that takes years to fully amortize. Before you start, profile: are slow pages actually waiting on cross-region DB hits, or are they waiting on uncached static assets, slow client JS, or N+1 queries?

Three topology options: (1) primary + read replicas (one writeable region, multiple read regions), (2) sharded by tenant (each region owns a slice of users), (3) true multi-master (every region writes, conflicts resolve). Start with (1). It covers 90% of needs at 10% of the operational cost. Only escalate if profiling shows write latency is the bottleneck.

On Neon, Supabase Team+, AWS RDS, or whatever managed Postgres you use, spin up a read replica per target region. Cross-region replication lag is typically 50-500ms depending on provider; verify the actual lag before you write code that assumes it.

Without app-level routing, every request hits the primary regardless of region. Split your DB connection: one for writes (always primary), one or more for reads (region-local replica). Most ORMs support multiple connections; route reads to the closest replica based on request region.

Read every DB query in the codebase. Classify each as:
- READ (SELECT, no side effects).
- WRITE (INSERT, UPDATE, DELETE, or any function that mutates state).
- TRANSACTION_READ (a SELECT inside a transaction that also writes).

Output:
1. A list of file:line references with classification.
2. A proposed code change for each READ to use the replica connection.
3. A list of TRANSACTION_READ queries to leave alone (must stay on primary).
4. Any query that's borderline (e.g. SELECT FOR UPDATE) - flag for human review.

Replicas lag, even fast ones. A user who creates a record on the primary and immediately reads from the replica may get the pre-create state. Three patterns: (1) read-your-own-writes by routing the next N seconds of reads to primary, (2) wait-for-replication if your provider exposes it, (3) accept the inconsistency for non-critical reads.

Replica lag is the silent killer. A replica with 30 seconds of lag will route reads that look stale; a replica with 5 minutes of lag will look broken. Monitor lag, alert on sustained lag over 5 seconds, fail over to primary if lag exceeds your tolerance.

Some writes are 'fire and forget' (analytics, audit logs, write-only feeds). For those, you don't need to wait on the round-trip to primary. Async write patterns: queue locally, flush to primary in the background. Your secondary-region users see fast page loads even on write-heavy interactions.

When primary goes down, what happens? Most managed Postgres providers offer automatic failover (promote a replica to primary). Test it. Run a fire drill: pick a quiet window, manually trigger the failover, time how long the app is down, capture the runbook.

Schema migrations are the most dangerous operation in multi-region. A migration on the primary must replicate cleanly to all replicas. Backwards-incompatible migrations break replicas mid-deploy. Always: add columns nullable first, deploy code that doesn't require them, backfill, then deploy code that uses them.

After deploy, measure. Track p50/p95/p99 page-load latency from each region. Compare against the baseline you measured in step 1. The goal: secondary-region users see latency within 30% of primary-region users. If you're not there, find the remaining bottleneck.