How do read replicas and read/write splitting work, and what's the catch (replication lag)?

#The topology

• One primary (writer) accepts all INSERT/UPDATE/DELETE and, in most engines, DDL.
• One or more read replicas continuously receive a stream of changes from the primary (via WAL shipping in Postgres?, binlog replication in MySQL, or engine-specific mechanisms) and apply them locally.
• The application routes writes and read-your-write-critical reads to the primary, and routes everything else — reports, dashboards, list pages, search — to whichever replica is least loaded.

This works because in the overwhelming majority of production systems, reads outnumber writes by 10:1 to 1000:1. Read replicas horizontally scale the read side without touching the write path at all, which is usually the actual bottleneck to defer (sharding the writer is much harder).

#How the data actually gets there

Two dominant replication modes:

• Asynchronous replication — the primary commits and returns to the client immediately; the change streams to replicas after the fact. Highest write throughput, but replicas can lag by anywhere from milliseconds to (under load or network partition) minutes.
• Synchronous / semi-synchronous replication — the primary waits for at least one replica (or a quorum) to acknowledge the write before returning. Guarantees no data loss on failover and bounds lag, at the direct cost of write latency.

Most systems default to async for the read-scaling replicas and reserve sync replication (if at all) for a small number of replicas used specifically for failover/HA, not for read traffic.

#Replication lag: the catch

Lag is the delay between a write committing on the primary and that write becoming visible on a replica. It comes from:

• Network latency between primary and replica (worse across regions).
• The replica applying changes single-threaded (classic MySQL single-threaded SQL thread) or being CPU/IO-starved by its own read traffic.
• Long-running transactions or heavy write bursts on the primary that the replica has to catch up on.
• Large schema changes or big batch jobs generating a wall of WAL/binlog to replay.

The symptom every backend engineer eventually debugs: a user submits a form, the write succeeds against the primary, the app immediately issues a SELECT against a replica to render the confirmation page — and the row isn't there yet. This is the classic read-after-write consistency violation, and it's the central tradeoff of read replicas: you're trading strict consistency for read scalability.

#Fixing read-after-write without giving up replicas

• Sticky/session routing: after a user writes, route their subsequent reads (for some window, or for that HTTP session) to the primary, or to the specific replica proven caught-up.
• Causal tokens: the write returns a log position/LSN; the read request carries that token and the router waits for (or picks) a replica whose applied position is ≥ the token before answering.
• Read from primary for known-critical paths: don't over-engineer — routing "read my own profile right after I edit it" to the primary, and leaving bulk browsing on replicas, solves 90% of complaints with zero extra machinery.
• Monitor and route around lag: replicas expose their lag (pg_stat_replication / SHOW SLAVE STATUS → Seconds_Behind_Master), so a router or proxy can pull an unhealthy, badly-lagging replica out of rotation automatically.

#Where the routing logic lives

• Proxy-based: PgBouncer + a routing layer, ProxySQL, or a managed layer (RDS Proxy, Aurora's reader endpoint) — the application connects to one place and the proxy decides. Centralizes the logic, but adds a hop.
• Application/ORM-level: the data access layer picks a connection pool based on statement type or an explicit hint. More visibility and control, more code to maintain.

A minimal application-level router, illustrating the read-after-write fix with sticky routing:

import time
import random

class ReplicaRouter:
    def __init__(self, primary_pool, replica_pools, sticky_window_seconds=5):
        self.primary_pool = primary_pool
        self.replica_pools = replica_pools
        self.sticky_window_seconds = sticky_window_seconds
        self._last_write_at = {}  # user_id -> monotonic timestamp

    def record_write(self, user_id):
        self._last_write_at[user_id] = time.monotonic()

    def get_connection_for_read(self, user_id):
        last_write = self._last_write_at.get(user_id)
        if last_write is not None:
            still_sticky = (time.monotonic() - last_write) < self.sticky_window_seconds
            if still_sticky:
                # Force this user's reads back to the primary until the
                # window expires, so they never see their own write "disappear".
                return self.primary_pool.get_connection()

        healthy_replicas = [r for r in self.replica_pools if r.lag_seconds() < 2.0]
        pool = random.choice(healthy_replicas or [self.primary_pool])
        return pool.get_connection()

    def get_connection_for_write(self):
        return self.primary_pool.get_connection()

The key details: writes always go to primary_pool; the sticky window means this specific user's next few reads bypass replicas entirely, no matter which replica they'd have landed on; and any replica reporting more than 2 seconds of lag is excluded from the pool before it's ever picked, rather than being trusted and returning stale data.

#Other failure modes worth naming

• Replica read-your-write across a chain: with cascading replicas (replica-of-a-replica), lag compounds — check the actual topology, not just "is there a replica."
• Failover promotes a lagged replica: if a replica is promoted to primary during an outage while behind, any writes acknowledged on the old primary but not yet replicated are lost. This is exactly why synchronous replication exists for the HA-designated replica, separate from the read-scaling fleet.
• Schema changes: a DDL change applied to the primary needs to reach every replica; a long-running migration can itself cause a lag spike across the fleet.