Read Replica Routing in a Monolith Without Stale-Read Bugs

What read replica routing is and why replication lag makes it hard

Read replica routing is the decision, made per query or per request, to send a read to a replica instead of the primary. Teams add replicas to a monolith to move read traffic, such as reporting queries, list pages, and search, off the primary. The primary then keeps its CPU, I/O, and connection slots for writes and for reads that must be current.

On Postgres, the default setup is asynchronous streaming replication. The primary writes every change to the write-ahead log (WAL). A WAL sender process streams those records to each replica, where a single startup process replays them in order. The primary commits without waiting for any of this, so a replica always trails the primary by some amount.

That amount varies a lot. Replay is serial, so anything that produces a large burst of WAL creates a backlog: bulk writes, backfills, index builds, and other schema migrations. Long transactions on the primary and aggressive vacuuming also add WAL volume. A slow or saturated network delays delivery. On the replica, a long-running query can conflict with replay, for example when vacuum removes rows that the query still needs. Postgres then either cancels the query or pauses replay, depending on the standby delay settings. While replay is paused, lag keeps growing.

Synchronous replication with synchronous_commit = remote_apply removes staleness for the replicas you name. A commit waits until those replicas have applied the change. The cost is higher write latency, and writes stall if a required replica is slow or down.

So under asynchronous replication, lag is normal. Routing a read is therefore a consistency decision, not just a load-balancing one.

Copy                 +------------------------------------+
                 |        Monolith app + router       |
                 +------------------------------------+
                     |                         |
                     | writes, reads that      | reads that
                     | must be current         | tolerate lag
                     v                         v
+-------------------------------+   +-------------------------------+
| PRIMARY                       |   | ASYNC REPLICA                 |
|  commit -> write WAL          |   |  startup process replays WAL  |
|  (does not wait for replicas) |   |  serially, in order           |
|  WAL sender ------------------+-->|  WAL receiver                 |
+-------------------------------+   +-------------------------------+
          ^                       ^                 ^
          |                       |                 |
  WAL bursts on primary:     network delay    query conflicts on replica:
  bulk writes, backfills,                     cancel the query, or
  index builds, migrations,                   pause replay (lag grows)
  long txns, vacuum

+---------------------------------------------------------------------+
| Alternative: synchronous_commit = remote_apply (named replicas)     |
| commit waits until they apply: no stale reads there,                |
| but higher write latency; writes stall if one is slow or down       |
+---------------------------------------------------------------------+

Under asynchronous streaming replication the primary commits without waiting, and serial WAL replay on the replica falls behind by an amount that grows with WAL bursts, network delay, and query conflicts, so each read's route is a consistency decision.

Why 'SELECTs go to replicas' breaks read-your-writes

The rule "SELECTs go to replicas, everything else to the primary" looks at each statement by itself. Consistency depends on the statements around it, so the rule fails in three common ways.

Reads inside a transaction. A transaction that writes a row and then selects it expects to see its own uncommitted change. If that SELECT goes to a replica, it runs outside the transaction. It reads a different snapshot that cannot include the write.

Reads after a write in the same request. A user saves a profile and the redirect loads it from a replica. The commit has not replicated yet, so the page shows the old data.

Reads that drive writes. A balance check, an "is this email taken?" lookup, or an inventory count read from a lagging replica can approve a withdrawal that overdraws the account. It can also let a duplicate through to a later write. A unique constraint on the primary catches some of these cases. Application-level checks have no such backstop.

These bugs depend on timing. In tests and on a quiet staging database, lag is close to zero and the replica usually catches up before the next query runs. Lag grows under write bursts, vacuum, or long-running queries on the replica, which means production at peak. Code review won't catch it either, because every line looks correct.

Connection handling hides the problem further. A request that checks out a replica connection early and holds it for the whole request sends its post-write reads there. An ORM that caches a connection per session or thread does the same thing. A transaction-level pooler can put consecutive statements on different backend connections, which breaks any routing that relies on session state. None of these raise an error. The query succeeds against the wrong database and returns stale data.

Copy# Naive router: picks a database per statement and ignores what came before it.
class NaiveRouter:
    def __init__(self, primary, replica):
        self.primary = primary   # DB-API style connection to the primary
        self.replica = replica   # DB-API style connection to a lagging replica

    def execute(self, sql, params=()):
        is_read = sql.lstrip().upper().startswith("SELECT")
        cur = (self.replica if is_read else self.primary).cursor()
        cur.execute(sql, params)
        return cur


def withdraw(db, account_id, amount):
    # Every write below runs inside the primary's transaction.
    # The balance read is routed to the replica, so it is not part of that
    # transaction and may predate a concurrent withdrawal that has not
    # replicated yet. It raises no error and returns a stale number.
    balance = db.execute(
        "SELECT balance FROM accounts WHERE id = %s", (account_id,)
    ).fetchone()[0]
    if balance < amount:
        raise ValueError("insufficient funds")
    db.execute(
        "UPDATE accounts SET balance = balance - %s WHERE id = %s",
        (amount, account_id),
    )
    db.primary.commit()


def save_profile_then_render(db, user_id, name):
    db.execute("UPDATE users SET name = %s WHERE id = %s", (name, user_id))
    db.primary.commit()
    # Same request, next statement: replica lag decides whether the user
    # sees the name they just saved. With near-zero lag in tests, this passes.
    return db.execute(
        "SELECT name FROM users WHERE id = %s", (user_id,)
    ).fetchone()[0]

A per-statement router sends a balance check and a post-write read to the replica. The decision approves a withdrawal from stale data and the page can show the old profile, and neither case raises an error.

Who should route reads to replicas, and when to avoid it

Replicas pay off when the primary is saturated by reads, meaning CPU or disk IO is consumed by SELECT traffic rather than writes, and when a meaningful share of that traffic can tolerate seeing data a few seconds old. Reports, search listings, and dashboards are the usual candidates. If the primary is write-bound, replicas do nothing for you. Every replica replays the same write stream, so write pressure is duplicated rather than relieved.

Try the cheaper options first. Query and index tuning often removes the load entirely. Caching handles hot, repeated reads. Vertical scaling buys time without changing application semantics. Materialized views precompute expensive aggregates. A separate analytics store moves reporting off the transactional database. Synchronous replicas avoid staleness but add commit latency to every write. If read volume is small, or most reads must be fresh, the routing complexity costs more than it saves.

Before you route anything, classify each read path:

If the third row is a small fraction of your traffic, replicas are probably not the right fix.

Read-your-writes strategies ranked by complexity

Each strategy below trades precision for plumbing.

Request pinning is a single flag: once the request writes, every later query in that request goes to the primary. It costs almost nothing, but it does not cover the common case. A user submits a form, gets redirected, and the next request reads from a lagging replica.

Session pinning fixes the redirect by sending a user to the primary for a fixed window after a write. The window is a guess. If it is too short, lag spikes leak stale reads. If it is too long, primary load grows. Heavy writers stay pinned almost permanently, so they never use replicas.

LSN routing removes the guess. After a write, capture the primary's WAL position with SELECT pg_current_wal_lsn() and store it with the user. Before reading, check a candidate replica with SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn. If the replica has not caught up, try another replica or fall back to the primary. The cost is that the LSN has to travel through sessions, cookies, and background jobs that act for a user. Each position check also adds a round trip unless you cache replica positions for a short time.

Copy-- 1. On the PRIMARY, right after the write transaction commits:
--    capture the WAL position the write must be visible at.
--    Store the result (e.g. '0/3A2B1C8') in the user's session or cookie,
--    or pass it along with any background job enqueued for that user.
SELECT pg_current_wal_lsn() AS write_lsn;

-- 2. On a candidate REPLICA, before serving the user's read:
--    $1 is the stored write_lsn. pg_last_wal_replay_lsn() returns NULL
--    if the server is not replaying WAL (e.g. it was promoted), so
--    treat NULL as "not safe" and route the read elsewhere.
SELECT COALESCE(pg_last_wal_replay_lsn() >= $1::pg_lsn, false) AS caught_up;

-- caught_up = true  -> run the read on this replica
-- caught_up = false -> try another replica, or fall back to the primary

-- 3. Optional: cache each replica's position for a short time so most
--    reads skip the extra round trip. A single poll per replica serves
--    every pending check against that cached value.
SELECT pg_is_in_recovery()      AS is_replica,
       pg_last_wal_replay_lsn() AS replay_lsn;

-- 4. Optional, run on the primary: replication lag in bytes for a
--    replica position read in step 3, useful for metrics and alerts.
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), $1::pg_lsn) AS lag_bytes;

Conceptual Postgres SQL for LSN-based read-your-writes routing: record the primary's WAL position after a write, then send a read to a replica only if that replica has replayed past that position.

How to implement a single routing layer in the data-access layer

All of these strategies fail if each call site decides for itself, so put the decision behind one function in the data-access layer, such as connection_for(hint). The hint is strong, read_your_writes(lsn), or stale_ok. If a caller passes no hint, it gets the primary. Controllers and jobs never name a database. They open a scope like with consistency(stale_ok): ..., and every query inside it asks the router for a connection.

The scope has to follow three rules:

• Transactions pin to the primary. If a transaction has started, or a write happens inside the scope, the router returns the primary until the scope ends. A replica connection cannot take writes, and splitting one unit of work across two nodes gives you a mix of snapshots.
• The scope selects a pool, not session state. With a transaction-mode pooler in front, session-level settings do not persist between transactions. Keep separate pools for the primary and the replicas, and let the router choose between them.
• ORM sessions bind once. Bind each ORM session to the connection the router returned when the scope opened. If the ORM picks connections lazily, a later query can quietly fall back to its default engine.

Background jobs need explicit handling. A job enqueued right after a commit can start before any replica has replayed that commit. You have two options. Either put the write's LSN in the job payload and run the job under read_your_writes(lsn), or mark the job type as strong.

Defaulting to the primary is the safe choice, but it has a cost. It offloads little traffic until teams go through their read paths and mark each one stale_ok or read_your_writes.

Copy# One entry point. Callers pass a hint and never name a database.
function connection_for(hint = STRONG):
    scope = current_scope()
    if scope.in_transaction or scope.has_written:
        return primary_pool.get()          # keep the whole unit of work on one node
    if hint == STALE_OK:
        return replica_pool.get()          # choose a pool, not session-level settings
    if hint is READ_YOUR_WRITES(lsn):
        replica = replica_pool.get()
        if replica.replayed_lsn() >= lsn:  # this replica has applied the caller's write
            return replica
        replica.release()
        return primary_pool.get()          # replica is behind, so read from the primary
    return primary_pool.get()              # no hint, or STRONG

# Read path: bind the ORM session once, when the scope opens.
with consistency(STALE_OK) as scope:
    session = orm.session(bind = connection_for(scope.hint))
    rows = session.query(Order).where(customer_id = 42).all()

# Write path: capture the commit position and pass it to the job.
with consistency(STRONG) as scope:
    session = orm.session(bind = connection_for(scope.hint))
    session.begin()
    order = session.insert(Order(customer_id = 42))
    session.commit()
    commit_lsn = session.current_wal_position()  # e.g. pg_current_wal_lsn() in PostgreSQL
enqueue("send_receipt", { order_id: order.id, min_lsn: commit_lsn })

# Job: may run before replicas catch up, so it carries the LSN it needs.
job send_receipt(payload):
    with consistency(READ_YOUR_WRITES(payload.min_lsn)) as scope:
        session = orm.session(bind = connection_for(scope.hint))
        order = session.get(Order, payload.order_id)
        mailer.send_receipt(order)

Language-neutral pseudocode for a single connection_for(hint) router. It defaults to the primary, pins transactions and writes to the primary, binds ORM sessions once per scope, and passes the commit LSN to a background job so the job can use read-your-writes.

Measuring lag, health checks, and failover to the primary

Measuring lag

Postgres gives you two lag signals. The first is a byte gap: compare the primary's current WAL position (pg_current_wal_lsn()) with the replica's last replayed position (pg_last_wal_replay_lsn()). The second is a time gap: now minus pg_last_xact_replay_timestamp() on the replica. The timestamp records when the last replayed transaction committed on the primary. If the primary is idle, nothing new commits, so this figure keeps growing even though the replica is fully caught up. Treat a large time gap as real lag only when the byte gap is also non-zero. Some teams avoid the problem by writing a heartbeat row on the primary every second or so.

Rotation with hysteresis

Take a replica out of rotation when lag goes above a high threshold. Put it back only after lag stays below a lower threshold for a sustained period. Two thresholds stop a replica that hovers near one limit from flapping in and out. Also set a time limit on how long a replica can stay out of rotation. A replica stuck past that limit should page someone instead of sitting there forever.

Fallback without overload

If all replicas lag together, sending every read to the primary can push it over during the incident you are already handling. Shed stale_ok reads by serving them from cache or degrading the feature. Cap fallback traffic with a rate limit, so the primary keeps capacity for writes and strong reads.

Replay conflicts

A long query on a replica can block WAL replay. max_standby_streaming_delay controls how long replay waits before Postgres cancels the conflicting query. A lower value means more cancelled queries but less lag. A higher value lets queries finish but allows lag to build. hot_standby_feedback reduces conflicts by telling the primary which rows replica queries still need, at the cost of extra bloat on the primary.

Testing for stale reads and the impact on cost and engineering time

Make lag visible in staging

Stale-read bugs rarely appear in development because a local replica, if one exists, catches up in milliseconds. To expose them, make lag large and predictable in staging. On a Postgres standby, recovery_min_apply_delay holds back WAL replay by a fixed interval. A few seconds is enough to break any read path that silently depends on fresh data.

With lag injected, write integration tests that perform a write and then immediately read it back through the normal request path: the same handler, middleware and data-access layer that production uses. Do not call the primary directly in the test. The test should pass only when the routing layer correctly sends that read to the primary or waits for the replica to reach the required LSN.

What it costs

The upfront work is building the routing layer and classifying existing reads. The ongoing work is maintaining LSN propagation through sessions or tokens, keeping lag dashboards and alerts accurate, and reviewing classifications as new queries are added.

Illustrative estimate (assumed numbers, not a benchmark):

Savings per year = incidents avoided × hours per incident

With 6 incidents avoided per year and 10 hours per incident, that is 6 × 10 = 60 engineer-hours. Compare this figure with your own estimate of the build and maintenance hours.

The larger benefit is harder to quantify. All consistency decisions live in one place you can audit. You can move read paths to replicas one at a time, and reverse any change with a single edit.

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, 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.