Re: Long running query causing XID limit breach

On Fri, May 24, 2024 at 10:34 AM sud <suds1434(at)gmail(dot)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..
>
> 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"?
>
> 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.
>
> Basically below are the combinations, i am confused between..
>
> hot_feedback_stanby ON and max_standby_streaming_delay=-1
> or
> hot_feedback_stanby OFF and max_standby_streaming_delay=-1
> Or
> hot_feedback_stanby ON and max_standby_streaming_delay=14 sec
> Or
> hot_feedback_stanby OFF and max_standby_streaming_delay=14 sec
>

As per my understanding here, this would be the behaviour. Others may
comment..

*hot_standby_feedback ON and max_standby_streaming_delay = -1:*
Ensures that long-running queries on the standby are not interrupted. The
primary waits indefinitely to avoid vacuuming rows needed by standby
queries.
But Can lead to significant replication lag and increased XID consumption
on the primary, potentially causing transaction ID wraparound issues.

*hot_standby_feedback OFF and max_standby_streaming_delay = -1:*
Ensures long-running queries on the standby are not interrupted. No
feedback is sent to the primary, reducing the risk of XID wraparound.
But The standby may fall significantly behind the primary, resulting in
high replication lag.

*hot_standby_feedback ON and max_standby_streaming_delay = 14 seconds:*
The primary prevents vacuuming rows needed by standby queries, reducing
query cancellations on the standby. The replication lag is limited to 14
seconds.
But Long-running queries on the standby that exceed 14 seconds may be
canceled, and the primary can still experience increased XID consumption.

*hot_standby_feedback OFF and max_standby_streaming_delay = 14 seconds:*
Limits replication lag to 14 seconds and reduces XID consumption on the
primary. Queries on the standby exceeding 14 seconds are canceled.
but Long-running queries on the standby are more likely to be canceled due
to the lack of feedback to the primary.