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 <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1qkTdo-00EbBd-2W
	for pgsql-admin@arkaria.postgresql.org; Sun, 24 Sep 2023 18:15:36 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1qkTdm-00A2oe-I0
	for pgsql-admin@arkaria.postgresql.org; Sun, 24 Sep 2023 18:15:34 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <rui@crazybean.net>)
	id 1qkTdm-00A2oO-5t
	for pgsql-admin@lists.postgresql.org; Sun, 24 Sep 2023 18:15:34 +0000
Received: from impout008aa.msg.chrl.nc.charter.net ([47.43.20.32]
 helo=impout008.msg.chrl.nc.charter.net)
	by makus.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <rui@crazybean.net>)
	id 1qkTdj-006PKg-IL
	for pgsql-admin@lists.postgresql.org; Sun, 24 Sep 2023 18:15:32 +0000
Received: from cerberus.crazybean.net ([68.116.174.119])
	by cmsmtp with ESMTP
	id kTdhqwO5fwCOhkTdhqBXXf; Sun, 24 Sep 2023 18:15:30 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.4 cv=TJRW9npa c=1 sm=1 tr=0 ts=65107cc2
 a=Cf+DCfj9UYuaXH9fDMmlHQ==:117 a=Cf+DCfj9UYuaXH9fDMmlHQ==:17
 a=xqWC_Br6kY4A:10 a=IkcTkHD0fZMA:10 a=zNV7Rl7Rt7sA:10 a=pGLkceISAAAA:8
 a=BsyV3F7GgGSDLEBUfIYA:9 a=QEXdDO2ut3YA:10
Received: from smtpclient.apple (<unknown>
 [2001:470:e117:4:9daf:f604:da6d:6bd3])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id b44f80e4
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Sun, 24 Sep 2023 14:15:28 -0400 (EDT)
Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.4\))
Subject: Re: Handling idle connections
From: Rui DeSousa <rui@crazybean.net>
In-Reply-To: 
 <CAJk5Ata=NXO46e7RcS-CxEXvroz=-fojS+XzpW-Bty+G-sx51A@mail.gmail.com>
Date: Sun, 24 Sep 2023 14:15:28 -0400
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <AB0BD6D8-DFE1-4918-9AF6-0A9A1ED5CEBE@crazybean.net>
References: 
 <CAJk5Ata=NXO46e7RcS-CxEXvroz=-fojS+XzpW-Bty+G-sx51A@mail.gmail.com>
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
X-Mailer: Apple Mail (2.3696.120.41.1.4)
X-CMAE-Envelope: 
 MS4xfKycVd1BkQs1kpG7yqBejMDKOUwbpU7BVW28ulIo6FjFTiOM6XveoXYT37dinExXgyC/B8c6JnliHFh2XcwQastSmHCQj3Y/hNznhokxuOjaKL93W6JK
 8GVg1yZx7YePskqCMXwVoEeBND6QCalZ0k7GeelI1ZdvEEd1TWn9OL2MzypqQlzEGvwP5oIG2Emumjjb1StrUnD5/zWXiUbQHoHhQZqdXZkuhLY91rwvnSdI
 icD7I6jN1AjT5mLQ4wyc5w==
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/AB0BD6D8-DFE1-4918-9AF6-0A9A1ED5CEBE%40crazybean.net>
Precedence: bulk



> On Sep 24, 2023, at 3:27 AM, Rajesh Kumar =
<rajeshkumar.dba09@gmail.com> wrote:
>=20
> Hi
>=20
> What is the best way to handle idle connections other than manually =
killing idle connections if I do not have connection pooler.

Are they actually a problem? Depending on your application, it may be =
doing connection pooling (most do) and/or killing the sessions could =
create problems for the application.

Here=E2=80=99s what I normally do. =20

1. Setup idle_in_transaction_session_timeout =E2=80=94 these are idle =
sessions with open transactions.  This is most likely a bug in the =
application or a very bad practice.  Applications shouldn=E2=80=99t be =
leaving open transaction idle for long periods of time =E2=80=94 at most =
a few minutes.  I seen environments where the thought was hour long idle =
in sessions transactions was considered normal; it=E2=80=99s not.  We =
had to create tickets to manually kill them after an hour or when they =
started to create issues. The development team was non responsive in =
fixing these issues and the DBA team didn=E2=80=99t have any authority =
to mandate it.

2. Setup TCP/IP timeouts to flush out abandoned idle sessions (i.e. =
server crashes, people unplugging the network cable without logging out, =
firewall dropping the connection without sending reset packets, etc).

3. Setup up a cron job to kill long idle sessions from non applications =
accounts/networks.=20

Killing idle sessions just because they are idle could cause problems =
for the application.  I seen one application a that would detect =
multiple bad connections and then do a hard reboot.  Killing idle =
sessions would cause the entire application to go down and reset itself =
resulting in the site not being available for couple of minutes.  Point =
is, know your application and why it currently has idle sessions before =
killing them indiscriminately.=