Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sBRAp-00GZPY-GH for pgsql-general@arkaria.postgresql.org; Mon, 27 May 2024 03:37:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sBRAn-0059lJ-Ke for pgsql-general@arkaria.postgresql.org; Mon, 27 May 2024 03:37:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sBRAn-0059l4-6U for pgsql-general@lists.postgresql.org; Mon, 27 May 2024 03:37:21 +0000 Received: from mail-vk1-xa31.google.com ([2607:f8b0:4864:20::a31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sBRAf-000x4n-PT for pgsql-general@lists.postgresql.org; Mon, 27 May 2024 03:37:20 +0000 Received: by mail-vk1-xa31.google.com with SMTP id 71dfb90a1353d-4e1c721c040so1836308e0c.3 for ; Sun, 26 May 2024 20:37:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716781032; x=1717385832; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=5w5L+bTjDUNfrKMzavxhJAEffxGe+QdMoTLZHliuqgo=; b=iPJC4tdeCyiGueC70x+u58iyusYSRLdJ4lOhYS36DA81XXFFH+8WnJAb0R/0FerylP CYYVuMIgT10EEBRINbJnwCZb1H7pQ9uz4VsBi+F+UMqbNVEqCJg9tdNrU/4tB+Ji8/2v QJ/0lLbLH4lWH6DiLxHrRoG9EPPllX9tRwPMze9+onZctVSsuD7H+/5PsSXrSer2QF9F 6looJCs3Ap0mlexeGjJu1vWd77cCdEdbeBtJFPzhO5ZaVd4e4MNyG9TyyNJux7pTcxAr B2KTnFl+1Q3Ph8mvi+ePQxwIHPI8vD2j3ACyCwHqBMlxBZtSnbY3vBfMi7lyMjfBjg7M 4prA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716781032; x=1717385832; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=5w5L+bTjDUNfrKMzavxhJAEffxGe+QdMoTLZHliuqgo=; b=bF7W5DUvMO0ttZ78cM0aF8vnMOl99ILXKB4uMq30R4xOaP+agTzKbsKywDsGxsnJLP o9ZUPYWXq0nEsRCIWGOyEnqE1KHi/9pyEb9hc4XGQ06IphNsonE25eaQMy5LUzwns83k TBUGcjXG18wyPcl5qqj7mpCcTgZf1iZC9WNsdLbqNbVWj+ONoTzFB2hAZrOazmbCVw03 OGu7eLsW6dmPZyQV4MwudWlJqc3LOv668e0fXwD9bd/pDB1CeSaCnfH8OXgn6Yb1mfRC KqhRu+xMyZ+M4y6oj6y2sXRt/Ag6gbXGX2gDelLk90u0tlark6LTjUTgVMkTmo+xHboS l3Fg== X-Gm-Message-State: AOJu0YzHV5m9WolekMYcm4qVgDpV4O0kDSsZRSCEGKqelnAGvwKAGtc+ YKxkxgTlx0BRnZDTbfDv5MEdyDXpNeqPHSaCVfOtxzi2Bj3UXuFxIY6rLfwt9D8NO6aKnQi52uq T+WhGkBrZ3N2AnuoCbiAidK8Wze4= X-Google-Smtp-Source: AGHT+IFPwMIf5DYgMFx8vey5wBfw1uaLIKAzApRX3LWHQeVCpr/rdIvawjrF2zCnjx5eRuwOfdFZ899eqyLRcErzN5E= X-Received: by 2002:a05:6122:2211:b0:4e4:e9db:6b22 with SMTP id 71dfb90a1353d-4e4f0222ac8mr7507197e0c.3.1716781032168; Sun, 26 May 2024 20:37:12 -0700 (PDT) MIME-Version: 1.0 References: <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at> In-Reply-To: From: sud Date: Mon, 27 May 2024 09:07:00 +0530 Message-ID: Subject: Re: Long running query causing XID limit breach To: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009466160619673bf3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009466160619673bf3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, May 27, 2024 at 12:55=E2=80=AFAM Torsten F=C3=B6rtsch wrote: > On Sun, May 26, 2024 at 8:46=E2=80=AFPM sud 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 h= igh >> availability and thus will be having minimal lag. and another standby wi= th >> max_standby_streaming_delay as "-1" i.e. it will wait indefinitely for t= he >> 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 f= or >> the second standby which is utilized for long running SELECT queries, so >> that primary won't be waiting for the response/feedback from this standb= y >> to vacuum its old transactions and that will keep the transaction id wra= p >> around issue from not happening because of the Read/Select queries on an= y >> 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/Primary First Replica/Standby for High Availability Second Replica for Reporting hot_standby_feedback=3DON hot_standby_feedback=3DON hot_standby_feedback=3D= OFF max_standby_streaming_delay=3D10 sec max_standby_streaming_delay=3D10 sec max_standby_streaming_delay=3D-1 (Infinite) statement_timeout =3D "2hrs" statement_timeout=3D"2hrs" No statement_timeou= t i.e. infinite idle_in_transaction_session_timeout=3D10minutes idle_in_transaction_session_timeout=3D10minutes No idle_in_transaction_session_timeout i.e. infinite autovacuum_freeze_max_age=3D100M autovacuum_freeze_max_age=3D100M autovacuum_freeze_max_age=3D100M Log_autovacuum_min_duration=3D0 Log_autovacuum_min_duration=3D0 Log_autovacuum_min_duration=3D0 --0000000000009466160619673bf3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Mon, May 27, 2024 at 12:55= =E2=80=AFAM Torsten F=C3=B6rtsch <tfoertsch123@gmail.com> wrote:
On Sun, May 26,= 2024 at 8:46=E2=80=AFPM 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 use= d as high availability and thus will be having minimal lag. and another sta= ndby with max_standby_streaming_delay as "-1" i.e. it will wait i= ndefinitely 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 t= he first standby which is used as HA/high availability. And keep the hot_st= andby_feedback as OFF for the second standby which is utilized for long run= ning SELECT queries, so that primary won't be waiting for the response/= feedback from this standby to vacuum its old transactions and that will kee= p the transaction id wrap around issue from not happening because of the Re= ad/Select queries on any of the standby.=C2=A0

Sure. That could work. Perhaps also set statement_tim= eout 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/Primary First Replica/Standby for High Availability Second Replica for Reporting
hot_standby_feedback=3DON hot_standby_feedback=3DON<= /td> hot_standby_feedback=3DOFF=
max_standby_streaming_delay=3D10 sec max_standby_streaming_dela= y=3D10 sec max_standby_streaming_dela= y=3D-1 (Infinite)
statement_timeout =3D "2hrs" statement_timeout=3D"= 2hrs" No statement_timeout i.e. infinite
idle_in_transaction_session_timeout=3D10minutes idle_in_transaction_sessio= n_timeout=3D10minutes No idle_in_transaction_ses= sion_timeout i.e. infinite
autovacuum_freeze_max_age=3D100M autovacuum_freeze_max_age= =3D100M autovacuum_freeze_max_age= =3D100M
Log_autovacuum_min_duration=3D0 Log_autovacuum_min_duratio= n=3D0 Log_autovacuum_min_duratio= n=3D0
=C2=A0
--0000000000009466160619673bf3--