Getting Started with PgBouncer on EDB Postgres AI – EnterpriseDB

How to deploy PgBouncer in EDB Postgres AI

You can enable PgBouncer in Cluster's Additional Settings Tab when creating or editing a new cluster. Refer to Creating a cluster

By default, read-write PgBouncer will be deployed. If read-only is enabled, read-only PgBouncer will be created. You will have 4 types of connection strings:

Connection String - Host(example)	Type
p-52l4paxwzi.0gww9tnhwis9gbln.biganimal.io	Read-write
p-52l4paxwzi-ro.0gww9tnhwis9gbln.biganimal.io	Read-only
p-52l4paxwzi-rwp.0gww9tnhwis9gbln.biganimal.io	PgBouncer Read-write
p-52l4paxwzi-rop.0gww9tnhwis9gbln.biganimal.io	PgBouncer Read-only

*Read-only traffic towards replica nodes only.

Understand the deployment

PgBouncer pods follow the cluster type.

Cluster Arch	PgBouncer Pods(rw)	PgBouncer Pods(ro) if enabled
Single-node cluster	1	1
HA(1 replica)	2 in 2 Availability Zones	2 in 2 Availability Zones
HA(2 replicas)	3 in 3 Availability Zones	3 in 3 Availability Zones

The following diagram highlights how introducing a database access layer based on PgBouncer changes the architecture of EDB Postgres for Kubernetes. Refer to Connection Pooling Architecture

Applications writing to the single primary via PgBouncer

User Role Privileges

To access the databases via PgBouncer, please ensure the auth user cnp_pooler_pgbouncer has the appropriate privileges.

1. cnp_pooler_pgbouncer needs the CONNECT privilege to the database, otherwise, you got the below error

FATAL: permission denied for database "d1"

It's usually caused by

REVOKE ALL PRIVILEGES ON DATABASE d1 from public ;

To resolve it by granting the connect privilege with the below SQL

GRANT CONNECT ON DATABASE <dbname> to cnp_pooler_pgbouncer;

2. cnp_pooler_pgbouncer needs the USAGE privilege on schema public, otherwise, you got the below error

FATAL: bouncer config error

It's usually caused by

REVOKE ALL PRIVILEGES ON SCHEMA public from public ;

To resolve it by granting the connect privilege with the below SQL

GRANT USAGE ON SCHEMA PUBLIC TO cnp_pooler_pgbouncer;

Important Parameters in PgBouncer

Please note that if there are multiple PgBouncer instances, the configuration for pools and connections will be cumulative. For instance, in a 2-node cluster, the default_pool_size would effectively double."

Below are the parameters that need to be configured before you start to use it. For the other parameters, please set them according to your expectations too.

Parameter	Definition&Default	Recommendation	Example
default_pool_size	Default is 20 How many server connections to allow per user/database pair on each PgBouncer instances.	pgbouncer_instances* default_pool_size* user-database pairs < max_connections	We have 2 users and both can connect 3 databases. So we have 6 user-database pairs. Our cluster is a 3-node cluster. default pool size = max_connections /(6*3).
max_client_conn	Default is 100 Maximum number of client connections allowed.	Consider your expected workload. Can be greater than max_connections Actual setting for cluster: pgbouncer_instances* max_client_conn	max_connections is 300 default_pool_size is 50 set max_client_conn to a much higher number, such as 1000, to handle bursts of activity and new connections being established
max_db_connections	Default is 0 Do not allow more than this many server connections per database (regardless of user).	pgbouncer_instances* max_db_connections* database_count< max_connections
max_user_connections	Default is 0 Configure a maximum for the user (i.e. all pools with the user will not have more than this many server connections).	pgbouncer_instances* max_user_connections* user_count< max_connections
server_idle_timeout	Default: 600.0 If a server connection has been idle more than this many seconds it will be closed. If 0 then this timeout is disabled. [seconds]	This timeout starts counting from the last time the session became idle. Each time the session becomes active, the idle timeout counter resets.	Make sure to adjust the values of these parameters according to your specific connection requirements.
server_lifetime	Default: 3600.0 The pooler will close an unused (not currently linked to any client connection) server connection that has been connected longer than this. Setting it to 0 means the connection is to be used only once, then closed. [seconds]	This parameter sets a limit on the total lifetime of a server connection from the time it was initially opened.	Make sure to adjust the values of these parameters according to your specific connection requirements.
tcp_keepcnt	Default is disable count of keepaliva packets	enable
tcp_keepidle	Default is disable how long the connection can be idle, ;; before sending keepalive packets	enable
tcp_keepintvl	Default is disable The time between individual keepalive probes.	enable

How to update max_connections on EDB Postgres AI – EnterpriseDB tells everything about max_connections in EDB Postgres AI. It's recommended to configure PgBouncer along with max_connections.

Limitation

PGD clusters can not enable PgBouncer
Support session mode only

If you want to deploy and manage PgBouncer outside EDB Postgres AI, see the How to configure EDB PgBouncer with EDB Postgres AI cluster knowledge-base article.

Monitoring

EDB Postgres AI offers PgBouncer metrics, which are documented here. You can check them in different ways EDB Docs - EDB Postgres for Kubernetes v1 - Connection pooling (enterprisedb.com)