WAIT FOR LSN on PostgreSQL 19, companion repo

Reproduces every number in Read your own writes, off the primary.

Two containers on one host: a primary and one asynchronous streaming replica, both postgres:19beta3. Primary on localhost:55432, replica on localhost:55433.

Requires Docker, psql, and Go (the benchmark client uses pgx).

./setup.sh          # build the pair from nothing: replication, role, schema
./experiments.sh    # the routing matrix + timeout budgets -> results.txt, results-*.jsonl
./durability.sh     # what synchronous replication does and doesn't fix
./lsn-trap.sh 20    # in-transaction LSN vs post-commit LSN, 20 rounds
./teardown.sh       # remove the containers and the replica volume
frameworks/         # the Go, Rails and Laravel integrations, executed (see its README)

bench/ is the workload: one iteration inserts a row on the primary and reads it back the way a request would, across four routing strategies.

-mode primary   write and read on the primary (sticky; always correct)
-mode replica   write on primary, read replica immediately (naive)
-mode sleep     write on primary, sleep, read replica       (-sleep 50ms)
-mode waitfor   write on primary, take the post-commit LSN, WAIT FOR it on the
                replica, read there; fall back to the primary on timeout
                                                            (-timeout 500ms)

Each run prints a JSON record (stale reads, where reads landed, fallbacks, latency percentiles) and a one-line human summary.

Which run is which table in the post

post	run	file
routing strategies	experiments.sh section A, idle replica, n=1000	results-idle.jsonl
synchronous_standby_names / remote_apply	durability.sh	results-durability.jsonl
TIMEOUT as a routing budget	experiments.sh section E, replica held 50 ms behind	results-timeout-*.jsonl
the 40-byte gap and its pg_walinspect record	lsn-trap.sh	stdout

results.txt is the human-readable transcript of one full experiments.sh run and also carries the two runs the post does not quote (results-underload.jsonl, results-lagging.jsonl), kept because the script produces them in the same pass.

The replay-lag-under-read-load figures in the post are illustrative and are not produced here.

Notes on the setup

• Nothing durability-related is passed on the container command line. Command-line options outrank postgresql.auto.conf, so a stray -c synchronous_commit=on silently defeats every ALTER SYSTEM in durability.sh.
• Readiness is checked on the TCP port from the host, not with pg_isready inside the container: the entrypoint runs a temporary socket-only server during initdb, which answers pg_isready before the real server exists.
• recovery_min_apply_delay is used to hold the replica a known distance behind. It models a lagging standby; it does not model a slow network.
• bench/ takes the token with pg_current_wal_insert_lsn() after the commit, where the post and frameworks/go use pg_current_wal_flush_lsn(). Both are past the commit record once the transaction has returned, and insert is the more conservative of the two. The benchmark predates the post's wording and has not been re-run to change it.
• Both nodes share a host and talk over loopback, so every latency here is a best case. A real cross-AZ replica adds its round trip to all of them.

What this setup cannot measure

Write-side throughput. On Docker for Mac the same 30-second pgbench run varied between 1,876 and 3,099 tps in an unchanged configuration, so the cost of synchronous_commit = remote_apply is not separable from noise here. The stale-read counts are stable and repeatable; the throughput numbers are not, and none are quoted in the post.