A read replica can serve queries while another database handles writes. That can move reporting, search, or other eligible reads away from the writer, but routing a connection is only part of the design. The application must specify which committed changes a result needs to include. This article uses a PostgreSQL physical hot standby, whose normal recovery mode permits reads but does not accept ordinary writes.

When it breaks

With asynchronous replication, a successful write can be followed by a stale replica read. A user changes an account and refreshes the page, yet the previous value reappears. The replica may be healthy and continuously receiving WAL; the required commit still has to be replayed. A freshness check based on receipt rather than replay tests the wrong boundary.

Even replay progress is insufficient when the query uses an older transaction snapshot. A repeatable-read transaction intentionally preserves its view as recovery advances. Waiting until a replica has applied a write does not refresh that transaction. A correct read-after-write path needs both adequate replay progress and a snapshot taken after the progress condition was satisfied.

The freshness promise must also be limited. Including this user’s earlier write does not guarantee that no newer transaction exists elsewhere. A balance used to authorize another debit still needs a transactional check on the write path. Replica read capacity does not replace the concurrency control that protects the business operation.

Check it quickly

Run this script as a non-root Linux account with PostgreSQL 18 server and client binaries in PATH. The recorded run used PostgreSQL 18.6. It builds private primary and standby clusters connected through Unix sockets, with no TCP listener. A named pipe keeps one reader session open so the snapshot boundary is controlled without installing a driver.

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-read-replica.XXXXXX)
mkdir "$lab/socket"
primary=$lab/primary
standby=$lab/standby
reader_pid=''
reader_open=0
cleanup() {
    if test "$reader_open" = 1; then exec 3>&-; fi
    if test -n "$reader_pid"; then wait "$reader_pid" || :; fi
    for cluster in "$standby" "$primary"; do
        if test -f "$cluster/postmaster.pid"; then
            pg_ctl -D "$cluster" -m fast -w stop >/dev/null
        fi
    done
}
trap cleanup EXIT
initdb -D "$primary" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
cat >> "$primary/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
port = 56432
wal_level = replica
wal_keep_size = '64MB'
CONF
pg_ctl -D "$primary" -l "$lab/primary.log" -w -t 15 start >/dev/null
export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un)
export PGCONNECT_TIMEOUT=2
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
CREATE TABLE account (id integer PRIMARY KEY,balance integer);
INSERT INTO account VALUES (1,100);
SQL
pg_basebackup -D "$standby" -R -X stream -c fast -w
cat >> "$standby/postgresql.conf" <<CONF
port = 56433
hot_standby = on
CONF
pg_ctl -D "$standby" -l "$lab/standby.log" -w -t 15 start >/dev/null
mkfifo "$lab/reader.sql"
PGPORT=56433 PGAPPNAME=replica_snapshot psql -X -v ON_ERROR_STOP=1 -qAt <"$lab/reader.sql" >"$lab/reader.log" 2>&1 &
reader_pid=$!
exec 3>"$lab/reader.sql"
reader_open=1
printf '%s\n' 'BEGIN ISOLATION LEVEL REPEATABLE READ;' 'SELECT balance FROM account WHERE id=1;' >&3
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT EXISTS (SELECT FROM pg_stat_activity WHERE application_name='replica_snapshot' AND state='idle in transaction' AND backend_xmin IS NOT NULL)")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || exit 1
    sleep 0.1
done
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_wal_replay_pause()' >/dev/null
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_get_wal_replay_pause_state()='paused'")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || exit 1
    sleep 0.1
done
psql -X -v ON_ERROR_STOP=1 -qAt -c 'UPDATE account SET balance=80 WHERE id=1'
target=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_current_wal_lsn()')
printf 'primary_balance '
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT balance FROM account WHERE id=1'
printf 'paused_replica_balance '
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT balance FROM account WHERE id=1'
for attempt in 1 2 3 4 5; do
    test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_last_wal_replay_lsn()>='$target'::pg_lsn")" = f
    sleep 0.1
done
echo fresh_read_gate_timed_out
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_wal_replay_resume()' >/dev/null
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_last_wal_replay_lsn()>='$target'::pg_lsn")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 150 || exit 1
    sleep 0.1
done
test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT balance FROM account WHERE id=1')" = 80
echo fresh_snapshot_after_replay_80
printf '%s\n' 'SELECT balance FROM account WHERE id=1;' 'COMMIT;' >&3
exec 3>&-
reader_open=0
wait "$reader_pid"
reader_pid=''
test "$(cat "$lab/reader.log")" = "$(printf '100\n100')"
echo old_repeatable_read_snapshot_still_100
printf 'lab_directory %s\n' "$lab"

The fixture begins with a balance of one hundred. On the standby, a repeatable-read transaction performs an initial query and remains open. The script confirms its backend is idle inside a transaction with a snapshot, then pauses actual WAL replay. The primary commits a change to eighty and captures a post-commit WAL position as the read’s progress target.

Expected output shows primary_balance 80 and paused_replica_balance 100. Five replay checks separated by short waits remain false, producing fresh_read_gate_timed_out. That result refuses to certify freshness while the required change is unapplied. The exercise does not silently treat the stale result as acceptable or pretend those polling intervals form a precise end-to-end latency guarantee.

Replay then resumes. After its LSN reaches the target, a new standby query must read eighty. The original repeatable-read session queries again and still reads one hundred. Its saved output must contain one hundred twice, proving the old snapshot survived the change in replay progress. Both facts can hold simultaneously: the server has caught up, and that particular transaction still sees older data.

The script commits the held read transaction, closes its pipe, stops both servers, and prints the retained log directory. Its assertions inspect database values and actual replay state. No production routing or application connection pool is changed.

Make the routing promise explicit

A progress token must refer to the same database history as the destination replica. An LSN is not a globally unique business version across unrelated clusters or diverged timelines. Bind any production gating design to the relevant topology and handle failover explicitly. The fixture uses one fixed primary and its direct standby, so that identity is established by construction.

If freshness cannot be established within the request budget, choose an explicit application response or an authorized read from the writer with capacity available. Do not let a deadline quietly weaken the meaning of the result. Acquire a fresh snapshot after the gate succeeds, and keep transactions short enough that snapshot retention does not defeat later checks.

Long standby queries can also conflict with recovery. PostgreSQL may cancel them to let replay proceed; feedback settings trade such cancellations against retention pressure on the primary. Monitor replay delay, query cancellations, and transaction age together. Measure the read workload actually moved off the writer, alongside the replication and operational costs of maintaining the copy.

Sources