Connection Pooling

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 connection pooling settings

Crunchy Bridge uses pgBouncer for connection pooling and transaction pooling mode is enabled.

Connection pooling is available on Standard and Memory-optimized plans only.

How to tell if you benefit from connection pooling

Connection pooling is especially helpful where 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). To tell if you would benefit you can run the following query:

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, the enabling connection pooling is highly recommended.

Enable connection pooling

To enable connection pooling for a database, run the following command (needs superuser privileges):

CREATE EXTENSION crunchy_pooler;

Use connection pooling

Only non-superuser roles can connect to the pooler (i.e. do not use the postgres role).

To connect to the pooler, use your cluster’s connection string per usual, except with 5431 for the port number, for example:

psql postgres://appuser:[email protected]:5431/mydb

Remove connection pooling

To disable connection pooling for a database, run DROP EXTENSION.