Re: Persistent Connections

On Tue, Jun 23, 2020 at 2:29 PM Bee.Lists <bee(dot)lists(at)gmail(dot)com> wrote:

> I have an issue with a server (v10) that’s seeing increasing connections
> until it’s maxxed-out.
>
> max_connections for my 4-core server is set to 12.
>
> I’ve installed pg_stat_activity and pg_stat_statements.
>

Do you see anything in pg_stat_activity that stays idle for a while and
then *does* disappear on its own? Perhaps some types of connections are
doing client side/application stuff before telling the DB to close the
connection.

> pg_stat_activity has shown a handful of idle queries that are quite
> simple. I’ve tested those queries and they seem fine on a workstation
> client. I’m assuming these queries somehow don’t finish and leave the
> connection open, but I could be wrong. All of this is new to me.
>

Idle means the query finished and that was the last query run. It isn't
active or waiting on another process, that connection is open by idle.

> I am running no connection pool tool of any sort, and I’ve never used
> one.
>

It sounds like a good time to set one up.

> How can I figure this out so the connections remain within the
> max_connections limit, and connections are not dropped?
>

I would increase the limit directly, or with a pooler and research which
connections are behaving, and which are taking too long to close or not
closing at all. You could set up a process to snapshot pg_stat_activity
every minute or 5 and trace which pids are terminating properly, and/or
make logging very verbose.