Thread: Handling idle connections

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

1) configure idle timeouts

2) figure out where they're coming from and fix the client

> On Sep 24, 2023, at 7:03 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Could u give me a tip on how to figure out where they are coming and what do u mean exactly by saying "fixing" them

Look at pg_stat_activity. There's the application_name column--if your apps are not setting that, the query column
showslast/current query and that is sometimes a clue. (Also, if your clients are not setting application name, fix
them.)

If you don't want client applications leaving connections idle, then clients should release connections when not using
them.

On 2023-09-24 12:57 +0530, Rajesh Kumar wrote:
> What is the best way to handle idle connections other than manually killing
> idle connections if I do not have connection pooler.

Couple of options depending on your Postgres version and whether you
only care about idle connections with open transactions:

* set idle_in_transaction_session_timeout [1] for idle connections with
  an open transaction

* set idle_session_timeout [2] (requires pg14+) for idle connections
  without an open transaction

* use a cron job that checks connections in view pg_stat_activity and
  terminates them as necessary

[1]
https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/
[2] https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT

-- 
Erik

What is the best way to handle idle connections other than manually killing idle connections if I do not have connection pooler.

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

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

Here’s what I normally do.

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

2. Setup TCP/IP timeouts to flush out abandoned idle sessions (i.e. server crashes, people unplugging the network cable
withoutlogging 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.

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

Rajesh Kumar <rajeshkumar.dba09@gmail.com> writes:
> 1. I see lot of idle connections where application_name is blank and also
> query is blank, i am identifying only with user. In this case how do I
> identify which application is using idle connection?

If pg_stat_activity.query is empty, then that is a connection that has
*never* issued a query (because once it has, the query column reflects
the last query even when the session is idle).  So apparently you have
a rather broken application that connects and then does nothing.

I'd suggest fixing the client side so that it sets application_name to
something that would help identify the situation.  If the client is
libpq-based, you might be able to do this by setting the PGAPPNAME
environment variable in an invoking script, rather than modifying
any low-level code.

            regards, tom lane

> I am assuming like tell them "check sessions are closed properly" like dat?

Don't open until needed, close when done. Assuming they're actually a problem--for smaller scale environments, it can
beperfectly OK to have clients holding connections open for the entire client lifetime. 

Set application name on connection, so that *if* there is a problem it's easier to track down.

> On Sep 26, 2023, at 8:55 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> You haven't answered my question.  If "query" is truly blank and it is not just a permission problem, then the issue
isnot that they fail to close connections after use, but rather that they open connections and then never use them. 

Which could easily be a matter of some pg library being used. As far as I see, there was also never an answer to
whetherthis is actually a problem and if so, how? 

And one more: if there is a problem, why not use pgbouncer?

We've given all the information we can about how to identify such connections. Any further help would depend on more
infofrom Rajesh.