Read Replica Routing in a Monolith: Consistency, Lag and Fallbacks

What read replica routing is and why naive routing breaks

Read replica routing is the logic that decides, for each query a monolith issues, whether it runs on the primary database or on one of its replicas. Teams adopt it to take read load off the primary. Dashboards, search pages, exports and list endpoints often account for most query volume, and moving them to replicas frees primary CPU, memory and connections for the work only the primary can do.

Replicas relieve read load and nothing else. Every write still lands on the primary, and every replica has to replay those same writes. If your primary is limited by write throughput, lock contention or write I/O, adding replicas will not help.

The usual first attempt is a blanket rule: writes go to the primary, every SELECT goes to a replica. This breaks because replication in most deployments, including the default streaming setup for postgres read replicas, is asynchronous. The primary commits and acknowledges the write before the replica has applied it. The gap between the two is replication lag. It is usually small, but it is unbounded and grows under heavy writes, long-running queries on the replica, or network trouble.

Consider a request that creates an order and then redirects to the order page. If the page reads from a replica that has not caught up, the order is missing and the user sees a 404. An update can also appear to revert. A background job can make a decision based on a row that has already changed. None of these produce an error; the query succeeds and returns stale data. Naive routing therefore turns a performance optimization into a correctness bug, and it is the hardest kind to reproduce.

Who should use replicas, and when to avoid them

Replicas pay off when reads dominate and most read paths can tolerate data that is a few hundred milliseconds or a few seconds old. Typical examples are a monolith serving catalog pages, dashboards, search listings and reports while writes stay modest. If the primary is saturated by writes, replicas do nothing for it. Every replica must replay the same WAL, so a write-bound primary stays write-bound. Workloads where users mostly read what they just wrote also benefit little, because read-your-writes rules send those reads back to the primary.

The operational cost is easy to underestimate. Each replica is a full instance with its own storage, monitoring and alerting. Major upgrades and parameter changes must be coordinated across the whole topology, and failover now has to repoint both writers and the router. A small team without on-call capacity may spend more time on that than it saves.

Before adding replicas, check whether one of these fixes the actual bottleneck:

The last option removes lag by making every commit wait for the standby. That trades the consistency problem for a write-latency and availability problem.

Classifying reads by consistency requirement

Routing decisions get easier once every read has a stated consistency requirement. Three classes cover almost everything a monolith does.

Strongly consistent reads must see the latest committed state. This includes read-your-writes (a user saves a profile and reloads it), reads whose result feeds a later write, such as checking stock before reserving it, and authorization checks. A revoked permission that is still visible on a lagging replica is a security bug, not a display glitch.

Bounded-staleness tolerant reads can be a few seconds old without harm. Examples are dashboards, product listings and search result pages. The user can refresh, and nothing downstream depends on the exact value.

Fully lag-tolerant reads can be minutes old. Reports, CSV exports and analytics queries fall here. They are also the heaviest queries, so moving them off the primary saves the most load.

Classify by where the data goes, not by who reads it. A background job that reads a stale balance and writes a derived total turns a temporary lag into a permanent wrong row. A cache filled from a replica keeps serving the old value after the replica catches up. A queue consumer that reads before the producer's write has replicated may act on a missing record and then drop the message. Any read that feeds persistence belongs in the strongly consistent class.

How routing works: a single choke point that defaults to the primary

Every query should pass through one function in the data access layer that picks a connection. When routing logic is spread across repositories, nobody can audit which reads might be stale. A single hook gives you one place to add lag checks, metrics and a kill switch.

Request flow:

request → router (consistency hint + session state) → primary pool (default, all writes, all transactions) or replica pool (explicit opt-in, lag under threshold)

Size the two pools independently. Replica traffic tends to be bursty and long-running, especially reports. If both kinds of traffic share one budget, a slow export can starve the connections that checkout writes depend on.

Two rules keep the router honest:

• Transactions stay on one connection. The decision is made once, at BEGIN. A read inside a write transaction must see that transaction's uncommitted rows, which only the primary connection holds.
• Unannotated queries go to the primary. Code has to declare that it tolerates stale data before it can reach a replica.

A pooler in transaction mode hands out a different server connection per transaction. Any session-level state is therefore unreliable: SET parameters, temp tables, advisory locks, and pinning that assumes a "sticky" backend. Keep pinning decisions in the application, keyed to the user session, not to the database session.

Opt-in routing delivers offload slowly, one endpoint at a time, but each move is a reviewed decision. Defaulting to replicas cuts primary load right away. The cost is stale-read bugs that only show up under lag, typically during the incidents when you can least afford them.

Copy// Single choke point: every query asks this function for a connection.
type Consistency = 'strong' | 'stale-ok';
interface Pool { acquire(): Promise<Conn>; }
interface Conn { query(sql: string, params?: unknown[]): Promise<unknown>; }

const MAX_LAG_MS = 2000;           // reads tolerate at most this much staleness
let replicasEnabled = true;        // kill switch: flip to false to drain replicas

export function makeRouter(primary: Pool, replica: Pool, lagMs: () => number) {
  return {
    // Default is 'strong': unannotated callers always land on the primary.
    async connectionFor(opts: {
      consistency?: Consistency;
      inTransaction?: boolean;      // decided once at BEGIN, never re-routed mid-transaction
      userWroteRecently?: boolean;  // app-level pin, keyed to the user session
    } = {}): Promise<Conn> {
      const wantsReplica =
        opts.consistency === 'stale-ok' &&
        !opts.inTransaction &&
        !opts.userWroteRecently &&
        replicasEnabled &&
        lagMs() < MAX_LAG_MS;
      // Separate pools, sized independently, so a slow report on the
      // replica pool cannot exhaust connections that writes depend on.
      return wantsReplica ? replica.acquire() : primary.acquire();
    },
  };
}

// Opt-in at the call site: this endpoint has been reviewed as stale-tolerant.
// const conn = await router.connectionFor({ consistency: 'stale-ok' });

A minimal connection-selection hook that defaults to the primary and uses the replica pool only for explicit stale-ok reads outside transactions, with lag, pinning and a kill switch checked in one place.

Implementing read-your-writes: session pinning and LSN-based routing

Session pinning

After any write, store a timestamp in the user's session or a signed cookie. For every read in that session, the router checks the timestamp: if the write happened within the pin window, the read goes to the primary. Choose a window comfortably above your normal p99 lag, such as several seconds. Pinning needs no database cooperation and works across any replica topology. However, the window is a guess. During a lag spike that outlasts it, stale reads come back. Every active writer also sends all of its traffic to the primary, so write-heavy users get no offload.

LSN-based routing

In Postgres, call pg_current_wal_lsn() on the primary after the transaction commits, and store that position with the session. Before using a replica, compare its pg_last_wal_replay_lsn() against the stored value, using the pg_lsn type rather than string comparison. If the replica has replayed past that point, it is safe to use. If not, try another replica or fall back to the primary. This is exact: a user is routed to the primary only while their own write is still in flight.

The cost is the check itself. It means either an extra query per read or a background poller that caches each replica's replay position. The position also has to travel with the work. A background job enqueued after a write, or a call to an internal service, needs the LSN passed along. Otherwise it silently loses the guarantee.

Copy# Read-your-writes via LSN routing, using psycopg 3.
# Assumes connections were opened with autocommit=True: `primary`, plus a list of `replicas`.

def write_and_capture_lsn(primary, session, sql, params):
    with primary.transaction():          # commits on exit
        primary.execute(sql, params)
    # Capture after commit so the position covers this write.
    lsn = primary.execute("SELECT pg_current_wal_lsn()::text").fetchone()[0]
    session["last_write_lsn"] = lsn       # also put this in any job payload or internal call
    return lsn


def replica_has_caught_up(replica, lsn):
    # Compare on the server as pg_lsn. Comparing strings gets it wrong ('0/9' > '0/1A').
    # This is the extra round trip per read. A poller that caches replay positions avoids it.
    row = replica.execute(
        "SELECT pg_last_wal_replay_lsn() >= %s::pg_lsn", (lsn,)
    ).fetchone()
    return row[0] is True                # NULL (server not in recovery) counts as not safe


def pick_read_connection(primary, replicas, session):
    lsn = session.get("last_write_lsn")
    if lsn is None:
        # No tracked write in this session, so any replica is acceptable.
        return replicas[0] if replicas else primary
    for replica in replicas:
        if replica_has_caught_up(replica, lsn):
            return replica
    return primary                       # no replica has replayed the write yet: fall back

A minimal Python router that records pg_current_wal_lsn() after a committed write, sends a read to a replica only once that replica's pg_last_wal_replay_lsn() has reached the recorded position (compared as pg_lsn), and otherwise falls back to the primary.

Measuring lag and health-aware routing in Postgres

Lag is best measured as a byte distance, not a clock delta. On the primary, pg_stat_replication lists each standby with its replay_lsn, and pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) gives the number of WAL bytes the replica has not yet applied. The view's replay_lag column adds a time-based estimate. On the replica itself, now() - pg_last_xact_replay_timestamp() looks convenient, but it measures time since the last replayed commit. When the primary is idle, that value keeps growing even though the replica is fully caught up. A routing layer that trusts it will eject healthy replicas during quiet periods. Use it only together with a check that the replica's replay position has reached the primary's current position.

Run a single background poller that checks each replica every second or two, rather than probing on every query. Mark a replica unhealthy when lag exceeds a threshold or the probe fails. Re-admit it only after several consecutive good samples, so a replica sitting near the threshold doesn't flap in and out of rotation.

Decide in advance what happens when no replica is healthy. Strict reads already go to the primary. Stale-tolerant reads can either fall back to the primary or fail fast. Fallback is risky because lag often spikes when the primary is already under heavy write load, and sending the read traffic back to it can make things worse. If you allow fallback, cap it with a concurrency limit and size the primary for that load.

Replicas also cancel queries on their own. When replay conflicts with a long-running query, Postgres waits up to max_standby_streaming_delay and then cancels the query. Enabling hot_standby_feedback prevents most of these cancellations, but it makes the primary keep dead rows that replica queries still need, which causes bloat on the primary. Send long analytical reads to a dedicated replica instead.

Copy-- Run on the PRIMARY: per-standby lag in WAL bytes, plus Postgres's own time estimate.
-- Byte distance is the routing signal; replay_lag is useful for dashboards.
SELECT application_name,
       client_addr,
       state,
       replay_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;

-- Run on the PRIMARY in the same poll cycle: capture the current WAL position.
-- The poller passes this value to each replica check below.
SELECT pg_current_wal_lsn() AS primary_lsn;

-- Run on each REPLICA: compare its replay position with the primary's LSN
-- captured above ('0/3A000148' is a placeholder for that value).
-- If lag_bytes <= 0 the replica is caught up, whatever time_since_last_commit says.
-- An idle primary makes time_since_last_commit grow, so don't use it alone.
SELECT pg_is_in_recovery()                                       AS is_standby,
       pg_last_wal_replay_lsn()                                  AS replay_lsn,
       pg_wal_lsn_diff('0/3A000148'::pg_lsn,
                       pg_last_wal_replay_lsn())                 AS lag_bytes,
       now() - pg_last_xact_replay_timestamp()                   AS time_since_last_commit;

-- Hot standby conflict settings (on the replica): how long replay waits before
-- cancelling conflicting queries, and whether to send feedback to the primary.
-- hot_standby_feedback = on avoids most cancellations but delays vacuum on the primary.
SHOW max_standby_streaming_delay;
SHOW hot_standby_feedback;

Health-poller queries that measure replica lag as WAL byte distance against the primary's current LSN, with the replay-timestamp delta kept only as a secondary signal, plus the settings that govern hot standby query cancellations.

Testing, observability, and impact on cost and engineering time

Testing with artificial lag

Stale-read bugs rarely appear in staging because replicas there are idle and catch up within milliseconds. To expose these bugs, configure a staging standby with recovery_min_apply_delay set to a few seconds. Then run your end-to-end flows against it. Any flow that writes and then reads through the replica will now fail visibly. These failures show which call sites are missing a consistency classification.

Observability

Tag every query with its routing decision. For example, add a SQL comment or set application_name to record the target and the consistency class. Slow-query logs and pg_stat_activity will then show where each query went. Emit a counter labeled by target (replica, primary) and by fallback reason (lag_exceeded, pinned_session, replica_unhealthy).

Track the replica share over time. A drop in that share usually means one of two things: lag is rising, or a new code path defaults to the primary.

Because routing happens at one choke point, a reviewer only needs to check one thing in a pull request: the classification at each call site. They don't have to reason about connection handling spread across the codebase. When a stale read is reported, the query tag shows which path served it.

Cost

Illustrative estimate (assumed numbers, not a benchmark):

Monthly cost = (replicas × instance cost) + (engineering hours × hourly rate ÷ amortization months)

Assumed inputs:

• 2 replicas at $600 each
• 120 hours of initial work at $100/hour, amortized over 12 months

Result: 1,200 + 12,000 ÷ 12 = $2,200/month

Compare this to the cost of scaling up the primary instead. If the primary upgrade costs less, replicas are not justified on cost alone.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to read replica routing monolith, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.