PostgreSQL Streaming Replication Lag and Read-Your-Own-Writes

00 /What You Will Learn

Before you start

Finish Lab 02 (Docker Compose in Practice) and Lab 04 (SQL Basics) first. You should be comfortable with docker compose up, with a table and an UPDATE. You do not need to know anything about replication yet.

01 /Why It Matters

Your app is popular and one database cannot answer all the reads. So you add copies. One primary takes every write. One or more replicas copy each change and answer reads. Most of your traffic is reads, so this works well, and almost every big system does it.

Copying takes time. For a moment, a replica is behind the primary. Now picture Aditi on a job site. She updates her profile and presses Save. The write goes to the primary. The page reloads and reads her profile from a replica that has not caught up. She sees her old profile. She thinks the save failed and presses Save again, or leaves. You did nothing wrong, and she is unhappy.

The lag is usually a few milliseconds, which is why this bug hides in testing. It shows up on the bad day: a big sale, a long running query on the replica, a slow disk, a network hiccup. This lab makes the bad day happen on demand.

02 /What We Are Building

Two PostgreSQL 16 containers. The replica starts by copying the primary (pg_basebackup), then follows its WAL stream. It is a hot standby: it can answer reads while it follows.

• WAL (write-ahead log): the ordered list of every change the primary makes. Replicas replay it.
• LSN: a position in the WAL, like a page number in a diary. We compare LSNs to see who is ahead.
• pg_stat_replication on the primary shows each replica and how far it has got.

03 /Words You Need

04 /Predict Before You Run

05 /Step by Step

Step 0: Get the lab

Download the zip with the button at the top of this page. Unzip it into a new folder and open a terminal inside that folder. Every command below runs from there.

mkdir postgres-replication-lag && cd postgres-replication-lag
unzip ~/Downloads/postgres-replication-lag.zip

mkdir postgres-replication-lag; cd postgres-replication-lag
Expand-Archive -Path "$HOME/Downloads/postgres-replication-lag.zip" -DestinationPath .

To run all five steps with checks, use ./test-replication-lag.sh or .\test-replication-lag.ps1. To explore by hand, start the lab and open a session on each database:

docker compose up -d --wait
docker exec -it lab-pg-primary psql -U postgres -d desi_shop
# in a second terminal
docker exec -it lab-pg-replica psql -U postgres -d desi_shop

Step 1: Is it streaming?

-- on the primary
SELECT client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;

You should see: one row, state = streaming, sync_state = async.

Step 2: Healthy path

-- on the primary
UPDATE users SET bio = 'Updated just now' WHERE id = 101;

-- on the replica, a moment later
SELECT bio FROM users WHERE id = 101;

You should see: the replica shows Updated just now. The replica is read only: try an UPDATE there and you get an error.

Step 3: Make it fall behind

-- on the replica
SELECT pg_wal_replay_pause();

-- on the primary
UPDATE users SET bio = 'Promoted to VP of Engineering' WHERE id = 101;
SELECT pg_current_wal_lsn();
SELECT sent_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_not_applied FROM pg_stat_replication;

-- on the replica
SELECT bio FROM users WHERE id = 101;

You should see: bytes_not_applied greater than 0, and the replica still showing the old bio. That is the stale read, on a real replica.

Step 4: The fix, read your own writes

The app took pg_current_wal_lsn() right after its write. Now it asks the replica:

-- on the replica
SELECT pg_last_wal_replay_lsn();

-- compare the two positions (on either server)
SELECT pg_wal_lsn_diff('<replica position>', '<write position>') >= 0;

You should see: f (false): the replica has not reached the write. The app reads from the primary instead, and the user sees their own change. The lab does exactly this with a small function, read_your_writes.

Step 5: Resume

-- on the replica
SELECT pg_wal_replay_resume();

You should see: the lag drops to 0 within a second or two and the replica shows the promotion. The same check now returns t (true), and the replica can serve this user again.

06 /What You Should See

The real output of the automated run (test-replication-lag).

07 /Look Inside

1. See the replica's own view

Run (on the replica): SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

You should see: t, then t or f, and two positions. With replay paused, the receive position is ahead of the replay position.

2. Try to write to the replica

Run (on the replica): UPDATE users SET bio = 'x' WHERE id = 101;

You should see: ERROR: cannot execute UPDATE in a read-only transaction. All writes go to the primary.

3. See how the replica was set up

Run: docker exec lab-pg-replica ls /var/lib/postgresql/data | grep standby

You should see: standby.signal. That file tells PostgreSQL to start as a standby that follows another server.

08 /Break It, Then Fix It

Predict: pause the replica, write ten changes to the primary, then resume. Is the lag in bytes bigger or smaller than for one change, and how long does catch-up take?

Answer: bigger, roughly in proportion to the amount written. Catch-up is fast for small writes (milliseconds) and takes longer as the backlog grows. In real systems a big batch job on the primary is a classic cause of lag.

Stretch: after pausing, read from the replica in a loop while you resume it, and note the moment the answer changes. That is your lag in time.

09 /What Changes in Real Projects

• Monitor lag in both seconds and bytes. Alert when it passes what your users can tolerate. Managed services expose it as a metric (for example ReplicaLag in Amazon RDS).
• Long queries on a replica can cause lag or get cancelled. The replica must choose between applying changes and keeping old rows for the query (max_standby_streaming_delay).
• Decide per screen. A product list can be a little stale. "My balance" and "my order" cannot. Send those reads to the primary, or use the LSN check from this lab.
• Sticky routing is simpler. Many apps send a user to the primary for a few seconds after their write. It is less exact than the LSN check and easy to build.
• Synchronous replication removes the lag and slows every commit. The next lab shows that tradeoff.

10 /Interview Corner

1. What is replication lag? The delay between a change on the primary and the same change being visible on a replica. It is measured in time, or in bytes of WAL not yet applied.
2. Why do stale reads happen? With asynchronous replication the primary commits before replicas have applied the change, so a replica read can return old data.
3. How do you give read-your-own-writes consistency? Send those reads to the primary, pin the user to the primary for a short time after a write, or compare the WAL position of the write with the replica's replay position before using the replica.

Follow-up questions the interviewer will ask

• How would you route reads so a user always sees their own writes?
• What happens to a replica that is offline for an hour?
• What does synchronous replication change, and what does it cost?

11 /Clean Up

docker compose down -v

The automated script cleans up after itself. Next: Lab 24, Synchronous versus Asynchronous Replication. What happens to a commit when the primary waits for the replica.