Re: Long running query causing XID limit breach - Mailing list pgsql-general

From sud
Subject Re: Long running query causing XID limit breach
Date
Msg-id CAD=mzVXiA=-21fVjv=a42ujcPB5mPt6ddqnE1h_KxJOY6XaE8A@mail.gmail.com
Whole thread Raw
In response to Re: Long running query causing XID limit breach  (Torsten Förtsch <tfoertsch123@gmail.com>)
List pgsql-general

On Mon, May 27, 2024 at 12:55 AM Torsten Förtsch <tfoertsch123@gmail.com> wrote:
On Sun, May 26, 2024 at 8:46 PM 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 high availability and thus will be having minimal lag. and another standby with max_standby_streaming_delay as "-1" 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.

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 for 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.

Thank you so much. Yes, planning to set it like below. Hope i am doing it correctly.

Master/PrimaryFirst Replica/Standby for High AvailabilitySecond Replica for Reporting
hot_standby_feedback=ONhot_standby_feedback=ONhot_standby_feedback=OFF
max_standby_streaming_delay=10 secmax_standby_streaming_delay=10 secmax_standby_streaming_delay=-1 (Infinite)
statement_timeout = "2hrs"statement_timeout="2hrs"No statement_timeout i.e. infinite
idle_in_transaction_session_timeout=10minutesidle_in_transaction_session_timeout=10minutesNo idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=100Mautovacuum_freeze_max_age=100Mautovacuum_freeze_max_age=100M
Log_autovacuum_min_duration=0Log_autovacuum_min_duration=0Log_autovacuum_min_duration=0
 

pgsql-general by date:

Previous
From: Torsten Förtsch
Date:
Subject: Re: Long running query causing XID limit breach
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: prevent users from SELECT-ing from pg_roles/pg_database