What exactly does a connection pool for databases like PostgreSQL do?

Question

I know the general idea that a connection pool is a pool of reusable connections that speeds up traffic to the database because it can reuse connections instead of constantly creating new ones.

But this is a very high level explanation. It doesn't explain what is meant by a connection and why the connection pool works, since even with a connection pool such as for example client -> PgBouncer -> PostgreSQL, while the client does not have to create a connection to the databasee, it still has to connect to create a connection to the proxy.

So what is the connection created from (e.g.) client -> PgBouncer and why is creating this connection faster than creating the connection PgBouncer -> PostgreSQL?

Answer 1

There are two uses of a connection pool:

• it prevents opening and closing database connections all the time

  There is certainly a certain overhead with establishing a TCP connection to pgBouncer, but that is way cheaper than establishing a database connection. When you start a database connection, additional work is done:

  • a server process is started, which is way more expensive than a TCP connection

  • PostgreSQL loads cached metadata tables

• it puts a limit on the number of client connections, thereby preventing database overload

  The advantage over limiting max_connections is that connections in excess of the limit won't receive an error, but will be queued waiting for a connection to become free.