Using Read Replicas the Right Way: Scaling Read Traffic

1. The problem read replicas actually solve

A read replica is a copy of a database instance that continuously receives changes from the primary server and serves read only traffic exclusively. The basic idea is simple: in most applications, the number of read operations exceeds the number of write operations by a wide margin, often ten to one or more. Instead of concentrating the entire load on a single instance, teams distribute read traffic across multiple read replicas and take pressure off the primary for write operations and critical transactions.

The effect shows up first in CPU and I/O utilization. A primary that has to handle write transactions and complex reporting queries at the same time reaches its limits quickly, visible as rising latency for every request, including writes. By offloading read traffic to read replicas, the primary stays reserved for the most critical workload: consistent, transaction safe writes. Reporting queries, analytics dashboards, and most display endpoints of an application are ideal candidates for this offload.

It is important to distinguish this from sharding: read replicas scale read traffic horizontally but change nothing about write load or the data volume a single server has to manage. Anyone who needs to scale write load or storage volume needs sharding or partitioning, not additional replicas. This distinction often decides whether a scaling project solves the real problem or just shifts symptoms around.

2. Replication types: physical, logical, statement based

Common relational databases offer several replication mechanisms that differ in granularity and flexibility. Physical replication, known as streaming replication in PostgreSQL, copies changes at the block level from the write ahead log (WAL). This is efficient and guarantees an exact binary copy, but it does not allow selective replication of individual tables and requires identical major versions between primary and replica.

Logical replication operates at the row level and lets you replicate only specific tables, synchronize across different database versions, or even write into a different schema. PostgreSQL implements this through publications and subscriptions, MySQL through row based binary logging. The advantage lies in flexibility, the downside in slightly higher overhead and more complex conflict handling for bidirectional replication.

Statement based replication, historically the first approach in MySQL, replicates the executed SQL statements themselves instead of the resulting data changes. The problem: non deterministic functions such as NOW() or RAND() can produce diverging results on the read replica when executed at a different point in time. Modern setups therefore rely almost exclusively on row based or physical replication, where the actual data changes are transmitted rather than the statements.

-- PostgreSQL: check replication status on the primary
SELECT
    client_addr,
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- MySQL/MariaDB: check status of a read replica
SHOW REPLICA STATUS\G
-- Important fields: Seconds_Behind_Source, Replica_IO_Running, Replica_SQL_Running

-- Create a publication for logical replication (PostgreSQL)
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- Subscribe on the replica side
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary.internal dbname=shop user=replicator'
    PUBLICATION orders_pub;

3. Understanding and measuring replication lag

Replication lag is the time span between a change on the primary and the moment the same change becomes visible on the read replica. This lag is not a rare exception but a structural property of asynchronous replication. Network latency, heavy write load on the primary, long running transactions on the replica, and insufficient I/O capacity on the replica side are the most common causes of growing lag.

An often overlooked pattern is the read after write case: a user updates a profile, the application writes to the primary, immediately redirects to a read view, and that view reads from a read replica that has not yet applied the change. To the user it looks as if the change was lost. This is solved either by briefly reading from the primary right after a write, or through so called read your writes routing, where the application carries a position marker of the last write and only reads from a replica once it has reached that marker.

Measuring replication lag should happen continuously, not just as a spot check. PostgreSQL provides pg_wal_lsn_diff() for byte distance and additionally timestamp based measurement via pg_last_xact_replay_timestamp(). MySQL provides a direct time value with Seconds_Behind_Source, which can become inaccurate under network problems because it is based on the timestamp of the last received event, not on a true end to end measurement.

4. Implementing read/write splitting in the application

Read/write splitting refers to the logic an application uses to decide whether a request goes to the primary or to a read replica. The simplest implementation happens at the ORM or repository layer: write operations (INSERT, UPDATE, DELETE) always go to the primary, pure SELECT queries are distributed across available replicas via round robin or weighted random selection. This split should live centrally in a connection layer, not scattered across individual repository methods, so it gets applied consistently.

A common mistake is deciding read/write splitting purely by HTTP method, routing every GET to a replica and every POST to the primary. That ignores the fact that many GET endpoints are called immediately after a write and therefore need consistent data. A more robust pattern is to mark transactions explicitly: within a transaction that contains even a single write, the entire connection stays on the primary, regardless of individual SELECT statements inside it.

Middleware solutions such as ProxySQL for MySQL or PgBouncer combined with pgpool II for PostgreSQL can handle read/write splitting transparently at the infrastructure level, so the application only sees a single connection while the distribution happens invisibly in the background. The advantage is less complexity in application code, the downside an additional infrastructure component that itself needs to run highly available.

-- Example connection routing configuration for an application
-- (pseudo configuration, common in many ORM adapters)

-- primary: write operations only and transactions with any write
-- replicas: pure read operations outside a write transaction

-- Example: force an explicit route inside a transaction
BEGIN;
-- entire transaction stays on primary, even though it starts with SELECT
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

-- Example: pure read operation outside a transaction,
-- allowed to be routed to a read replica
SELECT id, name, email FROM customers WHERE id = 42;

5. When consistency matters more than load distribution

Not every read query is a good fit for a read replica. Financial balances, stock checks before checkout, and any read operation that is directly part of a write flow should consistently read from the primary. The reason lies exactly in the replication lag described in the previous section: a stale stock count can lead to overselling, a stale account balance to a duplicate payout.

A practical rule of thumb is to classify read operations by their consistency requirement, not by technical convenience. Display data such as product descriptions, blog posts, or user profiles in a public context typically tolerate seconds of lag without a noticeable downside. Transaction critical data, on the other hand, needs either the primary or an explicit synchronization step that waits for the replica's replay position before answering the read request.

Some database systems offer built in mechanisms for this: PostgreSQL has supported synchronous replication since version 10 via synchronous_commit, where a commit is only confirmed after at least one replica has acknowledged the change. This eliminates lag entirely for those specific transactions but costs latency on every write and makes availability depend on the reachability of the synchronous replica, a classic tradeoff between consistency and availability.

6. Planning failover and replica promotion

If the primary fails, a read replica has to be promoted to become the new primary, a process called promotion. Automated failover requires a consensus mechanism that decides which replica gets promoted, typically the one with the least lag at the time of the failure. Tools such as Patroni for PostgreSQL or Orchestrator for MySQL handle this decision automatically and update DNS or proxy configuration so applications point to the new primary without manual intervention.

A critical pitfall is the so called split brain scenario: if the old primary becomes reachable again after a network partition event, but another replica has already been promoted, two primaries briefly exist at the same time, both accepting conflicting writes. Fencing mechanisms that actively isolate the old primary before a new promotion completes are therefore not an optional detail but a prerequisite for safe automated failover.

Manual failover remains the more pragmatic choice for many mid sized operations: a documented runbook that describes checking lag, promoting a replica, and updating application configuration in clear steps reduces the risk of misconfiguration compared to a rarely tested automated solution. In either case, it is important to rehearse the promotion process regularly in a staging environment, so it is not executed for the first time during an actual incident.

7. Monitoring: lag, health checks and alerts

Without continuous monitoring, replication lag stays invisible until it shows up as incorrect application data. A solid setup exports lag metrics in seconds and in bytes to a monitoring system such as Prometheus, with clearly defined thresholds for warnings and critical alerts. A lag of a few hundred milliseconds is unproblematic in many applications, while a lag of several minutes usually points to a structural problem, such as insufficient I/O capacity on the replica.

Health checks for read replicas should check more than plain reachability. A replica that is reachable but has not received any new changes for hours because the replication process silently stalled will consistently return stale data without a simple ping check ever noticing. A good health check therefore explicitly checks the timestamp of the last applied transaction and compares it against a threshold.

Alerting should be tiered: a warning at moderate lag growth, a critical alert once a threshold is crossed at which application logic starts displaying incorrect data. It is also important to automatically remove a replica from the load balancing pool once its lag exceeds a defined limit, instead of continuing to route requests to a demonstrably stale instance.

8. Scaling limits: how many replicas make sense

Read replicas do not scale linearly without limit. Every additional replica increases network and CPU load on the primary, because it has to stream changes to each replica individually. Beyond a certain count, in practice often between five and ten direct replicas, the primary itself becomes the bottleneck of the replication process rather than the bottleneck of application load.

Cascading replication partially solves this problem: instead of every replica reading directly from the primary, some replicas receive their changes from another replica, which itself reads from the primary. This reduces direct load on the primary but increases cumulative lag for replicas at the end of the cascade, another classic tradeoff between scalability and freshness.

Anyone who finds that even with optimal distribution of read traffic across read replicas, write load or data volume becomes the limiting factor, should not add more replicas but consider sharding or partitioning instead. Read replicas solve a read traffic problem, not a write load problem, and recognizing this limit clearly prevents an architecture team from investing time in the wrong scaling strategy.

9. Read replicas compared: approaches and tools

The choice between synchronous and asynchronous replication, between automated and manual failover, and between application side and infrastructure side read/write splitting largely determines how a team handles read replicas in practice. The table below compares the key decision points.

In practice, many teams combine both approaches: asynchronous read replicas for the bulk of read traffic, supplemented by a single synchronous replica for the most critical tables, such as payment status or inventory data. This hybrid strategy delivers the performance benefits of asynchronous replication without giving up consistency guarantees for the most important records.

10. Summary

Using read replicas the right way means more than spinning up an additional instance. Physical or logical replication provides the technical foundation, but the real work lies in handling replication lag: which read queries need consistent data, which can tolerate a short lag, and how the application makes this decision reliably. Read/write splitting should be implemented centrally, not scattered across individual code paths, and any transaction with a write component belongs consistently on the primary.

Failover planning, lag monitoring, and health checks are not an afterthought but an integral part of a production ready setup with read replicas. Anyone who considers these aspects from the start gains real load distribution without endangering the application's data consistency. It remains important to keep the boundary in view: replicas scale read traffic, not write load, and for the latter you need different tools such as sharding.

11. FAQ: Using Read Replicas the Right Way