Re: canceling/terminating statement due to conflict with recovery in Replica/DR instances

Hi Laurenz

Thanks for all the answers you give on this list.
Could you elaborate on why two or more standby servers would help in this
case ?

Med venlig hilsen

Peter Gram
Sæbyholmsvej 18
2500 Valby

Mobile: (+45) 5374 7107
Email: peter(dot)m(dot)gram(at)gmail(dot)com

On Tue, 30 Sept 2025 at 08:17, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2025-09-30 at 05:59 +0000, Ishan joshi wrote:
> > 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 to 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 = off
> >
> > 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.
> >
> > If I set these parameters to "-1" (disable) then there will be direct
> impact on
> > replication lag which will impact further queries on replica node and DR
> cluster.
> >
> > Can you please guide If any other better solution present for such
> scenario?
>
> No, there is no better solution.
>
> You can reduce replication conflicts by turning on "hot_standby_feedback"
> and by
> turning off "vacuum_truncate", but you probably won't be able to get rid
> of all
> replication conflicts.
>
> You can either have a small replay delay and canceled queries or no
> canceled
> queries, but the occasional replay delay.
>
> If you need both no delay and no canceled queries, the only clean solution
> is
> to have two standby servers.
>
> Yours,
> Laurenz Albe
>
>
>