Why can every Postgres read replica query return stale data?

In a monolith, a read replica is a second Postgres server that holds a copy of the primary's data and accepts read-only queries. The application still has one codebase and one logical database, but it now has two places to read from, and they do not always agree.

Postgres streaming replication works by shipping the primary's write-ahead log (WAL) to the standby, which replays it to reconstruct the same data pages. In the default asynchronous mode, the primary commits a transaction and returns success to the client without waiting for any standby. The replica learns about that commit some time later. That gap is replication lag. It is how the system normally runs, not a fault, so any query sent to a replica can return data that is older than what the primary holds.

Lag has four common sources:

  • Network delay. WAL has to cross the network before the replica can apply it. Cross-zone or cross-region links add more delay than a local link.
  • WAL replay contention. Replay runs largely in a single process on the standby. Heavy I/O or CPU pressure from read traffic slows it down.
  • Long-running queries on the replica. If replaying a change would conflict with a running query, Postgres can pause replay, governed by max_standby_streaming_delay, and lag grows while it waits.
  • Write bursts on the primary. Bulk updates, backfills and index builds can generate WAL faster than the replica can replay it.

Because lag is always present, adding a replica is a consistency decision first. Each read you move to it accepts staleness, and capacity is the benefit you get in exchange.

Who should add read replicas, and when should you avoid them?

Replicas pay off when a large share of reads can tolerate seconds of staleness and you can name those reads in code. Reports, search pages, analytics dashboards and admin listings usually qualify, because users do not expect them to reflect a write made a moment ago. Checkout totals, permission checks and "show me what I just saved" screens do not.

Workload Replica fit Why
Reporting and dashboards Good Stale-tolerant, often scan-heavy, easy to isolate
Search and listing pages Good Users rarely notice seconds of lag
Write-bound (ingest, queues, counters) Poor Replicas replay the same WAL, so they add no write capacity
Slow queries or missing indexes Poor The same bad plan runs on the replica; you have only copied the problem
Teams without routing conventions Poor Ad hoc routing produces intermittent stale-read bugs that are hard to reproduce

Illustrative estimate: classification caps the gain

These inputs are assumptions, not measurements. Suppose 80% of queries are reads and the rest are writes. After auditing, half of the reads, 40% of all traffic, are judged replica-safe. The primary still serves the 20% writes plus the 40% unsafe reads, which is 60% of queries. If every query cost the same, the primary's ceiling would rise by about 1 / 0.6 ≈ 1.67×. Adding more replicas does not change that figure. Only classifying more reads as safe raises it.

Fix slow queries first. A missing index or a bloated table caps throughput far more than the lack of a second server.

Where should query routing live: data-access layer, ORM or proxy?

You can put the routing decision in three places.

Data-access layer. Repository or query functions take an explicit target, such as primary or replica, and pick a connection pool. The decision sits next to the code that knows why the read happens.

ORM multi-database support. Many ORMs let you register several connections and attach a router hook. The hook usually sees the model and operation type but not the business intent. Defaults such as "all SELECTs go to the replica" are easy to enable and hard to audit.

SQL-aware proxy or pooler. The proxy parses each statement and sends reads to replicas. The application does not change.

          +----------------------+
          |   Monolith request   |
          |  (user, txn, writes) |
          +----------+-----------+
                     |
               +-----v-----+
               |  Router   |
               +-----+-----+
          writes /   |   \ safe reads
                /    |    \
      +--------v+ +--v-----+ +--------+
      | Primary | |Replica1| |Replica2|
      +---------+ +--------+ +--------+
Location Sees request context Sees transaction scope Change cost
Data-access layer Yes Yes Moderate
ORM router Partial Usually Low
SQL proxy No Only its own connection Lowest

A proxy sees only SELECT ... FROM orders. It cannot tell that the same user inserted that order 40 ms ago on another pooled connection. It also cannot tell that the request requires read-your-writes. Both cases produce an identical statement, so the proxy routes both to a replica and returns stale data.

The routing decision depends on who is asking and what they just wrote. Only the application carries that context, so the router belongs in the application.

+----------------------------- Monolith process ---------------------------+
| Request: user 42 inserted an order 40 ms ago; needs read-your-writes     |
|                                    |                                     |
|                                    v                                     |
|  +--------------------------------------------------------------------+  |
|  | Router in data-access layer: knows caller, txn scope, recent writes|  |
|  +--------------+-------------------------------------+---------------+  |
+-----------------|-------------------------------------|------------------+
                  | writes, read-your-writes reads      | safe reads
                  v                                     v
            +-----------+                     +---------+---------+
            |  Primary  |                     |                   |
            +-----+-----+                     v                   v
                  |                     +-----------+       +-----------+
                  +-------------------->| Replica 1 |       | Replica 2 |
                    async replication   +-----------+       +-----------+

+------------ Same read through a SQL proxy (no request context) ----------+
| SELECT ... FROM orders WHERE id = $1  -->  parsed as read  -->  replica  |
| Cannot see: caller, prior insert on another connection, read-your-writes |
| Result: the replica may lag behind the insert, so the read is stale      |
+--------------------------------------------------------------------------+

Routing in the application's data-access layer uses the caller's identity, transaction scope and recent writes to send consistency-sensitive reads to the primary, while a SQL proxy sees only the statement text and can send the same read to a lagging replica.

How do you classify reads so replicas are opt-in?

Every query goes to the primary unless the calling code explicitly asks for a replica. A forgotten annotation then costs some primary capacity, not correctness, so the common mistake is the cheap one.

In the data-access layer, put the opt-in at the call site, not in a global config. Two common forms are a replica_ok(max_staleness=...) context flag around a block of reads, or a @replica_safe annotation on a repository method. The router checks three conditions before using a replica:

  • The flag is present. The caller has accepted stale data and stated how stale.
  • No transaction is open on the primary connection. Reads inside a write transaction must see that transaction's uncommitted rows, and a replica cannot see them.
  • The result is not used to decide a write. Checking a balance before a debit, or checking a slug before an insert, must go to the primary. If those reads see stale data, the code makes the wrong write with no error at all.

The third condition is a code-review rule more than something the router can enforce, so the flag should be easy to spot in a diff.

Explicit intent also speeds up review and debugging. A reviewer sees replica_ok(max_staleness=5s) on a dashboard query and can judge whether five seconds is acceptable for that query, instead of guessing what the routing layer does. When someone reports "my update didn't show up," you can search for the flags on that request's code path and narrow down the cause in minutes. Without explicit flags, you would have to reconstruct proxy behavior from logs.

import contextvars
from contextlib import contextmanager
from datetime import timedelta

# None means the caller has not opted in, so reads go to the primary.
_replica_ok = contextvars.ContextVar("replica_ok", default=None)

@contextmanager
def replica_ok(max_staleness: timedelta):
    """Caller states in the diff that reads in this block may be this stale."""
    token = _replica_ok.set(max_staleness)
    try:
        yield
    finally:
        _replica_ok.reset(token)

class Router:
    # primary/replica are thin wrappers you own: in_transaction() -> bool,
    # lag() -> timedelta (e.g. measured from a heartbeat table), fetch_one(sql).
    def __init__(self, primary, replica):
        self.primary, self.replica = primary, replica

    def for_read(self):
        max_staleness = _replica_ok.get()
        if max_staleness is None:
            return self.primary  # default path: no flag, no stale reads
        if self.primary.in_transaction():
            return self.primary  # must see this transaction's uncommitted rows
        if self.replica.lag() > max_staleness:
            return self.primary  # replica is further behind than the caller accepted
        return self.replica

def dashboard_order_count(router):
    # Opt-in is visible at the call site: a reviewer can judge whether 5s is fine here.
    with replica_ok(max_staleness=timedelta(seconds=5)):
        return router.for_read().fetch_one(
            "SELECT count(*) FROM orders WHERE created_at > now() - interval '1 day'")

# No flag on reads that decide a write (balance before debit, slug before insert):
# those stay on the primary. The router cannot check this rule, so code review must.

A minimal data-access-layer sketch where reads default to the primary and only use a replica when the call site sets a replica_ok(max_staleness) flag, no primary transaction is open, and the replica's lag is within the stated bound.

How do you implement read-your-writes with stickiness or LSN tokens?

The simplest approach is stickiness. After a session writes, set a marker (a cookie or a cache key holding a timestamp) and route all of that session's reads to the primary until the marker expires. The window length sets the trade-off. If the window is shorter than your worst-case lag, users can still see stale data. If it is longer, more traffic stays on the primary, so a write-heavy feature can quietly undo the offload you added replicas for. Stickiness also only covers the session that wrote. A colleague on another device still reads from a replica.

LSN tokens replace the time guess with a position in the WAL. After the commit, run SELECT pg_current_wal_lsn() on the primary and return the value as a token. A later read sends the token back. The router then runs SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn on a candidate replica. If the result is true, the read goes to that replica. If not, the router either waits briefly and retries or falls back to the primary. The guarantee is exact, but it costs more:

  • The token has to travel through API responses, message headers and client storage.
  • Each read pays for an extra round trip or a wait.
Approach Staleness guarantee Added cost
Time-window stickiness Approximate, same session only Extra primary load
LSN token Exact, wherever the token travels Token plumbing, per-read check
synchronous_commit = remote_apply Replica has applied the commit before the client gets an acknowledgement Higher commit latency; writes block if the synchronous standby is unavailable

remote_apply takes effect only for standbys listed in synchronous_standby_names.

import time

# primary/replicas: psycopg 3 connections opened with autocommit=True.
READ_SQL = "SELECT id, status FROM orders WHERE id = %s"

def update_order(primary, order_id, status):
    with primary.transaction():
        primary.execute("UPDATE orders SET status = %s WHERE id = %s", (status, order_id))
    # Read the LSN after COMMIT returns. The current WAL position is at or past
    # our commit record, so a replica that has replayed this far sees the write.
    lsn = primary.execute("SELECT pg_current_wal_lsn()::text").fetchone()[0]
    return lsn  # hand back to the caller, e.g. in a response header

def caught_up(replica, token):
    # NULL (no replay position yet) counts as not caught up.
    row = replica.execute(
        "SELECT COALESCE(pg_last_wal_replay_lsn() >= %s::pg_lsn, false)", (token,)
    ).fetchone()
    return row[0]

def read_order(primary, replicas, order_id, token=None, max_wait_s=0.05):
    if token is None:  # no recent write to honour, so any replica will do
        return replicas[0].execute(READ_SQL, (order_id,)).fetchone()
    deadline = time.monotonic() + max_wait_s
    while True:
        for replica in replicas:
            # Replay only moves forward, so a read on the same connection
            # right after a passing check still sees the write.
            if caught_up(replica, token):
                return replica.execute(READ_SQL, (order_id,)).fetchone()
        if time.monotonic() >= deadline:
            break
        time.sleep(0.01)  # short wait for replay before giving up
    # No replica reached the token in time, so fall back to the primary.
    return primary.execute(READ_SQL, (order_id,)).fetchone()

LSN-token routing: capture pg_current_wal_lsn() after commit, then send a read to a replica only after its pg_last_wal_replay_lsn() reaches the token, falling back to the primary after a bounded wait.

How should you measure replication lag and route around unhealthy replicas?

Lag is a distance in the WAL stream, so measure it in bytes from the primary first. On the primary, pg_stat_replication has one row per connected standby. Compare pg_current_wal_lsn() against each row's replay_lsn with pg_wal_lsn_diff() to get bytes not yet replayed. The write_lag, flush_lag and replay_lag columns add time-based estimates. A standby missing from this view is disconnected, which is worse than lagging.

On the replica, now() - pg_last_xact_replay_timestamp() is a popular check, but it measures time since the last replayed transaction, not distance behind the primary. If the primary commits nothing for five minutes, a fully caught-up replica reports five minutes of lag. To avoid false alarms, pair it with a byte check. If pg_last_wal_receive_lsn() equals pg_last_wal_replay_lsn(), the replica has replayed everything it has received, so a large timestamp gap means the primary is idle, not that the replica is behind.

Thresholds and rotation

Have the router poll lag every few seconds and use two thresholds:

  • Eject threshold: a replica above it is removed from rotation.
  • Lower re-admit threshold: a replica rejoins only after staying below it for several consecutive checks.

Keeping the two thresholds apart prevents a replica near the limit from repeatedly dropping out and rejoining. Set the eject threshold from the staleness your opted-in reads can tolerate, not from whatever lag looks normal.

Falling back without overloading the primary

When most of the replica fleet is ejected, sending all replica traffic to the primary can overload it while it is already busy. Lag often rises because the primary is under heavy write load. Cap fallback traffic with a rate limit or a concurrency budget for replica-eligible reads. Shed reads that can tolerate failure, such as reports, exports and recommendations. Serve requests that have read-your-writes tokens first.

-- On the PRIMARY: bytes and time each connected standby is behind.
-- A standby that should exist but has no row here is disconnected.
SELECT
  application_name,
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication
ORDER BY replay_lag_bytes DESC NULLS FIRST;

-- On a REPLICA: separate an idle primary from a replica that is really behind.
-- If everything received has been replayed, a large timestamp gap
-- means the primary has not committed recently, not that the replica is lagging.
SELECT
  pg_is_in_recovery() AS is_replica,
  pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())
    AS unreplayed_bytes,
  CASE
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN interval '0'
    ELSE now() - pg_last_xact_replay_timestamp()
  END AS effective_lag,
  now() - pg_last_xact_replay_timestamp() AS raw_timestamp_gap;

Monitoring queries for replication lag: byte and time lag per standby from pg_stat_replication on the primary, and a replica-side check that reports zero lag when all received WAL has been replayed, so an idle primary does not look like lag.

What replica failure modes and testing practices should you plan for?

Recovery conflicts and query cancellations

A replica can cancel a query even when it is otherwise healthy. When VACUUM on the primary removes row versions that a long-running replica query still needs, the replica has two options: delay WAL replay, which increases lag, or cancel the query with a "conflict with recovery" error. max_standby_streaming_delay sets how long replay waits before it cancels. pg_stat_database_conflicts on the replica counts these cancellations by cause.

Enabling hot_standby_feedback makes the replica report its oldest snapshot to the primary, so vacuum keeps the rows the replica still needs. This reduces snapshot conflicts, but a slow analytical query on the replica now holds back cleanup on the primary. The result is table bloat and delayed vacuum on the node that handles your writes. Lock conflicts from DDL still cancel replica queries even with feedback enabled.

Debugging and testing

Replicas make some bugs non-deterministic. The same query can return different rows depending on which node served it. Tag every query with the node that handled it, using a log field, a trace attribute or a comment, so a bug report can be traced to a specific node.

In staging, inject artificial lag. recovery_min_apply_delay works for this. Lag exposes read-your-writes gaps before production does. Alert on the rate of primary fallbacks. A sudden rise usually means a replica is unhealthy or a classification change routed reads incorrectly.

The routing layer is real code that needs ongoing maintenance. In return, consistency decisions live in one place, and engineers do not need to reason about lag at every call site.

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.