> On Fri, May 24, 2024 at 10:34 AM sud <suds1434@gmail.com> wrote:
> > I am trying to understand these two parameters and each time it looks a bit confusing to me.
> > If These two parameters complement or conflict with each other.
> >
> > Say for example, If we set hot_feedback_standby to ON (which is currently set as
> > default ON by the way), it will make the primary wait till the query completion at
> > standby and can cause such a high bump in XID in scenarios where the query on standby
> > runs for days(like in our current scenario which happens). So we were thinking of
> > setting it as OFF, to avoid the transaction ID wrap around issue..
"hot_standby_feedback" is not "on" by default; you must have changed it.
This parameter will not make anything wait. The effect is that VACUUM on the
primary server won't remove any old row versions that are still needed by a
query on the standby. This holds back the "xmin" horizon and can lead to
bloat and, if you consume transaction IDs quickly, to wraparound problems.
> > But as you also mentioned to set the "max_standby_streaming_delay" to -1 (which is
> > currently set as 14 second in our case) ,it will wait infinitely , till the query
> > completes on the standby and wont apply the WAL which can cause override of the XID
> > which the standby query is reading from. But wont this same behaviour be happening
> > while we have hot_feedback_standby set as "ON"?
"It" is the standby, where you set the parameter. The primary is not affected by that
at all. If there is a replication conflict on the standby, replay of the WAL information
is delayed until the query is done.
There is no conflict between these settings; they do something different.
> > 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.
Yours,
Laurenz Albe