Replication and read replicas: why you still see old data right after writing Intermediate

Problem

The primary takes every write, and a read replica takes SELECTs to reduce load. User 42 changes their avatar from avatar-v1.png to avatar-v2.png, sees “Saved”, then hits F5. The profile page reads from the replica and still shows avatar-v1.png; a few seconds later it is correct.

The replica simply has not finished applying the change. How far behind is the replica, how do you measure it, and how do you make sure users always see what they just wrote (read-your-writes)?

What to choose (summary first)

Situation	Choose	Condition
Read-only copy of the whole cluster, failover standby	Streaming replication (hot standby)	Same major version
Copy a few tables, upgrade the major version	Logical replication	You sync schema and sequences yourself
Screens right after the user writes (profile, cart)	Read the primary, or wait for the replica to replay up to an LSN	The app stores the time or LSN of the write
Listings, reports that tolerate lag	Read the replica	Monitor replay_lag
A commit must be visible on the standby immediately	synchronous_commit = remote_apply	Commits get slower by the replay time

Core concepts

Every change is first written to the WAL (write-ahead log). A position in the WAL is an LSN (log sequence number), shaped like 0/3033518, comparable like an increasing number.

	Streaming (physical)	Logical
Sends	Block-level WAL records	Row-level changes
Scope	The whole cluster	Tables in a publication, received via a subscription
Receiver	A read-only standby	A regular database, writable (conflicts are easy)
Version	Must be the same major version	May differ in major version
Not replicated		DDL, sequences, large objects

The read replica here is a hot standby: a server that continuously replays WAL and still accepts read-only queries (hot_standby = on is the default); writes, SELECT ... FOR UPDATE, and nextval() are rejected.

Data on a standby is eventually consistent: a change becomes visible to new snapshots only after its commit record is replayed.

How it works

From commit to the moment the replica sees the data, which milestones does the WAL pass?

Loading diagram…

The four milestones are four LSN columns in pg_stat_replication on the primary. Alongside them:

• write_lag, flush_lag, replay_lag: the time from the primary flushing WAL until it receives the replica’s report that it has written / flushed / replayed.
• For an async replica, replay_lag approximates the delay before a new transaction becomes visible to queries.
• On an idle system that has caught up, the lag columns go back to NULL after a while.

The replica has received the WAL but not replayed it: F5 on the replica still shows the old avatar

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

The app writes avatar-v2.png to the primary; once the commit finishes, the primary returns OK to the user.

Step	Replica	User sees
After commit (LSN 0/3033518)	flush_lsn has arrived, replay_lsn has not	“Saved”
First F5	Snapshot does not yet include the commit record	avatar-v1.png (wrong)
After replay	replay_lsn ≥ 0/3033518	avatar-v2.png

Which milestone synchronous_commit waits for

Streaming replication is async by default. When synchronous_standby_names is non-empty, synchronous_commit decides how far the commit waits for the synchronous standby:

Value	Commit waits for	Visible immediately on the synchronous standby?
off	Not even the local flush	No
local	Local WAL flush	No
remote_write	Standby writes to the OS	No
on (default)	Standby flushes to disk	No: durable but not yet replayed
remote_apply	Standby finishes replay	Yes

When synchronous_standby_names is empty (the default), every value other than off only waits for the local flush. remote_apply also only guarantees visibility on the synchronous standby; other async replicas can still lag.

Operational details

Measuring lag. On the primary:

SELECT application_name, sync_state, replay_lsn, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_gap_bytes
FROM pg_stat_replication;

On the replica: pg_last_wal_replay_lsn() and now() - pg_last_xact_replay_timestamp(); the latter keeps growing when the primary has no new commits.

Conflicts on a hot standby. VACUUM on the primary can remove row versions that a long query on the replica still needs (MVCC). Replay waits for that query up to max_standby_streaming_delay (default 30s), then cancels it with SQLSTATE 40001.

• While waiting, replay stops: lag grows even though the network is fine.
• hot_standby_feedback = on (default off) tells the primary not to VACUUM rows the replica still needs; in exchange the primary keeps dead tuples longer, causing bloat.
• An ACCESS EXCLUSIVE lock (DDL) also causes conflicts; feedback does not cure this kind. Count cancelled queries with pg_stat_database_conflicts on the replica.

Failover is out of scope: promoting an async replica can lose the last commits that had not been sent yet.

Handling read-your-writes in the application

PostgreSQL does not know which request “belongs to a user who just wrote”: this is the application’s responsibility.

Option 1: read the primary for N seconds after a write (store lastWriteAt in the session). Simple, but N must exceed the p99 replay_lag, including when conflicts block replay; if lag exceeds N, the bug returns.

Option 2: compare LSNs. Right after COMMIT, take pg_current_wal_lsn(): the WAL write position, no smaller than the end of the commit record just flushed.

If the transaction ran with synchronous_commit = off, the commit record may not have been written yet: use pg_current_wal_insert_lsn(). When reading, use the replica only if pg_last_wal_replay_lsn() >= LSN.

The read right after a write

✕ Common

await primary.query('UPDATE users SET avatar_url = $2 WHERE id = $1', [42, url]);
return replica.query('SELECT avatar_url FROM users WHERE id = 42'); // may return the old version

✓ Prefer

await primary.query('UPDATE users SET avatar_url = $2 WHERE id = $1', [42, url]);
session.minLsn = (await primary.query('SELECT pg_current_wal_lsn() AS lsn')).rows[0].lsn;
// Subsequent read:
const ok = await replica.query('SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS ok', [session.minLsn]);
const db = ok.rows[0].ok ? replica : primary;

Why

No guessing N. The LSN may include WAL from other transactions: that only means waiting a little longer, never a wrong result.

Production also needs: checking the exact replica that will serve the read, a timeout, and a fallback to the primary.

Real-world example

Listings read the replica, the account page uses LSNs

Product listings read the replica. The “my account” page attaches minLsn to the session after every write; if the replica has not reached that LSN after 200 ms (an assumed threshold, chosen from measured replay_lag), it reads the primary and counts the fallback.

PostgreSQL 17 demo: a primary (port 5434) and a streaming replica (5435) created with pg_basebackup -X stream -C -S replica1 -R:

docker compose -f examples/compose.replication-read-replica.yml up -d --wait
npx --prefix examples ts-node examples/src/replication-read-replica/demo.ts
docker compose -f examples/compose.replication-read-replica.yml down -v

Natural lag on a single machine is tiny; the demo creates artificial lag with recovery_min_apply_delay = '3s' on the replica: WAL is received and flushed immediately, the commit record is replayed 3 seconds later. Output of one run (LSNs and ms values change every run):

Primary commit avatar-v2.png, pg_current_wal_lsn() = 0/3033518
Replica read immediately: avatar-v1.png (received=0/3033518, replayed=0/30334F0)
pg_stat_replication: async, sent=0/3033518 write=0/3033518 flush=0/3033518 replay=0/30334A8 replay_gap_bytes=112
Waiting for replay_lsn >= 0/3033518: 3007 ms, replica read: avatar-v2.png

replay on the primary side is lower than replayed on the replica side because the replica reports back periodically.

Running psql on the same stack, delay 3s, with the replica made sync via synchronous_standby_names = 'walreceiver':

Primary setting	UPDATE took	Replica read right after
synchronous_commit = on	1.034 ms	avatar-v1.png
synchronous_commit = remote_apply	3003.615 ms	avatar-v3.png

Conflict (replica set to max_standby_streaming_delay = '2s', a long query on the replica, the primary runs DELETE then VACUUM):

ERROR:  40001: canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Enable hot_standby_feedback and repeat: the query runs to completion, but VACUUM (VERBOSE) on the primary reports 1001 are dead but not yet removable.

Trade-offs

Pros

• Offloads reads from the primary.

• A streaming replica is a full copy, usable for failover.

• Logical replication allows partial copies and major version upgrades.

< slot="cons">

• An async replica always has a window of stale data; the app must route reads.

• Long queries on the replica: cancelled, extending lag, or causing bloat on the primary.

• remote_apply ties commit latency to the standby’s replay speed.

Cons

Production checklist

1. List the screens that need read-your-writes; route them to the primary or check the LSN.
2. Monitor replay_lag and the LSN gap per replica, with alert thresholds.
3. Time out and fall back to the primary when the replica has not caught up; measure the fallback rate.
4. Choose max_standby_streaming_delay and hot_standby_feedback based on the kind of queries on the replica; track pg_stat_database_conflicts and primary bloat.
5. Retry 40001 from the replica in a new transaction.
6. If you use remote_apply, measure the real commit latency, and prefer SET LOCAL per transaction.

Common mistakes

Believing synchronous_commit = on makes the replica readable immediately

With a synchronous standby, on only waits for the standby to flush WAL; the commit record may not be replayed yet, so queries still see old data (the demo above). Only remote_apply waits for replay.

Misconception	Consequence	What to do
Lag is small, so read-your-writes can be ignored	Bugs during conflicts or heavy write load	Route by LSN or by write time
now() - pg_last_xact_replay_timestamp() is the lag	False alarms when the primary is idle	Use replay_lag and the LSN gap
Logical replication copies the schema too	Subscriber errors on column changes; sequences drift at cutover	Run DDL on the subscriber first; sync sequences

Self-check

1. In the demo, flush already equals the commit’s LSN, yet the replica still returns avatar-v1.png. Why?
2. Why can a long reporting query on the replica make users see stale data for longer?
3. One synchronous standby, two async replicas, the primary uses remote_apply: reading from which replica guarantees read-your-writes?

Summary

Key takeaways

• Streaming copies the whole cluster to a read-only standby; logical copies per table and can cross versions.
• A replica sees a change only after replaying the commit record; receiving and flushing are not enough.
• Measure lag with replay_lag and the LSN gap; conflicts can stall replay.
• Read-your-writes is the app’s job (read the primary for N seconds after a write, or wait for an LSN); remote_apply does it on the synchronous standby at the cost of slower commits.

References

• Log-Shipping Standby Servers: async by default, remote_apply.
• Hot Standby: forbidden commands, kinds of conflicts, hot_standby_feedback.
• synchronous_commit: each wait level.
• Replication settings: defaults for delay and feedback.
• pg_stat_replication: LSN and lag columns.
• Recovery Information Functions: LSN and replay functions.
• Logical Replication Restrictions: what is not replicated.