3

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?

Martin01478
  • 387
  • 1
  • 5
  • 16

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263