java - Not able to open connection from PgBouncer (Connection attempt timed out)

Question

We are using PgBouncer as the middle ware for connection pooling for our spring-based application. Within the application, we are using HikariCP for application-level connection pooling.

We have set maxLifeTime value equal to one hour for HikariCP and idleTimeOut is set to 30 seconds.

I have observed that we are getting this error when app get a request after a long idle time:

Caused by: org.postgresql.util.PSQLException: Connection attempt timed out. at 
org.postgresql.Driver$ConnectThread.getResult(Driver.java:376) at
org.postgresql.Driver.connect(Driver.java:288) at
java.sql.DriverManager.getConnection(DriverManager.java:664) at
java.sql.DriverManager.getConnection(DriverManager.java:247) at
org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:88) at
org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:71) at
com.zaxxer.hikari.pool.HikariPool.addConnection(HikariPool.java:398) at
com.zaxxer.hikari.pool.HikariPool.lambda$addBagItem$2(HikariPool.java:309) at 

Answer 1

Choose only one connection pool PGBouncer/HikariCP

Consider using either PGBouncer in front your Postgres installation or any library for your language that deals with reusable connections by its own. Say, for Clojure, HikariCP would be a good choice

Notice there's an open issue for Hikari to support PGBouncer

Each app is using hikariCp with a maxPool of 10 and I have ~10 apps using it. I have a PgPool in front but it doesn't seem to be reusing connection that much. It seems like each app instance is trying to maintain it's own pool alive all the time making PgPool useless.