📝 Introduction: The Scribe Assistants
Imagine you are a teacher writing notes on a chalkboard in front of a class:
- The Primary Chalkboard (Primary Node): You are the only person allowed to write on the board. You write down the homework tasks.
- The Scribes (Replica Nodes): There are three assistant teachers sitting in the room. Every time you write a line on the board, they immediately write it down in their personal notebooks.
- The Read Load: When students want to check the homework, they don't crowd around your chalkboard. They walk to the assistant teachers and read their notebooks instead.
- Failover: If you suddenly get sick and leave, one of the assistant teachers steps up, grabs the chalk, and becomes the new writer.
In system design, this is Database Replication! It is the process of copying data from one primary database server to one or more replica database servers.
🏗️ Active-Passive Replication: The Mechanics
Here is how write logs move between servers across the network:
Client Writes (INSERT / UPDATE)
|
v
+-----------------------+
| PRIMARY NODE | <--- Handles all writes, appends to WAL
+-----------------------+
|
(Sends WAL Logs)
/ | \
v v v
+-----------------------------+
| REPLICA NODES | <--- Replay logs in background, handle reads
+-----------------------------+
Synchronous vs. Asynchronous Replication
Replication can be set up in two ways:
1. Synchronous Replication (Safety First)
The Primary writes the change and waits for all replicas to confirm they have copied it before replying "Success" to the client.
- Advantage: Zero data loss if the primary crashes.
- Disadvantage: Very slow! If one replica server gets slow or drops network packets, write queries hang.
2. Asynchronous Replication (Speed First)
The Primary writes the change, replies "Success" to the client immediately, and sends the update logs to replicas in the background.
- Advantage: Blazing-fast write queries!
- Disadvantage: Replication Lag. If the primary crashes before replicas copy the last changes, those changes are lost forever.
💻 Code Examples: Simulating Replication Lag
Let's write a python/code script to simulate how asynchronous replication lag occurs.
Multi-Language Execution
⚠️ Common Mistakes
1. Directing Write Queries to Replicas
Configuring application code to send INSERT or UPDATE queries to read replica nodes. Replicas are read-only; they will reject write queries, throwing connection exceptions!
2. Not Handling Replication Lag
Assuming replica nodes are always in sync. If a user changes their password on the primary, and is immediately redirected to login via a read replica node, they will get a "Wrong Password" error because the sync log hasn't arrived yet!
🔍 Interview Corner
Q1: What is the difference between Synchronous and Asynchronous replication?
- Synchronous replication requires the primary to wait for confirmation from replicas that the write log has been copied before returning success to the client, guaranteeing zero data loss at the cost of write speed.
- Asynchronous replication returns success immediately after the primary saves the write, copying data to replicas in the background, which is faster but subject to replication lag and potential data loss on crash.
Q2: What is "Failover" in database replication?
Failover is the process where a monitoring system detects that the primary database node has crashed, automatically promotes one of the read replicas to be the new primary node, and updates DNS records to route write traffic to it.
📝 Summary
- Replication copies data from one primary database node to multiple replica nodes.
- Primary handles write operations; Replicas handle read operations, enabling read scaling.
- Asynchronous replication is fast but suffers from replication lag.