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, 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