A connection pool is a cache of database connections that can be reused. When a request comes in from a client, an available connection from the pool is given for that request or transaction.
In contrast, without any connection pooling, the client has to reach out to the database to establish a connection. Opening new connections can impact availability and performance — in PostgreSQL, the server "forks" or creates a new process, and could use up available resources as well as prevent new connections from being established. Connection pooling helps mitigate these issues and ensure that your applications can scale.
Crunchy Bridge uses pgBouncer for connection pooling and transaction pooling mode is enabled.
See the PgBouncer How To page for guidance on how to set it up on Crunchy Bridge.
Connection pooling is especially helpful when you have a high number of connections from your application, often in a client-side pool or via multiple threads/processes on from your webserver.
You can run the following query to determine if you would benefit from connection pooling:
SELECT count(*), state FROM pg_stat_activity GROUP BY 2; count | state -------+------------------------------- 7 | active 69 | idle 26 | idle in transaction 11 | idle in transaction (aborted) (4 rows)
If you see a high number of idle connections relative to active ones, then using connection pooling is strongly recommended.