PostgreSQL: Database Layer Pooling v/s Application Layer pooling

Question

I would like to have some recommendations about connection pooling in PostgreSQL. We already have connection pooling using Pgbouncer behind an Azure LB. The pgbouncer itself is working pretty much good and later we planning to change pooling method to transaction. However we recently found that the nodejs client lib has connection pooling using pg.pool routed to LB. Now we have two layer of pooling one at application layer and other one at the db.

Is it a good practice to have multiple layer of pooling? Or just pgbouncer enough to handle this considering the transaction pooling method? What are the things to consider which layer of pooling best for our system?

Answer 1

It is usually better to have a single connection pool. Having the pool in your application server is better, because the way from the application to the pool is shorter, so the time between one thread releasing the pooled connection and the next one using it can be shorter.

The above is only true if you have a single or few application servers. If you have lots of application servers, pooling in the application server is no longer effective. For that, you should have a single connection pool, and that would be pgBouncer. In that case, you could see if you get better performance with double pooling (a connection pool in the application server and pgBouner) or only using pgBouncer.