Why does routing SELECTs to read replicas cause stale data bugs?

Read replica routing sends some queries to copies of the database instead of the primary. In a monolith the motive is usually load: most traffic is reads, so moving SELECTs off the primary frees CPU, I/O and connections for writes. Postgres replicas are typically asynchronous. The primary commits, acknowledges the client, and then streams WAL to replicas, which replay it. The gap between a commit on the primary and its replay on a replica is replication lag.

The classic failure looks like this (timings are illustrative):

Time Request Primary Replica
t0 POST /profile sets name to "Ana" Commits, returns 302 Still has "Anna"
t0 + 40 ms GET /profile after redirect Not consulted Serves "Anna"
t0 + 300 ms None None Replays WAL, now "Ana"

The user sees their edit vanish, reloads, and it's back. The router behaved correctly by its own rule: writes go to the primary, SELECTs go to a replica. That rule looks only at the statement. Whether the GET must reflect the previous POST depends on the session's history. Read-your-writes is a property of a sequence of requests, so routing by verb cannot provide it.

The bug rarely appears in development or on quiet days. When the primary is idle, replay usually finishes before the browser completes the redirect. Under load, lag grows. Write bursts produce more WAL to ship, a busy replica replays it more slowly, and long queries on the replica can hold replay back.

This guide's position is to route each request according to how stale its data is allowed to be. Some staleness is inherent once replicas are involved, so the goal is to decide where it is acceptable, not to eliminate it.

       Browser         App+router          Primary            Replica
          |                 |                 |                 |
t0        |-POST /profile-->|                 |                 |
          |                 |-UPDATE -------->|                 |
          |                 |<-COMMIT ok------|                 |
          |<-302 redirect---|                 |-WAL stream----->|
          |                 |                 |  WAL in flight  | name="Anna"
          |                 |                 |                 |
t0+40ms   |-GET /profile--->|                 |                 |
          |                 |-SELECT--------------------------->|
          |                 |<-name=Anna (stale)----------------|
          |<-200 "Anna"-----|                 |                 |
          |                 |                 |                 |
t0+300ms  |                 |                 |                 | replays WAL
          |                 |                 |                 | name="Ana"

With illustrative timings, the GET after the redirect reaches the replica before it has replayed the commit's WAL, so the user's update appears to be lost.

How does read replica routing work?

There are two parts: the replicas and the router.

The replicas. A Postgres streaming replica is a hot standby. It connects to the primary, receives WAL records as the primary generates them, and replays them in order. While it replays, it accepts read-only connections. Any write statement on a standby fails with an error. Replay is ordered and only moves forward. At any moment a replica reflects some past state of the primary, and that state is identified by the LSN it has replayed up to.

The router. The application holds at least two connection pools, one to the primary and one or more to replicas. The router is code that runs before each statement or each request and picks a pool. A naive router looks at one input, the statement type. A correct router looks at several:

  • Transaction state. If a transaction is open, every statement goes to the primary on the same connection.
  • Request or session history. If this request or this user wrote recently, reads go to the primary. "Recently" is either a time window or an LSN the replica must have reached.
  • Declared staleness tolerance. The call site says whether this read may be stale, either through an opt-in helper or through a classification of the code path.
  • Replica health. Replicas that lag past a threshold are left out of the pool.

The rest of this article covers each input: which reads tolerate staleness, how to implement read-your-writes, how to keep routing state through threads, tasks and job queues, and how to measure lag and exclude unhealthy replicas.

Which reads can safely go to a replica?

The useful question is not what a query looks like but what breaks if its answer is a few seconds old. The same SELECT against orders is harmless in a weekly report and a bug on the confirmation page rendered right after checkout. Classify each code path by the staleness it can tolerate:

Consistency class Typical reads Cost of a stale answer Route to
Strong Reads after your own write, reads inside a transaction, authorization and billing checks Wrong decisions: access granted after revocation, a charge evaluated against an old balance Primary only
Session The same user's pages shortly after they change something The user's edit appears lost, so they retry or file a ticket Primary for a window after a write, replica otherwise
Staleness-tolerant Dashboards, search listings, reports, public catalog pages Numbers or listings lag by seconds Replica

Reads inside a transaction belong on the primary for a mechanical reason. A replica connection is a separate session, so it cannot see the transaction's uncommitted writes or share its snapshot.

Replicas pay off for teams whose load is dominated by the third row: browse-heavy storefronts, content feeds, internal analytics. Those reads can move without per-request reasoning.

The case is weaker when most hot paths are correctness-critical, such as ledgers or inventory reservations, or when most traffic follows a write, as in multi-step forms or admin tools where every page load comes right after a save. In those systems little load leaves the primary, and you still take on a new class of bugs. Scaling the primary or caching specific results is often the better trade.

Who should use read replica routing?

Read replica routing suits teams that meet all of these conditions:

  • The primary is constrained by reads. CPU, I/O or connection count on the primary is driven mainly by SELECT traffic, not by writes, vacuum or a few bad queries. If writes are the bottleneck, replicas do nothing for you, because every replica replays every write too.
  • Much of the read traffic is staleness-tolerant. Profiling shows that listings, search, feeds, dashboards or reports make up a meaningful share of the read load, not just of the endpoints.
  • The team owns the data-access layer. Someone can add a router, contextvars or the equivalent, and an opt-in helper, and then review call sites as they move to replicas. The router is shared infrastructure and needs an owner.
  • Operations can watch lag. Someone already monitors the database or can add lag metrics and alerts. Without lag monitoring, replica routing fails quietly.

It is a poor fit for a team that has not yet tuned queries and indexes on the primary. The cheapest read is still the one you no longer need to run, and replicas copy inefficient queries to more machines.

When should you use read replica routing?

Use it when these conditions hold together:

  • Simpler options are used up or too expensive. Slow queries are fixed, obvious caching is in place, and the next primary instance size is unavailable, too costly, or would make failover take too long.
  • Read load and write load grow separately. Traffic growth comes mostly from browsing, search or reporting, and write volume stays relatively flat.
  • You can name the paths to move. You have a list of specific hot read paths, each with a stated staleness tolerance, rather than a plan to "send reads to replicas."
  • Heavy analytical queries compete with OLTP traffic. Reports, exports and ad-hoc analysis on a replica isolate their I/O and CPU from user-facing writes. This is often the lowest-risk first use, since these readers usually accept lag.

Start with the staleness-tolerant row of the table above, keep the primary as the default, and move paths one at a time while you watch both primary load and stale-read reports.

When should you avoid read replica routing?

Avoid it, or keep it to analytics only, in these cases:

  • Most hot paths need strong consistency. In ledgers, inventory reservations, permission checks and billing, almost every read goes to the primary anyway, so you take on routing complexity for little offload.
  • Most reads follow a write from the same user. In multi-step forms, admin back offices and editors that save on every change, session stickiness pins nearly all traffic to the primary.
  • The bottleneck is writes, locks or vacuum. Replicas replay the same write volume and do not reduce lock contention on the primary.
  • Nobody can watch lag. Without lag metrics and pool exclusion, a lagging replica keeps serving old data and the first signal is a user complaint.
  • The application cannot carry context reliably. If request state is regularly lost across thread pools, async tasks or job queues, and you cannot fix that, read-your-writes guarantees break in ways that are hard to find.
  • You expect a code change to fix a consistency bug. Replica routing adds staleness. It never removes it.

In these cases, vertical scaling, query and index work, targeted caching, or replicas limited to reporting usually cost less in total.

How does read-your-writes work with Postgres replicas?

There are two common mechanisms. Both send a user's reads to the primary until a replica has their latest write.

Session stickiness

When a request writes, store a marker such as last_write_at in the session or a signed cookie. For that user, send every read to the primary until now - last_write_at exceeds a window. Choose the window from observed lag, not intuition. If p99 replica lag is 300 ms, a 2-second window leaves headroom. A fixed window is still only a heuristic. Lag spikes during vacuum, bulk loads, or network trouble, and a spike past your window brings the stale reads back. Write-heavy users also refresh the marker on every write, so they stay pinned and their traffic returns to the primary. This is fine as long as such users are a small share of traffic.

LSN-based routing

After the write commits, run SELECT pg_current_wal_lsn() on the same connection and store the result with the session. Before a read, run SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(), $1) >= 0 on the replica. The routing check is roughly: if session.lsn is null or replica_caught_up(session.lsn) then use replica else use primary. You can also retry once after a short sleep before falling back to the primary.

LSN routing is exact, but each read routed this way pays for an extra round trip. The LSN also has to travel through sessions, cookies, and job payloads. If any hop drops it, the check passes and the replica serves stale data with no error.

import time

def record_write_lsn(primary_conn, session):
    with primary_conn.cursor() as cur:
        cur.execute("SELECT pg_current_wal_lsn()::text")
        session["last_write_lsn"] = cur.fetchone()[0]  # e.g. '0/3A1F2B8'

def replica_caught_up(replica_conn, lsn):
    with replica_conn.cursor() as cur:
        cur.execute(
            "SELECT coalesce(pg_wal_lsn_diff(pg_last_wal_replay_lsn(), %s::pg_lsn) >= 0, false)",
            (lsn,),
        )
        return cur.fetchone()[0]

def choose_read_conn(session, primary_conn, replica_conn, retry_sleep_s=0.05):
    lsn = session.get("last_write_lsn")
    # No recorded write: the replica is safe for this user.
    # Silent failure: if a session, cookie, or job payload dropped the LSN,
    # this branch also runs and the user can get stale data.
    if lsn is None:
        return replica_conn
    if replica_caught_up(replica_conn, lsn):  # extra round trip per read
        return replica_conn
    # Wait once briefly, then fall back to the primary.
    time.sleep(retry_sleep_s)
    if replica_caught_up(replica_conn, lsn):
        return replica_conn
    return primary_conn

LSN-based read-your-writes routing: capture pg_current_wal_lsn() on the primary after a write, then use a replica only after pg_wal_lsn_diff shows it has replayed past that LSN, with one short retry before falling back to the primary.

How do you implement query routing in a monolith?

Routing logic usually lives in three layers. Request-scoped middleware sets a "primary required" flag, either at the start of any non-GET request or as soon as the request performs its first write. An ORM-level database router reads that flag when it picks a connection. Explicit opt-in helpers let a call site declare that this one read tolerates staleness and may use a replica.

When retrofitting an existing monolith, make the primary the default and require opt-in for replicas. Routing every SELECT to a replica by default spreads the risk across thousands of call sites that were written assuming consistent reads. Opt-in confines it to code someone has reviewed. The cost is that little load moves at first. Replicas stay mostly idle until you profile the hot read paths, such as listing pages, search, and dashboards, and migrate them one at a time.

Transactions must stay on one connection. A read-modify-write sequence or a SELECT ... FOR UPDATE that runs on a replica either fails because replicas are read-only, or reads a stale row and then writes over a newer one. Routers that decide per statement can split a transaction this way without any error: the SELECT goes to the replica and the UPDATE goes to the primary. The router should pin every statement to the primary while a transaction is open.

Routing state is also easy to lose:

  • Thread pools: a thread-local flag does not follow work handed to a pool.
  • Async tasks: a spawned task may start without the request's context.
  • Transaction-mode poolers: these return the server connection to the pool after each transaction, so session-level settings do not persist between transactions.

Keep the flag in your application's request context and pass it explicitly when work moves to another thread or task.

import contextvars
from contextlib import contextmanager

_pinned = contextvars.ContextVar("pinned_to_primary", default=False)
_replica_ok = contextvars.ContextVar("replica_ok", default=False)
_in_txn = contextvars.ContextVar("in_transaction", default=False)

def start_request(method):
    # Middleware: any non-GET request reads its own writes from the primary.
    _pinned.set(method not in ("GET", "HEAD"))

def mark_write():
    # Call on first write so later reads in this request see it.
    _pinned.set(True)

@contextmanager
def replica_ok():
    # Opt-in: the caller declares this read tolerates replication lag.
    token = _replica_ok.set(True)
    try:
        yield
    finally:
        _replica_ok.reset(token)

@contextmanager
def transaction(conn):
    # Pin every statement in the transaction to one primary connection,
    # so SELECT ... FOR UPDATE and the following UPDATE cannot be split.
    token = _in_txn.set(True)
    try:
        with conn:  # e.g. a DB-API connection that commits/rolls back on exit
            yield conn
    finally:
        _in_txn.reset(token)

def choose_db():
    # Router: primary unless the read opted in and nothing pins it.
    if _in_txn.get() or _pinned.get() or not _replica_ok.get():
        return "primary"
    return "replica"

A framework-neutral Python router that defaults to the primary, uses contextvars for a request-scoped pin flag, provides an explicit replica opt-in helper, keeps transactions on the primary, and shows how to carry the context into a thread pool.

How should background jobs handle replication lag?

A job queue is the most common way the read-your-writes flag gets lost. The request commits a new order, enqueues "send confirmation for order 812", and returns. A worker picks the job up a few milliseconds later, routes its SELECT to a replica that has not yet replayed the commit, and gets zero rows. Depending on the code, the job either fails with a not-found error or, worse, treats the missing row as "nothing to do" and exits successfully. The worker runs in a separate process with no request context, so whatever sticky-primary state the web tier kept never reaches it.

There are three workable options:

  • Carry the LSN in the payload. After the transaction commits, read pg_current_wal_lsn() on the primary and add it to the job, for example {"order_id": 812, "min_lsn": "0/3A2F1C8"}. Before the worker reads from a replica, it checks pg_last_wal_replay_lsn() >= '0/3A2F1C8'::pg_lsn on that replica. If the check fails, the worker uses the primary or waits briefly. This approach is precise, and the job stays correct even if it is retried hours later.
  • Force the primary for creation-follow-up jobs. Some jobs exist only to act on an entity the enqueuing request just wrote. Mark those job types as primary-only. This is simple and easy to audit, at the cost of extra primary load.
  • Retry on not-found, with limits. Cap the number of attempts and use backoff. Fail loudly when the cap is reached rather than discarding the job. This handles missing rows but does nothing for rows that exist with stale column values.
import json
import psycopg2

primary = psycopg2.connect("host=db-primary.internal dbname=shop user=app")
replica = psycopg2.connect("host=db-replica-1.internal dbname=shop user=app")

def create_order_and_enqueue(queue, customer_id):
    with primary, primary.cursor() as cur:  # commits when the block exits
        cur.execute("INSERT INTO orders (customer_id) VALUES (%s) RETURNING id", (customer_id,))
        order_id = cur.fetchone()[0]
    # Read the LSN only after the commit, so it covers the new order row.
    with primary, primary.cursor() as cur:
        cur.execute("SELECT pg_current_wal_lsn()::text")
        min_lsn = cur.fetchone()[0]
    # The payload carries the routing state, because the worker has no request context.
    queue.put(json.dumps({"order_id": order_id, "min_lsn": min_lsn}))

def send_confirmation(raw_job):
    job = json.loads(raw_job)
    with replica, replica.cursor() as cur:
        # A NULL replay LSN means this server is not replaying WAL. Treat that as not caught up.
        cur.execute("SELECT coalesce(pg_last_wal_replay_lsn() >= %s::pg_lsn, false)",
                    (job["min_lsn"],))
        caught_up = cur.fetchone()[0]
    # Replay only moves forward, so a replica that passed the check stays safe to read.
    conn = replica if caught_up else primary
    with conn, conn.cursor() as cur:
        cur.execute("SELECT id, email, total FROM orders WHERE id = %s", (job["order_id"],))
        row = cur.fetchone()
    if row is None:
        # Never treat a missing row as nothing to do. Raise so the queue can
        # retry with a capped attempt count and dead-letter the job when the cap is hit.
        raise LookupError(f"order {job['order_id']} not found")
    send_email(row)  # application-specific

The enqueuing code records the primary's WAL LSN after commit and puts it in the job payload. The worker reads from the replica only if the replica has replayed past that LSN. Otherwise it falls back to the primary, and it raises on a missing row instead of exiting silently.

How do you measure replica lag and route around unhealthy replicas?

Measure lag from the primary's point of view

The common check, now() - pg_last_xact_replay_timestamp() run on the replica, reports the time since the last replayed commit. When the primary is idle, there is nothing to replay, so the value climbs even though the replica is fully caught up. You get false alarms at night and learn to ignore the alert.

Compare WAL positions instead. On the primary, pg_stat_replication shows each standby's replay_lsn and replay_lag. pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) gives the lag in bytes. Alert on both bytes and time. Byte lag shows how much work is queued, and time lag shows how stale reads actually are.

Route around lagging replicas

Remove a replica from the read pool when its lag crosses a threshold. Add it back only after it stays below a lower threshold for a while, so it doesn't flap in and out. Decide in advance what happens when every replica is excluded. Usually you shed staleness-tolerant reads or serve bounded-stale cached data. Don't fail everything over to the primary. Replicas often lag because the system is already under heavy load, and moving all reads to the primary can take it down too.

Lag also comes with a tuning tradeoff. Queries on a standby get cancelled when they conflict with WAL replay. Turning on hot_standby_feedback prevents those cancellations, but it holds back vacuum on the primary and causes table bloat. Pick one cost deliberately and monitor it.

Test with paused replay

On a staging replica, call pg_wal_replay_pause(), run your write-then-read flows, then call pg_wal_replay_resume(). Every stale-read path shows up within minutes and is easy to reproduce. In production the same bugs appear only during lag spikes, which are intermittent and hard to trace.

How can disciplined replica routing save engineering time?

Replica routing adds work up front. The disciplined version described here pays that back mainly by preventing a class of bugs that is expensive to debug.

  • Fewer intermittent bug hunts. Stale-read bugs appear only under lag, so they reach production as "sometimes my change doesn't save" reports that nobody can reproduce on demand. Defaulting to the primary and requiring opt-in keeps those bugs out of most of the codebase. Paused-replay tests in staging turn the remaining ones into deterministic failures you find before release.
  • One place to change routing. When the decision lives in a single router, fed by request context and explicit flags, changing policy means changing one function. Examples are adding a replica, excluding a lagging one, or moving a path back to the primary. Without a router, the same changes mean editing scattered connection choices.
  • Reviews stay small. An opt-in helper such as replica_ok() marks exactly where staleness is allowed. A reviewer checks those call sites and the stated tolerance, not every query in the change.
  • Job failures explain themselves. Carrying the LSN in job payloads and raising on missing rows replaces silent no-op jobs with retries and dead-lettered jobs, which show up in queue metrics instead of in customer reports.
  • Delaying bigger migrations. Moving well-defined staleness-tolerant reads off the primary can postpone a much larger project, such as sharding or a change of datastore, while you keep a single-primary monolith.
  • Reliable alerts. LSN-based lag measurement avoids the idle-primary false alarms that train on-call engineers to ignore lag alerts.

An illustrative estimate, using assumed inputs only: assume a team sees two hard-to-reproduce stale-read incidents per quarter after adopting verb-based routing. Assume each takes about three engineer-days to investigate, fix and verify. That comes to about six engineer-days per quarter. If the team instead spends an assumed four engineer-days once on a primary-default router, an opt-in helper and a paused-replay staging test, and that setup prevents most of those incidents, the cost is recovered within the first quarter or so. Substitute your own incident rate and investigation time. The comparison only holds if the incidents you are counting really come from routing.

What are the alternatives to application-level replica routing?

Application-level routing is one option. These alternatives move the cost elsewhere.

Synchronous replication with synchronous_commit = remote_apply. A commit returns only after the standbys listed in synchronous_standby_names have applied it, so a read on those standbys sees the write. The cost is on every write: each commit waits a network round trip plus replay time on the slowest required standby. Primary availability also becomes tied to replica health. If a required standby stalls or disconnects, commits on the primary block until it recovers or you change the configuration. Quorum settings reduce this risk but do not remove it.

Scaling the primary vertically. This adds no consistency bugs and requires no code changes. It works until you reach the largest instance you can buy, and failover takes longer as the instance grows.

Caching hot reads. A cache with explicit invalidation can offload more traffic than a replica, but it adds its own staleness model to maintain.

No replicas on user-facing paths. Use replicas only for analytics, exports and reporting, where some lag is acceptable.

Approach Consistency guarantee Primary load offloaded Implementation effort Failure mode
Verb-based routing None High Low Silent stale reads after writes
Sticky windows Read-your-writes if lag stays below the window Medium Low–medium Stale reads when lag exceeds the window
LSN routing Read-your-writes Medium–high High Falls back to the primary under lag, increasing its load
Opt-in replica reads Stale only where explicitly chosen Low–medium Medium (per call site) Under-adoption leaves the primary overloaded
Synchronous replication Fresh on synchronous standbys High Low (config) Write latency; primary stalls if a standby fails

Choose deliberately. Default every read to the primary. Allow staleness only on paths where someone has written down how much lag is acceptable and what the user sees when lag exceeds it.

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 replication lag, 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.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

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.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • 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.