PgBouncer Setup: Connection Pooling Modes and Config for PostgreSQL

PgBouncer is a lightweight connection pooler for PostgreSQL. Applications connect to PgBouncer, usually on port 6432, and PgBouncer reuses a small set of real server connections to the database. The key choice is the pool mode: session pooling (the default) keeps a server connection for the life of the client, transaction pooling hands it back after every transaction, and statement pooling hands it back after every query. This guide installs PgBouncer, writes a working configuration, and shows how to choose and test the mode.

Who this is for

This is for administrators and developers running PostgreSQL on Linux whose application opens many connections, who see errors such as "too many clients already", or who want to cap load on the database server. By the end you will have a running PgBouncer with authentication configured, a pool mode chosen on evidence, and a way to watch the pools.

If you are currently seeing connection exhaustion, read the too many clients error fix first, because a pooler is one of several remedies.

Why pool connections

Each PostgreSQL connection is a server process with its own memory, so thousands of mostly idle application connections are wasteful. A pooler accepts many client connections and multiplexes them onto fewer server connections. Applications that open and close a connection per request also benefit, because connecting to PgBouncer is cheaper than connecting to PostgreSQL each time.

The three pool modes

PgBouncer's configuration reference defines them as follows.

What breaks in transaction pooling

Because consecutive transactions from one client may run on different server connections, anything that stores state in the session is unreliable. PgBouncer's feature table marks these as never supported in transaction pooling:

• SET and RESET
• LISTEN
• WITH HOLD cursors
• SQL-level PREPARE and DEALLOCATE
• Temp tables with PRESERVE or DELETE ROWS
• The LOAD statement
• Session-level advisory locks

Two related points. Protocol-level prepared statements work in transaction pooling only when max_prepared_statements is set to a non-zero value; the default is 200, and 0 disables the support. And common startup parameters such as client_encoding, DateStyle, TimeZone and application_name stay consistent for the client because PgBouncer tracks their changes.

Review your application and driver for these features before you switch. Frameworks that run SET search_path or take session advisory locks need session mode or a code change. If you need LISTEN for notifications, connect those clients directly to PostgreSQL.

Prerequisites

• A PostgreSQL server you can reach, and a role with permission to create roles and functions if you use auth_query.
• A Linux host for PgBouncer. It can share the database host or run on its own host or on the application servers.
• Root or sudo access.
• A decision on where it listens, and firewall rules that allow only your application hosts to reach port 6432.

Step 1: Install PgBouncer

Install the pgbouncer package from your distribution or from the PostgreSQL project's package repositories, if available for your system. To build from source, PgBouncer's installation page lists these requirements: Libevent 2.0 or later, pkg-config, OpenSSL 1.0.1 or later for TLS support and a C11-capable compiler, plus GNU Make 3.81 or later for Autoconf builds. Autoconf is the recommended build for production builds and packaging.

./configure --prefix=/usr/local
make
make install

If you build from Git, run ./autogen.sh first, which needs autoconf, automake, libtool and pandoc. The documentation notes that with Autoconf, optional features such as systemd support must be enabled with flags like --with-systemd, and that example unit files exist in the source tree as etc/pgbouncer.service and etc/pgbouncer.socket.

Step 2: Create the configuration

The configuration file, commonly pgbouncer.ini, has a [databases] section and a [pgbouncer] section.

[databases]
appdb = host=10.0.0.10 port=5432 dbname=appdb
 
[pgbouncer]
listen_addr = 10.0.0.20
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
admin_users = pgbouncer_admin
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

What each line does, with the documented defaults:

The addresses, database name and user names above are placeholders. Replace them with yours.

Step 3: Set up authentication

PgBouncer supports several auth_type values, including md5 (the default), scram-sha-256, cert, hba, ldap and pam. With scram-sha-256, the auth_file must contain SCRAM secrets or plain-text passwords.

The auth file

Each line of auth_file starts with two double-quoted fields: the user name and then the password, which can be plain text, an MD5 hash or a SCRAM secret. PgBouncer ignores the rest of the line.

"appuser" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"
"pgbouncer_admin" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"

You can copy the stored secret for a role from pg_authid as a superuser. Keep the file readable only by the account PgBouncer runs as, because it holds credentials.

Look users up with auth_query

Maintaining a file by hand does not scale. With auth_user, PgBouncer looks up any user not found in the file by running auth_query as that user. The documented default query selects rolname and rolpassword from pg_authid, which needs elevated rights. The documentation therefore recommends a SECURITY DEFINER function instead of direct access to pg_authid. Design it so it exposes only the one user's password and is executable only by the lookup role. The auth_user's own password is read from auth_file, unless that role needs no password.

Step 4: Run it

Run PgBouncer as a service. If you use the packaged systemd unit, enable and start it. To run it by hand, the synopsis is:

pgbouncer -d /etc/pgbouncer/pgbouncer.ini

The -d option runs in the background. The documentation says that in daemon mode you must set pidfile as well as logfile or syslog. The -u option switches to a given user at start, and -v increases verbosity.

Connect your application by changing only the host and port in its connection string, for example host 10.0.0.20 and port 6432, with the same database name and credentials.

psql "host=10.0.0.20 port=6432 dbname=appdb user=appuser"

Step 5: Size the pools

Three numbers interact.

• max_client_conn is how many clients PgBouncer accepts. It can be much larger than the pool.
• default_pool_size is the server connections per user and database pair. If two roles use one database, up to twice this number of server connections can exist.
• max_db_connections caps server connections per database regardless of user; the default is 0, meaning unlimited.

Make sure the sum across all pools, plus any direct connections from other tools, stays below PostgreSQL's max_connections with headroom for administrators. Other settings that matter:

Start with a modest default_pool_size, load test, and watch cl_waiting rather than guessing. A larger pool is not always faster, because the database has finite CPU and disk.

Step 6: Monitor with the admin console

Connect to the virtual database named pgbouncer:

psql -h 10.0.0.20 -p 6432 -U pgbouncer_admin pgbouncer

Only users in admin_users or stats_users can log in. The console supports only the simple query protocol, so some drivers that use the extended protocol will not work.

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW CONFIG;

In SHOW POOLS, one row represents a database and user pair. Read these columns:

• cl_active: clients linked to server connections, or idle with no queued queries.
• cl_waiting: clients that have sent queries but have not yet got a server connection.
• sv_active: server connections linked to a client.
• sv_idle: server connections that are unused and immediately usable.
• maxwait: seconds the oldest queued client has waited. A steadily rising value suggests the pool cannot keep up.

RELOAD reloads the configuration and auth files and updates changeable settings, without dropping clients. PAUSE disconnects server connections once they are released and does not return until all have disconnected, and RESUME continues. SHOW CONFIG lists each setting's value and whether it is changeable at runtime.

Troubleshooting

Clients wait or time out

cl_waiting above zero and a growing maxwait mean demand exceeds the pool. Check for slow or idle-in-transaction sessions holding server connections, raise default_pool_size within PostgreSQL's limits, or fix the slow queries. Clients whose query is not assigned a server within query_wait_timeout are disconnected.

Unsupported startup parameter

By default PgBouncer rejects startup parameters it cannot track. If a driver sends one, either stop sending it or list it in ignore_startup_parameters, which accepts and ignores the parameter. Only ignore parameters whose loss is harmless.

Application errors only in transaction mode

Look for session state: SET statements, advisory locks, LISTEN, SQL-level PREPARE. Use session mode for those clients, or change the code. For driver-level prepared statements, check that max_prepared_statements is non-zero.

The console refuses a connection

Confirm the user appears in admin_users or stats_users, and that you connect to the database named pgbouncer on the PgBouncer port, not on 5432.

Cannot connect from another host

If listen_addr is unset, PgBouncer accepts only Unix socket connections. Set it to the address to listen on and open the firewall for port 6432 from the application hosts only.

Checklist

• Application reviewed for session-level features before choosing transaction mode.
• listen_addr, listen_port and firewall rules set deliberately.
• auth_type set to scram-sha-256 where your roles use SCRAM, with a restricted auth file or a SECURITY DEFINER lookup.
• Pool sizes chosen so the total stays under PostgreSQL's connection limit.
• Admin users limited to administrators.
• SHOW POOLS reviewed under load, with a baseline recorded.
• Logging and pid file configured, and the service enabled at boot.

References

• PgBouncer configuration: https://www.pgbouncer.org/config.html
• PgBouncer features (SQL feature map for pooling modes): https://www.pgbouncer.org/features.html
• PgBouncer usage, admin console and command-line options: https://www.pgbouncer.org/usage.html
• PgBouncer installation: https://www.pgbouncer.org/install.html