PgBouncer Connection Pooling: Modes and Pitfalls

Most PgBouncer deployments stand or fall on a single line of configuration: pool_mode. That line is usually mistaken for a performance setting — "transaction mode packs in more connections, so transaction it is" — but what you are actually doing is rewriting the session-state contract between your application and your database. Sign that contract without reading it and the bill arrives in production, usually at your busiest hour.

I have written before about what happens when a connection pool saturates and produces a queue-timeout-retry spiral: the signals, the intervention, the runbook. This piece looks one step earlier — how you build the pool largely determines what you will be facing that night. My focus here is PgBouncer's three modes, the true cost of transaction mode, and the defaults that have quietly changed over the past two years.

Three modes, one question: when is the connection returned?

Rather than memorising PgBouncer's modes, reduce them to a single question: when does the server connection go back to the pool? The official configuration documentation gives three answers:

• session (default): the server is released back to the pool after the client disconnects.
• transaction: the server is released back to the pool after the transaction finishes.
• statement: the server is released back after the query finishes; transactions spanning multiple statements are disallowed in this mode.

The fact that the default is session matters more than it looks. Out of the box, PgBouncer is not really a multiplexer — it only removes the cost of establishing connections. If you have long-lived client connections (and a typical application pool does), then in session mode one client connection effectively holds a server connection hostage. So if you dropped PgBouncer in without touching the pool_mode line and are wondering why your connection count did not fall, the system is behaving exactly as documented.

The real gain lives in transaction mode. In most web applications a request opens a short transaction measured in milliseconds and then sits idle for seconds. Return the connection at the transaction boundary and that same server connection serves other clients during the gap. That is what lets hundreds of clients fit onto dozens of server connections.

Statement mode I rarely recommend in practice. Because it forbids multi-statement transactions outright, your application code must never issue a BEGIN. The documentation is explicit about its purpose too: it is meant to enforce "autocommit" mode on the client and is mostly targeted at PL/Proxy. Put it behind a general-purpose API and you will get an error at the first multi-step transaction.

The bill for transaction mode: session state

The moment you switch to transaction mode, you are telling your application that two consecutive queries may not land on the same database connection. That single sentence takes every session-bound PostgreSQL feature off the table.

PgBouncer's feature matrix is not generous on this point, but it is honest. For transaction mode it writes a single word next to the following features — "Never":

• SET / RESET
• LISTEN
• WITH HOLD cursors
• PREPARE / DEALLOCATE
• PRESERVE ROWS / DELETE ROWS temp tables
• The LOAD statement
• Session-level advisory locks

The list of what transaction mode does support is just as instructive: startup parameters (such as client_encoding, DateStyle, Timezone), NOTIFY, WITHOUT HOLD cursors, ON COMMIT DROP temp tables, cached plan reset, and — when configured — protocol-level prepared statements. Note the asymmetry: NOTIFY works but LISTEN does not. You can send notifications from behind transaction mode, but you cannot listen for them. If you built a job queue on LISTEN/NOTIFY, the listener side has to live outside the pool.

So what is the mechanism behind these restrictions? The intuitive answer is wrong, which makes it worth dwelling on. PgBouncer has a server_reset_query parameter; its default is DISCARD ALL and it runs before a connection is returned to the pool. So far so good. But the documentation says something else immediately afterwards: when transaction pooling is used, server_reset_query is not used, because in that mode clients must not use any session-based features in the first place. And server_reset_query_always defaults to 0 — so the reset query runs only in pools that are in session-pooling mode.

The causation therefore runs the other way. Features do not break because a reset query wipes them; they break for a much blunter reason: consecutive transactions land on different server connections, and those connections know nothing about each other's session state. That is precisely why PgBouncer does not bother running a reset query at all.

So why is the DISCARD ALL list still worth reading? Because it is the official inventory of everything PostgreSQL considers session-scoped. In session mode, PgBouncer runs the equivalent of these nine statements before handing the connection to the next client:

CLOSE ALL;
SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
DISCARD SEQUENCES;

Put that list side by side with transaction mode's "Never" table and the overlap is no coincidence: both lists answer the same question — which state in PostgreSQL is bound to a session? UNLISTEN * and LISTEN, pg_advisory_unlock_all() and advisory locks, RESET ALL and SET are all looking at the same place. In session mode PgBouncer clears that state for you; in transaction mode no such guarantee is ever offered.

One more detail: DISCARD ALL cannot be executed inside a transaction block. That is why, in session mode, the cleanup can only happen once the client's transaction is closed.

Prepared statements: the default that changed in 2025

Most guides on the internet are stale here and still say "you cannot use prepared statements in transaction mode." The correct answer changed in 2023, and the default changed in 2025.

According to PostgreSQL's own announcement, PgBouncer 1.21.0 added support for named prepared statements, describing it as one of the project's most requested features (the announcement is dated 17 October 2023; the changelog puts the release on 16 October). The mechanism works like this: PgBouncer tracks protocol-level prepared statements, assigns them internal names of the form PGBOUNCER_{unique_id}, and rewrites the client's commands with those names before forwarding them to the server.

The truly critical change arrived in 1.24.0 (10 January 2025). The changelog line is unambiguous: prepared statement support is now enabled by default, with max_prepared_statements set to 200. In other words, if you installed a current PgBouncer from your distribution's package repository today, the feature is already on whether you asked for it or not.

There are two traps here, and both are easy to miss.

First: the SQL-level PREPARE, EXECUTE and DEALLOCATE commands are not part of this mechanism. In the documentation's words, they are forwarded straight to Postgres. So if your driver uses protocol-level prepared statements (the extended query protocol) you are covered; if you hand-write PREPARE in your code, you will still break in transaction mode. The difference lives in your driver's behaviour rather than your application code — which is precisely why this produces the classic "works on my machine" argument.

Second: if you are upgrading from an older PgBouncer, this is a behaviour change. The changelog notes it should only affect clients that actually use prepared statements, which is true — but "only" may cover a wide set for your application.

A correction on the capacity side: max_prepared_statements is not a wall but the size of an LRU cache kept per server connection. If you have an ORM that generates a large variety of query shapes, hitting the ceiling does not raise an error; the oldest statements are evicted and you pay the cost of re-preparing them. Version 1.24.0 also exposed prepared statement usage counters in SHOW STATS — that is the right place to measure rather than guess.

Advisory locks: a silent, two-sided breakage

The most insidious consequence of moving to transaction mode shows up in advisory locks. Insidious because it produces no error message at all.

The PostgreSQL documentation draws the distinction plainly: locks can be taken at session level (held until released or the session ends) or at transaction level (held until the current transaction ends, with no provision for manual release). pg_advisory_lock() is in the first group; pg_advisory_xact_lock() is in the second.

Now recall the mechanism from a moment ago: in transaction mode DISCARD ALL does not run, so neither does the pg_advisory_unlock_all() inside it. The result is the opposite of what intuition suggests. Your session lock is not released when the transaction ends; it stays hanging on that server connection. The connection goes back to the pool, is handed to another client, and the lock is still there — it stays until the connection itself closes via server_lifetime or an idle timeout. Meanwhile your next query lands on an entirely different connection, one that does not hold the lock.

So two failures run at once: no protection when you need the lock, and a leaked lock sitting in the pool when you think you released it. If you protect a cron job with a session advisory lock so that only one copy runs at a time, the second copy starts running quite happily — and the leaked locks accumulate over time.

The durable fix is to bind the lock to the transaction's lifetime:

-- Unreliable in transaction mode: the lock stays hanging on the connection
SELECT pg_advisory_lock(42);

-- Correct: the lock's lifetime matches the transaction boundary
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- protected work goes here
COMMIT;

There is one condition: the entire protected unit of work has to fit inside that transaction. If it does not, you need to change your locking strategy — not your pooling mode.

If you cannot touch the locking strategy right away, server_reset_query_always = 1 offers a stopgap: it runs the reset query in transaction mode too. The documentation describes this as a crutch for "broken setups that run applications that use session features over a transaction-pooled PgBouncer," and states its effect plainly — it changes non-deterministic breakage to deterministic breakage, with clients always losing their state after each transaction. It stops the leak; it does not make the lock work.

Sizing: the two-layer pool trap

The defaults are PgBouncer's most misleading aspect, because every one of them is modest and none of them was chosen for your workload. From the official documentation:

Parameter	Default	What it does
max_client_conn	100	Total client connections PgBouncer accepts
default_pool_size	20	Server connections per (database, user) pair
min_pool_size	0 (disabled)	Minimum connections kept ready in the pool
reserve_pool_size	0 (disabled)	Extra connections openable under pressure
reserve_pool_timeout	5.0	Seconds before the reserve kicks in
max_db_connections	0 (unlimited)	Server-connection ceiling per database
max_user_connections	0 (unlimited)	Server-connection ceiling per user
max_db_client_connections	0 (unlimited)	Client-connection ceiling per database
max_user_client_connections	0 (unlimited)	Client-connection ceiling per user
query_wait_timeout	120.0	Seconds a client tolerates waiting for a server

The last two arrived in 1.24.0 and are rarely used — yet in a multi-tenant deployment they are exactly what stops a single tenant from filling the entire queue.

A max_client_conn default of 100 explains why deployments that install PgBouncer "to handle thousands of clients" and then forget the setting keep hitting the same wall. This parameter exists to be high; even so, know its two costs before you raise it without limit. First, file descriptors: the documentation warns that when you increase this setting the operating system's file descriptor limits may also have to be increased, and gives the theoretical maximum as max_client_conn + (max pool_size * total databases * total users). Second, memory: the widely quoted "2 kB per connection" figure describes the case without prepared statements. With prepared statements active — the default since 1.24 — each client keeps a packet-rewriting buffer of at most four times pkt_buf, and the documentation's own worked example reaches ~17 MB for 1000 clients with 200 unique queries. Still cheap, but not 2 kB.

The expensive one is default_pool_size; that is what actually consumes PostgreSQL resources.

The real trap here is that in most deployments two pools sit on top of each other. Your application has its own connection pool (HikariCP, pgx, SQLAlchemy, pg-pool, whichever you use) and now there is PgBouncer as well. Your true concurrency ceiling is not the product of application_pool × replica_count and default_pool_size — it is the smaller of the two. Leave the application pool at 50 while writing default_pool_size = 20 in PgBouncer and 30 of your application's connections will never do any work; they will simply wait in PgBouncer's queue and come back as errors once query_wait_timeout expires. Personally, in transaction mode I would deliberately keep the application pool small and leave the packing to PgBouncer — defining a large pool in two places only moves the queue up one layer.

One detail about default_pool_size that arrived with 1.24.0: setting it to 0 now means unlimited. I would not recommend it in production. The whole purpose of a pool is to cap the concurrency reaching your database; remove the cap and PgBouncer stops being a protection and becomes a pipe.

One process, one core, one point of failure

Three operational facts get overshadowed by the mode debate, and all three should be known before you deploy.

PgBouncer uses one core. The documentation says so directly: PgBouncer is single-threaded and uses one CPU core per instance; to use more cores you run multiple instances listening on the same port via so_reuseport. The advice to be generous with max_client_conn has to be read alongside that ceiling — as client counts grow, the bottleneck may be that single core rather than the pool.

PgBouncer is a single point of failure. You have just put all your database traffic behind one process; if it dies, your application is down even though the database is healthy. A serious deployment either runs multiple instances behind a virtual IP and a load balancer, or deploys PgBouncer alongside the application servers as a sidecar. That is beyond this article's scope, but it belongs in your plan.

Changing the mode does not require downtime. The RELOAD command re-reads the configuration and updates changeable settings, and PgBouncer supports online restart/upgrade without dropping client connections. Combine that with per-database and per-user pool_mode and you can migrate service by service rather than all at once — I would leave the riskiest service for last.

Finally: PgBouncer is not the only option. pgcat, Odyssey and Supavisor make different trade-offs around multi-core operation, load balancing and read/write splitting, and cloud providers offer managed poolers of their own. PgBouncer remains the most common choice not because of its scope but because it does a narrow job predictably.

Observability: the admin console

Do not guess while tuning PgBouncer; the admin console tells you exactly what is stuck. The documented meanings of the SHOW POOLS columns:

• cl_active: client connections that are either linked to server connections or are idle with no queries waiting to be processed.
• cl_waiting: client connections that have sent queries but have not yet got a server connection.
• sv_active: server connections that are linked to a client.
• sv_idle: server connections that are unused and immediately usable for client queries.
• sv_used: server connections idle for longer than server_check_delay, so they need server_check_query to run before reuse.
• maxwait: how long the first (oldest) client in the queue has waited, in seconds.

maxwait is the only genuine alarm signal in that table, and the documentation frames it the same way: if it starts increasing, the current pool of servers is not handling requests quickly enough. If you had to pick a single pair of metrics for your monitoring, pick cl_waiting and maxwait. For the historical view there is SHOW STATS: total_xact_count, total_query_count, avg_query_time, and total_wait_time, which reports the time clients spent waiting for a server in microseconds.

Version 1.25.0 (9 November 2025) added something that makes this easier: if a client waits in the queue for more than 5 seconds without getting a connection, PgBouncer sends it a NOTICE message; the duration is adjustable or disableable via query_wait_notify. The same release added a transaction_timeout setting — configurable both globally and at the user level — a good safety valve against a forgotten open transaction holding a pool slot indefinitely.

The standard way to wire this into monitoring is a Prometheus exporter such as pgbouncer_exporter, which connects to that same admin console and scrapes SHOW POOLS/SHOW STATS. All it needs is a read-only user defined under stats_users — do not hand it full admin console access.

Security: the easiest section to skip

PgBouncer is a network service sitting in front of all your database traffic and your entire authentication flow — and it has seen a serious run of CVEs over the past year. It would be convenient for an article about mode selection to skip this section, but it cannot.

Top of the list: the auth_type default is still md5. The supported values are cert, md5, scram-sha-256, plain, trust, any, hba, ldap and pam. For a new deployment you should have a good reason to pick anything other than scram-sha-256.

The TLS defaults are surprising too. client_tls_sslmode defaults to disable — if a client requests TLS it is ignored and traffic flows over plain TCP. server_tls_sslmode defaults to prefer, meaning TLS is attempted first but falls back to plain TCP if refused, and the server certificate is not validated. Switching to SCRAM while leaving these two untouched means hardening authentication while carrying the credentials over an open network.

On the version side the picture is clearer still. Release 1.25.2 (8 May 2026) closes four CVEs at once; the scores in parentheses are NVD's own CVSS 3.1 base scores, with the CNA score noted where it differs:

• CVE-2026-6665 (NVD 9.8 CRITICAL; CNA 8.1 HIGH): the SCRAM code does not correctly check the return value of strlcat() when building the client-final-message. A malicious backend sending a server-final-message with a long nonce can trigger a stack overflow.
• CVE-2026-6664 (7.5 HIGH, CNA score): an integer overflow in network packet parsing code bypasses a boundary check; an unauthenticated remote attacker can crash PgBouncer with a malformed SCRAM authentication packet.
• CVE-2026-6666 (NVD 7.5 HIGH; CNA 5.9 MEDIUM): a possible null pointer reference can lead to a crash if a server sends an error response without a SQLSTATE field.
• CVE-2026-6667 (4.3 MEDIUM, CNA score): no appropriate authorization check is performed for the KILL_CLIENT admin command, so every user with access to the administration console could run it, when it should have been restricted to users listed in admin_users.

The previous patch release, 1.25.1 (3 December 2025), closed CVE-2025-12819 (NVD 8.1 HIGH; CNA 7.5 HIGH): an untrusted search path in the auth_query connection handler allowed an unauthenticated attacker to execute arbitrary SQL during authentication via a malicious search_path parameter in the StartupMessage. It requires three conditions to hold simultaneously — search_path included in track_extra_parameters, auth_user set, and auth_query written without fully-qualified object names — but the third is the default configuration itself. If you use auth_query, schema-qualifying its object names is a free hardening step.

Checking which version of PgBouncer your distribution's package repository is shipping may well be more urgent than anything else in this article.

A decision framework

For a new PgBouncer deployment, answer these in order:

1. Version. Are you on 1.25.2 or later? If not, solve that first.
2. Authentication and transport. Is auth_type = scram-sha-256? Have you moved client_tls_sslmode and server_tls_sslmode off their defaults? If you use auth_query, are its object names schema-qualified?
3. Mode. Does your code use SET, LISTEN, WITH HOLD cursors, session advisory locks or persistent temp tables? If so, either remove them or leave that service in session mode. Since the mode is settable per database and per user, you do not have to make one global decision.
4. Prepared statements. Does your driver use the protocol level, or are you writing SQL-level PREPARE? The latter will not work in transaction mode.
5. Advisory locks. Convert every remaining pg_advisory_lock call to pg_advisory_xact_lock; server_reset_query_always = 1 can stop the leak in the meantime.
6. Sizing. Be generous with max_client_conn and conservative with default_pool_size, and raise the file descriptor limit alongside it. Compare your total application pool against the PgBouncer pool, and make sure the bottleneck is one you chose deliberately.
7. Scale and continuity. If one core will not be enough, plan a multi-instance setup with so_reuseport; and consider redundancy for PgBouncer itself.
8. Measurement. Put cl_waiting and maxwait from SHOW POOLS, and total_wait_time from SHOW STATS, under monitoring.
9. Safety valves. Set query_wait_timeout, transaction_timeout and client_idle_timeout deliberately, based on your workload.

Conclusion

Reading the PgBouncer documentation end to end leaves me with this: the tool does not hand you a performance knob, it offers you a trade. The packing density that transaction mode buys is what you get in exchange for giving up PostgreSQL's notion of a session. If your application leans on session state, the bill comes due — sometimes as an explicit error, and sometimes, as with advisory locks, without a sound.

That was also the biggest surprise in the research. The intuitive explanation — "DISCARD ALL wipes everything" — is both widespread and wrong; in transaction mode that query never runs. The restrictions exist not because of a cleanup but because the connections shift under you. The difference is not academic: believe the first story and you will assume your session advisory lock was released, when in fact it is left hanging in the pool.

That is why it is more accurate to treat the pool_mode line as an architectural boundary rather than a setting. Choose it knowing which side of that boundary you are standing on, and PgBouncer runs quietly for years.

Official Sources

• PgBouncer — Configuration (pool_mode, server_reset_query, defaults)
• PgBouncer — Features (pooling mode feature matrix)
• PgBouncer — Usage (SHOW POOLS / SHOW STATS)
• PostgreSQL — DISCARD
• PostgreSQL — Advisory Lock Functions (System Administration Functions)
• PostgreSQL News — PgBouncer 1.21.0 released: now with prepared statements
• PgBouncer changelog (NEWS.md, GitHub)
• NVD — CVE-2026-6664 (PgBouncer SCRAM integer overflow)