3

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
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
Munish Dhiman
  • 511
  • 1
  • 7
  • 22
  • Why not using only one connection pool Hikari / PgBouncer ? – Ori Marko Sep 02 '19 at 11:38
  • HikariCP was already part of the application and DevOps team added the PgBouncer thing later on. It is also not clear to me if PgBouncer will let Hikari to pool the connection or not. – Munish Dhiman Sep 02 '19 at 11:44

1 Answers1

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.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • Thank you for this link, I already read this link but it was not very conclusive to me. No such other articles are available online at this moment, unfortunately. We will try to go with one connection pooling approach as recommended by you and will see how it works. – Munish Dhiman Sep 02 '19 at 11:50