Primary-Replica Database Architecture

📋 Overview

The Primary-Replica (Master-Slave) setup is a foundational data replication pattern used to scale read-heavy applications and provide high availability. In this architecture, a single "Primary" node handles all data-modifying operations (Writes), while one or more "Replica" nodes maintain copies of the data to serve Read requests. This separation allows the system to scale horizontally by adding more replicas as read demand increases.

---

🏗️ Core Principles & Characteristics

• Role Separation:
  • Primary: Source of truth for all INSERT, UPDATE, and DELETE operations.
  • Replica: Read-only copies that synchronize with the primary.
• Replication Lag: The delay between a write on the primary and its visibility on a replica. In asynchronous setups, this is inevitable.
• Failover (Promotion): If the primary fails, a replica is "promoted" to become the new primary to maintain system availability.
• Load Balancing: A "Read-Write Splitter" (proxy or client logic) routes writes to the primary and reads to the replicas.

---

⚖️ Trade-offs: Pros & Cons

• Pros: Drastically increases read throughput; provides redundancy; offloads intensive reporting/analytical queries from the main write path.
• Cons: Write Bottleneck (scaling writes is still hard); Inconsistency (users might read stale data immediately after a write); complexity in managing failover and re-pointing replicas.

---

🌍 Real-World Implementation

• MySQL Replication: Uses a Binlog (Binary Log) to stream changes from master to slaves.
• Amazon RDS: Provides "Read Replicas" with a single click, handling the underlying synchronization and health checks.
• Reporting Instances: A dedicated replica is often used solely for heavy business intelligence (BI) reports to ensure they don't slow down the live user experience.

---

💡 Interview "Gotchas" & Tips

• The "Read-Your-Own-Writes" Problem: If a user updates their profile and the app redirects them to the "View Profile" page (which reads from a replica), they might not see their change yet. Solution: Read from the Primary for a few seconds after a write.
• Sync vs. Async Replication:
  • Sync: No data loss, but high write latency (waits for replicas).
  • Async: Fast writes, but potential data loss if the primary crashes before syncing.
• Binary Logs: Explain that replication usually works by shipping transaction logs, not by re-executing full SQL queries.

---

📐 Suggested Architecture Primitives

• Database Proxy: Tools like MaxScale or ProxySQL to handle automatic read-write splitting.
• Health Checks: Automated scripts to monitor replication lag and pull "lagging" replicas out of the load balancer.
• Quorum-based Writes: (Advanced) Requiring a certain number of replicas to acknowledge a write for higher durability.