Database replication copies data across multiple servers to achieve high availability, fault tolerance, and read scalability. Understanding replication strategies — their guarantees, failure modes, and performance tradeoffs — is essential for system design interviews and production database architecture. This guide covers primary-replica, multi-master, synchronous vs asynchronous replication, and the practical challenges of replication lag.
Primary-Replica (Master-Slave) Replication
The most common replication topology. One primary (master) node accepts all writes. One or more replica (slave) nodes receive a copy of every write from the primary and serve read queries. The primary writes changes to a replication log (Write-Ahead Log in PostgreSQL, Binary Log in MySQL). Replicas continuously read the log and apply changes to their local copy. Read scaling: distribute read queries across replicas. If the primary handles 1,000 reads/sec and you add 2 replicas, total read capacity becomes 3,000 reads/sec. Writes still go to the primary. Failover: if the primary fails, promote a replica to become the new primary. Clients are redirected to the new primary (via DNS update or proxy reconfiguration). During promotion, unacknowledged writes on the old primary may be lost (depending on the replication mode). PostgreSQL supports primary-replica replication natively with streaming replication. MySQL uses binary log replication. AWS RDS automates replica creation, promotion, and DNS switching.
Synchronous vs Asynchronous Replication
Asynchronous replication: the primary commits the write locally and responds to the client immediately. The replica receives the change later (milliseconds to seconds). Pros: low write latency (no waiting for the replica), primary is not blocked by slow replicas. Cons: if the primary fails before the replica receives the change, data is lost. The replica may be seconds behind the primary (replication lag). Synchronous replication: the primary waits for at least one replica to confirm it received and persisted the change before responding to the client. Pros: zero data loss on failover (the replica has every committed write). Cons: write latency increases by the network round-trip to the replica (0.5-1ms within a datacenter, 50-200ms cross-region), and if the synchronous replica goes down, the primary is blocked. Semi-synchronous: the primary waits for one replica to acknowledge but does not wait for all. This provides durability (at least one replica has the data) without the risk of being blocked by a single slow replica. PostgreSQL synchronous_commit = on with synchronous_standby_names enables semi-synchronous. MySQL semi-synchronous replication plugin provides the same.
Replication Lag and Its Impact
Replication lag is the delay between a write on the primary and its visibility on a replica. With asynchronous replication, lag is typically 10-100ms under normal load but can spike to seconds or minutes during: high write throughput (the replica cannot apply changes as fast as they arrive), replica maintenance (vacuum, index rebuild), network congestion between primary and replica, or resource contention on the replica (CPU, I/O). Impact on application behavior: (1) Read-your-own-writes violation — a user writes a comment, then immediately refreshes and does not see it (the read went to a lagging replica). Solution: route reads for recently-written data to the primary (read-after-write consistency). (2) Monotonic read violations — a user sees a newer version of data, refreshes, and sees an older version (read went to a different, more lagged replica). Solution: pin a user session to a specific replica. (3) Stale search results — Elasticsearch index updated from a replica may show older data than the primary. Monitoring: track replication lag as a key metric. Alert when lag exceeds a threshold (e.g., 5 seconds). PostgreSQL: query pg_stat_replication for write_lag, flush_lag, replay_lag. MySQL: check Seconds_Behind_Master.
Multi-Master (Multi-Primary) Replication
Multi-master replication allows writes on any node. Each node replicates its writes to all other nodes. Use cases: multi-region active-active (write to the nearest region), high write availability (any node failure does not block writes). Challenge: write conflicts. If two nodes update the same row simultaneously, the changes conflict. Conflict resolution strategies: (1) Last-writer-wins (LWW) — the write with the latest timestamp wins. Simple but silently discards data. (2) Application-level resolution — the database detects the conflict and surfaces both versions to the application, which decides how to merge. CouchDB and PouchDB use this approach. (3) Conflict-free replicated data types (CRDTs) — data structures that merge automatically without conflicts. Counters, sets, and registers that support concurrent modifications mathematically. Production multi-master systems: Galera Cluster (MySQL), CockroachDB (distributed SQL with Raft-based consensus), Cassandra (eventual consistency with tunable consistency levels), DynamoDB Global Tables (last-writer-wins). Multi-master adds significant complexity. Use it only when the use case requires it (multi-region writes, high write availability). For most applications, primary-replica with automatic failover is sufficient.
Replication Topologies
Beyond simple primary-replica: (1) Chain replication — primary -> replica A -> replica B -> replica C. Each node replicates to the next. Reduces load on the primary (only one replication stream) but increases end-to-end lag. (2) Star topology — primary replicates to multiple replicas directly. Each replica is independent. Most common and simplest to manage. (3) Cascading replication — primary replicates to regional leaders, which replicate to local followers. Reduces cross-region bandwidth. PostgreSQL supports cascading replication natively. (4) Circular replication (legacy MySQL) — each node replicates to the next in a ring. Fragile — a single node failure breaks the ring. Avoid in modern deployments. (5) Read replicas for analytics — create a dedicated read replica for analytics queries (complex aggregations, reporting). These queries can run for minutes without affecting the primary or user-facing replicas. The analytics replica can lag without impacting user experience.
Choosing a Replication Strategy
Decision framework: (1) Read scaling needed, writes are moderate — primary-replica with async replication and 2-3 read replicas. Route reads to replicas, writes to primary. Handle replication lag at the application level (read-after-write consistency for the current user). (2) High availability within a single region — primary-replica with semi-synchronous replication and automatic failover. AWS RDS Multi-AZ does this automatically (1-2 minute failover). (3) High availability across regions — async replication to a cross-region read replica. On primary region failure, promote the cross-region replica. Accept RPO = replication lag. (4) Active-active multi-region writes — multi-master (DynamoDB Global Tables, CockroachDB). Accept the complexity of conflict resolution. (5) Analytics isolation — dedicated read replica for reporting queries with higher acceptable lag. In system design interviews: state the replication mode and justify it. “We use async primary-replica replication with 3 read replicas for read scaling. The application routes the creating user reads to the primary for read-after-write consistency. The 99th percentile replication lag is under 100ms.”
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”What is the difference between synchronous and asynchronous database replication?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Asynchronous replication: the primary commits a write and responds to the client immediately. The replica receives the change later (milliseconds to seconds). Pros: low write latency, primary is not blocked by slow replicas. Cons: if the primary fails before the replica receives the change, that data is lost. Synchronous replication: the primary waits for at least one replica to confirm it received and persisted the change before responding. Pros: zero data loss on failover. Cons: write latency increases by the network round-trip to the replica (0.5-1ms within a datacenter, 50-200ms cross-region), and the primary is blocked if the replica goes down. Semi-synchronous is the practical middle ground: wait for one replica to acknowledge, but not all. This provides durability without being blocked by a single slow replica.”}},{“@type”:”Question”,”name”:”How does replication lag affect application behavior?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Replication lag is the delay between a write on the primary and its visibility on a replica. Common impacts: (1) Read-your-own-writes violation — a user writes a comment, refreshes, and does not see it because the read went to a lagging replica. Fix: route reads for recently-written data to the primary. (2) Monotonic read violations — a user sees newer data, refreshes, and sees older data because the second read went to a more lagged replica. Fix: pin a user session to a specific replica. (3) Stale search results — search indexes updated from replicas show older data. Monitor lag as a key metric. PostgreSQL: query pg_stat_replication for write_lag, flush_lag, replay_lag. Alert when lag exceeds your threshold (e.g., 5 seconds).”}},{“@type”:”Question”,”name”:”When should you use multi-master replication?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Multi-master allows writes on any node, but introduces write conflict complexity. Use multi-master only when: (1) Multi-region active-active is required — users in each region write to the local database for low latency. (2) High write availability is critical — any node failure must not block writes. Conflict resolution strategies: last-writer-wins (simple but silently discards data), application-level merge (custom logic per data type), or CRDTs (data structures that merge automatically). Production systems: DynamoDB Global Tables (LWW), CockroachDB (Raft consensus avoids conflicts), Cassandra (tunable consistency). For most applications, primary-replica with automatic failover is sufficient and much simpler. Only choose multi-master when the use case explicitly requires concurrent writes from multiple regions.”}},{“@type”:”Question”,”name”:”How do you choose a database replication strategy?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Match strategy to requirements: (1) Read scaling, moderate writes — primary-replica with async replication and 2-3 read replicas. Handle lag at the application level. (2) High availability, single region — primary-replica with semi-synchronous replication and automatic failover. AWS RDS Multi-AZ automates this. (3) High availability across regions — async replication to a cross-region read replica. On primary region failure, promote the replica. Accept RPO = replication lag. (4) Active-active multi-region writes — multi-master with conflict resolution. Accept the complexity. (5) Analytics isolation — dedicated read replica for heavy reporting queries. In interviews, state the replication mode and justify: We use async primary-replica with 3 read replicas. The application routes the creating user reads to the primary for read-after-write consistency.”}}]}