Replication (Primary-Replica / Master-Slave)

In modern web architectures, read queries typically outnumber write operations by 10:1 to 100:1 (Read-Heavy workloads). A single database server processing 100,000 queries per second will rapidly exhaust its CPU and memory bandwidth.

Primary-Replica (Leader-Follower) Replication distributes database load across multiple nodes:

1. The Primary Node (Leader / Master): The single authoritative node that accepts all write mutations (INSERT, UPDATE, DELETE) and executes DDL schema migrations.
2. Read Replicas (Followers / Slaves): Read-only copies of the database that continuously replay the Primary's log stream, serving read queries (SELECT) to scale read throughput horizontally.
3. High Availability & Disaster Recovery: If the Primary node suffers hardware failure, a monitoring orchestrator (Patroni, Orchestrator, AWS RDS Multi-AZ) automatically promotes a healthy Read Replica to become the new Primary, restoring write availability in under 30 seconds.

How do changes flow from the Primary to Replicas?

• Physical Streaming Replication (PostgreSQL): The Primary's walsender process streams raw binary Write-Ahead Log (WAL) byte records over a dedicated TCP connection to the replica's walreceiver process. The replica applies the WAL changes directly to its local buffer pool and disk pages.
• Statement-Based Replication (Legacy MySQL): The primary logs the raw SQL query string (e.g., UPDATE users SET status='active'). Flaw: Non-deterministic functions (NOW(), UUID(), RAND()) cause replicas to diverge from the primary!
• Row-Based Replication (Modern MySQL Binlog): The primary logs the exact byte diff of the modified row (Before-Image and After-Image), guaranteeing 100% deterministic replica fidelity.

Because replication is almost universally asynchronous to maximize write throughput, there is an unavoidable delay called Replication Lag (typically 5ms to 500ms over healthy networks, but stretching to seconds during heavy batch writes).

The "Read-Your-Own-Writes" Disaster:

1. User Alice updates her profile name to "Alice Smith" (writes to Primary).
2. The browser immediately reloads and queries a Read Replica for the updated profile.
3. The replica has not yet processed the WAL stream (50ms lag).
4. The replica returns the old name "Alice Jones".
5. Alice panics and assumes her update failed!

Architectural Solutions:

• Pin User to Primary After Writes: After a user performs a write mutation, route all subsequent read queries for that specific user to the Primary node for 5–10 seconds, falling back to replicas only after replication lag has cleared.
• Track Monotonic Commit Timestamps: The client receives a Commit Log Sequence Number (LSN / GTID). Replicas only satisfy reads if their local LSN ≥ client LSN; otherwise, the request waits or routes to Primary.