PostgreSQL connection pooling with PgBouncer

Transaction pooling is the mode most teams choose when they want PgBouncer to share a small set of PostgreSQL server connections among many clients, and it is also the mode that breaks application code without warning. In this mode PgBouncer assigns a server connection to a client only for the duration of a transaction, then returns it to the pool. The next transaction from the same client may run on a different backend process. Anything that stores state on the server connection, rather than inside a transaction, can therefore appear to work in testing and fail intermittently in production, depending on which backend happens to serve each request.

The features most often affected are session-level SET and RESET commands, session-scoped advisory locks (pg_advisory_lock, as opposed to the transaction-scoped variant), temporary tables, cursors declared WITH HOLD or otherwise kept open across transactions, LISTEN and NOTIFY subscriptions, and server-side prepared statements. A SET issued in one transaction may be invisible to the next one. A session advisory lock may be acquired on one backend and never released by the client that expects it. A temporary table created in one request may not exist in the next. LISTEN registrations are tied to a backend, so notifications may be delivered to a connection the client is no longer using. Prepared statements are the subtle case: drivers and ORMs often prepare a statement once and reuse its name on every later call, so a request that lands on a backend without that statement fails with an error about a missing prepared statement.

Inventory before switching modes. Start with the codebase and the driver configuration, not the database. Search for SET and SET SESSION, RESET, pg_advisory_lock, CREATE TEMP or TEMPORARY TABLE, DECLARE with HOLD, LISTEN and NOTIFY, and any call that names a prepared statement. Then check the ORM and driver settings: many libraries have a switch that disables or changes server-side prepared statement caching, and some enable it by default. Ask each application team to confirm which features they use and whether any framework, migration tool or background job issues session commands on their behalf. Record the result per service, because one legacy job can force a whole pool into session mode.

Decide what must change. Session-scoped state should be rewritten as transaction-scoped state where possible: use SET LOCAL inside a transaction, pg_advisory_xact_lock in place of session locks, and ordinary tables or CTEs instead of temporary tables that must persist across requests. Cursors and LISTEN loops generally need a dedicated connection, so they belong on a separate pool or a direct connection in session mode. Prepared statements usually need either disabling client-side caching or moving the affected services to a session-mode pool. The right split depends on how many services are affected and how much rewriting the team can absorb, so treat it as a scoped decision recorded before cutover.

Verify the result. Run the integration suite through PgBouncer in transaction mode with a pool small enough to force connection reuse, since larger pools hide the problem. Confirm with the vendor documentation for each driver and ORM how its prepared statement cache behaves under transaction pooling and which version introduced the setting you plan to use. Illustrative example: a service sets a search_path with a plain SET at login, then runs a report that reads unqualified table names; under transaction pooling the report sometimes queries the wrong schema. A SET LOCAL inside the report transaction removes the dependency. Ask the PgBouncer documentation for the current list of supported settings before you rely on any of them.

The verification plan should prove that PgBouncer changes where client connections land, but not what the application does with the data. Start from the application feature inventory. Every feature that opens connections, runs multi-statement transactions, uses session state or depends on prepared statements gets a functional test that runs through the pooler and the same test against a direct connection. Compare more than pass or fail. Compare returned values, error codes, row counts and final table contents. Session-level behavior deserves particular attention, because temporary tables, session variables, advisory locks, LISTEN and NOTIFY, and prepared statements can behave differently depending on the pool mode. Confirm against the documentation for the PgBouncer version you plan to deploy which features your chosen mode supports, and write tests for each feature that the mode does not support.

Load tests should reproduce the peak client count taken from the application inventory, plus a margin the team agrees on before the test starts. Run each test long enough for the pool to reach steady state, and watch PgBouncer's own statistics for client wait time, active and idle server connections and the number of clients queued for a server connection. The purpose is to confirm that queueing is predictable: latency rises in a controlled way, clients wait instead of failing, and the pool never pushes PostgreSQL past its configured connection limit. Repeat the run with a smaller pool to show how the sizing decision in the change record behaves at the edge. Include a few long-running transactions in the mix, because they can hold server connections and starve short requests if the pool is sized too tightly.

Failure tests should cover four events: a PgBouncer restart, a PostgreSQL restart, dropped server connections (for example, terminating backend processes with administrative commands) and expired or rotated credentials. Before each run, write down the expected client behavior. A safe pool returns clear connection or query errors that the application logs, and it retries only where the transaction is safe to repeat. It must not return stale results, duplicate writes or a commit the client believes failed. After each event, run the same reconciliation queries used in functional testing and compare the database state with the transaction log kept by the test harness. The rollback test is the last failure test: move each application back to direct connections, repeat the functional checks, and confirm that stopping the pooler leaves no residual effect on the database or the applications.

To prove that the cutover did not change transaction outcomes, run a fixed set of business transactions with identical inputs, once through the pooler and once directly, and compare commit or rollback status, returned values and the resulting table state. Store the test scripts, PgBouncer and PostgreSQL versions, configuration files, pool settings, timestamps, results and sign-offs in the change record. Record every deviation with its cause and the approver who accepted it. Confirm with the security and compliance owners which evidence they need, including who ran the tests and where the raw output is kept, so the record can be reviewed after the change without rerunning anything.

Watch five things after go-live: client connections, server connections, wait queues, PgBouncer's administrative statistics, and authentication errors. The PgBouncer admin console, reached through a virtual database named pgbouncer, exposes SHOW CLIENTS, SHOW SERVERS, SHOW POOLS and SHOW STATS. A sustained count of waiting clients in SHOW POOLS means the pool is too small for the workload or that transactions hold server connections too long. Trend query and transaction rates and wait times from SHOW STATS so that changes in application behavior appear before users report them. Authentication failures show up in the PgBouncer log and the PostgreSQL log, and a sudden rise often follows a password rotation or an edit to the auth file. On the PostgreSQL side, compare server connections against max_connections and watch memory per backend, since each pooled server connection is still a backend process. Confirm exact column names and log messages against the PgBouncer version you run.

Back up the configuration and auth file. The PgBouncer configuration file and the authentication file are part of your recovery set. With those two files, plus any definition of the auth_query role, you can rebuild the pooler on a new host with the same databases, users and settings. Keep them in version control or your configuration management system, and handle password hashes and secrets under your existing secret-management policy. Pooled session state does not need backup. Client sessions and server connections are transient, and PgBouncer holds no application data. PostgreSQL backups remain the data protection layer, as covered in the backup and recovery design. Until you have restored the pooler from the stored files in a non-production environment and confirmed that an application connects and authenticates, the backup is only an assumption.

Treat pool sizes as controlled configuration. The settings that matter most are default_pool_size, max_client_conn, max_db_connections and the per-database and per-user pool limits. Each one caps how many server connections PgBouncer can open, and the combined total must fit inside PostgreSQL's max_connections with room left for administrative sessions, replication and maintenance. Record every change in a ticket with the reason, the expected effect on wait queues, the owner, and the previous value for rollback. Test changes in staging under a load profile that resembles production. A larger pool can move the bottleneck to database CPU or memory, so judge each change by database health as well as by queue length. Ask your team to define who may approve an increase before the first incident forces the question.

Plan PgBouncer upgrades as security maintenance, separate from PostgreSQL minor releases. Version 1.26.1 was released on October 14, 2026 with security fixes, so any installation running an earlier release should be reviewed promptly after you read the release notes. Subscribe to the project's announcements, read the changelog for configuration changes, and confirm in staging that your configuration file still loads on the new binary. Keep the previous binary and configuration on hand so rollback is a package or file swap followed by a restart. Because PgBouncer sits in the client connection path, coordinate the restart with the application team and confirm that clients, including those with long-lived connections, reconnect cleanly. Record the installed version in your inventory so that future advisories can be matched to your estate quickly.

Major PostgreSQL upgrades change how the pooler and the application behave, even when PgBouncer itself does not change. After a major upgrade, rerun the application feature tests that pooling can break. Prepared statements, session-level SET commands, LISTEN and NOTIFY, advisory locks, temporary tables and session-scoped settings all depend on which server connection a client receives. Confirm the authentication path as well. PostgreSQL 18 deprecated MD5 password authentication in favor of SCRAM, so check that the auth file hashes and client drivers match the method your pg_hba.conf enforces. Run these tests through PgBouncer, because that is the path production traffic takes. Finally, hand over five documents before the pooler is treated as operational: an architecture diagram, a pool mode rationale, an application feature inventory, a restart and rollback runbook, and an ownership record that names who maintains the configuration, who is on call, and who approves pool size changes.