Re: Long running query causing XID limit breach

On Sun, May 26, 2024 at 2:24 AM yudhi s <learnerdatabase99(at)gmail(dot)com> wrote:

>
>
> *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.
>
>
>

Thank you so much.
Does it mean that the last one we should go for i.e. (*hot_standby_feedback
OFF and max_standby_streaming_delay = 14 seconds), *as because high
availability is also a key requirement in any production environment, so
keeping 14 seconds lag is kind of okay and also at the same time
keeping hot_standby_feedback OFF will make sure the transaction id
wraparound around won't happen because of any long running query on standby
as it won't wait for the stand by feedback for vacuuming the tables.

But i have one question here , does max_standby_streaming_delay = 14 ,
means the queries on the standby will get cancelled after 14 seconds?