PgBouncer is the standard answer when an application opens more PostgreSQL connections than the server can usefully service. A process-per-request runtime and a serverless function that dials the database directly get there by completely different routes and land in the same place. The part that goes wrong is the pool mode, because the aggressive setting is the one everybody wants and it silently changes the semantics your code was written against.
Three Modes, One Real Decision
Session pooling assigns a server connection for the life of the client connection. It is safe and it buys you very little, since a worker process holding a connection for the length of a request is the situation you were trying to fix.
Transaction pooling assigns a server connection for the length of a transaction and returns it to the pool at commit. This is the mode that delivers the numbers people install PgBouncer for, and it is the mode with consequences.
Statement pooling returns the connection after every individual statement, which forbids multi-statement transactions entirely. It exists for specific workloads and is almost never what you want.
What Transaction Mode Takes Away
The rule is that anything living outside a transaction stops being reliable, because between two statements you may be on a different backend. That is a longer list than it first appears:
SET / RESET at session scope
LISTEN / NOTIFY
advisory locks taken outside a transaction
WITH HOLD cursors
temporary tables
session-scoped GUCs set by a connection hookSession-level SET is the one that catches real applications. A framework bootstrap that sets search_path or timezone once on connect works perfectly in development against a direct connection, and in production the setting lands on whichever backend happened to serve that statement. Every later query gets a different backend without it. The failure is intermittent and nearly impossible to reproduce on demand.
Advisory locks are worse, because the failure is silent rather than noisy. A lock taken with pg_advisory_lock() outside a transaction belongs to a session you no longer control. The transaction-scoped variant, pg_advisory_xact_lock(), is released at commit and behaves correctly under transaction pooling. If you use advisory locks for job coordination, that one function name is the difference between working and quietly running the same job twice.
Prepared Statements Are Fixed, with Conditions
For years the answer to prepared statements under transaction pooling was that you could not use them. PgBouncer 1.21, released in October 2023, changed that. Set max_prepared_statements to a non-zero value and PgBouncer tracks named prepared statements itself, rewriting them to internal names and re-preparing them on whichever backend a client lands on:
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200The value is the size of an LRU cache of statements kept on each server connection, so it wants to be at least as large as the number of distinct statements a typical request issues. Zero disables the feature and restores the old behavior.
The condition attached is important. This only works for protocol-level prepared statements, the ones sent through the extended query protocol. A literal PREPARE foo AS SELECT ... sent as a plain text query is invisible to PgBouncer and still breaks, because from the outside it is just another statement.
For PHP specifically, this means checking what PDO is actually doing. With ATTR_EMULATE_PREPARES left on, PDO interpolates parameters client-side and sends plain SQL, so there are no real prepared statements and nothing to break. Turn emulation off and you get genuine protocol-level prepares, which is what you want for both correctness and plan reuse, and which is exactly the case max_prepared_statements exists to handle.
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);How I Choose
Start with transaction mode, then go looking for the session state your application depends on rather than waiting for it to surface. Grep for LISTEN, for pg_advisory_lock, for temporary tables, and for anything setting a GUC on connect. Move search_path out of a connection hook and into the connection string, where PgBouncer passes it through as part of the startup parameters and it survives correctly.
Keep a second pool in session mode on a different port for the small number of things that genuinely need a stable session, which is usually a migration runner and whatever handles LISTEN. Two pools with clear rules beats one pool with exceptions nobody remembers.
And size the pool against what PostgreSQL can actually do, since the point of pooling is to keep the database out of the region where it spends more time context switching than working. A pool larger than the database can service just moves the queue.