Read Replicas and Read/Write Splitting

Read-heavy, write-light workloads are typical of most Internet applications. In content communities, e-commerce product pages, and news portals, the read-to-write ratio is often 10:1 or even 100:1. The primary database must handle not only writes but also massive read traffic, and sooner or later the read pressure will overwhelm it: CPU saturation, exhausted connections, and slow queries consuming resources needed for writes. At that point, the answer is not a more expensive machine, because vertical scaling always has a ceiling. Instead, distribute the reads by adding read replicas. This is the most basic and mature way to scale database read capacity horizontally.

1. Why Read Replicas Are Necessary

When a single primary handles all reads and writes, the typical failure sequence looks like this: read QPS rises → the connection pool fills up → CPU usage spikes → slow queries drag down the entire system → write requests also begin timing out → cascading failure.

Many people's first reaction is to "add CPU, add memory, and upgrade to a more expensive instance." This approach is called scaling up, but it has physical and economic limits. The alternative is scaling out: distribute read traffic across multiple machines and let the primary handle only writes. Read replicas are the foundation of this approach.

The primary focuses on writes and strongly consistent reads, while replicas handle other reads and distribute the load across multiple machines. It is simple and blunt, but effective. This is also a standard production capability in MySQL and PostgreSQL.

2. Primary-Replica Architecture

The core mechanism behind read replicas is primary-replica replication, formerly called Master-Slave replication and now usually called Primary-Replica in documentation:

          ┌────────────────────┐
Writes ──>│  Primary Database  │
          └─────────┬──────────┘
                    │ Replication (asynchronous / semi-synchronous / synchronous)
         ┌──────────┴──────────┐
         ▼                     ▼
   ┌──────────┐          ┌──────────┐
   │ Replica 1│          │ Replica 2│  <── Reads
   └──────────┘          └──────────┘

The primary executes writes (INSERT/UPDATE/DELETE) and records the changes in the binlog for MySQL or WAL for PostgreSQL. Replicas pull and replay those logs to keep their data consistent.

Replication falls into three categories based on consistency strength:

• Asynchronous replication (Async): The primary returns as soon as its write completes, without waiting for replica acknowledgment. This provides the best performance, but data may be lost if the primary fails, and replicas can lag. It is the default in MySQL and PostgreSQL.
• Semi-synchronous replication (Semi-sync): The primary waits until at least one replica has received the log, but not necessarily replayed it, before returning. This is a compromise; MySQL provides a semi-synchronous replication plugin.
• Synchronous replication (Sync): The primary waits until a replica has replayed the change before returning. This offers the greatest data safety but significantly increases write latency, so it is uncommon in production.

Most Internet workloads use asynchronous replication, trading some consistency for performance and then compensating for the small delay at the application level.

3. Implementing Read/Write Splitting

Once primary-replica replication is in place, the next step is to send application reads to replicas and writes to the primary. There are two mainstream approaches:

1. Application-Level Routing

The ORM or connection layer identifies the SQL statement type and routes it automatically: SELECT goes to a replica, while INSERT/UPDATE/DELETE goes to the primary. Mainstream ORMs and frameworks, including Prisma, TypeORM, Django ORM, and application layers above MyCat, support configuring one primary with multiple replicas and provide built-in routing.

// Pseudocode: DataSource configuration
{
  master:  { url: 'mysql://primary...'  },
  replicas: [
    { url: 'mysql://replica1...' },
    { url: 'mysql://replica2...' },
  ],
}
// SELECT automatically goes to a replica (round-robin/random); writes automatically go to the primary

Advantages: It is controllable and transparent, and business rules can be embedded, such as "force reads to the primary for five seconds after a write." Disadvantages: Every language and framework must be configured separately, and cross-database transactions and multiple data sources become complicated.

2. Proxy-Level Routing

An intermediate proxy hides primary-replica details. The application sees only one logical database, while the proxy automatically routes SQL statements.

• MySQL ecosystem: ProxySQL, MySQL Router, and MaxScale, which has powerful routing capabilities
• PostgreSQL ecosystem: Pgpool-II for connection pooling and routing, and HAProxy for pure load balancing without SQL awareness; PgBouncer is mainly a connection pool and is not well suited to statement-level routing

Application ──> [ Proxy Layer: ProxySQL / Pgpool ] ──> Primary (writes)
                          │
                          ├──> Replica 1 (reads)
                          └──> Replica 2 (reads, load-balanced)

Advantages: The application remains unaware of the topology, and scaling and failover are transparent to the business. Disadvantages: It adds another network hop, the proxy itself must be highly available, and operational costs are significant.

In practice, the two approaches are often combined: the proxy layer handles basic routing and high availability, while the application layer exercises fine-grained control over critical paths, such as strongly consistent reads after writes.

4. The Consistency Trap of Replication Lag

Asynchronous replication introduces a classic problem: replication lag.

Immediately after the primary completes a write, a replica may not yet have replayed it. If a user reads immediately after writing, such as refreshing the page after posting a comment, and the read goes to a replica, the user sees stale data: "Where did the comment I just posted go?" This read-after-write inconsistency is the most common consistency trap with read replicas. Lag is usually measured in milliseconds, but it can reach seconds under heavy load.

There are four countermeasures; choose according to the scenario:

1. Route strongly consistent reads after writes to the primary: On critical paths, such as a user's own reads after their writes, force reads to the primary for a period of 5-30 seconds after the write. Implement this with a session marker or sticky session. This is the most common approach.
2. Never use replicas for critical paths: Strongly consistent reads such as user home pages, order details, and account balances always go to the primary. Rankings, recommendations, and reports that tolerate eventual consistency go to replicas.
3. Wait for replication to catch up: Check the replication position before reading and wait until the replica catches up. MySQL provides WAIT_FOR_EXECUTED_GTID_SET, while PostgreSQL provides pg_wait_for_replication_lsn. This is accurate but increases latency, so use it cautiously under high concurrency.
4. Accept it at the business level: In many scenarios, users will not notice, such as when new content appears on a list page a few seconds late. Eventual consistency is sufficient.

Do not pursue strong consistency everywhere. Identify the few paths that require it, pin those reads to the primary, and let replicas handle everything else.

5. Replica Count: More Is Not Always Better

A common beginner's misconception is that if replicas distribute reads, adding a dozen of them must provide unbeatable read performance. That is wrong. Replica count has clear diminishing returns:

• Every replica increases replication load on the primary: The primary must send binlog/WAL data to every replica. More replicas mean greater network and CPU overhead on the primary.
• Operational costs increase linearly: Every additional replica is another machine to monitor, back up, patch, and include in failover procedures.
• Lag becomes harder to manage: With more replicas, any one of them can fall behind and require intervention, increasing the probability of lag-related incidents.

The rule of thumb is simple: use enough replicas to distribute the read load. Start with one or two, monitor primary CPU, connection counts, and replica lag, and add more only when you actually reach a bottleneck. Two to five replicas cover most scenarios. Blindly piling on replicas buys more expensive and complex operations, not performance.

6. When to Use Read Replicas (and When Not To)

Signals That You Should

• Reads are the actual bottleneck: Primary CPU remains above 70%, connections are exhausted, and slow queries accumulate, with SELECT statements responsible for most of the pressure.
• Reads can tolerate eventual consistency: A delay of a few seconds is acceptable for list pages, search, recommendations, reports, and analytics.
• Foundational optimization is complete: Indexes, queries, and table schemas have already been optimized, and the system has genuinely reached the point where horizontal scaling is necessary.

Cases Where You Should Not

• The workload is write-heavy: Read replicas cannot help write-intensive workloads because they do not distribute writes. Consider database and table sharding instead.
• Consistency requirements are extremely strict: Financial accounts, inventory deductions, and payments cannot tolerate any inconsistency caused by lag.
• Traffic has not reached a bottleneck: Often, adding an index or rewriting a query solves the problem. Do not reach for replicas reflexively. Architectural complexity is debt, so avoid taking it on when possible.

Decision order: optimize first (indexes/queries/Schema), then add caching, then read replicas, and finally sharding. Read replicas are a horizontal-scaling measure used after other optimizations have been exhausted, not the first lifeline to grab.

7. Read Replicas vs. Caching

Read replicas and caches such as Redis or Memcached both reduce read pressure, but they serve entirely different purposes and are often confused:

In practice, they are often combined:

Read request ──> Cache layer (Redis, absorbs hot reads) ──hit──> Return directly
                        │ miss
                        ▼
                  Read replica (handles all reads) ──> Refill cache

The cache sits in front and absorbs 80%-95% of hot reads, while the remaining long-tail reads go to replicas. This reduces the number of replicas required and makes the overall architecture more economical. The cache is the first line of defense; replicas are the second. Do not use replicas to do a cache's job: replicas can handle arbitrary reads, but they are expensive and heavy. Do not rely on a cache without replica capacity either: if the cache experiences widespread invalidation or a cache avalanche, the primary will be overwhelmed without replicas as a fallback.

8. Cross-Region Replicas: Local Reads and Disaster Recovery

Replicas within one region address read pressure, but geographically distant users still face physical latency. When users in Asia access a primary in US East, the speed of light alone imposes an RTT of more than 100 milliseconds.

Cross-region replicas copy data to different geographic regions for two purposes:

1. Geo-local Reads: Deploy replicas close to users to reduce read latency significantly. Asia-Pacific users read from an Asia-Pacific replica, while US East users read from a US East replica.
2. Disaster Recovery: If the entire primary region fails because of a data-center power outage or a severed fiber cable, a cross-region replica can be promoted to primary to maintain business continuity.

        ┌────────────────────┐
        │ Primary Region     │  Writes
        │ (US East): Primary │
        └─────────┬──────────┘
                  │ Cross-region replication (usually asynchronous, with greater lag)
        ┌─────────┴─────────┐
        ▼                   ▼
  ┌────────────────┐  ┌────────────────┐
  │ Asia-Pacific   │  │ Europe Replica │  Local reads + disaster recovery
  │ Replica        │  │                │
  └────────────────┘  └────────────────┘

Note that cross-region replication usually has greater lag, from tens to hundreds of milliseconds depending on distance, making consistency traps more pronounced. Strongly consistent reads after writes must return to the primary region, and the business design must account for this fully. For example, after a user publishes content, refreshing the home page should use the primary region even if the user is in Asia-Pacific. List pages can read from the nearest replica and tolerate a delay of a few seconds.

Summary

• Read replicas address read-pressure bottlenecks by using primary-replica replication to distribute reads across multiple machines, allowing the primary to focus on writes and strongly consistent reads.
• Read/write splitting has two implementation layers: application-level routing, where the ORM or connection layer routes statements, and proxy-level routing, where ProxySQL or Pgpool hides topology details. They are often combined in practice.
• Asynchronous replication introduces lag. Read-after-write inconsistency is the most common trap. Identify critical paths and pin strongly consistent reads to the primary.
• Use only as many replicas as needed; more is not always better. Cross-region replicas support both geo-local reads and disaster recovery, but latency and consistency must be reassessed.
• The decision order remains: optimize first (indexes/queries/Schema), then cache, then add read replicas, and finally shard.

Key takeaway: A read replica is not a silver bullet. It is a tool for horizontally scaling read capacity. In the right scenario, such as a read-heavy workload that tolerates eventual consistency, it is powerful. In the wrong scenario, such as a write-heavy workload, strict consistency requirements, or traffic below the bottleneck threshold, it is a burden.