Tell the primary what the standby's readers still need
## The bug
A read against the standby could be destroyed by a vacuum on the primary, through no fault of the application. It came back either as `SQLSTATE 40001`:
```
SqlError { sqlState = "40001", sqlExecStatus = FatalError
, sqlErrorMsg = "canceling statement due to conflict with recovery"
, sqlErrorDetail = "User query might have needed to see row versions that must be removed." }
```
or, when the session was holding a snapshot, as the connection being terminated outright.
Found from nix-ci, where a web handler reading the replica returned a 500 carrying exactly that error. It is rare on an idle machine and much less rare on a loaded one.
## Root cause
`postgresqlStandbySetupFunc` appends `port`, `unix_socket_directories`, `listen_addresses`, `hot_standby = on` and `recovery_min_apply_delay` to the standby's `postgresql.conf`. It configures the standby to serve reads and to stay deliberately behind, and then leaves postgres' defaults to decide what happens when recovery meets one of those reads. Those defaults are:
- `hot_standby_feedback = off` — the primary is never told what a standby reader is holding, so it is free to vacuum those row versions away;
- `max_standby_streaming_delay = 30s` — when replaying the cleanup conflicts with a reader, recovery cancels the reader.
Worth knowing about that 30 seconds: **it is measured from when the record arrived, not from when the query started.** A standby that is already behind can have spent the whole budget before a query begins, and the query is then cancelled at once. That is why nix-ci saw this as an instant 500 under load rather than as a wait, and why raising the number would not have helped.
## The test that reproduces it
`StandbySpec`, "keeps a read on the replica alive while the primary vacuums what it is reading". It holds a snapshot on the standby, updates every row on the primary and vacuums away the versions that snapshot needs, then reads again through the same transaction.
Two details are load-bearing:
- **`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ`.** Read committed takes a new snapshot per statement and releases the old one, so nothing would be held across the cleanup and the conflict would never arise. My first attempt left this out and passed against the unfixed code. An application that reads twice and compares is the one that needs this.
- **`runSqlPoolNoTransaction` for the `VACUUM`**, which refuses to run inside a transaction block.
Before the fix it fails in about 90 seconds, with the connection gone. After it, about one second — because no conflict arises at all, so no delay budget is spent.
## The fix
`hot_standby_feedback = on` stops it at the source: the standby reports the oldest snapshot its readers need and the primary keeps those row versions. The base backup already takes a replication slot, which is what the feedback is recorded against, so there was nowhere else it needed to go.
`wal_receiver_status_interval = 1s` has to come down with it. The default is ten seconds, which is longer than the databases here live, and feedback that arrives after the vacuum protects nothing.
`max_standby_streaming_delay = -1` and `max_standby_archive_delay = -1` make recovery wait rather than cancel.
## The judgement call in that last part, which you may want to overrule
**Feedback alone is enough to make the regression test pass** — I checked, with the delay left at its default of 30s. So if you want the minimal fix, drop the two `-1` lines and the test still goes green.
I kept them because feedback closes the window rather than the door. It cannot retract a cleanup already in flight, it says nothing about conflicts that are not about row versions (a lock that replayed DDL wants, for one), and it leaves the pre-spent-budget case above intact. Since the reported symptom is a rare flake, a fix that only narrows the window leaves a rare flake behind.
The cost is that recovery can now stall behind a query instead of killing it. In this harness that reads as correct to me — a replica in a test suite is there to be behind, and a test that wants it caught up says so with `awaitReplica` — but it does turn "a confusing error" into "a hang until the suite timeout" if a test ever leaves a transaction open. Your call.
## Things to check manually
- Whether you want the two `-1` lines at all, per the section above.
- `wal_receiver_status_interval = 1s` is a guess at "short enough to matter, long enough not to be silly". It is the shortest whole second; the GUC is in seconds, so sub-second values risk rounding to 0, which *disables* feedback entirely.
- The regression test costs a `VACUUM` over 100 rows and is fast when passing, but it is the kind of test that goes from one second to ninety if it ever regresses, rather than failing quickly.
`nix flake check` passes.