5

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 hot_standby_feedback:

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.

And for 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?

4

With hot_standby_feedback on, vacuum can still be done, but it will be less effective as some tuples that would otherwise be vacuumed now have to be delayed to a later vacuum. The only real downside that I know of is the increased bloat. How much of a downside this is depends entirely on your usage. The worst case is if your database has small, intensively updated tables, like a work-queue table. Those could get extremely bloated. If you don't have that kind of table, you probably won't have a problem.

The problems with max_standby_*_delay are that other queries running on the standby are also having their horizon held back by potentially large amounts, and that if you hold back the stand-by long enough accumulated un-replayed WAL files will fill up your hard drive and you will lose the stand-by.

| improve this answer | |
0

hot_standby_feedback affects the activity of primary server, whereas max_standby_*_delay affect the activity of standby server.

Cons of hot_standby_feedback is obvious:Primary cannot remove dead tuples while conflict occurs.

max_standby_*_delay are for standby servers, and do not affect to the primary's activity. This is one of the pros of these params. Cons of them is the following: When a conflict occurs on a standby, its standby suspends replaying WAL log for up to max_standby_*_delay. Hence, if one conflict occurs by a query, then subsequent queries read old data (snapshot of database just before the conflict occurs), cannot read latest data since the standby suspends replaying WAL.

If you run only one query on the standby, I think you can set -1 to max_standby_*_delay.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.