PgBouncer

PostgreSQL forks a backend process for every connection. Each one costs memory and a slot in the shared state, and the cost is paid whether the connection is running a query or sitting idle in a web framework’s pool. PgBouncer sits in front of Postgres and lets thousands of client connections share a small number of real server connections.

It is a single-threaded process built around a non-blocking event loop. That is why it can hold tens of thousands of client sockets on modest hardware, and equally why one PgBouncer process saturates a single CPU core. Both facts drive the configuration decisions below.

Run 1.25.2 or later. Version 1.25.2 (May 2026) fixed four CVEs, including CVE-2026-6664, an integer overflow that lets an unauthenticated remote attacker crash PgBouncer with a malformed SCRAM packet. CVE-2026-6667 in the same release fixed an authorisation gap where any admin-console user could run KILL_CLIENT, not just those in admin_users.

Pool modes, and what each one breaks

pool_mode is the single most consequential setting. It decides when a server connection is returned to the pool, which in turn decides what PostgreSQL features your application can still use.

The default is session. It is also the mode that gives you the least: a typical application holds its connection open for the life of the process, so a “pooled” connection is pinned to one client anyway.

transaction mode is what almost everyone actually wants. It is also where people get hurt, because a server connection can serve a different client on the very next transaction. Anything whose lifetime is the session and not the transaction stops being reliable:

• SET / RESET outside a transaction. The setting may land on a connection another client later uses
• Session-level advisory locks (pg_advisory_lock). Use the _xact_ variants instead
• LISTEN / NOTIFY
• WITH HOLD cursors
• Temporary tables

Prepared statements are the exception that changed. Before 1.24.0 they were effectively unusable in transaction mode. Since 1.24.0 protocol-level prepared statement support is enabled by default with max_prepared_statements = 200, so most drivers now work. Read the documented limitations before relying on it, and note that PREPARE/EXECUTE issued as plain SQL is still not tracked.

If you need any of those, either isolate that workload onto a session-mode pool or route it around PgBouncer entirely. You can declare different modes per database, so a single PgBouncer can serve a transaction pool for the app and a session pool for the job runner.

Note also that PgBouncer tracks only a fixed set of parameters per client: client_encoding, datestyle, timezone, standard_conforming_strings and application_name. Anything else your driver sends in the startup packet raises an error unless you list it in ignore_startup_parameters, or list it in track_extra_parameters so PgBouncer restores it when the client becomes active.

Sizing the pool

Two numbers matter and they are frequently confused.

• max_client_conn (default 100) is how many clients may connect to PgBouncer. This is the number you raise into the thousands.
• default_pool_size (default 20) is how many server connections PgBouncer opens to Postgres per user/database pair. This is the number you keep small.

The whole point is that the first is large and the second is small. A pool size of 20 backed by max_client_conn = 5000 means 5000 application connections contend for 20 Postgres backends, which is usually far more throughput than 5000 real backends would deliver.

Raising max_client_conn requires raising the operating system file descriptor limit too. The documented theoretical maximum, when each user connects under its own name, is:

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

If every client connects as the same user, the total users term drops out. You will not reach this in practice, but size ulimit -n against it rather than against max_client_conn alone.

Supporting settings worth knowing:

min_pool_size has a subtlety: it is only enforced for pools where the [databases] entry sets a forced user, or where at least one client is already connected. An idle pool with neither will not be pre-warmed.

A minimal configuration

[databases]
appdb = host=10.0.1.10 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 10.0.1.20
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_lifetime = 3600
server_idle_timeout = 600

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

admin_users = pgb_admin
stats_users = pgb_stats

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /run/pgbouncer/pgbouncer.pid

listen_port defaults to 6432, deliberately not 5432, so PgBouncer and Postgres can coexist on one host without a clash.

Authentication without a password file

Maintaining userlist.txt by hand means every password rotation touches two systems and drifts. auth_query instead lets PgBouncer look the hash up in Postgres itself.

The naive version of this query grants PgBouncer read access to pg_authid, which is every password hash in the cluster. Do not. The project documents a SECURITY DEFINER function that returns exactly one user’s hash and nothing else:

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END
    FROM pg_authid
    WHERE rolname = i_username AND rolcanlogin
    INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
   SET search_path = pg_catalog, pg_temp;

REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

Three details carry the security here, and all three are load-bearing:

1. SECURITY DEFINER runs the function as its owner, so the pgbouncer role never needs pg_authid rights of its own.
2. The pinned search_path stops a caller from shadowing pg_authid with their own object.
3. The explicit REVOKE then narrow GRANT means only the pooler can call it.

The expiry check matters too: rolvaliduntil < now() returns NULL for an expired role, so an expired password cannot authenticate through the pooler after it has stopped working directly.

That check is not cosmetic. Its absence was CVE-2025-2291: before 1.24.1, PgBouncer ignored VALID UNTIL when looking up password hashes, so an expired password kept working through the pooler after it had stopped working against Postgres directly. If you run a custom auth_query written against an older release, it will not have this check. The fix changed the documented default, so upgrading alone does not repair a hand-written function.

A second auth_query hazard, CVE-2025-12819, was fixed in 1.25.1: an unauthenticated attacker could execute arbitrary SQL during authentication via a malicious search_path in the startup packet. It required all three of track_extra_parameters including search_path, a non-empty auth_user, and an auth_query without fully-qualified object names. Fully qualifying every object in your auth_query removes the third condition independently of version.

Then wire it up:

[databases]
appdb = host=10.0.1.10 dbname=appdb auth_user=pgbouncer

[pgbouncer]
auth_type = scram-sha-256
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

Prefer scram-sha-256 over md5. If auth_type is md5 and the user has a SCRAM secret, PgBouncer uses SCRAM automatically, but configuring md5 leaves MD5 available as a fallback you did not intend. plain sends the password in clear text and is deprecated; trust and any skip authentication and belong nowhere near a network-facing pooler.

Using more than one core

PgBouncer is single-threaded. On a busy pooler you will see one core pinned at 100% while the rest of the machine idles. The supported fix is several PgBouncer processes sharing one port via so_reuseport, peered so they can hand off cancellation requests to each other.

Each process needs its own peer_id and a [peers] section listing all of them:

[databases]
postgres = host=localhost dbname=postgres

[peers]
1 = host=/tmp/pgbouncer1
2 = host=/tmp/pgbouncer2

[pgbouncer]
peer_id = 1
so_reuseport = 1
unix_socket_dir = /tmp/pgbouncer1
listen_addr = 10.0.1.20
listen_port = 6432

The second process is identical except peer_id = 2 and unix_socket_dir = /tmp/pgbouncer2. The peering exists because a query cancellation may arrive at a different process than the one holding the query; without [peers], cancellations get silently dropped.

Remember that pool sizes are per process. Four processes at default_pool_size = 25 can open 100 server connections, not 25. Divide, or you will quietly exceed max_connections on the database.

The admin console

PgBouncer exposes a virtual database called pgbouncer that speaks the Postgres protocol, so psql is the management interface. Only roles listed in admin_users (full control) or stats_users (read-only) may connect.

psql -h 10.0.1.20 -p 6432 -U pgb_admin pgbouncer

The commands worth memorising:

SHOW POOLS is the diagnostic that matters. The cl_waiting column counts clients queued for a server connection. If it is persistently above zero, the pool is too small or queries are too slow; if sv_idle is high while cl_waiting is also high, the problem is not pool size but something pinning connections, typically session-scoped state in transaction mode.

PAUSE and RESUME are what make PgBouncer useful during maintenance: pause the pool, fail the database over, repoint PgBouncer, resume. Clients block rather than error.

What changed, and in which release

Configuration advice for PgBouncer ages badly because defaults have shifted. If you are on an older release, these are the differences that affect the guidance above.

Two of these are behaviour changes rather than additions, and both bite on upgrade: default_pool_size = 0 meant “no connections” in the intuitive reading but means unlimited from 1.24.0, and enabling prepared statements by default alters how transaction mode behaves for drivers that previously fell back to simple query mode.

Diagnostics

Connect to the admin console (psql -p 6432 pgbouncer) and run SHOW POOLS:

SHOW POOLS;

The columns that matter: cl_active (client connections assigned a server), cl_waiting (clients queued because no server is free), sv_active (server connections doing work), sv_idle (server connections doing nothing), sv_used (returned to pool but not yet tested).

Clients waiting but servers idle. The most common misconfiguration. Likely cause: pool_mode is session and long-lived clients are holding connections without running queries. Switch to transaction mode if the application tolerates it, or raise default_pool_size.

sv_active at default_pool_size continuously. The pool is saturated. Either the backend is slow (check PostgreSQL for long queries) or default_pool_size is too low for the workload. Raising it beyond max_connections / number_of_databases pushes the bottleneck to PostgreSQL.

Authentication failures. SHOW STATS shows total_login_time rising; application logs show no such user or password authentication failed. Confirm auth_query is returning the correct password hash format. PgBouncer requires MD5 or SCRAM secrets stored in userlist.txt or returned by auth_query; if PostgreSQL was upgraded to SCRAM but PgBouncer still has old MD5 hashes, every login fails.

High avg_wait_time in SHOW STATS. Clients are queuing. The number to watch is avg_wait_time in microseconds. If it exceeds your application SLO (e.g. 100ms), clients are spending more time waiting for a connection than executing queries.

SHOW STATS;
-- Look at: total_wait_time, avg_wait_time

Server connections not returning to idle. Run SHOW SERVERS and look at state. If connections stay in active longer than expected, the problem is in PostgreSQL (long transactions, lock waits) not PgBouncer. Cross-reference with pg_stat_activity on the backend.

PgBouncer using 100% of one CPU. PgBouncer is single-threaded by default. If it is the bottleneck (visible as one core at 100% while others idle), deploy the multi-process so_reuseport configuration described in the peering section above.

Where PgBouncer is the wrong tool

It pools connections. It does not route reads to replicas, does not do failover, and does not shard. If you need read/write splitting or automatic failover, that is Pgpool-II territory, or a proxy such as HAProxy paired with a topology manager like Patroni. A common and sound production layout is Patroni for failover, HAProxy or keepalived for the virtual IP, and PgBouncer purely for pooling. Each component does one job.