Gate Reads on Replica Lag

Measure replay lag on the replica, route to the primary above a threshold, and alert before stale reads become visible to users.

~30 minpostgresqlreplicationreliability

Task

Prevent stale reads by checking replica lag before routing, and fall back to the primary when the replica is too far behind.

When to use this

A user edits their profile and is redirected to a read page served by a replica that has not replayed the change, so the edit appears to have vanished.

Prerequisites

PostgreSQL 13 or newer with streaming replication
A read-routing layer that can choose between primary and replica pools
psql access to the replica for the verification step

Steps

1. Measure replay lag in milliseconds and whether the replica has caught up.

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag_ms,
       pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS caught_up;

Expected output: lag_ms between roughly 5 and 50 on a healthy replica, with caught_up true.

2. Alert from the primary on byte lag for every connected standby.

SELECT application_name, state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Expected output: Each standby reports state streaming and lag_bytes well under the alert threshold, for example 16 MB.

3. Route reads with a cached health decision so the probe does not run on every query.

const LAG_SQL = `SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag_ms,
                        pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS caught_up`;

let cached = { at: 0, target: primary };
async function readTarget() {
  if (Date.now() - cached.at < 1000) return cached.target;
  try {
    const { rows } = await replica.query(LAG_SQL);
    const lagMs = Number(rows[0].lag_ms ?? 0);
    const healthy = rows[0].caught_up && lagMs < 2000;
    cached = { at: Date.now(), target: healthy ? replica : primary };
  } catch {
    cached = { at: Date.now(), target: primary };
  }
  return cached.target;
}

Expected output: Reads use the replica while lag stays under two seconds and switch to the primary otherwise.

4. Pause replay on the replica, write on the primary, and confirm the routing decision changes.

psql -h replica -d appdb -c 'SELECT pg_wal_replay_pause();' && psql -h primary -d appdb -c "INSERT INTO audit_log (action) VALUES ('lag-test');" && sleep 3 && node -e "import('./src/read-target.js').then(async (m) => console.log(await m.readTarget() === m.primary ? 'primary' : 'replica'))"

Expected output: primary; after SELECT pg_wal_replay_resume() on the replica, routing returns to the replica within a few seconds.

Troubleshooting

Problem

lag_ms is null

Solution

The replica has not replayed any transaction yet. Treat null as zero lag only when caught_up is true, and keep a fresh replica out of rotation until it catches up.

Problem

Traffic flaps between primary and replica

Solution

Add hysteresis: enter fallback at two seconds of lag, return to the replica only below 500 ms, and cache the decision for about a second.

Problem

The author still sees stale data right after writing

Solution

Lag gating is statistical. Pin the author's session to the primary for a few seconds after a write, or wait until the replica replays the commit LSN.

Limitations

Lag is sampled once per cache window; the replica can fall behind immediately after a healthy reading.
Falling back to the primary reduces read scaling and can overload it during an incident.
It does not provide read-your-writes guarantees; that needs session pinning or LSN waiting.

Replication is asynchronous by default

Streaming replication ships WAL to standbys that replay it a moment later. The gap is usually milliseconds, but it grows during write bursts, network trouble, or a slow standby. Reads routed to a lagging replica can miss a row the user just wrote, which looks like data loss even though nothing was lost.

Measure on the replica, alert on the primary

pg_last_xact_replay_timestamp() tells you when the last replayed transaction was committed on the primary; subtracting it from now() yields time lag. Pair it with pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() to distinguish “caught up” from “idle but healthy”. On the primary, pg_stat_replication gives byte lag per standby and is the better source for alerting, because it works even when application traffic is quiet.

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag_ms,
       pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS caught_up;

Route with hysteresis, not a raw threshold

A single threshold makes routing flap when lag hovers around it. Enter fallback at a clearly unhealthy value, leave it only when the replica is comfortably caught up, and cache the decision for about a second so a burst of requests does not run the probe hundreds of times. Treat a failed probe as unhealthy: if you cannot prove the replica is current, do not serve reads from it.

Handle read-your-writes separately

Lag gating reduces the probability of stale reads; it does not guarantee them away. A user who just saved a form may still hit a replica that has not replayed their commit. Pin that session to the primary for a short window, or capture the commit LSN and wait until the replica reports pg_last_wal_replay_lsn() >= lsn. Cache reads for other users can stay on the replica.

Verify with a paused replay

SELECT pg_wal_replay_pause() on the replica stops replay without breaking the connection, which is exactly the failure you want to simulate. Write on the primary, wait past the cache window, and confirm the routing function returns the primary. Resume with pg_wal_replay_resume() and watch traffic return. Keep this as a runbook drill, not just a one-off test.