Designing a Multi-Region Database: Replication Topologies, Conflict Resolution, and Latency-Aware Routing

A single-region database is simple. You write to one place, reads come from one place, and consistency is a solved problem at the transaction layer. The moment you span regions, you trade that simplicity for a set of hard problems: which writes win when two regions accept the same row concurrently, how you route reads without adding a round trip to the wrong continent, and how you satisfy data residency requirements without fracturing your schema.

This article covers the three main replication topologies, when each one makes sense, what conflict resolution actually looks like in production, and how to build latency-aware routing that does not require a centralized coordinator.

Why Multi-Region Matters

Three forcing functions justify the complexity:

Latency. A request from Tokyo to a database in us-east-1 pays roughly 150ms in network round trips before any query runs. For read-heavy workloads where p99 latency is a product requirement, that is the ceiling you cannot engineer around. A read replica in ap-northeast-1 changes the answer.

Availability. A single-region deployment is as available as that region. AWS us-east-1 has had multi-hour outages. If your SLA requires 99.99% uptime or your RTO is under 30 seconds, you need the database to survive a full region loss without manual intervention.

Data sovereignty. GDPR Article 44 restricts personal data transfers outside the EU without adequate protection. Healthcare, financial services, and government contracts often require data residency at the country level. The database tier is almost always in scope.

None of these are reasons to add a second region speculatively. The operational cost is real: schema migrations require coordinated rollouts, monitoring complexity doubles, and debugging replication lag at 3am in an active-active setup is significantly harder than debugging a single Postgres instance.

Replication Topology 1: Single-Leader

Single-leader replication is what most relational databases ship with by default. One node (the leader) accepts writes. Every other node (followers) receives a stream of changes from the leader and applies them in order. Reads can be served from followers, with the caveat that they may be behind.

This topology avoids write conflicts by definition: there is only one place where writes happen. The tradeoffs are:

• Write throughput is bounded by the leader’s capacity
• Writes from a distant region pay cross-region latency (writes to us-east-1 from Singapore still travel the Atlantic wire)
• Leader failover requires election and potential data loss if followers are behind

Single-leader works well when most of your traffic is reads and you can tolerate eventual consistency on reads. Postgres streaming replication, MySQL binlog replication, and Aurora Global Database all implement this pattern. Aurora Global Database is worth understanding specifically: it replicates at the storage layer using the Redo log rather than at the query layer, which reduces lag to typically under 1 second across regions.

// Single-leader routing: always write to the leader, route reads to the
// nearest follower with acceptable lag.

interface LeaderConfig {
  region: string;
  writeEndpoint: string;
  readEndpoint: string;
  lagThresholdMs: number;
}

interface FollowerConfig {
  region: string;
  readEndpoint: string;
  estimatedLagMs: number; // updated periodically via monitoring
}

class SingleLeaderRouter {
  private leader: LeaderConfig;
  private followers: FollowerConfig[];

  constructor(leader: LeaderConfig, followers: FollowerConfig[]) {
    this.leader = leader;
    this.followers = followers;
  }

  getWriteEndpoint(): string {
    return this.leader.writeEndpoint;
  }

  getReadEndpoint(requiredFreshness: "strong" | "eventual", callerRegion: string): string {
    if (requiredFreshness === "strong") {
      // Strong reads must go to the leader to avoid stale data.
      return this.leader.readEndpoint;
    }

    // Find the closest follower with acceptable lag.
    const acceptable = this.followers.filter(
      (f) => f.estimatedLagMs <= this.leader.lagThresholdMs
    );

    if (acceptable.length === 0) {
      // All followers are behind; fall back to the leader.
      return this.leader.readEndpoint;
    }

    // Prefer the follower in the caller's region, else pick the lowest-lag one.
    const regional = acceptable.find((f) => f.region === callerRegion);
    if (regional) return regional.readEndpoint;

    return acceptable.sort((a, b) => a.estimatedLagMs - b.estimatedLagMs)[0].readEndpoint;
  }
}

The requiredFreshness parameter is load-bearing. Most reads in a typical SaaS application can tolerate a few hundred milliseconds of staleness. Profile reads are fine from a follower. A payment status check immediately after a write is not.

Replication Topology 2: Multi-Leader

Multi-leader replication allows writes in multiple regions simultaneously. Each region has a leader that accepts writes locally and replicates those writes to the leaders in other regions. The benefit is write latency: a user in Singapore writes to a Singapore leader and pays local latency. The cost is conflict resolution: two regions can write the same row concurrently, and you have to decide what the correct final state is.

This is where most teams underestimate the complexity. Conflicts are not rare edge cases in a busy multi-leader setup. Any time two writes to the same row happen in different regions within the replication lag window (often 50-200ms for cross-continental links), you have a conflict.

CockroachDB, Cassandra (multi-datacenter), DynamoDB Global Tables, and FaunaDB all implement variants of multi-leader or leaderless topologies. Each one has a different answer to “what happens when two writes conflict.”

Replication Topology 3: Leaderless

Leaderless replication (popularized by Dynamo, Cassandra, and Riak) removes the concept of a designated writer. Any node can accept a write. Consistency is achieved through quorums: a write is only acknowledged when W nodes confirm it, and a read is only returned when R nodes respond and the caller can reconcile the versions. The invariant for strong consistency is W + R > N (where N is total replicas).

In practice, Cassandra deployments use LOCAL_QUORUM for both reads and writes within a data center, which avoids cross-region round trips for the quorum while relying on asynchronous replication between data centers. This means cross-region replication is eventually consistent by design.

The failure mode is silent divergence: two nodes can hold different values for the same key, and unless a read repair or compaction reconciles them, both versions persist. Read repair happens lazily at read time, which means a row that has not been read recently may have unreconciled replicas sitting in different nodes across regions.

Conflict Resolution

Conflict resolution is the hardest part of multi-region database design. There are three main strategies, each with clear boundaries for when it breaks down.

Last-Write-Wins

Last-write-wins (LWW) is the simplest strategy: when two versions of a row conflict, keep the one with the higher timestamp. Cassandra uses this by default. DynamoDB Global Tables use it.

The problem is clock skew. Physical clocks in distributed systems drift. A write that happened “earlier” by wall clock may have a higher timestamp due to drift on that server. LWW with physical clocks silently discards writes, and the discarded write is gone.

The mitigation is hybrid logical clocks (HLC) or vector clocks. An HLC combines physical time with a logical counter that increments whenever two events are observed to be concurrent. This gives you a total order that is consistent with causality even under clock skew.

// Hybrid logical clock: combines physical time with a logical counter.
// HLC guarantees that if event A happened before event B, A's HLC < B's HLC.

interface HLCTimestamp {
  physicalMs: number;  // wall clock milliseconds
  logical: number;     // counter for events in the same physical millisecond
  nodeId: string;      // tiebreaker for identical physical+logical timestamps
}

function hlcNow(lastKnown: HLCTimestamp, nodeId: string): HLCTimestamp {
  const now = Date.now();
  if (now > lastKnown.physicalMs) {
    return { physicalMs: now, logical: 0, nodeId };
  }
  // Clock went backward or two events in same millisecond: increment logical.
  return { physicalMs: lastKnown.physicalMs, logical: lastKnown.logical + 1, nodeId };
}

function hlcReceive(local: HLCTimestamp, remote: HLCTimestamp, nodeId: string): HLCTimestamp {
  const now = Date.now();
  const maxPhysical = Math.max(now, local.physicalMs, remote.physicalMs);

  if (maxPhysical === now) {
    return { physicalMs: now, logical: 0, nodeId };
  }
  if (maxPhysical === local.physicalMs && maxPhysical === remote.physicalMs) {
    return { physicalMs: maxPhysical, logical: Math.max(local.logical, remote.logical) + 1, nodeId };
  }
  if (maxPhysical === local.physicalMs) {
    return { physicalMs: maxPhysical, logical: local.logical + 1, nodeId };
  }
  return { physicalMs: maxPhysical, logical: remote.logical + 1, nodeId };
}

function compareHLC(a: HLCTimestamp, b: HLCTimestamp): number {
  if (a.physicalMs !== b.physicalMs) return a.physicalMs - b.physicalMs;
  if (a.logical !== b.logical) return a.logical - b.logical;
  return a.nodeId.localeCompare(b.nodeId);
}

function resolveConflictLWW<T extends { hlc: HLCTimestamp }>(a: T, b: T): T {
  return compareHLC(a.hlc, b.hlc) >= 0 ? a : b;
}

Even with HLC, LWW is only safe for last-write-semantics. If two users update the same profile field independently and both writes are correct, LWW throws one away. For any write pattern where concurrent writes to the same field represent independent valid mutations, LWW is the wrong tool.

CRDTs

Conflict-free replicated data types (CRDTs) resolve conflicts by ensuring all concurrent operations commute: apply them in any order and you get the same result. This is not magic. It only works because the data structure is designed so that the merge operation is associative, commutative, and idempotent.

The practical CRDTs worth knowing for database work:

• G-Counter: a grow-only counter. Each node has its own counter slot. The global value is the sum. Concurrent increments from different regions always produce the correct total because addition commutes.
• LWW-Register: a single value with a timestamp. This is the atomic unit of last-write-wins, but made explicit.
• OR-Set (Observed-Remove Set): a set where add and remove operations are tracked with unique tags. An element is in the set if any add tag exists for it with no corresponding remove. Concurrent add and remove of the same element resolve deterministically in favor of add.
• RGA (Replicated Growable Array): used in collaborative text editing. Each character has a unique ID and a “insert after” pointer. Concurrent inserts at the same position are ordered by node ID as a tiebreaker.

// G-Counter CRDT: grow-only, safe for concurrent increments across regions.

type NodeId = string;

interface GCounter {
  counts: Record<NodeId, number>;
}

function gcIncrement(counter: GCounter, nodeId: NodeId): GCounter {
  return {
    counts: {
      ...counter.counts,
      [nodeId]: (counter.counts[nodeId] ?? 0) + 1,
    },
  };
}

function gcMerge(a: GCounter, b: GCounter): GCounter {
  const allNodes = new Set([...Object.keys(a.counts), ...Object.keys(b.counts)]);
  const merged: Record<NodeId, number> = {};
  for (const node of allNodes) {
    merged[node] = Math.max(a.counts[node] ?? 0, b.counts[node] ?? 0);
  }
  return { counts: merged };
}

function gcValue(counter: GCounter): number {
  return Object.values(counter.counts).reduce((sum, n) => sum + n, 0);
}

// PN-Counter: supports both increment and decrement.
interface PNCounter {
  increments: GCounter;
  decrements: GCounter;
}

function pnIncrement(counter: PNCounter, nodeId: NodeId): PNCounter {
  return { ...counter, increments: gcIncrement(counter.increments, nodeId) };
}

function pnDecrement(counter: PNCounter, nodeId: NodeId): PNCounter {
  return { ...counter, decrements: gcIncrement(counter.decrements, nodeId) };
}

function pnMerge(a: PNCounter, b: PNCounter): PNCounter {
  return {
    increments: gcMerge(a.increments, b.increments),
    decrements: gcMerge(a.decrements, b.decrements),
  };
}

function pnValue(counter: PNCounter): number {
  return gcValue(counter.increments) - gcValue(counter.decrements);
}

CRDTs are not a universal solution. They work for specific data types and operations. A PN-Counter works for inventory reservation only if negative inventory is acceptable (or if you enforce a floor at the application layer with a compensation mechanism). For arbitrary JSON mutations to the same document, there is no CRDT that is also ergonomic to use.

Application-Level Conflict Resolution

When LWW loses correct writes and CRDTs do not fit the data model, you handle conflicts at the application layer. This means detecting conflicts and surfacing them for resolution, either automatically with domain logic or by presenting them to a user.

The detection step requires version vectors (similar in concept to vector clocks). Each write carries a version vector that describes which writes it has seen. When a read returns multiple versions of a row, the caller compares version vectors to determine if one is causally newer or if they are genuinely concurrent.

// Version vector conflict detection for application-level resolution.

type VersionVector = Record<NodeId, number>;

function vvIncrement(vv: VersionVector, nodeId: NodeId): VersionVector {
  return { ...vv, [nodeId]: (vv[nodeId] ?? 0) + 1 };
}

type Ordering = "before" | "after" | "concurrent" | "equal";

function compareVersionVectors(a: VersionVector, b: VersionVector): Ordering {
  const allNodes = new Set([...Object.keys(a), ...Object.keys(b)]);
  let aLeadsB = false;
  let bLeadsA = false;

  for (const node of allNodes) {
    const av = a[node] ?? 0;
    const bv = b[node] ?? 0;
    if (av > bv) aLeadsB = true;
    if (bv > av) bLeadsA = true;
  }

  if (!aLeadsB && !bLeadsA) return "equal";
  if (aLeadsB && !bLeadsA) return "after";  // a is causally newer than b
  if (bLeadsA && !aLeadsB) return "before"; // a is causally older than b
  return "concurrent"; // genuine conflict: neither dominates
}

interface VersionedRow<T> {
  data: T;
  vv: VersionVector;
  regionId: NodeId;
}

function detectConflict<T>(
  local: VersionedRow<T>,
  remote: VersionedRow<T>
): { conflict: true; versions: [VersionedRow<T>, VersionedRow<T>] } | { conflict: false; winner: VersionedRow<T> } {
  const ordering = compareVersionVectors(local.vv, remote.vv);

  if (ordering === "after") return { conflict: false, winner: local };
  if (ordering === "before") return { conflict: false, winner: remote };
  if (ordering === "equal") return { conflict: false, winner: local }; // same write

  // "concurrent": genuine conflict, surface to application logic.
  return { conflict: true, versions: [local, remote] };
}

Once you have concurrent versions, what you do with them is domain-specific. A shopping cart can merge line items by taking the union. A bank balance cannot merge concurrent debits and credits without running compensating transactions. A user profile field can prompt the user to choose. Document the policy per entity type; inconsistency in conflict handling is worse than a wrong choice applied consistently.

Latency-Aware Routing

Routing database requests to the right region is not just “pick the nearest endpoint.” You have to account for read staleness requirements, write path consistency, the current replication lag per region, and whether the request carries session affinity (a user who just wrote data should read from a region that has seen that write).

The routing layer needs three inputs per request: the caller’s region, the freshness requirement, and the session’s write vector (which regions have seen the user’s recent writes).

// Latency-aware router with session affinity and lag awareness.

interface RegionHealth {
  regionId: string;
  readEndpoint: string;
  writeEndpoint: string;
  latencyMs: number;        // measured round-trip from current process
  replicationLagMs: number; // lag behind the global write frontier
  healthy: boolean;
}

interface RoutingSession {
  lastWriteRegion: string;
  lastWriteAt: number; // epoch ms
}

interface RoutingRequest {
  type: "read" | "write";
  freshnessRequirement: "strong" | "bounded" | "eventual";
  session?: RoutingSession;
  staleness_budget_ms?: number; // for "bounded" reads
}

class MultiRegionRouter {
  private regions: RegionHealth[];
  private localRegion: string;

  constructor(regions: RegionHealth[], localRegion: string) {
    this.regions = regions;
    this.localRegion = localRegion;
  }

  route(request: RoutingRequest): string {
    const healthy = this.regions.filter((r) => r.healthy);

    if (request.type === "write") {
      // For single-leader: always route to the designated write region.
      // For multi-leader: route to local region to minimize write latency.
      const local = healthy.find((r) => r.regionId === this.localRegion);
      if (local) return local.writeEndpoint;
      // Local region unhealthy: fall over to the lowest-latency healthy region.
      return this.closestHealthy(healthy).writeEndpoint;
    }

    // Read path.
    if (request.freshnessRequirement === "strong") {
      // Strong reads must go to the primary (or the writer region in multi-leader).
      const primary = healthy.find((r) => r.replicationLagMs === 0);
      return primary ? primary.readEndpoint : this.closestHealthy(healthy).readEndpoint;
    }

    if (request.freshnessRequirement === "bounded" && request.staleness_budget_ms !== undefined) {
      // Eligible regions must have lag within budget.
      const eligible = healthy.filter(
        (r) => r.replicationLagMs <= (request.staleness_budget_ms ?? Infinity)
      );
      if (eligible.length === 0) {
        // No region meets staleness budget; fall back to strong read path.
        const primary = healthy.find((r) => r.replicationLagMs === 0);
        return primary ? primary.readEndpoint : this.closestHealthy(healthy).readEndpoint;
      }
      // Among eligible, prefer session-affine region, then closest.
      return this.selectWithAffinity(eligible, request.session);
    }

    // Eventual: route to lowest-latency healthy region, respecting session affinity.
    return this.selectWithAffinity(healthy, request.session);
  }

  private selectWithAffinity(candidates: RegionHealth[], session?: RoutingSession): string {
    if (session) {
      const sinceWriteMs = Date.now() - session.lastWriteAt;
      // Within 5 seconds of a write, prefer the writer's region to avoid reading
      // your own write from a lagging replica.
      if (sinceWriteMs < 5000) {
        const writerRegion = candidates.find((r) => r.regionId === session.lastWriteRegion);
        if (writerRegion) return writerRegion.readEndpoint;
      }
    }
    return this.closestHealthy(candidates).readEndpoint;
  }

  private closestHealthy(candidates: RegionHealth[]): RegionHealth {
    return candidates.sort((a, b) => a.latencyMs - b.latencyMs)[0];
  }
}

The session affinity window (5 seconds in the example) is not arbitrary. It should be set to the 99th percentile replication lag of your slowest replica. If your cross-region lag is consistently under 500ms, 2 seconds is sufficient and reduces the chance of bouncing strong reads to the leader unnecessarily. Monitor this and tune it.

Tradeoffs

Dimension	Single-Leader	Multi-Leader	Leaderless
Write latency	High for remote regions	Low (write locally)	Low (write locally)
Read latency	Low with local replicas	Low with local replicas	Low with local replicas
Conflict complexity	None (serialized)	High (must resolve)	High (must resolve)
Consistency model	Configurable to strong	Eventual by default	Tunable via quorum
Failover behavior	Leader election required	Automatic (other leaders exist)	Automatic (quorum survives)
Schema migration risk	Low	High (coordinate across leaders)	High (coordinate across nodes)
Operational complexity	Low to medium	High	High

Production Considerations

Schema migrations in multi-region. In a single-leader setup, run migrations on the leader; replicas pick up the DDL change via replication. In multi-leader and leaderless, you need a migration strategy that keeps all regions compatible during the rollout. The safe pattern: expand (add column, both old and new code work) then migrate data then contract (remove old column). Never ship a migration that breaks an older code version, because older versions may still be running in other regions.

Monitoring replication lag. Lag is your primary operational metric. Alert when lag exceeds your staleness budget. If it consistently exceeds 500ms, investigate the write throughput, network bandwidth between regions, or replication thread contention before adding more read replicas.

Conflict rate as a signal. In multi-leader setups, track the conflict rate per entity type. A high conflict rate on a specific table is a signal that the application’s write pattern is not compatible with multi-leader. Either serialize those writes through a single region (using application-level routing) or redesign the data model to use a CRDT that fits the access pattern.

Testing geo-routing locally. You cannot easily reproduce 150ms cross-region latency in a dev environment. Use tc netem (Linux traffic control) to add simulated latency to loopback interfaces when testing routing logic. It catches bugs in session affinity and lag fallback paths that are invisible at local speeds.

Data sovereignty at the row level. For GDPR compliance in a multi-region database, you often need row-level residency enforcement, not just cluster-level. If a user’s personal data must stay in the EU, that constraint has to be enforced at the write path (reject writes that would land data outside the permitted region) and at the replication path (exclude covered rows from cross-region replication). Both Cassandra and CockroachDB support data placement policies that enforce region pinning; verify that your conflict resolution path cannot move pinned data.

Where Each Topology Belongs

Single-leader fits most systems that are adding a second region for availability or EU data residency. The write latency penalty for remote users is real, but acceptable if your write volume is low relative to read volume. Aurora Global Database is the lowest-friction path here.

Multi-leader is justified when you have high write volume from geographically distributed users and you have invested in conflict resolution for every entity type. This is not a decision to make mid-sprint. It belongs in a design review with examples of every write pattern in your application.

Leaderless replication belongs in use cases where availability dominates consistency requirements: IoT telemetry, event collection, activity tracking. If “never lose a write” matters more than “always return the authoritative value,” leaderless is the right default. Cassandra with LOCAL_QUORUM within each region and asynchronous cross-region replication is a proven production configuration.

The common mistake is reaching for multi-leader because it sounds symmetric and clean, then discovering that 15% of your tables have write patterns that require conflict resolution policies you have not defined. Start single-leader, add read replicas in each region, and move specific tables to multi-leader only when the write latency data forces it.

The database topology decision is hard to reverse. Make it explicitly, not as a side effect of picking a managed database that defaults to multi-leader.