Backend
PgBouncer Pooling Modes: Session vs Transaction vs Statement
July 20269 min read
Photo by KeepActive Australia from Melbourne, VIC, Australia via Wikimedia Commons (CC BY-SA 4.0)
Session pooling assigns a backend connection to a client for the whole session and only releases it on disconnect, preserving every Postgres feature but limiting concurrency to the pool size. Transaction pooling releases the backend after each transaction, so many idle clients can share a small pool. Transaction mode is the standard choice for stateless web and API workloads.
Yes, since PgBouncer 1.21 you can, as long as max_prepared_statements is set to a non-zero value. As of 1.24.1 it defaults to 200 and is enabled out of the box. The support only covers protocol-level prepares from the driver, not raw SQL PREPARE statements you type yourself, which PgBouncer cannot see.
Use Little's Law: multiply peak transactions per second by average transaction duration in seconds to get the floor, then add 30 to 50 percent headroom. Cap the result near (cpu_cores times 2) plus 1 per database for CPU-bound loads, and make sure the sum of all pools stays below the Postgres max_connections setting.
This happens in transaction or statement pooling mode when the backend that prepared a statement is not the same backend that runs the execute. Fix it by upgrading to PgBouncer 1.21 or later and setting max_prepared_statements to a non-zero value so PgBouncer re-prepares statements on the fly. Older versions require disabling prepared statements in the driver instead.
Statement pooling releases the backend after every single statement and forbids multi-statement transactions entirely, effectively forcing autocommit. It is only appropriate for niche setups like query routers in front of sharded Postgres. Ordinary applications should use transaction mode instead, because statement mode breaks normal transaction semantics.
Photo by KeepActive Australia from Melbourne, VIC, Australia via Wikimedia Commons (CC BY-SA 4.0)
Key Takeaway
PgBouncer offers three pooling modes. Session pool keeps a backend for a whole client session and preserves every Postgres feature. Transaction pool reuses backends between transactions for far higher concurrency. Statement pool recycles per statement. Pick transaction mode for web apps, then size default_pool_size to your real concurrent-transaction count, not your client count.
Postgres opens a full OS process per connection, so a few hundred idle clients can quietly eat gigabytes of RAM and starve the ones doing real work. PgBouncer sits in front of Postgres as a lightweight connection pooler: thousands of clients connect to it, and it multiplexes them onto a small pool of real backend connections. The single setting that decides how aggressively it multiplexes is pool_mode, and choosing it wrong is how you either waste your whole pool or corrupt query results across users.
I run PgBouncer in front of every Postgres instance I own on my VPS, and the pooling mode is the first knob I set. Here is what each mode actually does, the prepared-statement trap that used to make transaction mode painful, and a sizing method that survives a traffic spike.
The pooling mode decides one thing: how long a server connection stays assigned to a client connection before it goes back into the pool for someone else to use. That single decision cascades into which Postgres features you are allowed to use, because features like prepared statements, session variables, and temp tables live on the server connection, not the client one. The longer the assignment, the more session state survives, and the fewer connections you can share.
In session mode a backend is handed to a client the moment it connects and only returned when that client disconnects. It behaves exactly like talking to Postgres directly, so every feature works: SET commands, LISTEN/NOTIFY, session-scoped temp tables, advisory locks, and text-level PREPARE all persist. The cost is that concurrency is capped at your pool size. If default_pool_size is 20, only 20 clients can be connected and doing anything at once; client 21 waits. Session mode is the right choice when clients hold connections briefly and you depend on session state, but it wastes backends on idle web clients that keep a connection open between requests.
In transaction mode a backend is assigned only for the duration of a transaction. The instant your transaction commits or rolls back, PgBouncer returns that backend to the pool, even if the client stays connected. This is the mode that lets 2000 web clients share 25 backends, because most of those clients are idle between requests. It is the default recommendation for stateless request/response apps: NestJS services, serverless functions, anything that opens a connection, runs a query or a short transaction, and moves on.
Transaction mode breaks anything that assumes the same backend across transactions. A SET statement outside a transaction, a session-scoped temp table, LISTEN/NOTIFY, and unnamed cursors can all land on a different backend next time and silently misbehave. If your ORM sets session variables per connection, verify they run inside the transaction or use SET LOCAL.
Statement mode returns the backend after every single statement. It carries all the session-state limitations of transaction mode and adds one more: multi-statement transactions are forbidden outright, because the second statement of a transaction might land on a different backend. PgBouncer effectively enforces autocommit. This mode exists for niche setups like fan-out query routers in front of sharded Postgres. For an ordinary application it is almost never what you want; if you are reaching for it, look at your query patterns first.
| Property | Session | Transaction | Statement |
|---|---|---|---|
| Backend released after | Client disconnect | Each transaction | Each statement |
| Concurrency vs pool_size | Low (1 client = 1 backend) | High (many clients share) | Highest |
| Multi-statement transactions | Yes | Yes | No |
| Session vars / temp tables / LISTEN | Safe | Unsafe unless transaction-scoped | Unsafe |
| Prepared statements | Native | Needs max_prepared_statements greater than 0 | Needs max_prepared_statements greater than 0 |
| Best fit | Long-lived stateful clients | Web / API / serverless | Sharded fan-out routers |
Here is the failure that taught me to read the changelog. Modern Postgres drivers use protocol-level prepared statements by default. Under old PgBouncer in transaction mode, a client would PREPARE a statement on backend A, get a different backend B on the next transaction, try to EXECUTE, and Postgres would throw a prepared statement does not exist error. The classic workaround was disabling prepared statements in the driver entirely, sacrificing plan caching.
PgBouncer 1.21 fixed this properly. When max_prepared_statements is non-zero, PgBouncer tracks each protocol-level prepare, gives it an internal name, and re-prepares it on whatever backend a client lands on before forwarding the execute. As of 1.24.1 this is on by default with max_prepared_statements set to 200. The catch, and it is a real one: this only covers protocol-level prepares from the driver. A raw SQL PREPARE foo AS ... that you type yourself is invisible to PgBouncer because it does not parse query text, and DEALLOCATE sent as raw SQL is equally invisible.
; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction
; enable prepared-statement tracking in transaction mode (default 200 since 1.24.1)
max_prepared_statements = 200
; total client connections PgBouncer will accept
max_client_conn = 2000
; backends opened to Postgres per (user, database) pair
default_pool_size = 25
; small headroom for admin / bursty queries
reserve_pool_size = 5
reserve_pool_timeout = 3Check what you are actually running with SHOW VERSION on the PgBouncer admin console before assuming prepared statements work in transaction mode. Anything below 1.21 does not have the feature at all, and 1.21 to 1.24.0 needs max_prepared_statements set explicitly.
The mistake everyone makes is sizing the pool to client count. In transaction mode you size it to concurrent transactions, which is far smaller. Use Little's Law: the number of backends you need equals your peak transaction rate multiplied by average transaction duration. If you serve 500 transactions per second and each one holds a backend for 20 milliseconds, you need 500 times 0.02, which is 10 backends. Add roughly 50 percent headroom for jitter and you land around 15.
There is a hard ceiling from the other direction too. Postgres itself does best with a small number of active backends, roughly cpu_cores times 2 plus 1 for CPU-bound work. Piling on more connections past that point makes throughput worse, not better, because backends fight over the same cores and locks. So your pool has a floor set by concurrency demand and a ceiling set by Postgres parallelism, and the sum of all pools across every database must stay under the server max_connections.
# PgBouncer admin console: connect and watch live pool state
psql -p 6432 -U pgbouncer pgbouncer
-- backends busy/idle and clients waiting per pool
SHOW POOLS;
-- if cl_waiting is consistently > 0, default_pool_size is too small
-- if sv_idle is always high, you are over-provisioned
-- Little's Law sanity check:
-- default_pool_size >= peak_tps * avg_tx_seconds * 1.5My rule of thumb: transaction mode with max_prepared_statements left at its default, default_pool_size derived from Little's Law and capped by core count, and reserve_pool_size for the occasional burst. Reach for session mode only when you genuinely need per-session state, and treat statement mode as a specialist tool you will probably never configure. Get the mode right first; the pool size is just arithmetic after that.