Re: Long running query causing XID limit breach

On Mon, May 27, 2024 at 2:50 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

>
> > > But again for HA , in case primary down we should not be in big lag
> for the standby
> > > and thus we want the standby also with minimal lag. And as you
> mentioned there will
> > > never be incorrect results but at amx it will be query cancellation,
> so I was thinking,
> > > if it's fine to just keep the "hot_feedback_standby" as OFF and let the
> > > max_standby_streaming_delay set as it is like 14 sec. Let me know your
> thoughts.
> >
> You cannot have it.
> Let me repeat: you cannot have it.
>
> The only way you can have no delay in replication AND no canceled queries
> is
> if you use two different standby servers with different settings for
> "max_standby_streaming_delay". One of the server is for HA, the other for
> your long-running queries.
>
> When you suggest having different max_standby_streaming_delay for first
replica (say 10 sec for High availability) and second replica(say -1 for
long running queries). Do you also suggest keeping "hot_feedback_standby"
as "OFF" for all the three instances i.e. master and both the replicas?

Also OP has added a few other parameters as below, do you think these
should be needed?
I think the master and first replica should have the same set up because
in case of any disaster to master the first replica should be able to take
the place of master.

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