Postgresql: Querying on Hot Stand-by, option to continue ignoring effects of vaccum on any relevant rows?

user_7391114 0
Viewed 33

My long running SELECT queries against Hot stand-by are failing apparently due to replay on the standby leading to vacuum of some of the rows matching my query.

Is there support for an option where I can ask the Hot stand-by server to not bother about such changes to the rows (even the rows that were updated/deleted) and continue with the scan, for my query? Or, is dropping all queries where a matching row was cleaned up during replay, vaccumm something the server always does and there's no other way supported.

postgresql
1 Answers

You can use hot_standby_feedback to tell the primary server to not vacuum rows that the standby server is still using. If you are concerned about affecting the primary in this way, you could instead use one of max_standby_streaming_delay or max_standby_archive_delay (depending on if you are streaming or copying log files).

These are all detailed here: https://www.postgresql.org/docs/current/runtime-config-replication.html

user_8956400
Related