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> <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
In-Reply-To: <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Sun, 26 May 2024 02:29:48 +0530
Message-ID: <CAD=mzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: yudhi s <learnerdatabase99@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000042e04d06194d918e"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw%40mail.gmail.com>
Precedence: bulk

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

On Sun, May 26, 2024 at 2:24=E2=80=AFAM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

>
>
> *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 du=
e
> 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 =3D 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 =3D 14 ,
means the queries on the standby will get cancelled after 14 seconds?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sun, May 26, 2024 at 2:24=E2=80=AFAM y=
udhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99=
@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_=
quote"><div><b>hot_standby_feedback ON and max_standby_streaming_delay =3D =
-1:<br></b><br>Ensures that long-running queries on the standby are not int=
errupted. The primary waits indefinitely to avoid vacuuming rows needed by =
standby queries.<br>But Can lead to significant replication lag and increas=
ed XID consumption on the primary, potentially causing transaction ID wrapa=
round issues.<br><br><b>hot_standby_feedback OFF and max_standby_streaming_=
delay =3D -1:<br></b><br>Ensures long-running queries on the standby are no=
t interrupted. No feedback is sent to the primary, reducing the risk of XID=
 wraparound.<br>But The standby may fall significantly behind the primary, =
resulting in high replication lag.<br><br><br><b>hot_standby_feedback ON an=
d max_standby_streaming_delay =3D 14 seconds:<br></b><br>The primary preven=
ts vacuuming rows needed by standby queries, reducing query cancellations o=
n the standby. The replication lag is limited to 14 seconds.<br>But Long-ru=
nning queries on the standby that exceed 14 seconds may be canceled, and th=
e primary can still experience increased XID consumption.<br><br><b>hot_sta=
ndby_feedback OFF and max_standby_streaming_delay =3D 14 seconds:<br></b><b=
r>=C2=A0Limits replication lag to 14 seconds and reduces XID consumption on=
 the primary. Queries on the standby exceeding 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></blockquote><div><br></div><div>Thank you so much.</div>=
<div>Does it mean that the last one we should go for i.e. (<b>hot_standby_f=
eedback OFF and max_standby_streaming_delay =3D 14 seconds), </b>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 keepin=
g=C2=A0hot_standby_feedback OFF will make sure the transaction id wraparoun=
d around won&#39;t=C2=A0happen because of any long running query on standby=
 as it won&#39;t wait for the stand by feedback for vacuuming the tables.<b=
r></div><div><br></div><div>But i have one question here , does=C2=A0max_st=
andby_streaming_delay =3D 14 , means the queries on the standby will get ca=
ncelled after 14 seconds?</div></div></div>

--00000000000042e04d06194d918e--