Primary-Replica Replication for Read Scaling

The Problem: A Single Server's Limits

A single database server, no matter how powerful, is a bottleneck. As an application grows, this single server has to handle an increasing number of connections and queries. This leads to several problems:

1. Performance Degradation: CPU, memory, and disk I/O become saturated, causing all queries to slow down.
2. Single Point of Failure (SPOF): If the server crashes or the hardware fails, your entire application goes down. While the WAL ensures you won't lose committed data, the service will be unavailable until the database is restored and restarted.
3. High Latency for Global Users: If your server is in one region (e.g., North America), users in other parts of the world (e.g., Asia) will experience significant network latency for every database interaction.

To solve these issues, we can't just rely on a single server. We need to distribute the load. This is where replication comes in.

The Primary-Replica Architecture

The most common approach to replication is the primary-replica model (also known as leader-follower or master-slave). Here's how it works:

• Primary (Leader): You designate one database server as the primary. This node is the single source of truth and is the only one that accepts write operations (INSERT, UPDATE, DELETE).
• Replicas (Followers): You create one or more copies of the primary database, called replicas. These nodes are typically read-only. Their job is to stay in sync with the primary by applying the same changes that happen there.
• Read/Write Split: Your application is configured to send all write queries to the primary and distribute read queries (SELECT) across the pool of replicas.

This architecture directly addresses the problem of scaling read-heavy workloads. Most web applications have a much higher ratio of reads to writes. By offloading all the read traffic to replicas, you free up the primary to focus exclusively on handling writes, dramatically increasing the overall capacity of your system.

How Does Replication Actually Work?

This is where our last lesson pays off. The primary needs a way to tell the replicas about its changes. The Write-Ahead Log (WAL) (or its equivalent, like MySQL's binary log) is the perfect mechanism for this. Since it's a complete, ordered stream of all changes, the primary can send this log to its replicas.

The replicas then "replay" the log to apply the same changes in the same order, keeping their data consistent with the primary. There are a few ways this can be implemented.

To summarize the replication mechanisms:

• Statement-Based Replication: The primary sends the raw SQL statements (UPDATE users SET...) to the replicas, which then re-execute them.
  • Pro: Very compact, low network bandwidth.
  • Con: Dangerous. Non-deterministic functions like NOW() or RANDOM() would produce different results on different servers, leading to data drift. Replicas must also redo the expensive work of planning and executing the query.
• Physical (Log Shipping) Replication: The primary streams its low-level WAL files to the replicas. This is what PostgreSQL's streaming replication does.
  • Pro: Efficient and exact. The replica applies the exact physical changes, avoiding the non-determinism problem.
  • Con: Tightly coupled to the database's physical storage format and version. You typically cannot replicate between different major versions of the database software.
• Logical (Row-Based) Replication: The primary decodes the WAL into a high-level, logical description of the changes (e.g., "row with ID 42 in table users had column name changed from 'A' to 'B'").
  • Pro: Decoupled from the storage engine. Allows replication between different database versions, or even to different database systems. More flexible for partial replication (e.g., only certain tables).
  • Con: Can be more verbose than statement-based replication for updates affecting many rows.

The Catch: Replication Lag and Eventual Consistency

In the quest for performance, most replication setups are asynchronous.

Asynchronous replication means that when a client issues a write to the primary, the primary writes to its WAL and immediately responds "Success!" to the client. The process of sending the log to the replicas happens in the background.

This creates a delay known as replication lag. The replicas are always a little bit behind the primary. This leads to a state called eventual consistency: if you stop all writes, the replicas will eventually catch up and be identical to the primary, but at any given moment, they might be slightly out of date.

This has profound implications for your application.

The "read-your-writes" problem described in the article is one of the most common issues. A user performs an action (like posting a comment), the app redirects them to the comment thread, but the read query goes to a replica that hasn't received the new comment yet. To the user, it looks like their comment disappeared.

To build robust systems, you must account for this. Some strategies include:

• For a short period after a user performs a write, route all their read requests to the primary.
• Design the UI to handle eventual consistency gracefully (e.g., showing a "pending" state).
• For critical operations, force the read to go to the primary.

Handling Failures

Replication doesn't just improve performance; it's also critical for high availability.

As the video explained:

• Replica Failure: This is a non-event. The load balancer detects the failure and stops sending it traffic. The system continues operating with slightly reduced read capacity. You can spin up a new replica to replace the failed one.
• Primary Failure: This is a critical event. The system can no longer accept writes. The recovery process involves:
  1. Detecting the failure.
  2. Choosing a replica to promote. This is typically the replica with the least replication lag.
  3. Promoting the replica to become the new primary (making it read-write).
  4. Reconfiguring the application and other replicas to use the new primary.

Because of replication lag, even the most up-to-date replica might be missing a few transactions that were committed on the old primary just before it crashed. This means primary failover in an asynchronous system carries a risk of data loss.

To avoid this, you can use synchronous replication. The primary waits for at least one replica to confirm it has received the change before responding to the client. This guarantees consistency and prevents data loss on failover, but it comes at the cost of higher write latency and reduced availability (a write will fail if no synchronous replicas are available).

Conclusion

In this lesson, we dissected the primary-replica replication model, a cornerstone of scalable backend architecture.

Key Takeaways:

• Replication for Scalability and Availability: It solves the limitations of a single database by distributing read load (scaling) and providing live standbys in case of failure (availability).
• Primary-Replica Model: A single primary node handles all writes, while multiple read-only replica nodes handle reads, allowing for horizontal scaling of read-heavy workloads.
• WAL is the Engine: The Write-Ahead Log (or binary log) provides the ordered stream of changes that is shipped from the primary to the replicas.
• Asynchronous Replication and Lag: The default asynchronous model is fast but introduces replication lag, leading to eventual consistency and potential for stale reads. You must design your application with this in mind.
• Failover and Promotion: In case of a primary failure, a replica is promoted to take its place. In an asynchronous setup, this can involve a small amount of data loss.

Preview of the Next Lesson

Primary-replica replication is fantastic for scaling reads, but what about writes? All writes must still go through the single primary server, which can eventually become a bottleneck for write-intensive applications.

In our next lesson, we will tackle this problem by exploring sharding. We'll learn how to horizontally partition data across multiple primary databases, allowing us to scale writes and build truly massive-scale systems.