Re: Long running query causing XID limit breach

On Mon, May 27, 2024 at 12:55 AM Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
wrote:

> On Sun, May 26, 2024 at 8:46 PM sud <suds1434(at)gmail(dot)com> wrote:
>
>> Would you agree that we should have two standby, one with default
>> max_standby_streaming_delay (say 10 sec ) which will be mainly used as high
>> availability and thus will be having minimal lag. and another standby with
>> max_standby_streaming_delay as "-1" i.e. it will wait indefinitely for the
>> SELECT queries to finish without caring about the lag, which will be
>> utilized for the long running SELECT queries.
>>
>> And keep the hot_standby_feedback as ON for the first standby which is
>> used as HA/high availability. And keep the hot_standby_feedback as OFF for
>> the second standby which is utilized for long running SELECT queries, so
>> that primary won't be waiting for the response/feedback from this standby
>> to vacuum its old transactions and that will keep the transaction id wrap
>> around issue from not happening because of the Read/Select queries on any
>> of the standby.
>>
>
> Sure. That could work. Perhaps also set statement_timeout on the first
> replica, just in case.
>

Thank you so much. Yes, planning to set it like below. Hope i am doing it
correctly.

Master/Primary First Replica/Standby for High Availability Second Replica
for Reporting
hot_standby_feedback=ON hot_standby_feedback=ON hot_standby_feedback=OFF
max_standby_streaming_delay=10 sec max_standby_streaming_delay=10 sec
max_standby_streaming_delay=-1
(Infinite)
statement_timeout = "2hrs" statement_timeout="2hrs" No statement_timeout
i.e. infinite
idle_in_transaction_session_timeout=10minutes
idle_in_transaction_session_timeout=10minutes No
idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=100M autovacuum_freeze_max_age=100M
autovacuum_freeze_max_age=100M
Log_autovacuum_min_duration=0 Log_autovacuum_min_duration=0
Log_autovacuum_min_duration=0