PgBouncer: choose a pool mode and test application compatibility

PgBouncer is a connection pooler for PostgreSQL. Applications connect to it, and it assigns database connections from a pool. It can reduce connection churn and control how many backend connections an application uses, but the pooling mode determines which session assumptions remain valid.

Start by identifying the problem: repeated connection setup, too many idle connections, or a queue caused by slow transactions. Pooling changes connection reuse; query plans, indexes, and lock contention still need their own investigation. This guide covers mode selection, connection budgets, and a compatibility test plan for an existing private test environment.

Validation, November 9, 2026: the SQL probe passed a PostgreSQL syntax parser; the pool-settings fragment and connection-budget arithmetic were checked locally. No PostgreSQL or PgBouncer process was available, and Docker Engine was unavailable. Authentication, TLS, backend reassignment, prepared statements, temporary-table cleanup, queue behavior, performance, and Hivenet deployment remain untested. Expected outputs below are checks to perform, not observed results.

Where PgBouncer fits

A typical request passes through an application’s own connection pool, then PgBouncer, then PostgreSQL. Those are separate limits. For example, an ORM can retain connections to PgBouncer while PgBouncer uses fewer database backends for short transactions.

Placement depends on your deployment. Our API hosting guide compares application-hosting options; adding a pooler also means owning its configuration, monitoring, and recovery. PgBouncer’s FAQ discusses placement and explains that database failover detection requires an external arrangement.

If you are still deciding who should operate the database, start with the PostgreSQL hosting guide. For a disposable database exercise, use the private PostgreSQL Docker Compose guide. That example does not include PgBouncer or publish a host database port: this guide’s loopback commands require a separately configured, authenticated lab endpoint. Adding a pooler to the Compose network needs its own configuration and access tests. Keep Hivenet evaluation within the documented development/testing scope.

Choose a pooling mode before choosing pool sizes

Mode	When the backend becomes reusable	What to assess
Session	After the client disconnects	A useful starting point when the application depends on session state; long-lived clients can retain backends.
Transaction	After the transaction ends	Suitable for sharing backends between short, independent transactions, after compatibility testing.
Statement	After the statement completes	Disallows multi-statement transactions; a poor default for an application that uses them.

The official pooling feature map is the starting reference. In transaction mode, one client connection can use different PostgreSQL backends across transactions. An open transaction, including one left idle, still occupies a backend.

Record the exact versions of PgBouncer, PostgreSQL, the driver, and any ORM before testing:

pgbouncer --version

The release notes, checked on November 9, 2026, list PgBouncer 1.26.1 and describe security fixes and parameter-tracking changes. Use a maintained, patched build. The notes also flag issues around search_path tracking introduced in 1.26.0, so test schema selection explicitly rather than assuming every SET command survives backend changes.

Audit session-dependent behavior

List the features used by normal requests, background jobs, migrations, and long-lived connections. Check each against transaction pooling:

• Session settings: prefer transaction-scoped settings where the operation allows them. PostgreSQL’s SET LOCAL documentation limits their effect to the current transaction. Tracking other settings depends on the parameter and the PostgreSQL/PgBouncer versions.
• Notifications: LISTEN registers a database session. Give a long-lived listener a session-pooled or direct connection instead of assuming it can follow a changing backend.
• Temporary tables: PostgreSQL supports ON COMMIT DROP. A table created, used, and dropped within one transaction differs from a temporary table expected to survive across transactions.
• Locks: distinguish session-level from transaction-level advisory locks. A lock attached to a backend session cannot safely represent ownership by a client that may switch backends.

Use a separate, private session-pooled or direct route for a tool that needs persistent session state. Check the tool’s documentation before routing migrations through the same endpoint as ordinary requests.

Prepared statements need a driver-specific check

Protocol-level named prepared statements and SQL PREPARE are different mechanisms. PostgreSQL’s SQL PREPARE creates a statement in the current database session. Do not use a successful SQL PREPARE/EXECUTE pair on one backend as proof of compatibility across transactions.

PgBouncer can track protocol-level named statements with a nonzero max_prepared_statements. Check the driver’s requirements too. For example, the Psycopg prepared-statement guide specifies Psycopg 3.2+, PgBouncer 1.22+, and client libpq 17+ for its documented support path, with a separate workaround for older libpq. These are compatibility requirements; use maintained releases for deployment.

Run the actual application queries often enough to trigger automatic preparation. Then repeat them after backend reassignment, transaction rollback, and connection recycling. A first successful query can miss a failure that appears only after the driver’s preparation threshold.

Build a connection budget across every instance

Begin with PostgreSQL’s connection limits. Reserve capacity for administration, monitoring, maintenance, and other clients before assigning a budget to application pools. Increasing max_connections also increases some PostgreSQL resource allocations.

As a hypothetical budget, a database with 100 slots might set aside 20 for reserved and operational access, leaving 80. Two PgBouncer instances capped at 30 backend connections each would consume up to 60 for the relevant database, leaving 20 within that application budget. This is arithmetic for planning, not a sizing recommendation or benchmark.

PgBouncer’s limits apply per instance. Its default_pool_size applies to each user/database pair, while max_db_connections caps a PgBouncer database alias. Count multiple aliases that target the same PostgreSQL database, additional users, reserve pools, and deployment replicas. A frontend client limit is not the backend connection budget.

The following is only a pool-settings fragment for an already configured isolated test instance. It is not a complete configuration: database mappings, authentication, listening addresses, and TLS still need their own setup.

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 10
reserve_pool_size = 0
max_db_connections = 30
max_prepared_statements = 100
query_wait_timeout = 10

Review these options in the configuration reference. The ten-second queue timeout is a test choice, not a query execution timeout or a production recommendation. Match application deadlines and cancellation behavior to the intended queue policy.

Budget application pools as well. Four replicas with three workers and a maximum of ten connections per worker can create 120 PgBouncer clients. Include permitted overflow and background workers. The SQLAlchemy pooling documentation explains pool limits and reset behavior; use the equivalent guidance for your actual driver.

Keep both connection hops private and authenticated

Review application-to-PgBouncer and PgBouncer-to-PostgreSQL separately. Bind only the intended interfaces, restrict network access, and keep administrative access separate from the application role. Verify authentication on both hops.

TLS must also be checked on each hop. For libpq clients, verify-full validates the certificate chain and hostname. For PgBouncer’s PostgreSQL connection, configure server_tls_sslmode = verify-full with the correct server_tls_ca_file and a hostname covered by the server certificate. The PgBouncer TLS reference explains these settings. A private network does not itself prove that either peer’s identity was checked.

Run a small transaction probe, then the application tests

Use a disposable database and a non-superuser test role allowed to connect and create temporary tables. For the following command, assume an authenticated PgBouncer endpoint already exists on loopback port 6432 with the alias pooler_lab. Replace the role and endpoint with your lab values. Keep passwords out of command lines.

Save this as pooler-probe.sql:

BEGIN;
SET LOCAL statement_timeout = '2s';
CREATE TEMP TABLE pooler_article_probe (id integer) ON COMMIT DROP;
INSERT INTO pooler_article_probe VALUES (1), (2);
SELECT pg_backend_pid() AS backend_pid,
       current_setting('statement_timeout') AS timeout,
       count(*) AS row_count,
       sum(id) AS total
FROM pooler_article_probe;
COMMIT;
SELECT to_regclass('pg_temp.pooler_article_probe') IS NULL AS temp_table_gone;

Run it first against the direct test database, then through PgBouncer. This is the pooled invocation:

psql "host=127.0.0.1 port=6432 dbname=pooler_lab user=pooler_app connect_timeout=5" -X -v ON_ERROR_STOP=1 -f pooler-probe.sql

psql uses -X to ignore local startup customizations and ON_ERROR_STOP to stop on a script error. Expected values are 2s, two rows, a total of three, and temp_table_gone = true. The backend PID is informational; it is not expected to match between runs. After COMMIT, transaction pooling may assign the final query to a different backend. A true result alone therefore does not prove cleanup on the backend that created the table. Check that lifecycle separately on the direct connection or in a controlled single-backend lab; the parser check does not establish it.

This probe checks a transaction-scoped setting and temporary-table workflow. It does not exercise the driver’s prepared-statement protocol, tenant isolation, or production concurrency. Add these application-specific checks:

1. Run representative reads and writes with the real driver and compare returned data with the direct-connection baseline.
2. Use concurrent clients in the same user/database pool. Verify that a client works after it receives a different backend PID in a later transaction; a single quiet client may keep getting the same backend.
3. Trigger a controlled error, roll back, and confirm that the next operation succeeds with the intended transaction settings and no state from another client.
4. Exercise prepared queries, cancellation, timeouts, migrations, and any schema or tenant selection the application uses.

Observe the queue before increasing a limit

Configure a monitoring role in stats_users, then inspect the virtual pgbouncer administration database:

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pooler_monitor connect_timeout=5" -X -c "SHOW POOLS;"

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pooler_monitor connect_timeout=5" -X -c "SHOW STATS;"

The admin-console reference defines cl_waiting as clients waiting for a server connection. Compare it with backend activity, queue wait, and transaction duration. Check PostgreSQL’s pg_stat_activity for long or idle transactions and waits. An application-side pool can also queue requests before they reach PgBouncer.

To investigate query workload over time, use pg_stat_statements to compare execution time and call counts. Read those results alongside the pooler’s queue statistics. The guide’s session-based interval example needs a direct database connection; a transaction-pooled connection can change backends between statements.

Measure request latency, errors, and database load under representative concurrency. Our FastAPI deployment guide discusses application load testing. More connections can increase contention when PostgreSQL is already busy; investigate the bottleneck before raising the cap.

Roll out the chosen mode to a small share of traffic with a tested rollback route. Before related schema changes, keep a verified PostgreSQL backup and restore process. Record the versions, driver settings, correctness checks, and queue behavior that justified the change.

Before treating the pooler as ready for a workload, record the exact versions, authentication and TLS results on both hops, driver compatibility, rollback behavior, and the connection budget across all instances. A valid SQL script or a lower backend count does not establish application correctness or production readiness.