CODEWITHSUNDEEP
postgresqlconnection-poolinghaproxypgbouncerpgpool
Martin01478
Martin01478

Reputation: 525

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

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?

Upvotes: 4

Views: 337

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246473

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.

Upvotes: 4

Related Questions