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.
- Script opens the database connection
- Script executes the five queries in sequence
- 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?