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>
 <CAD=mzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw@mail.gmail.com>
 <CAKkG4_nLNSDSDd7emC+p4rEicmeCBCBJozzy3QRFvO7BWh3SRA@mail.gmail.com>
 <CAD=mzVUzDD_XDa+BJ8fH96pEwgTG1NHUvaoyVePLhp3xEN=J9A@mail.gmail.com>
 <CAKkG4_npZ8Mn-KeT2=vyOgbr3B37bL5ZfbA5y=Yv3RwhBioHXQ@mail.gmail.com> <CAD=mzVUGzU3vEMp4AY17vG1xDkVjOCUhuXnLt3NnAB9jhSyfRA@mail.gmail.com>
In-Reply-To: <CAD=mzVUGzU3vEMp4AY17vG1xDkVjOCUhuXnLt3NnAB9jhSyfRA@mail.gmail.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Sun, 26 May 2024 21:25:26 +0200
Message-ID: <CAKkG4_nAivibkJigR1Pwtd5J7SkCWWMjivPbMLSgykKJa1AM0Q@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009d2d900619605d8f"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_nAivibkJigR1Pwtd5J7SkCWWMjivPbMLSgykKJa1AM0Q%40mail.gmail.com>
Precedence: bulk

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

On Sun, May 26, 2024 at 8:46=E2=80=AFPM sud <suds1434@gmail.com> wrote:

> Would you agree that we should have two standby, one with default
> max_standby_streaming_delay (say 10 sec ) which will be mainly used as hi=
gh
> availability and thus will be having minimal lag. and another standby wit=
h
> max_standby_streaming_delay as "-1" i.e. it will wait indefinitely for th=
e
> SELECT queries to finish without caring about the lag, which will be
> utilized for the long running SELECT queries.
>
> And keep the hot_standby_feedback as ON for the first standby which is
> used as HA/high availability. And keep the hot_standby_feedback as OFF fo=
r
> the second standby which is utilized for long running SELECT queries, so
> that primary won't be waiting for the response/feedback from this standby
> to vacuum its old transactions and that will keep the transaction id wrap
> around issue from not happening because of the Read/Select queries on any
> of the standby.
>

Sure. That could work. Perhaps also set statement_timeout on the first
replica, just in case.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, May 26, 2024 at 8:46=E2=80=AFPM s=
ud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wro=
te:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">Would you agree that we sh=
ould have two standby, one with default max_standby_streaming_delay (say 10=
 sec ) which will be mainly used as high availability and thus will be havi=
ng minimal lag. and another standby with max_standby_streaming_delay as &qu=
ot;-1&quot; i.e. it will wait indefinitely for the SELECT queries to finish=
 without caring about the lag, which will be utilized for the long running =
SELECT queries.<br></div><div class=3D"gmail_quote"><br><div>And keep the h=
ot_standby_feedback as ON for the first standby which is used as HA/high av=
ailability. And keep the hot_standby_feedback as OFF for the second standby=
 which is utilized for long running SELECT queries, so that primary won&#39=
;t be waiting for the response/feedback from this standby to vacuum its old=
 transactions and that will keep the transaction id wrap around issue from =
not happening because of the Read/Select queries on any of the standby.=C2=
=A0</div></div></div></blockquote><div><br></div><div>Sure. That could work=
. Perhaps also set statement_timeout on the first replica, just in case.</d=
iv></div></div>

--0000000000009d2d900619605d8f--