Theory
Database Replication Strategies
📋 Overview
Replication is the process of maintaining identical copies of a dataset across multiple distributed nodes. It is the primary architectural tool for achieving High Availability (HA), Disaster Recovery (DR), and Read Scalability. By distributing data, systems can handle massive read traffic and survive the failure of individual servers or entire data centers without data loss or downtime.
🏗️ Core Principles & Characteristics
1. Topologies
- Leader-Based (Primary-Replica): One node accepts writes; others replicate for reads. Simplest to manage.
- Multi-Leader: Multiple nodes accept writes and sync. Ideal for multi-region active-active setups.
- Leaderless (Quorum-based): Any node can accept any request. Reliability depends on Quorum math ($W+R > N$).
2. Synchronization
- Synchronous: Leader waits for replicas to confirm. High Durability, High Latency.
- Asynchronous: Leader returns immediately after local write. Low Latency, Risk of Data Loss.
⚖️ Trade-offs: Pros & Cons
- Pros: horizontal read scaling; geographic latency reduction; protection against hardware failure; enables offline data analysis.
- Cons: Consistency Challenges (Replication Lag); increased storage and network costs; complexity in conflict resolution (especially in Multi-Leader).
🌍 Real-World Implementation
- MySQL/Postgres (Leader-Based): Standard for most web apps. Reads are offloaded to "Slaves" while the "Master" handles transactions.
- Cassandra (Leaderless): Built for massive global scale where losing a few nodes shouldn't impact write availability.
- Redis (Asynchronous): Prioritizes performance over strict durability, often used for session stores.
- GoldenGate / Debezium: External tools that facilitate replication between different types of databases (e.g., Oracle to Snowflake).
💡 Interview "Gotchas" & Tips
- Replication Lag: The #1 problem in interviews. Discuss how to handle a user "reading their own write" by routing them to the Leader temporarily.
- Split-Brain: What happens if two nodes think they are the leader? Mention Quorums or Consensus Services (Zookeeper) as the solution.
- Semi-Synchronous: A middle ground where the leader waits for at least one replica to acknowledge, providing durability without the extreme latency of full sync.
📐 Suggested Architecture Primitives
- Binlog / WAL: The underlying stream of changes used for replication.
- Read-Write Splitter: A proxy layer that intelligently routes SQL queries based on their type.
- Consistent Hashing: Often used in leaderless systems to determine which nodes "own" a specific range of data.
- Vector Clocks: For resolving write conflicts in Multi-Leader or Leaderless architectures.
Canvas