max_standby_streaming_delay is not a query timeout, whatever its name and its default suggest. It is the amount of replication lag you have agreed to buy with standby queries, and the queries are merely what gets thrown overboard when the budget runs out. max_standby_archive_delay is the same knob for WAL that arrives through restore_command instead of a walsender; I’ll treat the two as one parameter with two names and come back to why there are two.
Both default to 30s, both are in milliseconds if you leave off the unit, both are sighup, so a reload on the standby is enough. -1 means wait forever and 0 means cancel at once. They are read by the startup process on the standby, so the copy in the primary’s postgresql.conf does nothing until the day that server is itself a standby, which is a reason to set them identically everywhere rather than a reason to leave them out. If hot_standby is off there are no queries to cancel and none of this applies.
A recovery conflict is what happens when the startup process reaches a WAL record it cannot apply while some backend on the standby is in the way: a vacuum cleanup record removing row versions a standby snapshot could still see, an AccessExclusiveLock taken on the primary (DDL, TRUNCATE, LOCK TABLE, and, as we’ll get to, plain VACUUM) against a table a standby query has open, a page cleanup that needs a buffer some cursor has pinned, a DROP TABLESPACE while temp files live in it. On the primary the same collision makes somebody wait. On the standby the record has already happened and must be applied; the only question is how long the startup process waits for the backend to get out of the way before it cancels it, and that is what these two parameters set. log_recovery_conflict_waits covers what the wait looks like in the log.
The clock starts before your query does
The delay is not measured from the moment the conflict starts. The startup process keeps a private timestamp of the last time it obtained fresh WAL, XLogReceiptTime. While streaming, it advances each time the startup process goes back to the walreceiver for more and finds it had already processed everything before the most recent chunk the walreceiver flushed; reading from the archive, it advances each time a new segment file is opened. The cutoff for a conflict is that timestamp plus the delay. When the standby is keeping up, the timestamp is milliseconds old when a conflict arrives and the query gets the full thirty seconds. When the standby is behind, for any reason, the timestamp does not move, because the startup process is working through WAL that arrived some time ago, and the grace period is whatever remains of a budget that was already being spent.
The docs say that for a standby whose tables are heavily updated upstream, a finite value “can be considered similar to setting statement_timeout”. Only if yours is the only query on the standby, and the reason is the clock. Here is a standby at max_standby_streaming_delay = '10s', with two identical REPEATABLE READ transactions that each take a snapshot and then pg_sleep(60). The first starts two seconds before a DELETE plus VACUUM on the primary; the second starts five seconds after it, with a second DELETE plus VACUUM on another table having gone by in between:
1 query1 start t+0.00s 2 primary: delete+vacuum a t+2.01s 3 primary: delete+vacuum c t+5.06s 4 query2 start t+7.12s 5 query1: ERROR: canceling statement due to conflict with recovery 6 query1: DETAIL: User query might have needed to see row versions that must be removed. 7 query1: Time: 12219.187 ms (00:12.219) 8 query2: ERROR: canceling statement due to conflict with recovery 9 query2: DETAIL: User query might have needed to see row versions that must be removed. 10 query2: Time: 5134.480 ms (00:05.134)
Query 1 got its ten seconds. Query 2 was canceled about thirty milliseconds after query 1, because by the time the startup process reached the second vacuum’s records the ten seconds had been spent on query 1 and the receipt timestamp had not moved. A standby under a heavy write load produces the same effect without any help from a slow query: replay stays behind the walreceiver, the timestamp stops advancing, and 30s rounds down to nothing. pg_stat_replication.replay_lag on the primary, and wait_event = RecoveryConflictSnapshot on the startup row of the standby’s pg_stat_activity, are what the waiting looks like from outside; pg_stat_database_conflicts on the standby is where the cancellations are counted, by reason.
Which backends get canceled is also broader than people expect. For a cleanup record, the startup process asks the proc array for every backend in that database whose snapshot is old enough to see the removed rows; it neither knows nor cares which table the backend is reading. The transactions above only ever touched table b. They were canceled by vacuums of a and c. The error is SQLSTATE 40001, serialization_failure, the same class a SERIALIZABLE retry loop already handles, and retrying is the correct response: the next attempt takes a snapshot that does not need the removed rows.
Two variations end the session instead of the statement. If the backend is idle in a transaction that still holds a snapshot (a REPEATABLE READ transaction that has run one query, say; a READ COMMITTED transaction idle between statements has let its snapshot go, unless it has a cursor open, and is not a target), there is no statement to cancel, and the answer to the startup process’s signal is FATAL: terminating connection due to conflict with recovery. The same happens if the conflict arrives while the backend is inside a subtransaction, because an error there would only roll back to the savepoint and leave the parent transaction’s snapshot and locks exactly where they were:
1 FATAL: 40001: terminating connection due to conflict with recovery 2 DETAIL: User query might have needed to see row versions that must be removed. 3 HINT: In a moment you should be able to reconnect to the database and repeat your command.
One SAVEPOINT is enough, which means an ORM that opens a savepoint around every nested block (Django’s atomic() inside another atomic() does exactly this, a design I have some responsibility for) turns every conflict on a reporting standby into a dropped connection rather than a retryable error. Two conflicts don’t wait at all, whatever you set: replaying DROP DATABASE terminates every session in that database immediately, and invalidating a logical slot on a standby (PostgreSQL 16 and later) doesn’t consult the delay either.
Why there are two
PostgreSQL 9.0 went into beta with one parameter, max_standby_delay, which compared the latest commit, abort, or checkpoint timestamp found in the WAL with the standby’s clock. Tom Lane’s May 2010 thread “max_standby_delay considered harmful” listed what that gets wrong: clock skew between the two servers stretches or erases the grace period, an idle primary produces no fresh timestamps so replayed records look ancient, and WAL restored from an archive is old by definition and gets no grace at all. His July 2010 commit replaced the WAL timestamps with the receipt clock described above and split the parameter, because the two ways WAL arrives reset that clock differently: streaming per chunk, the archive per segment file (16 MB, unless you changed it at initdb). max_standby_archive_delay is therefore the budget for applying one restored segment, and it starts when the file is opened. With restore_command set, no primary_conninfo, max_standby_archive_delay = '2s', the streaming delay at its default, and log_recovery_conflict_waits on, the same conflicting query is canceled two seconds after the segment lands:
1 LOG: restored log file "00000001000000000000000D" from archive 2 LOG: recovery still waiting after 1024.342 ms: recovery conflict on snapshot 3 LOG: recovery finished waiting after 2029.996 ms: recovery conflict on snapshot
In practice the archive delay governs a standby catching up from the archive after its streaming connection dropped, one replaying its own pg_wal after a restart (the startup process treats a segment opened from pg_wal the same as one from the archive), and one that was never given a primary_conninfo. In each case the standby is behind, and a standby that is behind should be catching up rather than hosting reports, so this is the one of the two you can keep short with a clear conscience.
What hot_standby_feedback does and doesn’t fix
hot_standby_feedback is the remedy the docs lead with, and for cleanup conflicts it works: the standby reports its oldest snapshot upstream, at most once per wal_receiver_status_interval, and VACUUM on the primary leaves those rows alone. With it on and a REPEATABLE READ transaction open on the standby, the same DELETE plus VACUUM VERBOSE reports 50000 are dead but not yet removable and a removable cutoff equal to the backend_xmin the primary now shows in pg_stat_replication, and the standby transaction runs to completion. The cost is bloat on the primary in proportion to your longest standby transaction, which is exactly the cost that transaction would impose if it ran on the primary.
What feedback does not prevent is lock conflicts, and the lock conflict people forget is VACUUM itself. When it finds empty pages at the end of a table it truncates them, truncation takes an AccessExclusiveLock, and that lock is WAL-logged and replayed as a lock on the standby. With feedback on, a DELETE of a table’s tail already replayed, and a standby transaction sitting on the table after a SELECT count(*), a VACUUM on the primary reports truncated 1728 to 0 pages, and ten seconds later:
1 ERROR: canceling statement due to conflict with recovery 2 DETAIL: User was holding a relation lock for too long.
That one is fixed on the primary, not the standby: vacuum_truncate = off, as a reloption since PostgreSQL 12 or as a server parameter in 18, on the tables where it bites.
So the setting is a decision about what the standby is for, and it deserves the same seriousness as an RPO. If the standby exists to be failed over to, leave both parameters at 30s or lower, turn on hot_standby_feedback if the cancellations bother you, and make the application retry 40001, which it should be doing anyway. If it exists to run long reports, the number you write here is the replication lag you have agreed to accept, so write the number you would be willing to explain after a failover. -1 spelled out is “unbounded”, and I have watched it get chosen by people who would not have chosen it under that name.