Tuning PgBouncer

PgBouncer is a connection pooler for PostgreSQL. It can be used to improve the performance of PostgreSQL by reducing the number of open connections to the database server. It is a common tool in high transaction and highly available environments. Keep in mind that there are no "best" values for tuning PgBouncer. This settings for the configuration parameters listed in this post will be dependent on the specific workload hitting PgBouncer and the backend database. Tuning a database is more of an art than a science. It will require load testing and compensating for edge cases.

max_client_conn: This parameter sets the maximum number of client connections that PgBouncer can handle simultaneously. It determines the total number of connections that can be established to the PostgreSQL server through PgBouncer. The current setting can be checked with the ulimit command. Be sure to increase the open file descriptors in /etc/security/limits.conf for pgbouncer user account. Set the soft limit greater than:

max_client_conn + (max pool_size * total databases * total users)

min_pool_size and max_pool_size: These parameters define the minimum and maximum number of connections that can be held in the pool for each database. The pool size can dynamically increase or decrease within this range based on client demand. the purpose of min_pool_size is to keep a certain number of idle connections in the pool to serve incoming client requests promptly. Do not set min_pool_size too high without careful consideration since it will waste system resources.

default_pool_size: Specifies the default connection pool size for each database. This value determines the initial number of connections PgBouncer will open for each database when it starts. If your application tends to have a significant number of idle connections waiting for new requests, setting default_pool_size higher than min_pool_size can help maintain better connection availability during periods of low activity.

reserve_pool_size: Sets the number of additional connections that PgBouncer keeps in reserve, not available to clients. These reserved connections are used to handle transactional queries and are released back to the pool when the transaction is completed. This is disabled by default with a setting of 0.

max_db_connections: Specifies the maximum number of connections that PgBouncer can open to a single database. It helps limit the maximum load that a single database can put on the PostgreSQL server. This setting should be less than or equal to the max_connections setting for backend PostgreSQL server. Setting this slightly lower will ensure that there spare connections for a DBA to make a direct connection to the database.

server_lifetime and server_idle_timeout: These parameters control the lifespan and idle timeout of server connections. server_lifetime defines the maximum time a server connection can remain active, while server_idle_timeout specifies the maximum time a server connection can remain idle before being closed. Setting this these too low will cause churn as connections are killed and re-established. Setting them to high will mean that connections are not available to other clients if they are not released.

query_timeout: Defines the maximum time a client query can execute before it is canceled. This helps prevent queries from taking too long and blocking other client requests. This is a safeguard against poorly written queries. This will be specific to the workload, and some workloads such as reporting services will need higher values.

server_round_robin: When enabled, this option distributes connections across multiple PostgreSQL servers in a round-robin fashion. It can be useful for load balancing if you have multiple PostgreSQL servers.

As with any application environment, load testing is a valuable step in the deployment process. Load testing will not uncover all performance problems, but an effective testing strategy should help mitigate the common issues. PgBouncer will probably need to be retuned as workload grows.