PostgreSQL Connection Pooling: PgBouncer's Three Modes, the Prepared Statement Trap and Multi-Tenant Sizing

Every client connection to PostgreSQL is a separate operating system process on the server. Opening one means authentication, a fork and cold caches; keeping one open means a few megabytes of memory per connection and extra work for the scheduler. As application servers multiply, 30 replicas each holding a pool of 20 show up at the database as 600 connections. Most of them sit idle, yet the database pays for every one. A connection pooler closes that gap: it hands out plenty of connections to clients, opens few to the database, and pairs them only when needed. This article covers PgBouncer's three pool modes, which workload each fits, how to size pools in a multi-tenant setup, and the trap that bites almost everyone: prepared statements.

Three modes, one decision

PgBouncer's modes differ in how long a server connection stays assigned to a client.

Session mode assigns a server connection when the client connects and keeps it until the client disconnects. Everything on the server side keeps working: session variables, prepared statements, temporary tables, locks. The only gain is caching the cost of opening connections; the number of concurrent connections does not shrink.

Transaction mode assigns the server connection at the start of a transaction and reclaims it at COMMIT or ROLLBACK. Two consecutive transactions from the same client may run on different server connections. A thousand clients of which 40 are actually inside a transaction at any moment get by with 40 server connections. This is where the real win is, and it is the mode you want in production nearly every time.

Statement mode reclaims the connection after every statement; multi-statement transactions are rejected. It has a narrow niche such as autocommit-only read traffic.

The decision is simple: aim for transaction mode and strip everything that depends on session state out of the application. Session mode is a stopgap for the migration period, not a destination.

What breaks in transaction mode

In transaction mode a client cannot assume it will see the same server connection across two transactions. Every feature built on that assumption either fails or, worse, misbehaves silently:

• Session settings made with SET (SET search_path, SET timezone) vanish on the next transaction. Worse, the setting stays on the server connection and leaks to whichever client gets it next. Use SET LOCAL inside the transaction; it is discarded at commit.
• LISTEN and NOTIFY do not work; the notification arrives on a connection the client no longer holds.
• Session-level advisory locks (pg_advisory_lock) stay on the wrong connection. Prefer the transaction-level variants (pg_advisory_xact_lock).
• Temporary tables and WITH HOLD cursors become unreachable after the transaction ends.
• Named prepared statements deserve their own section below.

Hunting for these in the codebase is the real work of moving to transaction mode. The PgBouncer config is five lines; cleaning up the code can take weeks.

The prepared statement trap

Most drivers use server-side prepared statements for performance: the plan is built once, stored under a name, and later calls send only the name and parameters. That name belongs to the server connection. In transaction mode the client lands on a different server connection for its next transaction and gets "prepared statement does not exist". The nasty part is that it appears randomly and only under load; a single-client test always gets the same connection back and never sees it.

There are two ways out. The first is to disable server-side prepared statements in the driver:

• Go pgx: default_query_exec_mode=simple_protocol in the connection string
• Python psycopg 3: prepare_threshold=None
• JDBC: prepareThreshold=0
• Rails: prepared_statements: false

The second is to let PgBouncer track protocol-level prepared statements itself. Since version 1.21, setting max_prepared_statements to a value above zero makes PgBouncer re-prepare a client's statement on whichever server connection it lands on. This applies only to protocol-level statements (the driver's Parse message), not to SQL PREPARE. For a new setup take the second route and keep the driver optimisation. If you are stuck on an older PgBouncer, the first route is the only one.

A baseline configuration

The file below is a starting point that runs in transaction mode and tracks prepared statements:

[databases]
app = host=pg-primary port=5432 dbname=app pool_size=30

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 80

max_prepared_statements = 200
server_idle_timeout = 300
query_wait_timeout = 60
ignore_startup_parameters = extra_float_digits

admin_users = pgbouncer_admin

What the values mean: max_client_conn is the total number of client connections accepted, default_pool_size the number of server connections opened per database and user pair. reserve_pool_size adds extra connections when a client has waited longer than reserve_pool_timeout seconds. query_wait_timeout is how long a client waits in the pool before it gets an error; leave it unbounded and a stalled database turns into a silently piling application tier, so you see a hang instead of a timeout.

The ignore_startup_parameters line swallows startup parameters that some drivers, JDBC among them, send and PgBouncer does not recognise; without it those clients cannot connect at all.

Sizing

Do not derive the number of server connections from the number of clients; derive it from how many concurrent queries the database can execute efficiently. The common starting rule is twice the core count plus the number of disks. On an 8-core server, around 20 active connections deliver more throughput than 200 because context switching and lock contention drop. From there, tune with two columns of SHOW POOLS: if cl_waiting is consistently above zero and maxwait grows, the pool is too small; if sv_idle stays high, it is too large.

PgBouncer keeps one pool per database and user pair. In a multi-tenant system with a database per tenant that product explodes: 200 tenants and a pool of 20 means a potential 4000 connections to the database. Cap it with max_db_connections and max_user_connections, leave min_pool_size at zero so idle tenants hold nothing, and use a wildcard entry instead of listing every tenant in [databases]:

[databases]
* = host=pg-primary port=5432 pool_size=5

Whatever the numbers, the total must stay below PostgreSQL's max_connections and leave the superuser_reserved_connections share untouched.

Where to put it

Either as a sidecar next to the application or as a central service. A sidecar means a separate pool per application replica; it does not solve the connections-times-replicas problem, it only postpones it. A central deployment genuinely merges connections and should be the default. Its one weakness is that PgBouncer is single-threaded and a single process saturates at very high request rates. You can run several PgBouncer processes sharing the same port, or consider a multi-threaded alternative such as PgCat. PgCat can also split reads from writes; if you do not need that, PgBouncer's maturity and simplicity win.

Do not remove the application-side pool (HikariCP, pgx pool), but shrink it. The application pool manages the cost of establishing connections; PgBouncer manages the total count on the database side. They do different jobs.

When not to use it

If you run a single application replica and the total connection count is well within what the database handles comfortably, PgBouncer only adds a failure point. If your architecture is built on LISTEN/NOTIFY, transaction mode is closed to you and session mode does not deliver the pooler's main benefit, so an application pool is enough. Finally, restarting PgBouncer drops every client connection; do not put it in production before learning the RELOAD, PAUSE and RESUME admin commands for config changes and maintenance.