Database Replication: Scaling Reads with Master-Slave Architecture

Previously in this course, we explored Horizontal Scaling and Load Distribution to handle increased traffic at the application layer. Today, we turn our attention to the data layer, specifically how replication allows us to scale database performance and ensure high availability.

In a single-node database setup, all reads and writes compete for the same CPU, memory, and I/O resources. As your user base grows, this becomes a hard bottleneck. Replication solves this by creating copies of your data, allowing you to distribute the workload.

Understanding Master-Slave Replication

The most common pattern for beginners is Master-Slave (or Primary-Replica) replication.

• The Primary (Master): The source of truth. It handles all write operations (INSERT, UPDATE, DELETE).
• The Replicas (Slaves): Read-only copies of the primary. They asynchronously receive data changes from the primary and apply them locally.

By separating traffic, you gain two major benefits:

1. Read Scalability: You can add multiple replicas to handle an increasing volume of GET requests.
2. High Availability: If the primary fails, a replica can be promoted to take over, minimizing downtime.

Configuring Read Replicas

In modern cloud environments, you rarely set up replication manually from scratch. Instead, you use managed services (like AWS RDS, Google Cloud SQL, or Azure Database).

To configure a replica, the provider typically performs a snapshot of the primary, restores it to a new instance, and enables "Binary Log" (binlog) streaming. The replica continuously reads the binlog from the primary and executes the same transactions.

Redirecting Traffic: The Application Layer

Simply having a replica isn't enough; your application must know where to send queries. We implement a Connection Router pattern.

In your application code, you create two distinct connection pools: one pointing to the primary and one pointing to the replica endpoint.

Worked Example: Python/SQLAlchemy Routing

Below is a simplified implementation showing how to route traffic based on the query intent:

PYTHON
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 1. Define connection strings
PRIMARY_URL = "mysql+pymysql://user:pass@primary-db:3306/app_db"
REPLICA_URL = "mysql+pymysql://user:pass@replica-db:3306/app_db"

# 2. Create engines
primary_engine = create_engine(PRIMARY_URL)
replica_engine = create_engine(REPLICA_URL)

def get_session(write=False):
    # Route based on operation type
    engine = primary_engine if write else replica_engine
    return sessionmaker(bind=engine)()

# Usage
# Writing data(Always to Primary)
session = get_session(write=True)
session.execute("INSERT INTO users(name) VALUES (CE9178">'Alice')")
session.commit()

# Reading data(To Replica)
session = get_session(write=False)
user = session.execute("SELECT * FROM users WHERE name = CE9178">'Alice'").fetchone()

Hands-on Exercise: Diagramming the Flow

In your running system design document, update your architecture diagram:

1. Add a second database node labeled "Read Replica."
2. Draw a dashed line from the Primary to the Replica to represent the replication stream.
3. Add a "Traffic Router" block between your application servers and the database layer.
4. Describe in two sentences how you would handle the scenario where a user updates their profile and immediately refreshes the page—will they see their changes? (Hint: Consider "Read-after-write consistency").

Common Pitfalls

• Replication Lag: Because replication is asynchronous, a replica might be a few milliseconds (or seconds) behind the primary. This causes "stale reads" where a user might not see the data they just saved.
• Over-scaling: Don't add 10 replicas if your bottleneck is actually missing indexes. Always check Database Indexing for Joins: Architecting High-Performance Queries before throwing hardware at the problem.
• Primary Dependency: If your primary goes down, all writes fail. You must implement automated failover logic to promote a replica to primary status during an outage.

FAQ

Q: Why not use synchronous replication for everything? A: Synchronous replication forces the primary to wait for the replica to acknowledge the write, which significantly increases latency and reduces write throughput. It is only used for high-stakes financial data where zero data loss is required.

Q: Can I have multiple masters? A: "Multi-master" replication exists but introduces massive complexity regarding conflict resolution (when two users update the same record at the same time). Stay with Master-Slave until you hit scale limits that demand more.

Recap

We’ve learned that replication is our primary tool for scaling read-heavy workloads. By splitting traffic between a primary and read replicas, we improve both performance and high availability. Remember, always consider the impact of replication lag on your user experience.

Up next: Database Partitioning and Sharding — learning how to split your data horizontally to scale beyond a single node's storage limits.