PostgreSQL Hot Standby Feedback: Canceled Queries or Bloat?
PostgreSQL hot standby feedback can prevent some read queries on a standby server from being canceled because of conflicts with WAL replay, but it can make the primary retain dead row versions longer and accumulate table bloat. It is a trade-off, not a way to eliminate both problems: enable it when standby query continuity matters, and monitor primary storage and vacuum health.
What hot standby feedback does
A hot standby is a PostgreSQL server that replays changes received from a primary while also accepting read-only queries. Replay must keep the standby’s data consistent with the primary’s committed changes. Sometimes a query on the standby needs to keep an older snapshot available while replay reaches a change that would remove or alter data visible to that query. This is a recovery conflict.
Without feedback, PostgreSQL can resolve certain conflicts by delaying replay for a limited time and, if the conflict persists, canceling standby queries. The delay is constrained by settings such as max_standby_streaming_delay for streaming replication. Those settings control how long replay may be delayed; they do not guarantee that every query will finish.
With hot_standby_feedback enabled on the standby, it sends information about its oldest relevant query horizon upstream. The primary can then avoid vacuuming row versions that might still be needed by standby queries. This reduces cancellations caused by cleanup conflicts, but does not prevent every kind of standby query cancellation.
How the trade-off works
PostgreSQL uses multiversion concurrency control (MVCC): an update or delete generally leaves an old row version that may remain visible to a transaction with an older snapshot. Vacuum eventually removes row versions that are no longer needed. A standby query can need such versions even though the primary’s own transactions no longer do.
Feedback tells the primary, in effect, that a standby still needs an older horizon. Vacuum must respect that information. If a long-running standby query holds the horizon back, dead tuples can remain in primary tables and indexes, increasing disk use and potentially slowing scans. Autovacuum may run but be unable to remove those particular versions yet.
For example, suppose a reporting query on a standby runs for 40 minutes while the primary repeatedly updates a large table. Feedback may help the query finish by preventing vacuum from removing versions it might need. The same protection can leave more obsolete versions on the primary during that period. Once the query ends and the feedback horizon advances, vacuum can clean up eligible versions, but cleanup is not necessarily immediate.
What feedback prevents—and what it does not
The setting is intended to reduce cancellations caused by recovery conflicts involving cleanup of row versions. It is not a blanket “never cancel queries” switch. Queries can still be canceled for other recovery conflicts, server shutdown or failover events, client or application timeouts, and other operational reasons.
Feedback also does not make replay wait indefinitely for every query. It addresses conflicts by influencing cleanup on the primary, which shifts pressure toward primary storage and vacuum. Replay delay settings address a different part of the trade-off: they let the standby postpone applying WAL, which can increase replication lag and does not protect the primary from bloat in the same way.
Nor does feedback guarantee that bloat will occur. The impact depends on workload, how long queries retain old snapshots, how much data is updated or deleted, and how quickly vacuum can catch up. The risk is greatest when long-running standby queries coincide with frequent changes to large tables.
How to choose a setting
Enable hot_standby_feedback when canceled standby reads are costly—for instance, long analytical reports—and the primary has enough storage headroom and effective monitoring. Leaving it disabled may be preferable when primary storage and vacuum responsiveness matter more than uninterrupted standby queries, or when standby queries are short and can be retried.
- Consider enabling it for reporting workloads where query restarts are disruptive and primary bloat can be monitored and managed.
- Consider leaving it off if the primary is already close to storage limits, tables change rapidly, or long snapshots are common and hard to control.
- Review the whole replication design if both low lag and long-running standby queries are essential. One setting cannot guarantee no cancellations, no lag, and no bloat simultaneously.
The parameter is set on the standby, typically in its PostgreSQL configuration, and requires a reload rather than a full server restart in supported configurations. Check the documentation for the PostgreSQL version in use and confirm the active value with SHOW hot_standby_feedback;. Configuration management and managed database services may require changing the setting through their own supported controls.
Monitoring and practical safeguards
Monitor both sides of the trade-off. On the standby, check replication lag and recovery-conflict statistics, including the conflict categories exposed through PostgreSQL’s statistics views. On the primary, track table and index growth, dead-tuple estimates, autovacuum activity, and available disk space. View and statistic names can differ across PostgreSQL versions, so consult the matching version’s documentation.
Useful safeguards include:
- Set sensible application-side limits for report duration and transaction lifetime. A transaction left open after its query finishes can still retain an old snapshot.
- Schedule intensive reports thoughtfully, especially on frequently updated tables.
- Alert on sustained replication lag, rapid relation growth, low disk headroom, and vacuum falling behind.
- Test representative long-running queries in a staging environment and observe what happens on both primary and standby.
- Have a response plan for a persistently old feedback horizon, such as identifying the session or workload responsible before changing settings or terminating work.
Do not treat a rising dead-tuple estimate alone as proof that feedback is the cause. Updates, deletes, workload spikes, and vacuum settings can also affect it. Diagnose trends alongside active transactions, replication state, and vacuum logs.
Alternatives and common misconceptions
One alternative is to keep feedback disabled and tune standby replay-delay settings to give queries more time. This may reduce cancellations, but replay can fall behind, and delay limits are not the same as protecting every query from cleanup conflicts. Another option is to shorten or split reports, run them against a different reporting system, or make the application retry queries canceled during recovery.
A common misconception is that hot_standby_feedback directly creates dead tuples. It does not create extra row versions; normal updates and deletes do that. Feedback can prevent vacuum from removing versions that would otherwise be removable, so those versions remain longer and can contribute to bloat. Another misconception is that disabling feedback prevents all primary bloat: ordinary workload and vacuum conditions can cause bloat regardless.
Frequently Asked Questions
Does hot_standby_feedback stop all canceled queries?
No. It reduces cancellations caused by certain cleanup conflicts. Other recovery conflicts, connection interruptions, and application timeouts can still cancel or end a query.
Can hot_standby_feedback cause bloat on the primary?
It can contribute to bloat by making vacuum retain dead row versions while the standby reports that older snapshots may need them. The size and duration of the effect depend on workload and query duration.
Does enabling it increase replication lag?
Not necessarily by itself in every workload. It can protect standby queries by delaying cleanup on the primary; replay-delay settings, network capacity, and WAL generation are separate factors that can affect lag. Monitor actual replication behavior.
Is hot_standby_feedback enabled by default?
It is commonly disabled by default in PostgreSQL, but defaults and configuration can vary by version or service. Check the running server with SHOW hot_standby_feedback;.
Should I use feedback or increase max_standby_streaming_delay?
They address different parts of the trade-off. Feedback can reduce cleanup-conflict cancellations at the potential cost of primary bloat; a longer replay delay gives queries more time but can increase standby lag and still end in cancellation.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.