PgBouncer in front of Postgres is the standard fix for connection exhaustion, and the standard way to break an application in ways that only appear under load. The reason is pool mode: session pooling is safe and pools almost nothing, transaction pooling gives you the numbers you wanted and quietly removes session state your ORM assumed. Here is what each mode actually does, which features stop working, and the settings that decide whether it helps.
Postgres allocates a backend process per connection. A few hundred idle connections from an application that opens one per worker will eat memory and scheduler time long before they do any work, and the database gets slower while doing nothing.
PgBouncer sits in front and hands a small set of real server connections to a large set of clients. It solves the problem completely, and it introduces one decision that decides whether your application still behaves.
The decision is pool_mode
There are three, and the default is not the one people mean.
session is the default. A server connection is held for as long as the client stays connected. Everything behaves exactly like a direct connection, and you have pooled almost nothing: a hundred connected clients still hold a hundred server connections.
transaction releases the server connection back to the pool at the end of each transaction. This is the mode that produces the numbers people install PgBouncer for. Between transactions, your client has no server connection at all, and the next transaction may land on a different backend.
statement releases after every single statement and disallows multi-statement transactions. Narrow, and rarely what an application wants.
# /etc/pgbouncer/pgbouncer.ini
[databases]
app = host=10.0.0.5 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
server_reset_query = DISCARD ALL
What transaction pooling takes away
Anything that lives on a session rather than in a transaction stops being reliable, because the next statement may run on a different backend.
SEToutside a transaction. Set asearch_pathor a timezone once at connect, and it applies to whichever backend you happened to hold at the time.- Session-level advisory locks. Take one, release the connection, and it is held by a backend you no longer have.
LISTENandNOTIFY. The listener is attached to a backend that has moved on.- Temporary tables. Created on one backend, invisible to the next.
WITH HOLDcursors.
None of these fail loudly at deploy time. They fail under load, when the pool is actually being shared, which is why the symptom is usually "it works in staging".
Prepared statements, which used to be the classic trap
Protocol-level named prepared statements are a session feature, and for years they simply could not be used through transaction pooling. Many ORMs use them by default, which is why the standard advice was to turn them off in the driver.
PgBouncer now tracks them itself. max_prepared_statements defaults to 200 and enables protocol-level prepared statement support in transaction and statement pooling mode, at the cost of some CPU and memory.
That changes the advice: check your PgBouncer version and the effective value before you disable prepared statements in your driver, because you may be paying a real performance cost for a workaround you no longer need.
Sizing, which is where people go wrong in the other direction
default_pool_size is the number of server connections per user and database pair. It defaults to 20. max_client_conn is how many clients may connect to PgBouncer at all, and defaults to 100.
The mistake is treating a bigger pool as a faster database. Postgres does not go faster with more concurrent backends; past the point where they saturate CPU and disk, it goes slower. A pool of 20 to 40 in front of a machine with 8 cores is a normal answer. Forty application containers each opening 10 connections to a pool of 200 is a way to reproduce the original problem through a proxy.
Raise max_client_conn freely, since idle client connections in PgBouncer are cheap. Raise default_pool_size carefully, and only with a measurement behind it.
Watch it while it works
-- connect to the pgbouncer admin console, not to Postgres
SHOW POOLS;
SHOW STATS;
cl_waiting in SHOW POOLS is the number that matters. If clients are waiting, the pool is too small for the load or a transaction is holding a connection far too long. Persistent waiting with an idle database usually means the second one, and the fix is in the application, where a transaction was opened around work that did not need to be inside it.
The safe order to adopt it
- Deploy in
sessionmode. Nothing changes behaviourally, and you get an inventory of who connects. - Find and fix the session-state assumptions listed above.
- Move to
transactionmode in staging under realistic concurrency, not with one test user. - Watch
cl_waitingand error rates for a week before tuning sizes.
Connection pooling that holds up under real load, rather than in a demo, is part of our infrastructure management work.
Frequently asked questions
Why do hundreds of idle connections slow down PostgreSQL?
Postgres allocates a backend process for every connection. A few hundred idle connections from an application that opens one per worker eat memory and scheduler time long before they do any work, so the database gets slower while doing nothing. PgBouncer addresses the problem by handing a small set of real server connections to a large set of clients.
Which PgBouncer pool_mode actually reduces the number of Postgres connections?
Transaction mode is the one that delivers the reduction. PgBouncer defaults to session mode, which holds a server connection for as long as the client stays connected, so a hundred connected clients still hold a hundred server connections. Transaction mode returns the connection to the pool at the end of each transaction, and the next transaction may land on a different backend. Statement mode releases after every statement and disallows multi-statement transactions, which rarely suits an application.
What stops working under PgBouncer transaction pooling?
Anything that lives on the session instead of in a transaction becomes unreliable, because the next statement may run on a different backend. The list covers SET outside a transaction (such as a search_path or timezone set once at connect), session-level advisory locks, LISTEN and NOTIFY, temporary tables and WITH HOLD cursors. None of these fail at deploy time. They fail under load, when the pool is actually being shared, which is why the usual symptom is that everything worked in staging.
Do prepared statements work with PgBouncer transaction pooling?
PgBouncer can track protocol-level named prepared statements itself through max_prepared_statements, which defaults to 200 and enables support in transaction and statement pooling mode at some cost in CPU and memory. For years they could not be used through transaction pooling, and the standard advice was to turn them off in the driver. Check the PgBouncer version and the effective max_prepared_statements value before disabling prepared statements in an ORM, since that workaround may no longer be needed.
What is the difference between default_pool_size and max_client_conn in PgBouncer?
default_pool_size is the number of server connections per user and database pair, with a default of 20. max_client_conn is how many clients may connect to PgBouncer at all, and it defaults to 100. Idle client connections in PgBouncer are cheap, so max_client_conn can be raised freely, while default_pool_size should only go up with a measurement behind it.
Does a larger PgBouncer pool make Postgres faster?
No. Postgres does not speed up with more concurrent backends, and past the point where they saturate CPU and disk it slows down. A pool of 20 to 40 in front of a machine with 8 cores is a normal answer. Forty application containers each opening 10 connections to a pool of 200 just reproduces the original problem through a proxy.
How do I tell whether a PgBouncer pool is too small?
Connect to the PgBouncer admin console, not to Postgres, and run SHOW POOLS, where cl_waiting is the number that matters. Waiting clients mean the pool is too small for the load or a transaction is holding a connection far too long. Persistent waiting while the database sits idle usually points to the second cause, and the fix is in the application, where a transaction was opened around work that did not need to be inside it.
What is the safe way to move an application to PgBouncer transaction pooling?
Deploy PgBouncer in session mode first, which changes nothing behaviourally and gives an inventory of who connects. Find and fix the code that relies on session state. Then switch to transaction mode in staging under realistic concurrency, with more than one test user, and watch cl_waiting and error rates for a week before tuning pool sizes.
Read how we handle it in DevOps as a Service.
Related Articles
How to Enable IPv6 on AWS EC2 and Troubleshoot
A guide to enabling IPv6 on AWS EC2 instances, covering VPC and subnet configuration, instance-level setup, security groups, NACLs, troubleshooting, and testing IPv6 connectivity.
Server & DevOpsHow to Upgrade PostgreSQL Major Versions With Almost No Downtime
An in-place major upgrade takes your database down for as long as the upgrade runs, and once it has started there is no way back. Logical replication turns that into a cutover you can measure in seconds, with the old server still consistent and still able to take traffic if the first minute goes badly. The method works because the new server is built and caught up while the old one keeps serving. The risk is entirely in what logical replication declines to carry across, so this guide spends most of its time on sequences, DDL, large objects and tables without a replica identity.
Server & DevOpsThe GitHub to GitLab Migration Nobody Warns You About
GitLab imports your repositories, reviews and history almost completely. It imports none of your GitHub Actions. What moves, what you rewrite, and when.