PgBouncer

When to use PgBouncer

PgBouncer is generally recommended for OLTP workloads. All application connections should be routed through PgBouncer whenever possible. Learn more about the pros and cons of the different connection methods on the connections overview page. PlanetScale provides several options for using PgBouncer, including local PgBouncers and dedicated replica PgBouncers. PgBouncer connections operate in transaction mode, which means pooled server connections are assigned to client connections on a per-transaction level. This provides excellent performance for OLTP workloads but limits certain PostgreSQL features that require persistent connections. Learn more at the PgBouncer documentation.

When to NOT use PgBouncer

For use cases that require long-running operations, direct connections on port 5432 are recommended. For example:

Schema changes and DDL
OLAP, analytics, reporting, or batch processing
Session-specific features: Custom session variables, temporary tables
ETL processes and data streaming
Long-running transactions or queries that span multiple transactions
Creating a local backup with pg_dump

Local PgBouncer

Every PlanetScale for Postgres database includes an instance of PgBouncer running on the same node as the primary Postgres database (local PgBouncer). To connect to PgBouncer, use the same credentials as a direct connection, but use port 6432 instead of the Postgres default of 5432. For example:

psql 'host=xxxxxxxxxx-useast1-1.horizon.psdb.cloud port=6432 user=postgres.xxxxxxxxxx password=pscale_pw_xxxxxxxxxxxxxxxxxx dbname=my_database sslnegotiation=direct sslmode=verify-full sslrootcert=system'

NoteThe local PgBouncer does not support routing queries to replicas. All connections through the local PgBouncer are automatically routed to the primary database, regardless of the username specification. Use a dedicated replica PgBouncer for replica access.

Dedicated replica PgBouncers

Dedicated replica PgBouncers can be created to run on nodes separate from the Postgres servers. This is useful for applications that send significant read traffic to replicas and need connection pooling. This offers similar high-availability benefits as the local PgBouncer but is used for read-only replica traffic.

Creating a dedicated replica PgBouncer

You must be a database or organization administrator to create PgBouncers.

From the PlanetScale organization dashboard, select the desired database
Navigate to the Cluster configuration page from the menu on the left
Choose the branch where you want to add a PgBouncer in the “Branch” dropdown
Select the PgBouncers tab
Scroll down to the “Dedicated replica PgBouncers” section
Click the “Add a replica PgBouncer” button

In the pop-up dialog, give the new PgBouncer a descriptive name. Note that names can not be modified after creation.
Select a size based on your connection pooling needs (see PgBouncer pricing for available sizes)

Click “Create PgBouncer”
Wait a few minutes for the creation to complete

A new entry for the PgBouncer will appear in the Dedicated replica PgBouncers section once provisioning is complete. Multiple replica PgBouncers can be created if needed. This is useful for adding additional PgBouncer capacity or for having distinct bouncers for different client applications to manage connection pooling with more precision.

Connecting to dedicated replica PgBouncers

Connect to dedicated replica PgBouncers by appending |pgbouncer-name to the username of any role you have created. For example, if your username is user1.abcdefghi and the dedicated replica PgBouncer is named read-bouncer, the connection username should be user1.abcdefghi|read-bouncer. The hostname and password remain the same. Use port 6432 for dedicated PgBouncer connections:

psql 'host=xxxxxxxxxx-useast1-1.horizon.psdb.cloud \
      port=6432 \
      user=postgres.xxxxxxxxxx|read-bouncer \
      password=pscale_pw_xxxxxxxxxxxxxxxxxx \
      dbname=my_database \
      sslnegotiation=direct \
      sslmode=verify-full \
      sslrootcert=system'

Configuring PgBouncers

Each PgBouncer on the “PgBouncers” tab can be individually configured with a section like this under each PgBouncer:

The basic settings are at the top, with advanced settings available as an option. Adjusting advanced settings is not recommended unless there is a good understanding of how PgBouncer works.

Configurable parameters

The following parameters can be configured for both the local and dedicated replica PgBouncers.

Basic settings

Parameter	Description
default_pool_size	How many server connections to allow per user/database pair. Default: `20`
min_pool_size	Add more server connections to pool if below this number. Improves behavior when load returns after inactivity. Default: `0`
max_client_conn	Maximum number of client connections allowed. Default: `100`
server_lifetime	The pooler will close unused server connections that have been connected longer than this. 0 means use once then close. Default: `3600` seconds
server_idle_timeout	Close server connections idle longer than this many seconds. 0 disables this timeout. Default: `600` seconds

Advanced settings

Advanced parameters should only be adjusted with a thorough understanding of PgBouncer internals.

Parameter	Description
Connection Limits
max_prepared_statements	When non-zero, PgBouncer tracks protocol-level named prepared statements in transaction and statement pooling mode. Default: `200`
max_db_connections	Do not allow more than this many server connections per database (regardless of user). 0 is unlimited. Default: `0`
max_db_client_connections	Do not allow more than this many client connections per database (regardless of user). 0 is unlimited. Default: `0`
max_user_connections	Do not allow more than this many server connections per user (regardless of database). 0 is unlimited. Default: `0`
max_user_client_connections	Do not allow more than this many client connections per user (regardless of database). 0 is unlimited. Default: `0`
reserve_pool_size	How many additional connections to allow to a pool. 0 disables. Default: `0`
reserve_pool_timeout	If a client has not been serviced in this time, use additional connections from the reserve pool. 0 disables. Default: `5` seconds
Timeouts
query_timeout	Cancel queries running longer than this. Use with smaller server-side statement_timeout for network problems. Default: `0` seconds
query_wait_timeout	Maximum time queries wait for execution. Client disconnected if query not assigned to server in time. 0 disables. Default: `120` seconds
client_idle_timeout	Close client connections idle longer than this. Should be larger than client-side lifetime settings. Default: `0` seconds
client_login_timeout	Disconnect clients that don’t log in within this time. Prevents dead connections stalling SUSPEND and restart. Default: `60` seconds
idle_transaction_timeout	If a client has been in “idle in transaction” state longer, it will be disconnected. Default: `0` seconds
cancel_wait_timeout	Maximum time cancel requests wait for execution. Client disconnected if not assigned to server in time. 0 disables. Default: `10` seconds
autodb_idle_timeout	How long database pools stay cached after last use. After timeout, unused pools are freed and stats reset. Default: `3600` seconds
suspend_timeout	How long to wait for buffer flush during SUSPEND or reboot (-R). Connection dropped if flush fails. Default: `10` seconds
Server Health
server_check_query	Simple query to check if server connection is alive. Empty string disables sanity checking. Default: `select 1`
server_check_delay	How long to keep released connections available for immediate re-use without running server_check_query. 0 always runs check. Default: `30` seconds
Logging
log_connections	Log successful logins. Default: `1` (enabled)
log_disconnections	Log disconnections with reasons. Default: `1` (enabled)
log_pooler_errors	Log error messages the pooler sends to clients. Default: `1` (enabled)
Parameter Handling
ignore_startup_parameters	Allow additional startup parameters that PgBouncer normally rejects. Specify here so PgBouncer knows admin handles them. Default: `extra_float_digits`
track_extra_parameters	Additional parameters to track per client beyond the defaults. Maintained in client cache and restored when client active. Default: `IntervalStyle`
Low-Level Performance
pkt_buf	Internal buffer size for packets. Affects TCP packet size and memory usage. No need to set large for libpq packets. Default: `4096` bytes
sbuf_loopcnt	How many times to process data on one connection before proceeding. Prevents big result sets stalling PgBouncer. 0 = no limit. Default: `5`
disable_pqexec	Disable Simple Query protocol (PQexec). Improves security by preventing some SQL injection attacks. 0 = enabled, 1 = disabled. Default: `0`
Infrastructure (Local PgBouncer only)
Number of processes	Sets the number of PgBouncer processes that will run on each node in this branch’s cluster. Default: `1`

Learn more about PgBouncer configuration on their official website.

How PgBouncer works

Connection reuse is the key mechanism that makes PgBouncer effective. When a client completes a transaction, PgBouncer returns the server connection to the pool rather than closing it. The next client transaction can immediately reuse that existing connection without incurring the overhead of spawning a new Postgres process. This allows a single pooled connection to serve hundreds or thousands of client connections over its lifetime, enabling applications to scale far beyond the constraints of direct connections.

Pooling modes

PgBouncer supports three pooling modes that determine how connections are assigned:

Session Pooling: Each client connection is given a dedicated connection from the PgBouncer pool for its entire duration. This mode does not provide connection multiplexing benefits.
Statement pooling: Assigns client connections to pooled server connections on a per-query basis. This mode does not allow multi-statement transactions, which is unsuitable for most use cases.
Transaction Pooling: Assigns client connections to pooled server connections on a per-transaction level and allows multi-statement transactions. This is the most suitable mode for the vast majority of workloads and is used by all PlanetScale PgBouncer instances.

Limitations of transaction pooling

PgBouncer’s transaction pooling mode provides excellent performance for OLTP workloads but limits certain PostgreSQL features that require persistent connections:

Prepared statements that persist across transactions (protocol-level prepared statements work with max_prepared_statements configured)
Temporary tables
LISTEN/NOTIFY
Session-level advisory locks
SET commands that persist beyond a transaction

For operations requiring these features, use a direct connection instead (see the connections overview).

Benefits during maintenance operations

Using PgBouncer provides improved availability during configuration changes. When modifying Postgres Parameters, some changes require the server to be restarted. When these restarts happen, any direct connections to Postgres will be terminated. However, when using PgBouncer, client connections are maintained and PgBouncer handles reconnecting to Postgres after it restarts. The operations philosophy documentation covers more details on how connections are managed during various database lifecycle operations.

Scaling PgBouncer

PgBouncer itself is a lightweight process, but high connection volumes or high query throughput can eventually exhaust its capacity. PlanetScale offers multiple PgBouncer sizes to handle different workload demands. Each size provides increased CPU and memory resources, allowing PgBouncer to handle more concurrent client connections and higher query throughput without becoming a bottleneck. See PgBouncer pricing for available sizes.

PgBouncer error messages

PgBouncer has custom error messages that may be encountered in addition to standard Postgres errors. The PgBouncer config documentation describes these errors and can be a helpful resource for troubleshooting connection issues.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.

Pricing

Platform

When to use PgBouncer

When to NOT use PgBouncer

Local PgBouncer