Database Replication in System Designing

When your database grows to a size where a single machine can no longer handle the volume of incoming read and write requests, scaling horizontally at the data tier becomes mandatory. The first line of defense is Database Replication . Replication is the practice of sharing data across multiple database servers to increase read throughput, introduce fault tolerance, and ensure high availability. 1. The Primary–Replica Architecture The standard model for scaling relational databases (like PostgreSQL or MySQL) is the Primary–Replica (Master-Slave) configuration. The Primary Node: Handles 100% of the application's Write operations ( INSERT , UPDATE , DELETE ). The primary is the single source of truth. Whenever data changes on the primary, those changes are written to a transaction log and broadcasted down to the replicas. The Replica Nodes: Handle Read operations ( SELECT ). Replicas maintain an exact mirror copy of the primary node's data. Why split them? In most web applications, traffic is heavily asymmetrical—often seeing a 9:1 ratio of reads to writes (e.g., millions of users browse tweets, but only a fraction are actively posting). By pointing all read queries to a pool of multiple replicas, you can scale your read capacity almost infinitely. 2. The Synchronization Trade-off: Sync vs. Async How data moves from the primary to the replicas introduces a major architectural trade-off between Data Consistency and Write Latency . A. Synchronous Replication (Consistency First) The Flow: The client sends a write request to the primary. The primary writes the data locally, forwards it to the replicas, and blocks (waits) . It only sends a success confirmation back to the client after the replicas acknowledge they have successfully saved the data. Pros: Strict consistency. If the primary node explodes a millisecond later, zero data is lost because the replicas have an exact, real-time match. Cons: High write latency. Your write speed is limited by the slowest replica node and network propagation delay. If a replica hangs, the entire system's write availability grinds to a halt. B. Asynchronous Replication (Performance First) — The Standard The Flow: The primary writes the data locally and instantly returns a success confirmation to the client. It then fires off the data updates to the replicas in the background asynchronously. Pros: Fast writes. The client doesn't have to wait for the network hop to the replicas. Cons: Risk of data loss. If the primary crashes before the background log syncs to the replicas, those un-replicated transactions are gone forever. 3. The Operational Challenge: Replication Lag Because asynchronous replication happens in the background, it takes time for a write on the primary to physically travel across the network and apply to the replicas. This delay is known as Replication Lag . Replication lag causes Stale Reads (Eventual Consistency): A user updates their profile name from "Alice" to "Bob" (Write goes to Primary ). The user instantly refreshes the page (Read goes to Replica ). Because of network congestion, the replica hasn't received the update yet. The page renders "Alice". Two seconds later, the sync completes, and subsequent reads show "Bob". How to mitigate Replication Lag in System Design: Read-Your-Own-Writes: For sensitive operations (like updating a password or checking a checkout balance), force the system to route reads directly to the primary node for a brief window (e.g., 5 seconds) after a write, bypassing the replicas until they have time to sync. Monotonic Reads: Ensure that a single user's session is pinned to the exact same replica node. This prevents a jarring user experience where a user refreshes the page multiple times and flips back and forth between fresh data and stale data. 4. Failover and High Availability (HA) Beyond scaling read throughput, replication provides your system with a built-in safety net called Failover . If the primary node crashes or goes offline: Health checks detect the primary is dead. The system initiates a Failover protocol. One of the healthy, up-to-date replica nodes is dynamically elected and promoted to be the new Primary . The load balancer or application configuration updates its connection strings to route writes to this newly promoted node, keeping the app alive with minimal downtime.