What are the pros and cons of client-side connect pools vs external connection pools for PostgreSQL?

Question

Given a PostgreSQL database that is reasonably configured for its intended load what factors would contribute to selecting an external/middleware connection pool (i.e. pgBouncer, pgPool) vs a client-side connection pool (HikariCP, c3p0). Lastly, in what instances are you looking to apply both client-side and external connection pooling?

From my experience and understanding, the disadvantages of an external pool are:

• additional failure point (including from a security standpoint)
• additional latency
• additional complexity in deployment
• security complications w/ user credentials

In researching the question, I have come across instances where both client-side and external pooling are used. What is the motivation for such a deployment? In my mind that is compounding the majority of disadvantages for a gain that I appear to be missing.

Answer 1

Usually, a connection pool on the application side is a good thing for the reasons you detail. An external connection pool only makes sense if

• your application server does not have a connection pool

• you have several (many) instances of the application server, so that you cannot effectively limit the number of database connections with a connection pool in the application server