PgBouncer accumulating multiple connections in IDLE

Question

I use a PostgreSQL database managed by DigitalOcean, and to connect to it I use the connection pool provided by them.

The pooling system they use is PgBouncer, and the pooling model I use is Transaction mode.

However, I noticed that it ends up leaving many connections idle for up to 10 minutes, until they are closed.

And there are almost no connections with active status running, at the time of my query, this number of connections is between 0 and 1. Sometimes 5 appears and disappears quickly.

And when I see there are about 150 idle connections (today there were 250 idle connections!).

My application makes several AJAX connections, at each URL/script call, it connects to the database (never using persistent mode, but using SSL), and executes the queries.

Let's say a script will 5 queries to the database.

1. Script opens the database connection
2. Script executes the five queries in sequence
3. Script is finished

During this process I don't reopen the connection or close the connections after each query.

My application doesn't work with prepared statements either.

My question is, is this some glitch in my PHP application connecting to the database or normal PgBouncer behavior?

Answer 1

You should check server_idle_timeout value in the pgbouncer.ini config.

From https://www.pgbouncer.org/config.html

server_idle_timeout

If a server connection has been idle more than this many seconds it will be closed. If 0 then this timeout is disabled. [seconds]

Default: 600.0

Also in PostgreSQL 14 you can set the idle_session_timeout parameter to disconnect client sessions that are idle.