Skip to content

Replication and read replicas

A replica is the primary some way in the past: fine for reads that tolerate that, wrong for users who must see their writes.

Predict first

Before you read: predict

A proctor marks flag 31 as resolved. The UPDATE runs on the primary, and its COMMIT returns. The app has one streaming replica, with default settings. The proctor's flag list then reloads, and its SELECT goes to the replica.

What can the reload show?

Pick one

The 30-second version

A streaming replica replays the primary's WAL in order, some way behind, and by default the primary's COMMIT does not wait for it. So a replica read can return rows from before a commit, with no error. To read your own writes, read the primary, or wait until the replica's replay position passes your commit's LSN. Replicas never take writes.

A replica is the primary some way in the past: fine for reads that tolerate that, wrong for users who must see their writes.

How it works

Step 1 of 13

Streaming replication: the primary streams its write-ahead log (WAL) to a standby, which saves it to disk, then replays it in order.
In the kitchen
  • the runner delivers pages in order; the recorder copies them in later → WAL is received and saved, then replayed (steps 1, 8)

Analogy: An excavation's day book and the trench books

The site director numbers each find in the day book; a runner carries each new page to every trench, whose recorder copies it into the trench book when free.

On the dig In Postgres
the runner delivers pages in order; the recorder copies them in later WAL is received and saved, then replayed (steps 1, 8)
trench books are for looking things up; new finds go to the director a hot standby is read-only (step 2)
the director numbers a find without waiting for the runner asynchronous commit (step 3)
"check trench C's book" for a find entered a minute ago a stale replica read (steps 4–5)
"copied up to page 212" on the book's cover, before looking the replay LSN wait (step 7)
the director waits for trench C's signature: "page arrived", or "copied in" on vs remote_apply (steps 9–10)
the director's tent burns; trench C copies in every delivered page, then takes over promotion (step 12)
the director walks back in and carries on numbering in her own book a second primary (step 13)

In real terms: a replica read answers "what did the primary say a little while ago?"; ask it only questions where that is good enough.

Breaks down when: one page of the day book can hold half of a day's finds. Wrong inference: "a replica can show half a transaction." A standby shows a transaction only once its commit record is replayed: all of it, or none (not executed here).

Contrasting case: a dig with only the director's day book. Every look-up sees every find, and every look-up queues at one tent.

Examples, run for real

4.1 ExamHall: an autosaved answer shows blank

EXH-1280: a candidate autosaved answer 17, and the review screen, which reads from a replica, showed it blank. MR !1480 sends a candidate's reads to the primary for 5 s after each write. Replay was paused before the autosave; the review times use a virtual clock:

4.1 candidate autosaves answer 17 = C on the primary; the review screen reads it (asynchronous standby)
  before (review reads the replica)            answer 17 shows blank · autosave's COMMIT had returned
  MR (primary for 5 s after a write)           review at +2 s: primary -> C · review at +7 s, replica still behind: replica -> blank
  after replay resumes                         replica -> C (the write was delayed, not lost)

The COMMIT had returned, yet the replica had not replayed it (steps 3–4). The pin fixed the review inside its window and failed after it, while replay was still behind (step 6).

4.2 What a standby accepts

4.2 what a standby accepts, and what the primary can see of its lag
  INSERT on the standby                        ERROR 25006 "cannot execute INSERT in a read-only transaction"
  the INSERT on the primary, replay paused     pg_stat_replication: flushed yes · replayed no · standby rows 0

Writes go only to the primary (step 2). pg_stat_replication on the primary shows the gap between WAL saved and WAL replayed (step 8).

4.3 Synchronous levels

The standby is named as synchronous; replay is paused before each COMMIT:

4.3 synchronous_standby_names = 'standby1' (pg_stat_replication: standby1 sync); replay paused before each COMMIT
  off          COMMIT returned while replay paused: yes · standby shows the row when COMMIT returned: no
  local        COMMIT returned while replay paused: yes · standby shows the row when COMMIT returned: no
  remote_write COMMIT returned while replay paused: yes · standby shows the row when COMMIT returned: no
  on           COMMIT returned while replay paused: yes · standby shows the row when COMMIT returned: no
  remote_apply COMMIT waited (wait_event SyncRep) while replay paused; standby row meanwhile: no · after resume, COMMIT returned; standby shows the row: yes
  on, standby frozen (docker pause), replay running: COMMIT waited (wait_event SyncRep) · returned once the standby answered again

on waited for the standby's disk, not its replay, so the row was not yet visible there (steps 9–10). Paused replay held only remote_apply; on waited once the standby stopped answering (step 11).

Picture it

two black storage drums side by side on a pale background: a larger one in front with a pale cyan lid and lavender bands, and a smaller one behind with a lime lid and lime bands. There are no labels, arrows or text.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

After a purchase commits on the primary, the "My tickets" page reads from an asynchronous replica and sometimes shows no ticket. The team wants the buyer always to see the new ticket, while the page still reads the replica whenever the replica has it.

Which change does that?

Pick one

Question 2 of 5

A video platform runs one asynchronous standby. The standby has received and saved the WAL of a new upload's commit, but its replay is behind, so queries there do not show the upload yet. The primary's server dies, and the team promotes the standby with pg_promote().

Once promotion finishes, what does a SELECT for the upload on the new primary return?

Pick one

Question 3 of 5

After a price change commits on the primary, the product page decides where to read:

The page often finds the replica ready and still shows the old price.

Which is the bug?

Code for this question
const { rows: [w] } = await primary.query('SELECT pg_current_wal_lsn() AS lsn'); // after the UPDATE's COMMITconst { rows: [r] } = await replica.query(  'SELECT pg_last_wal_receive_lsn() >= $1::pg_lsn AS ready', [w.lsn]);return r.ready ? readPrice(replica) : readPrice(primary);
Pick one

Question 4 of 5

A payments service runs one primary and one streaming standby. The primary's VM freezes; a failover script promotes the standby with pg_promote(), and the app servers switch to it, all but one. Then the old primary's VM resumes, and that one app server, which still has the old address, sends it a new charge.

What happens to the charge?

Pick one

Question 5 of 5

So that senders always see their own message in the thread view, which reads the replica, the send handler runs SET synchronous_commit = remote_apply before its INSERT. The primary's postgresql.conf has synchronous_standby_names = ''. A sender's own message is still sometimes missing from the thread view.

Which is the bug?

Pick one

Go deeper

Pro tips
  1. Return the commit's LSN from every write endpoint and let reads carry it. When: moving user-facing reads to replicas (steps 6–7).
  2. Watch replay_lsn, not only flush_lsn, in pg_stat_replication. When: alerting on replica freshness (step 8).
Source
  • PostgreSQL 17 documentation, 26.2 Log-Shipping Standby Servers: https://www.postgresql.org/docs/17/warm-standby.html · fetched 2026-09-28. "Streaming replication is asynchronous by default … there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby" (steps 3–4). "If the primary server crashes then some transactions that were committed may not have been replicated to the standby server, causing data loss" (step 13). Synchronous replication: remote_apply "will cause each commit to wait until the current synchronous standbys report that they have replayed the transaction, making it visible to user queries"; such commits "may never be completed if any one of the synchronous standbys should crash" (steps 10–11); synchronous_standby_names names the standbys that commits "made when synchronous_commit is set to on, remote_apply or remote_write will wait for responses from" (step 11). Promotion: "Before failover, any WAL immediately available in the archive or in pg_wal will be restored, but no attempt is made to connect to the primary". Valid as of 2026-09-28.
  • PostgreSQL 17 documentation, 19.5 Write Ahead Log, synchronous_commit: https://www.postgresql.org/docs/17/runtime-config-wal.html · fetched 2026-09-28. "If synchronous_standby_names is empty, the only meaningful settings are on and off; remote_apply, remote_write and local all provide the same local synchronization level as on". With a synchronous standby, remote_apply waits until the commit record is "applied … so that it has become visible to queries on the standby(s)", on until it is "flushed … to durable storage", remote_write until "written … to their file systems". In full: "When set to on, commits wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to durable storage" (step 11: replay plays no part in on). Valid as of 2026-09-28.
  • PostgreSQL 17 documentation, 26.4 Hot Standby: https://www.postgresql.org/docs/17/hot-standby.html · fetched 2026-09-28. Connections are "strictly read-only"; INSERT, UPDATE, DELETE and other DML "will produce error messages" (step 2); "Running the same query nearly simultaneously on both primary and standby might therefore return differing results. We say that data on the standby is eventually consistent with the primary" (steps 4–5). Query conflicts may delay WAL application up to max_standby_streaming_delay (a real source of replay lag; not executed). Valid as of 2026-09-28.
  • PostgreSQL 17 documentation, 26.3 Failover: https://www.postgresql.org/docs/17/warm-standby-failover.html · fetched 2026-09-28. Valid as of 2026-09-28.
  • PostgreSQL 17 documentation, 9.28 System Administration Functions: https://www.postgresql.org/docs/17/functions-admin.html · fetched 2026-09-28. pg_last_wal_receive_lsn(): "the last write-ahead log location that has been received and synced to disk"; pg_last_wal_replay_lsn(): "the last write-ahead log location that has been replayed during recovery". pg_wal_replay_pause(): "A request doesn't mean that recovery stops right away", so the scripts wait for pg_get_wal_replay_pause_state() = paused; "If a promotion is triggered while recovery is paused, the paused state ends and promotion continues". Valid as of 2026-09-28.
  • PostgreSQL 17 documentation, Appendix A. Error Codes: https://www.postgresql.org/docs/17/errcodes-appendix.html. 25006 read_only_sql_transaction. Valid as of 2026-09-28.
  • Kleppmann, Designing Data-Intensive Applications, ch. 5, "Problems with Replication Lag" (curriculum source; not re-read for this lesson). Names reading your own writes and monotonic reads (steps 5–7), which this lesson executes on Postgres.

Related lessons

Checked against the tools and docs as of 2026-09-28.