Database Replication

Database replication is the process of copying and maintaining database objects across multiple servers so that each server holds the same data, providing redundancy, fault tolerance, and read scalability.

What It Really Means

Every production system that matters runs on more than one database server. If your single database server dies — hardware failure, data center outage, corrupted disk — your data is gone and your application is down. Replication solves this by keeping identical copies of your data on multiple machines.

But replication is not just about safety. It is about performance and geography. A user in Tokyo should not wait 200ms for every database read to travel to a server in Virginia. With replication, you place copies of the data close to your users. You also distribute read traffic across multiple replicas so that no single server becomes a bottleneck.

The fundamental tension in replication is between consistency and performance. Keeping every replica perfectly in sync at all times requires coordination, and coordination means waiting. The more replicas you have and the farther apart they are, the more expensive that coordination becomes. Every replication strategy is a different answer to the question: how much inconsistency are you willing to tolerate, and for how long?

How It Works in Practice

Leader-Follower (Primary-Replica) Replication

This is the most common replication topology. One node is designated the leader (primary) and accepts all writes. It streams changes to one or more followers (replicas) that serve read-only queries.

PostgreSQL uses write-ahead log (WAL) streaming. Every change to the database is first written to the WAL, then the WAL records are shipped to replicas which replay them.

MySQL uses binary log replication. The primary records all data-modifying statements in a binary log, and replicas read and replay that log.

Amazon RDS automates this pattern. You click "Create Read Replica" and AWS handles WAL shipping, network configuration, and monitoring. Aurora takes it further with a shared storage layer — replicas share the same physical storage as the primary, reducing replication lag to single-digit milliseconds.

Multi-Leader Replication

Sometimes one leader is not enough. If you have data centers in New York and London, a single leader in New York means every write from London pays a transatlantic round-trip. Multi-leader replication allows writes at both data centers.

CouchDB and PouchDB use multi-leader replication for offline-first applications. Users can write to their local database while disconnected, and changes sync when connectivity is restored.

The problem with multi-leader is conflict resolution. If two leaders accept conflicting writes to the same row simultaneously, which one wins? Strategies include last-write-wins (LWW), custom merge functions, and CRDTs.

Leaderless Replication

Apache Cassandra and Amazon DynamoDB use leaderless replication. Clients write to multiple replicas simultaneously and read from multiple replicas, using quorum logic to determine the correct value. There is no single point of failure for writes.

Implementation

Trade-offs

Synchronous vs Asynchronous Replication

Advantages

• High availability: Promote a replica to primary when the primary fails
• Read scalability: Distribute read traffic across replicas
• Geographic distribution: Place data close to users
• Backup without downtime: Take backups from replicas without loading the primary

Disadvantages

• Replication lag: Replicas may serve stale data (async mode)
• Complexity: Managing failover, split-brain, and conflict resolution
• Cost: Each replica requires its own storage, compute, and network
• Write bottleneck: Leader-follower does not scale writes

Common Misconceptions

• "Replication is the same as backup" — Replication propagates every change, including accidental deletes and corrupted data. You still need point-in-time backups for disaster recovery.
• "More replicas always means better performance" — Each replica adds replication overhead to the primary. Beyond 5-10 replicas, the primary can become bottlenecked shipping WAL records.
• "Async replication means data loss is guaranteed on failover" — Data loss only occurs if the primary fails before the replica catches up. With sub-second replication lag, the risk is typically small.
• "Leader-follower replication prevents split-brain" — If network partitions occur and a replica is promoted prematurely, you can end up with two leaders. You need proper leader election and fencing mechanisms.
• "Replication replaces sharding" — Replication copies the full dataset to each node. It scales reads, not writes or storage. For write scaling, you need database partitioning.

How This Appears in Interviews

Replication is foundational in system design interviews. Expect these patterns:

• "Design a system that survives a data center outage" — Cross-region replication with automatic failover. Discuss RTO/RPO trade-offs and synchronous vs async replication.
• "A user updates their profile but sees stale data on refresh" — Classic replication lag. Explain read-after-write consistency by routing recent writes to the primary.
• "How does Amazon RDS Multi-AZ work?" — Synchronous replication to a standby in another availability zone with automatic DNS failover.
• "Compare leader-follower vs leaderless replication" — Leader-follower is simpler but has a write bottleneck. Leaderless (Cassandra-style) scales writes but requires quorum reads for consistency.

See our interview questions on distributed systems for more practice.

Related Concepts

• Consistent Reads — handling stale data from replicas
• Leader Election — choosing a new primary when the current one fails
• Write-Ahead Log — the mechanism that makes replication possible
• Quorum — how leaderless replication achieves consistency
• Split-Brain Problem — what happens when replication goes wrong
• System Design Interview Guide
• Algoroq Pricing — access all distributed systems concept deep-dives