So I've recently become aware of PgBouncer and I'm struggling to understand how it replaces a connection pool on a multi-threaded server. I looked over HikariCP/Apache DBCP2 and PgBouncer but it doesn't seem to address that.
Suppose I have a Java server that handles a request per thread, and it connects to the db through JDBC. JDBC drivers are supposed to be thread-safe, but requests on the same connection can only be served one at a time. A pool like Hikari allows each thread to grab an existing connection (saving time by not having to establish a new connection per request), and then use it exclusively for db transactions within that thread (saving time when there's other simultaneous requests).
How exactly does pgbouncer solve both of those problems? If I use a single global connection to pgbouncer that is shared by all the threads, we still have the blocking problem. If I create a connection per request, we still have the overhead of establishing a connection with pgbouncer (which I understand is cheaper than connecting to the db, but this still seems odd).
If you are using middleware connection pool it doesn’t makes sense to also have connection pools on the instances side or have one connection to pool per instance. For each request to DB instance should establish new connection to pgbouncer. As connection to pgbouncer is a regular TCP connection in opposite to connection to DB being CPU and memory expensive it should be cheap to establish them.Yes, it will add some latency, but if you are using pgbouncer in the same datacenter as instances it should be something around few ms for AWS. If you have multiple instances accessing DB using shared pool instead of instance local pool this latency overhead could be a lot smaller than gain you will get from managing connections more efficiently