Track 2: data-layerID: database-replication

Mode: structured•Difficulty: intermediate•⏱️ 15 mins

Module · Data Layer · 15 min

Database Replication — Leader-Follower & Read Scaling

Most real-world applications are 95% Read-Heavy. Scaling a database requires decoupling writes from reads using a single Primary Leader and multiple Read Replicas.

01 / Concept Cards (Theory)

Primary-Replica Architecture

Understand how data replication works, why read replicas scale query throughput, and the challenges of replication lag.

1. The Core Architecture: Primary (Leader) + Replicas (Followers)

When millions of users read profiles, posts, or product catalogs simultaneously, a single database server will exhaust its CPU and disk I/O.

The solution is Leader-Follower (Master-Slave) Replication:

👑 Primary Database (Leader)Handles ALL Writes (`INSERT`, `UPDATE`, `DELETE`)Acts as the single source of truth. As soon as a transaction commits, it records changes to a Replication Binary Log (binlog) and streams them to all follower replicas.

📖 Read Replicas (Followers)Handles ALL Reads (`SELECT` Queries)Continuously ingest the binlog and apply changes locally. You can spin up 5, 10, or 20+ replicas behind a read connection pool to handle massive read throughput!

2. Visualizing the Replication Data Flow

Watch the live query routing below: Write traffic (Orange) goes exclusively to the Primary DB, which asynchronously streams its binlog sync (Green) to Replicas, while Read traffic (Blue) is distributed across Replicas.

⚡ Primary-Replica Topology & Query Routing

3. Synchronous vs Asynchronous Replication & Replication Lag

How quickly should changes from the Primary reach the Replicas? This introduces a classic distributed systems trade-off between latency and consistency.

⚡ Asynchronous Replication (Default)Fast Writes, Eventual ConsistencyThe Primary commits the write and immediately responds to the client. It sends updates to replicas in the background.

Risk — Replication Lag: Replicas may be milliseconds behind. If a user updates their profile and instantly reloads the page, a replica might serve stale data!

🔒 Synchronous ReplicationStrong Consistency, Slower WritesThe Primary blocks the client response until at least one (or all) replicas confirm they wrote the change to disk.

Risk — Latency & Availability: If any replica is slow or has a network hiccup, all write queries freeze!

💡 Production Best Practice (Semi-Synchronous): The Primary waits for 1 replica to acknowledge (ensuring zero data loss if Primary dies), while remaining replicas update asynchronously.

4. Failover & Disaster Recovery: What If a Server Dies?

High availability means your website never crashes even during hardware failure or power loss. Here is how both Primary and Replica failures are handled:

📖 Case 1: A Read Replica Dies

• Multiple Replicas Available: The connection pool instantly detects the dead replica and redistributes read queries to the surviving healthy replicas. A new replacement replica is spun up in the background.

• Only 1 Replica Existed: Read queries temporarily fall back directly to the Primary DB so users experience zero downtime while a replacement replica provisions and syncs.

Recovery: New replica restores from snapshot + catches up via binlog replay.

👑 Case 2: The Primary Leader Dies

Consensus monitoring detects the Primary is offline. The system promotes the most up-to-date Read Replica to become the New Primary Leader.

All app servers are immediately notified to route writes to the new Leader, and a new follower replica is spawned to restore redundancy.

Data Catch-up: Recovery scripts replay any missing transaction logs before promotion.

🛡️ Preventing "Split-Brain" with Quorum Voting (> 50% Majority)

If a network partition cuts communication between data centers, the old Primary might still think it's the Leader while the other side promotes a new one. To prevent two conflicting leaders from corrupting data, servers use Quorum Consensus:

In a 3-Server Cluster: Majority = 2 servers.
• Isolated side with 1 server: Demoted automatically (refuses writes).
• Connected side with 2 servers: Continues as the valid Leader.

Why clusters use ODD numbers (3, 5, 7):
An odd number can never produce a 50/50 tie, guaranteeing that exactly ONE side can hold the majority vote.

5. Putting It All Together: The End-to-End Request Journey

Let's trace how everything we've learned (DNS → Load Balancer → Stateless Web Tier → Replicated Databases) works together in production:

🌐 Full System Architecture — DNS → LB → Web Tier → Replicated DB

🤔 But how does the App Server know which Replica to contact?

The app server doesn't pick replicas manually. In production, one of these three patterns handles it automatically:

① Database ProxyA proxy like ProxySQL or PgBouncer sits between app and DB. App connects to one endpoint — the proxy routes writes to Primary and round-robins reads across healthy replicas.

② Cloud Reader EndpointAWS RDS / Cloud SQL provides a single reader endpoint URL that auto-balances SELECT queries across all active replicas internally.

③ App Connection PoolsThe app maintains two pools in code: a writePool (→ Primary) and a readPool (→ list of replica hosts, round-robin).

STEP 1

DNS Resolution: User requests api.example.com. DNS returns the public IP address of the Load Balancer.

STEP 2

Load Balancer Ingress: User sends HTTP request to the Load Balancer, which distributes traffic evenly across healthy Web Servers (Server 1 / Server 2).

STEP 3

Write Routing (5% of traffic): If the user creates an account or posts a photo (INSERT/UPDATE), the web server routes the query to the Primary Database (Master).

STEP 4

Read Routing (95% of traffic): If the user views a profile or scrolls a feed (SELECT), the web server routes the query to any available Read Replica (Slave).

STEP 5

Continuous Replication: The Primary asynchronously streams all new commits via its binlog to all Read Replicas to keep them synchronized.

6. Interactive Replication Scenario Quiz

Test your architecture knowledge with real-world scenarios:

📰 A news website has 10 million daily readers who read articles, and only 5 editors who publish 20 articles per day. The database CPU is at 98%.

✅ Adding Read Replicas is correct! The traffic is overwhelmingly read-heavy (>99.9% reads). Adding read replicas scales read throughput seamlessly without the high operational complexity of database sharding.

💬 A user submits a comment on a post, the page reloads, but their comment is not visible for 2 seconds. What is causing this?

✅ Replication Lag is correct! In asynchronous replication, there is a small delay between the write committing on the Primary and syncing to the Read Replica. If the reload reads from a replica that hasn't caught up, it serves stale data.

💳 A banking app is transferring $5,000 between accounts. The customer must immediately see their updated balance upon confirmation.

✅ Route critical read to Primary DB is correct! For "Read-Your-Own-Writes" consistency on financial transactions, reading directly from the Primary guarantees you get the latest committed state, completely avoiding replica lag.

💥 The Primary database hardware experiences catastrophic power loss at 2 AM. How does the system recover?

✅ Automatic failover is correct! Consensus health checks detect the failure, promote the most synchronized follower to Leader, and redirect app server write traffic automatically.

7. System Design Interview Comparison Matrix

Replication strategies quick reference table:

Pattern	Synchronous Replication	Asynchronous Replication
Write Latency	Higher (Waits for replica acknowledgment)	Lowest (Primary responds immediately)
Data Consistency	Strong (Zero data loss on leader crash)	Eventual (Small replication lag window)
System Availability	Lower (Slow replica halts write traffic)	High (Primary operates independently)
Best Used For	Financial, Payments, Critical Inventory	Social Media, Feeds, Analytics, Catalogs

02 / Practice Exercise

Build Your Replication Architecture

Design a high-availability Primary-Replica cluster that separates writes from reads.

Interactive CanvasMode: Freeform Drag & Connect

Task: Primary-Replica Database Cluster

Grid Overlay

🎯

Scenario Prompt: Design a scalable database architecture: Connect the App Server to the Primary DB for writes, connect the App Server to both Read Replicas for reads, and establish the binlog replication stream from the Primary to both Read Replicas.

Available Components (Click to place on canvas):

🖱️Canvas is emptyClick components above to place them onto the canvas.
Drag components to arrange them freely, and click two nodes to connect them.

💡 Drag to move • Click Node A ➔ Node B to connect • Click ✕ to delete0 components placed