6

After switching to PgBouncer in a Spring application that uses HikariCP(or Apache DBCP2) do I need to make additional configuration for HikriCP(or Apache DBCP2) side?

Radu Dumbrăveanu
  • 1,266
  • 1
  • 22
  • 32
  • Why you need 2 connection pool tools? See https://stackoverflow.com/questions/55161884/spring-boot-2-1-app-without-hikaricp-connection-pooler – Ori Marko Jun 13 '19 at 13:55
  • 2
    There's an open issue https://github.com/brettwooldridge/HikariCP/issues/1042 – Ori Marko Jun 24 '19 at 12:19

1 Answers1

9

If you are using PgBouncer, then you definitely don't need HikariCP around.

The reasoning is simple, a Database connection pool was invented to protect the database from the huge memory and performance cost of establishing a Database Connection and tearing it down to the server (A Database Connection is not a TCP connection, it is much more than that). The database connection pool accomplishes that by reusing already establish backend connections taking place. And this is already fulfilled with PgBouncer.

The space between HikariCP and PgBouncer is no longer a Database connection world, but rather a TCP connection, which is much more cheaper to construct specially in the same data center and doesn't require extra memory and CPU to construct.

Infact, using HikariCP infront of PgBouncer is like using an HTTP Connection Pool to re-use HTTP connections in-front your NGINX Webserver. Which I don't think anyone would find useful.

Also, having a Hikari connection pool for an application will limit the maximum connections it can use when it's heavy loaded. So let's say you have Microservice A, Microservice B and Microservice C. And let's assume microservice A requires high throughput to the database while B and C are light loaded throughput. Having HikariCP with a pool size of 10 on each microservice will just create a bottle neck for microservice A completely and will require you go through some performance optimization drills to resize the pool every now and then which is completely unnecessary.

DISCLAIMER: I'm curious of any counter arguments against what I just mentioned.

Basil Musa
  • 8,198
  • 6
  • 64
  • 63
  • What kind of datasource class should I use in the bean declaration? e.g. ` ...` – Radu Dumbrăveanu Jul 14 '20 at 14:02
  • 2
    This is a good point; basically what you've written here is true, but there seems to be no alternative for DataSource implementation in Spring that doesn't actually create new DB connection each time you communicate with PgBouncer, which leads to extreme performance drops compared to Hikari + PgBouncer. Do you have any suggestions on how to solve this problem? – wcislo Oct 06 '21 at 12:01
  • I just recently learned about pgbouncer. What I'm having a hard time understanding is how is this affected by multithreading? Let's say I have 4 threads that are trying to talk to the database at the same time, if I have a single shared connection in my app the db requests will be sequential and not parallel. If you have an app level pool (hikari or otherwise), each thread can use its own connection. Am I missing something here? – aiguofer Feb 15 '23 at 23:53