PostgreSQL Read Replica Lag: Routing Reads and Read-Your-Writes

How streaming replication works and where lag comes from

A read replica is a hot standby: a second PostgreSQL server that continuously applies the primary's write-ahead log (WAL) and accepts read-only queries. With asynchronous streaming replication, the default, the primary commits a transaction once its WAL is durable locally. It does not wait for any standby. Shipping that WAL happens afterward, in the background.

Copy            writes                      reads
  App ─────────────┐          ┌───────────────── App
                   ▼          ▼
  ┌──────────────────────┐   ┌───────────────────────────────┐
  │ PRIMARY              │   │ STANDBY                       │
  │  backends ─► WAL     │   │  WAL receiver ─► write ─► flush│
  │              │       │   │                         │      │
  │        WAL sender ───┼──►│                    startup     │
  │                      │net│                    (replay) ─► │ visible
  └──────────────────────┘   └───────────────────────────────┘

A change travels through several stages before a query on the replica can see it:

1. Send. The WAL sender reads new WAL and streams it over the network. A slow link, or a burst of writes such as a bulk load or index build, causes a backlog here.
2. Write. The WAL receiver hands the bytes to the standby's operating system.
3. Flush. The receiver forces the data to the standby's disk. Slow storage delays this step.
4. Replay. The startup process applies the WAL to data pages. This is the only point where changes become visible to readers. Replay is single-threaded, so it can fall behind a primary that is writing from many sessions at once, even when the network and disk are keeping up.

Because each stage can stall independently, a replica can have its WAL safely on disk and still be serving data that is several seconds old. "Lag" is therefore several distinct measurements, and only replay lag tells you what your users will see.

For this reason, adding a replica is a consistency decision before it is a scaling decision. Each read you route to the replica implicitly accepts some amount of staleness, and you need to decide how much.

Copy┌────────────────────────────────────────────────────────────────────┐
│ Application: routes writes and reads                               │
└───────┬─────────────────────────────────────┬──────────────────────┘
        │ writes                              │ reads
        ▼                                     ▼
┌─────────────────────────────┐       ┌─────────────────────────────┐
│ PRIMARY                     │       │ STANDBY (hot standby)       │
│                             │       │                             │
│ client backends             │       │                             │
│   │ generate WAL            │       │                             │
│   ▼                         │       │                             │
│ WAL flushed locally         │       │                             │
│   │ COMMIT returns here     │       │                             │
│   ▼                         │       │                             │
│ WAL sender ─────────────────├─(1)──►│ WAL receiver                │
│                             │ send  │   │ (2) write: standby OS   │
│                             │       │   │ (3) flush: standby disk │
│                             │       │   ▼                         │
│                             │       │ startup process             │
│                             │       │   │ (4) replay: 1 thread    │
│                             │       │   ▼                         │
│                             │       │ pages updated: now visible  │
└─────────────────────────────┘       └─────────────────────────────┘

Per-stage progress in pg_stat_replication (queried on the primary):
(1) sent_lsn   (2) write_lsn, write_lag   (3) flush_lsn, flush_lag
(4) replay_lsn, replay_lag: only this stage changes what reads see

With asynchronous streaming replication the primary commits before the standby receives anything, and WAL then passes through send, write, flush and replay, each of which can fall behind independently, so a replica has several kinds of lag.

Measuring postgresql read replica lag in terms your application understands

Staleness has to be measured before it can be bounded, and PostgreSQL shows it from both ends of the connection.

On the primary, pg_stat_replication has one row per connected standby. The sent_lsn, write_lsn, flush_lsn and replay_lsn columns show how far WAL has travelled: sent over the network, written by the standby, flushed to its disk, and applied so queries can see it. The write_lag, flush_lag and replay_lag columns report the same progress as time intervals. For read routing, replay is the stage that matters, because a row is not visible on the replica until its WAL has been replayed. To get byte lag, pass the primary's current WAL position and replay_lsn to pg_wal_lsn_diff().

On the standby, pg_last_wal_replay_lsn() returns the replay position. pg_last_xact_replay_timestamp() returns the commit time of the last replayed transaction. Subtracting that timestamp from now() is the usual way to compute "seconds behind."

Time lag is based on the timestamps of past commits, not on the work still queued. If the primary receives no writes, the last commit time stops moving and the measured lag grows, even though the replica is fully caught up. If one bulk transaction is replaying, the timestamp and the lag samples still describe earlier, smaller commits, so the lag looks small while gigabytes of WAL are still queued.

Alerting on either number alone will produce false alarms in quiet periods and miss real backlogs. Alert only when both are elevated, and treat time lag as meaningful only when byte lag is nonzero.

Copy-- On the PRIMARY: one row per connected standby.
-- Byte lag is measured against the primary's current WAL position.
SELECT application_name,
       client_addr,
       state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS unsent_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS unreplayed_bytes,
       write_lag,
       flush_lag,
       replay_lag  -- the stage that controls what readers can see
FROM pg_stat_replication
ORDER BY unreplayed_bytes DESC;

-- On the STANDBY: the same picture from the replica's side.
-- unreplayed_bytes covers only WAL already received, not WAL still on the primary.
-- seconds_behind reports 0 when everything received has been replayed,
-- so an idle primary does not make a caught-up replica look stale.
SELECT pg_is_in_recovery()                     AS is_standby,
       pg_last_wal_receive_lsn()               AS received_lsn,
       pg_last_wal_replay_lsn()                AS replayed_lsn,
       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 0
         ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
       END                                     AS seconds_behind,
       pg_last_xact_replay_timestamp()         AS last_replayed_commit;

Queries that report replica lag as both bytes and time, from the primary through pg_stat_replication and from the standby through the replay functions, so the two signals can be read together.

Who should use replicas, and when to avoid them

Replicas earn their keep in two situations. The first is read offload: a workload where reads far outnumber writes and a large share of those reads, such as catalog pages, dashboards and search listings, can tolerate data that is a few seconds old. The second is isolation. Reporting and analytical queries that scan large tables compete with transactional traffic for CPU, I/O and buffer cache. Moving them to a dedicated standby keeps them from evicting the primary's hot pages.

Replicas do nothing for write throughput, because every standby replays the full WAL stream. They also do not relieve connection pressure on the primary if most of that pressure comes from writers. If most of your reads must be current or follow a write closely, you will route nearly everything back to the primary anyway, and you will still pay the routing complexity. Small teams should also count the operational cost. A replica adds lag monitoring, failover procedures, and schema migrations that must be checked against long-running standby queries.

Before adding a replica, or alongside one, consider these options:

Routing reads by staleness tolerance, not by statement type

The rule "every SELECT goes to a replica" treats SQL syntax as a proxy for consistency requirements, and it isn't one. A better starting point is to classify each read by how much staleness it can tolerate:

• Must be current: a balance check before a debit, a permissions lookup after a role change, the page a user lands on right after saving.
• A few seconds is acceptable: feeds, search results, listing pages.
• Minutes is acceptable: reports, exports, analytics dashboards.

Where the split lives

A proxy sees statements, not intent. It has two options. It can send everything ambiguous to the primary, which erodes the benefit of having replicas. Or it can route by statement type, which silently serves stale data to the reads that must be current.

Two cases break correctness regardless of where routing happens:

• Splitting inside a transaction. A transaction's reads must see its own uncommitted writes, and a replica has none of them.
• A SELECT that calls a function with side effects. If the function inserts, updates or takes advisory locks, it fails on a read-only standby, or it runs in the wrong place.

The example that follows keeps two pools and requires callers to pass a staleness tolerance. Anything other than an explicit "minutes" or "seconds" defaults to the primary.

Copyimport enum
from psycopg2.pool import ThreadedConnectionPool

class Staleness(enum.Enum):
    CURRENT = "current"   # balance before debit, permissions after a role change
    SECONDS = "seconds"   # feeds, search results, listing pages
    MINUTES = "minutes"   # reports, exports, dashboards

# Two pools, chosen explicitly by the caller. Placeholder credentials only.
primary = ThreadedConnectionPool(2, 20, "host=db-primary.internal dbname=app user=app_rw password=change-me")
replica = ThreadedConnectionPool(2, 20, "host=db-replica.internal dbname=app user=app_ro password=change-me")

def _pool_for(staleness):
    # Only an explicit tolerance may leave the primary. CURRENT, None or anything
    # unexpected falls back to the primary, so a mistake costs load, not correctness.
    if staleness in (Staleness.SECONDS, Staleness.MINUTES):
        return replica
    return primary

def read(sql, params=None, *, staleness):
    # staleness is keyword-only with no default: every call site must declare it.
    # Do not use this for SELECTs that call functions with side effects, or for
    # reads inside a transaction; those belong on the primary connection doing the writes.
    pool = _pool_for(staleness)
    conn = pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params)
            rows = cur.fetchall()
        conn.rollback()  # close the implicit transaction before returning the connection
        return rows
    finally:
        pool.putconn(conn)

# Usage
balance = read("SELECT balance FROM accounts WHERE id = %s", (42,), staleness=Staleness.CURRENT)
feed = read("SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50", staleness=Staleness.SECONDS)

Application-side read routing in Python with psycopg2: separate primary and replica pools, a required staleness argument at every call site, and a fallback to the primary for anything not explicitly marked as tolerant of lag.

Implementing read-your-writes and monotonic reads

Read-your-writes is a promise to one user: after they change something, their next read shows the change. A replica cannot keep that promise by itself, so the application needs a mechanism. There are two common ones.

Time-window pinning. After a write, route that session to the primary for a fixed window, such as a few seconds longer than your lag budget. It needs no database cooperation. The weakness is that it guesses: if lag ever exceeds the window, the user sees stale data.

LSN tracking. After commit, run SELECT pg_current_wal_lsn() on the primary and store the result in the session or a cookie. Before a routed read, query the chosen replica with SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn. In pseudocode: if session.lsn is null or replica.replayed(session.lsn) then use replica else use primary. The value is captured just after the commit, so it is slightly conservative, which is safe. This checks replay progress directly instead of relying on a timer.

Monotonic reads is a separate problem. If one user's requests are balanced across replicas with different lag, a row can appear on one request and vanish on the next. Selecting a sticky replica per session, for example by hashing the session ID, prevents data from moving backward.

Every option sends some reads back to the primary. Size the primary with that traffic in mind.

Copyimport hashlib

# Assumes psycopg2-style connections: primary and replicas are open connection objects.

def write_and_record_lsn(primary, session, sql, params):
    with primary.cursor() as cur:
        cur.execute(sql, params)
    primary.commit()
    # Captured just after commit, so it is at or past our commit record (safe, slightly conservative).
    with primary.cursor() as cur:
        cur.execute("SELECT pg_current_wal_lsn()::text")
        session["lsn"] = cur.fetchone()[0]

def sticky_replica(replicas, session_id):
    # Same session always maps to the same replica, so reads never move backward.
    h = int(hashlib.sha256(session_id.encode()).hexdigest(), 16)
    return replicas[h % len(replicas)]

def replica_has_replayed(replica, lsn):
    with replica.cursor() as cur:
        # NULL (e.g. not in recovery) counts as not caught up.
        cur.execute(
            "SELECT COALESCE(pg_last_wal_replay_lsn() >= %s::pg_lsn, false)",
            (lsn,),
        )
        return cur.fetchone()[0]

def connection_for_read(primary, replicas, session):
    replica = sticky_replica(replicas, session["id"])
    lsn = session.get("lsn")
    if lsn is None or replica_has_replayed(replica, lsn):
        return replica
    # Replica has not replayed this user's last write yet: fall back to the primary.
    return primary

Python sketch combining LSN-based read-your-writes (record pg_current_wal_lsn() after commit, compare against pg_last_wal_replay_lsn() on the replica) with hash-based sticky replica selection for monotonic reads.

Lag budgets, health checks and hot standby query conflicts

Evict a replica when its lag exceeds the budget for the traffic it serves. Evictions can cascade. When one replica leaves, the others absorb its reads, their replay competes with more query load, and their lag grows. If evicted traffic falls back to the primary, the primary also generates WAL more slowly relative to demand and serves more reads, so the remaining replicas fall further behind and get evicted too. Four guards help:

• Hysteresis: evict at one threshold and readmit only below a lower one. This stops a replica near the limit from flapping in and out of rotation.
• Minimum pool size: never evict below N replicas. Past that point, a stale replica is better than none.
• Shed, don't redirect: reject or degrade staleness-tolerant reads instead of sending them to the primary.
• Cap fallback: limit how many reads per second may reach the primary, so it is protected first.

Hot standby conflicts are a separate failure. Replay may need to remove row versions that a long query on the standby can still see. When that happens, PostgreSQL cancels the query after a grace period.

A typical analytics replica sets hot_standby_feedback = on with a modest max_standby_streaming_delay. The lag budget must then allow for that delay.

Synchronous replication as an alternative, and the cost in engineering time

If the application needs read-after-write on a replica rather than tolerating lag, PostgreSQL can enforce it. synchronous_standby_names lists which standbys count as synchronous, for example FIRST 1 (replica_a, replica_b) or ANY 1 (...). synchronous_commit sets how long each commit waits. It can be set per session or per transaction, so only critical writes need to pay the cost.

Only remote_apply makes a write visible on the standby when the commit returns. The cost is at least one network round trip plus replay time on every commit. If no listed standby is available, commits wait indefinitely. A replica failure therefore becomes a write outage unless you have enough candidates or someone intervenes.

The asynchronous design also costs engineering time. Staleness classification and routing are upfront work, LSN tracking adds plumbing, and replicas add operational load. That cost is paid once and is visible. Without it, you pay later in intermittent stale-read bugs that are hard to reproduce, noisy lag alerts with no defined threshold, and capacity plans built on offload you never get.

Illustrative estimate (assumed numbers, not a benchmark). Effective offload = R × (1 − F), where R is the share of reads that are eligible for a replica and F is the share of those eligible reads that fall back to the primary inside a read-your-writes window. With R = 0.8 and F = 0.25, offload is 0.8 × 0.75 = 0.6. That means 60% of reads leave the primary, not 80%.

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 postgresql read replica 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.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• 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.