PgBouncer

PgBouncer is made available on Bridge instances by default to ease connection management by multiplexing native Postgres connections across its own "virtual" connections. However, in order to make use of the PgBouncer service, you must take one extra step on each database you want to use it on by installing the crunchy_pooler extension.

Note that the max_connections configuration parameter can be set higher than the default of 500 if that's preferred instead of or in addition to using PgBouncer. That change requires a Postgres restart, and that the same or greater max_connections value be set on any replicas.

Activating PgBouncer with the `crunchy_pooler` extension

As superuser, run the following in the database to install the crunchy_pooler extension:

CREATE EXTENSION crunchy_pooler;

What is `crunchy_pooler`?

crunchy_pooler is simple extension that creates a user called crunchy_pooler. This user has access to a single function called user_lookup that allows PgBouncer to authenticate incoming connections. That way, when a client makes a connection to PgBouncer, it can check whether its credentials are valid by querying Postgres' canonical user store.

Info

The crunchy_pooler extension must be installed individually in each database where you want to connect to PgBouncer. If crunchy_pooler has not been installed, you may receive an error like:

failed: FATAL: bouncer config error

To resolve the error, connect to the database and run: create extension crunchy_pooler;.

Connecting to PgBouncer

Clients will connect to PgBouncer using the same connection string they'd use for the main Postgres database, except on port 5431 instead of the usual 5432:

psql postgres://my_application_user:my_application_password@p.43lmodgbqvdmlpbjirv22dfciu.db.postgresbridge.com:5431/mydb

Only roles without superuser or replication privileges will be able to connect through PgBouncer. You might choose to connect to PgBouncer using the application role, an individual user role created for team members, or any custom user roles that you may have created (for example, using the CREATE ROLE SQL command). However, the user_lookup function created by crunchy_pooler will deny lookups on superusers and replication roles. See User Management for more about Postgres users and roles on Crunchy Bridge.

Hint

The terms "user" and "role" in Postgres are largely synonymous. One minor difference is that CREATE USER (versus CREATE ROLE) implies LOGIN privilege, so according to the principle of least privilege, CREATE ROLE is the better choice for users/roles that are meant for use by applications rather than people.

Pooling modes

Transaction

Bridge instances will run PgBouncer in transaction pooling mode by default, since that's the mode we recommend most people use.

With prepared statements

Prepared statements previously could not be used with transaction pooling mode, however that changed in PgBouncer version 1.22.0. PgBouncer in transaction pooling mode has been compatible with prepared statements on Crunchy Bridge since February 13, 2024. Clusters created before that date should be refreshed to get the latest server image.

In order to use prepared statements, the max_prepared_statements configuration parameter must be set to a value greater than 0. The default on Crunchy Bridge is 250, but you can set it to a different value if desired.

DROP EXTENSION crunchy_pooler;