When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on
hot_standby_feedback or set the
max_standby_*_delay settings to -1?
My understanding is that the
hot_standby_feedback prevents the master from doing things like
VACUUM until it is safe to do likewise on the standby where the
max_standby_*_delay settings allow
VACUUM to commence on the master but the standby, if necessary, waits to apply any vacuum cleanup that could conflict with a long running query.
Furthermore, the docs state for
Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby.
max_standby_*_delay the docs state:
If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.
It's still unclear to me which is better and what the exact pros and cons of each are?