Re: Long running query causing XID limit breach

Hello Laurenz,

Thank you so much.This information was really helpful for us
understanding the working of these parameters.

One follow up question i have , as we are setting one of the
standby/replica with value idle_in_transaction_session_timeout=-1 which can
cause the WAL's to be heavily backlogged in a scenario where we have a
query running for very long time on that instance. So in that case will
there be chances of instance restart and if that can be avoided anyway?

And the plan is to set these system parameters with different values in
writer/read replica , so in that case if we apply the "alter system"
command on the primary , won't the WAL going to apply those same commands
forcibly on reader instance making those same as the writer instance
configuration( but we want the reader replica configuration to be different
from writer)?

Appreciate your guidance.

On Wed, May 29, 2024 at 1:38 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2024-05-29 at 01:34 +0530, yudhi s wrote:
> > > The only way you can have no delay in replication AND no canceled
> queries is
> > > if you use two different standby servers with different settings for
> > > "max_standby_streaming_delay". One of the server is for HA, the other
> for
> > > your long-running queries.
> >
> > When you suggest having different max_standby_streaming_delay for first
> replica
> > (say 10 sec for High availability) and second replica(say -1 for long
> running queries).
> > Do you also suggest keeping "hot_feedback_standby" as "OFF" for all the
> three
> > instances i.e. master and both the replicas?
>
> The parameter is ignored on the master.
> It needs to be off on the standby that is running long queries.
> For the other standby it probably doesn't matter if you are not running any
> queries on it. I would leave "hot_standby_feedback = off" there as well.
>
> Actually, I would set "hot_standby = off" on the standby that is only used
> for HA.
>
>
> - I would leave "hot_standby_feedback" off everywhere.
> - "max_standby_streaming_delay" should be -1 on the reporting standby and
> very
> low or 0 on the HA standby. It doesn't matter on the primary.
> - "statement_timeout" should be way lower on the first two nodes.
> - "idle_in_transaction_session_timeout" is good.
> - I would leave "autovacuum_freeze_max_age" at the default setting but 100
> million
> is ok too.
>
> Yours,
> Laurenz Albe
>