MIME-Version: 1.0
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
 <abd3bc064d16bc93a2d8661a692903da97d2c154.camel@cybertec.at>
 <CAD=mzVVvK8xk-9m8h3Xu27cGN7BW329HKYdO+0EMXfWvSD3AGA@mail.gmail.com>
 <bed28c629a839b1f354e18f416a87fd5f4f78ba7.camel@cybertec.at>
 <CAD=mzVVqR-mKUFHetsejFWSPQPbLjTVhCmBebJTFX5XmYp+nGg@mail.gmail.com>
 <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at> <CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com>
In-Reply-To: <CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sun, 26 May 2024 02:24:20 +0530
Message-ID: <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: sud <suds1434@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000afede006194d7d81"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn%2BakhMAWEOGw%40mail.gmail.com>
Precedence: bulk

--000000000000afede006194d7d81
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, May 24, 2024 at 10:34=E2=80=AFAM 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 que=
ry
> 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 whi=
ch
> 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 infinitel=
y
> , till the query completes on the standby and wont apply the WAL which ca=
n
> cause override of the XID which the standby query is reading from. But wo=
nt
> this same behaviour be happening while we have hot_feedback_standby set a=
s
> "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 yo=
u
> mentioned there will never be incorrect results but at amx it will be que=
ry
> 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=3D-1
> or
> hot_feedback_stanby OFF and max_standby_streaming_delay=3D-1
> Or
> hot_feedback_stanby ON and max_standby_streaming_delay=3D14 sec
> Or
> hot_feedback_stanby OFF and max_standby_streaming_delay=3D14 sec
>

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


*hot_standby_feedback ON and max_standby_streaming_delay =3D -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 =3D -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 =3D 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 =3D 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.

--000000000000afede006194d7d81
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, May 24, 2024 at 10:34=E2=80=
=AFAM sud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div dir=3D"ltr">I am trying to understand these two parameters=
 and each time it looks a bit confusing to me. If These two parameters comp=
lement or conflict with each other.<br></div><div class=3D"gmail_quote"><di=
v><br></div><div>Say for example, If we set hot_feedback_standby to ON (whi=
ch is currently set as default ON by the way), it will make the primary wai=
t till the query completion at standby and can cause such a high bump in XI=
D 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,=C2=A0to=
 avoid the=C2=A0transaction ID wrap around issue..</div><div><br></div><div=
>But as you also mentioned to set the &quot;max_standby_streaming_delay&quo=
t; to -1 (which is currently set as 14 second=C2=A0in our case) ,it will wa=
it infinitely , till the query completes=C2=A0on the standby and wont apply=
 the WAL which can cause override of the XID which the standby query is rea=
ding from. But wont this same behaviour be happening while we have hot_feed=
back_standby set as &quot;ON&quot;?</div><div><br></div><div>But again for =
HA , in case primary down we should not be in big lag for the standby and t=
hus we want the standby also with minimal lag. And as you mentioned there w=
ill never be incorrect results but at amx it will be query cancellation, so=
 I was thinking , if it&#39;s=C2=A0fine to just keep the &quot;hot_feedback=
_standby&quot; as OFF and let the max_standby_streaming_delay set as it is =
like 14 sec. Let me know your thoughts.</div><div><br></div><div>Basically =
below are the combinations, i am confused between..=C2=A0</div><div><br></d=
iv><div>hot_feedback_stanby ON and max_standby_streaming_delay=3D-1</div><d=
iv>or</div><div>hot_feedback_stanby OFF and max_standby_streaming_delay=3D-=
1<br></div><div>Or</div><div>hot_feedback_stanby ON and max_standby_streami=
ng_delay=3D14 sec<br></div><div>Or</div><div>hot_feedback_stanby OFF and ma=
x_standby_streaming_delay=3D14 sec<br></div></div></div></blockquote><div><=
br></div><div>As per my understanding here, this would be the behaviour. Ot=
hers may comment..</div><div><br></div><div><br><b>hot_standby_feedback ON =
and max_standby_streaming_delay =3D -1:<br></b><br>Ensures that long-runnin=
g queries on the standby are not interrupted. The primary waits indefinitel=
y to avoid vacuuming rows needed by standby queries.<br>But Can lead to sig=
nificant replication lag and increased XID consumption on the primary, pote=
ntially causing transaction ID wraparound issues.<br><br><b>hot_standby_fee=
dback OFF and max_standby_streaming_delay =3D -1:<br></b><br>Ensures long-r=
unning queries on the standby are not interrupted. No feedback is sent to t=
he primary, reducing the risk of XID wraparound.<br>But The standby may fal=
l significantly behind the primary, resulting in high replication lag.<br><=
br><br><b>hot_standby_feedback ON and max_standby_streaming_delay =3D 14 se=
conds:<br></b><br>The primary prevents vacuuming rows needed by standby que=
ries, reducing query cancellations on the standby. The replication lag is l=
imited to 14 seconds.<br>But Long-running queries on the standby that excee=
d 14 seconds may be canceled, and the primary can still experience increase=
d XID consumption.<br><br><b>hot_standby_feedback OFF and max_standby_strea=
ming_delay =3D 14 seconds:<br></b><br>=C2=A0Limits replication lag to 14 se=
conds and reduces XID consumption on the primary. Queries on the standby ex=
ceeding 14 seconds are canceled.<br>but Long-running queries on the standby=
 are more likely to be canceled due to the lack of feedback to the primary.=
<br></div><div><br></div><div>=C2=A0</div></div></div>

--000000000000afede006194d7d81--