1

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?

2
  • "I noticed that it ends up leaving many connections idle for up to 10 minutes" - well that's the whole point of a connection pooler. Don't physically close the connection so that it can be re-used later. As long as their state is "idle" there is nothing to worry about.
    – user1822
    Jun 1, 2022 at 13:33
  • @a_horse_with_no_name But that's his problem, because instead of using the old ones, he prefers to create new connections.
    – Tom
    Jun 1, 2022 at 20:20

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.