MIME-Version: 1.0
References: 
 <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
In-Reply-To: 
 <EA2PR84MB3780EB1126501FD490A07807A91AA@EA2PR84MB3780.NAMPRD84.PROD.OUTLOOK.COM>
From: Imran Khan <imran.k.23@gmail.com>
Date: Tue, 30 Sep 2025 15:36:48 +0300
Message-ID: 
 <CAC4eXDhX7Zw5w8Z52USrCZFT6W8y30nZaaouwTtW0COb=cvtzQ@mail.gmail.com>
Subject: Re: canceling/terminating statement due to conflict with recovery in
 Replica/DR instances
To: Ishan joshi <ishanjoshi@live.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002beb4e06400402ca"
Archived-At: 
 <https://www.postgresql.org/message-id/CAC4eXDhX7Zw5w8Z52USrCZFT6W8y30nZaaouwTtW0COb%3DcvtzQ%40mail.gmail.com>
Precedence: bulk

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

Hi Isha,

 I believe you have partitions and correct type of indexes created for
those tables. Also, is this 25 TB size grown over many years or just few
years old? Parameters tuning can help but won't be a permanent solution.
Having multiple replicas I believe can make sense at this point.

Thanks,
Imran

On Tue, Sep 30, 2025, 8:59=E2=80=AFAM Ishan joshi <ishanjoshi@live.com> wro=
te:

> Hi Team,
>
> We are using Postgresql 16.9 in production and with large database about
> 25TB of size. We have patroni setup with replica instance and DR patroni
> setup with patroni streaming.
>
> We have high volume and frequent commit in the database. There are few
> large tables for which we asked client to execute queries on DR/Replica
> instances but these queries are start getting failed with "canceling
> statement due to conflict with recovery" and "terminating statement due t=
o
> conflict with recovery" error.
>
> As I understand the behavior is correct but we need to get rid of this
> issue.
>
> I gone through the old posts and some documentation and got to know that
> below parameters can help to reduce this error.
>
> max_standby_streaming_delay
> max_standby_archive_delay
> hot_standby_feedback =3D off
>
> Our queries are running for long period that makes me to set this value t=
o
> some minutes/hours (lets set 900s) which is not feasible for production a=
s
> it will start impacting the replication lag. Also, the queries will fail =
if
> it reaches to mentioned thresholds.
>
> If I set these parameters to "-1" (disable) then there will be direct
> impact on replication lag which will impact further queries on replica no=
de
> and DR cluster.
>
> Can you please guide If any other better solution present for such
> scenario?
>
> Thanks & Regards,
> Ishan Joshi
>

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

<div dir=3D"auto">Hi Isha,=C2=A0<div dir=3D"auto"><br></div><div dir=3D"aut=
o">=C2=A0I believe you have partitions and correct type of indexes created =
for those tables. Also, is this 25 TB size grown over many years or just fe=
w years old? Parameters tuning can help but won&#39;t be a permanent=C2=A0s=
olution. Having multiple replicas I believe can make sense at this=C2=A0poi=
nt.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks,=C2=A0</div><=
div dir=3D"auto">Imran</div></div><br><div class=3D"gmail_quote gmail_quote=
_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Sep 30, 2025, 8:5=
9=E2=80=AFAM Ishan joshi &lt;<a href=3D"mailto:ishanjoshi@live.com">ishanjo=
shi@live.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">


<div dir=3D"ltr">
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
Hi Team,<br>
<br>
We are using Postgresql 16.9 in production and with large database about 25=
TB of size. We have patroni setup with replica instance and DR patroni setu=
p with patroni streaming.</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
We have high volume and frequent commit in the database. There are few larg=
e tables for which we asked client to execute queries on DR/Replica instanc=
es but these queries are start getting failed with &quot;canceling statemen=
t due to conflict with recovery&quot; and
 &quot;terminating statement due to conflict with recovery&quot; error.<br>
<br>
As I understand the behavior is correct but we need to get rid of this issu=
e.<br>
<br>
I gone through the old posts and some documentation and got to know that be=
low parameters can help to reduce this error.=C2=A0<br>
<br>
max_standby_streaming_delay=C2=A0</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
max_standby_archive_delay=C2=A0</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
hot_standby_feedback =3D off</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
Our queries are running for long period that makes me to set this value to =
some minutes/hours (lets set 900s) which is not feasible for production as =
it will start impacting the replication lag. Also, the queries will fail if=
 it reaches to mentioned thresholds.<br>
<br>
If I set these parameters to &quot;-1&quot; (disable) then there will be di=
rect impact on replication lag which will impact further queries on replica=
 node and DR cluster.</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
Can you please guide If any other better solution present for such scenario=
?</div>
<div id=3D"m_3890055601400987236Signature">
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
Thanks &amp; Regards,</div>
<div style=3D"font-family:Calibri,Helvetica,sans-serif;font-size:12pt;color=
:rgb(0,0,0)">
Ishan Joshi</div>
</div>
</div>

</blockquote></div>

--0000000000002beb4e06400402ca--