Parameter · Replication / Standby Servers
max_standby_streaming_delay
Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data. -1 means wait forever.
- Default:
30s - Type: integer (ms)
- Range: -1 … max (2147483647)
- Applies: Reload
In plain words
On a hot standby, replaying the primary's changes sometimes conflicts with a query that is still running (for example, VACUUM removed rows that query needs). Replay can wait for the query, making the standby fall behind, or cancel it. max_standby_streaming_delay is how long replay is allowed to wait before it cancels the conflicting queries.
Higher values favour long reports; lower values keep the standby current. -1 waits forever; 0 cancels immediately.
For DBAs and operators
Applies to WAL received by streaming (archive-restored WAL uses max_standby_archive_delay). The budget is not per query: it is the total time replay may be behind since that WAL was received. After a long wait, the next conflict may get almost no grace time.
ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Other DETAIL lines name the cause: User was holding a relation lock for too long., User was holding shared buffer pin for too long., User was or might have been using tablespace that must be dropped. Idle-in-transaction sessions get FATAL: terminating connection due to conflict with recovery.
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin,
confl_tablespace, confl_deadlock, confl_active_logicalslot
FROM pg_stat_database_conflicts;Typical setups: a reporting replica with a large value (or -1) and monitoring of replay lag; an HA standby with the default 30 s so failover stays quick. hot_standby_feedback avoids most snapshot conflicts at the price of primary bloat.
How to change it
Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().
See it in context
Hot standby and recovery conflicts Startup process Streaming replication
Related parameters
max_standby_archive_delay hot_standby_feedback hot_standby recovery_min_apply_delay