PgBouncer explained: pooling modes, settings and pitfalls
Session, transaction and statement pooling, what breaks in transaction mode, key pgbouncer.ini settings, PgBouncer vs Pgpool-II and managed proxies, and production tips.
On this page 8 sections
PgBouncer is a lightweight connection pooler for PostgreSQL. Applications connect to it as if it were the database (on port 6432 by default), and it lends them one of a small number of real server connections, for a whole session, a single transaction or a single statement, depending on pool_mode. Transaction mode gives the big win, letting thousands of clients share a few dozen server connections, but it breaks session features such as SET, LISTEN and session advisory locks, so check your app before you switch.
Why Postgres needs a pooler
PostgreSQL runs one server process per connection, and the project deliberately leaves pooling out of the core server. Its wiki explains why too many connections hurt: past the point where CPU and disks are busy, extra concurrent transactions only add contention for locks, memory and CPU caches, so throughput falls. Even idle connections cost something. In a 2020 analysis on PostgreSQL 12, committer Andres Freund found that 10,000 idle connections roughly halved the throughput of 48 active ones. PostgreSQL 14 removed much of that bottleneck, but thousands of connections are still best avoided.
PgBouncer solves this cheaply. It needs about 2 kB of memory per client connection by default, supports most configuration changes without a restart, and hands out server connections only when a client actually needs one, which in transaction mode means only while a transaction is running.
Pooling modes
| Mode | A server connection is held for | What works | Use it for |
|---|---|---|---|
session (the default) | The client's whole connection | Every PostgreSQL feature | Apps that rely on session state, and clients that connect and disconnect often |
transaction | One transaction | Most features; the session-level ones listed below break | Web apps and background workers: the usual production choice |
statement | One statement; multi-statement transactions are refused | Autocommit-style queries only | Rare, specialised setups |
Session mode saves the cost of connecting, but a server connection stays tied to a client for as long as the client stays connected, so it doesn't reduce the number of server connections your app holds. Transaction mode is what lets, say, 2,000 client connections run on 40 server connections: between transactions, a client holds nothing.
What breaks in transaction mode
In transaction mode, two consecutive transactions from the same client can run on different server connections. Anything that expects state to survive between transactions breaks. PgBouncer's feature map lists the incompatible features; here they are with workarounds:
| Feature | In transaction mode | What to do instead |
|---|---|---|
SET and RESET | Never supported | Use SET LOCAL inside a transaction, or set defaults per role with ALTER ROLE ... SET |
LISTEN | Never supported (NOTIFY works) | Give listeners a direct or session-mode connection |
Cursors WITH HOLD | Never supported | Keep cursors inside one transaction; in Django, set DISABLE_SERVER_SIDE_CURSORS = True |
SQL PREPARE and DEALLOCATE | Never supported | Use protocol-level prepared statements, which PgBouncer tracks |
| Temporary tables that outlive a transaction | Never supported (ON COMMIT DROP works) | Create and drop them within one transaction |
LOAD | Never supported | Load libraries through server configuration |
| Session-level advisory locks | Never supported | Use transaction-level locks such as pg_advisory_xact_lock |
Two details are easy to miss. First, protocol-level prepared statements, which many drivers use, have worked in transaction mode since PgBouncer 1.21 and are on by default since 1.24, with max_prepared_statements = 200. Second, a plain SET doesn't fail loudly; it leaks. The setting stays on the server connection and affects whichever client uses it next. PgBouncer tracks a set of startup parameters such as application_name and TimeZone, and version 1.26 extended tracking to every parameter PostgreSQL reports, including search_path on PostgreSQL 18. Anything else you SET still leaks.
Key settings
| Setting | Default | What it does |
|---|---|---|
pool_mode | session | When a server connection returns to the pool |
max_client_conn | 100 | Client connections PgBouncer accepts in total; raise the operating system's file-descriptor limit with it |
default_pool_size | 20 | Server connections per user and database pair |
min_pool_size | 0 | Server connections kept ready, so traffic returning after a quiet spell doesn't wait for new ones |
reserve_pool_size and reserve_pool_timeout | 0 and 5 seconds | Extra connections for a pool whose clients have waited longer than the timeout |
max_db_connections and max_user_connections | 0 (unlimited) | Caps per database and per user, across pools |
query_wait_timeout | 120 seconds | Disconnects a client that has waited this long for a server connection |
server_idle_timeout and server_lifetime | 600 and 3,600 seconds | Close idle and old server connections |
max_prepared_statements | 200 | Prepared statements tracked per server connection in transaction mode |
A starting configuration for a web app, with illustrative numbers for a 16-core database server:
[databases]
exams = host=10.0.1.20 port=5432 dbname=exams
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 4000
default_pool_size = 30
reserve_pool_size = 5
max_db_connections = 40
query_wait_timeout = 20Pools exist per user and database pair, so the server connections PostgreSQL sees are the sum of all pools, not one pool's size. Giving each workload its own database user, such as web, workers and reports, gives each its own pool, and max_db_connections caps the total. A shorter query_wait_timeout than the default makes overload fail quickly rather than leaving students staring at a spinner for two minutes.
For the pool size itself, start near twice the database server's CPU cores in total across pools, the rule of thumb on the PostgreSQL wiki, then load test. If SHOW POOLS shows clients waiting while database CPU is low, the pool is too small; if CPU is pegged and latency climbs as you add connections, it is too big. Sizing a pool with Little's Law walks through the arithmetic.
PgBouncer vs Pgpool-II vs managed proxies
| Option | How it pools | What else it does | Watch out for |
|---|---|---|---|
| PgBouncer | Shared pools in session, transaction or statement mode | Little else: authentication, TLS and an admin console | Single-threaded, and the transaction-mode limits above |
| Pgpool-II | Each of num_init_children child processes (32 by default) caches up to max_pool connections (4 by default); clients beyond the child count wait in a queue | Read load balancing, automatic failover, and a watchdog for its own high availability | Heavier to run; caches aren't shared between child processes |
| Newer poolers such as Odyssey and PgDog | Transaction pooling; Odyssey is multi-threaded | PgDog adds read load balancing and sharding | Younger projects with smaller communities |
| Amazon RDS Proxy | Reuses connections at transaction boundaries unless a session is pinned | Managed, highly available proxy for RDS and Aurora | For PostgreSQL, SET, PREPARE, temporary tables, cursors, LISTEN, session advisory locks and a DISCARD ALL reset query all pin sessions |
| Built-in poolers from cloud providers | Azure's flexible server offers an optional built-in PgBouncer on port 6432, in transaction mode by default; Google Cloud SQL Enterprise Plus offers managed pooling in transaction or session mode | Managed upgrades | Fewer settings exposed, and feature or tier restrictions |
For most Django, Rails or Node apps on PostgreSQL, PgBouncer in transaction mode is a sensible default. Consider Pgpool-II when you want failover and the routing of reads to read replicas in the same component, and a managed proxy when you would rather not run the pooler yourself.
Running it in production
- Choose where it runs. Next to each app server, it adds no network hop and no shared point of failure, but every copy has its own pools, so connection counts multiply again. As a central tier, it caps the total, but it must be highly available: run at least two instances behind a load balancer or DNS name, and divide the connection budget between them. On the database host, it is simplest, but it competes with PostgreSQL for CPU.
- Use more than one core. PgBouncer is single-threaded. On Linux,
so_reuseportlets several PgBouncer processes listen on the same port, with the kernel spreading connections between them. The same option enables rolling restarts: PgBouncer 1.26 removed the old online-restart mode in favour of it. - Watch the admin console. Connect to the special
pgbouncerdatabase and runSHOW POOLS. A non-zerocl_waiting, and amaxwaitthat keeps rising, mean clients are queueing for server connections. Alert on both. - Secure it like a database. Use
scram-sha-256authentication and TLS, and keep it patched: version 1.26.0, released in September 2026, fixed three security vulnerabilities. - Tell the app it is behind a pooler. Disable server-side cursors in Django, move
SETcalls into transactions or role defaults, and give anyLISTENworker its own direct connection.
New to the idea? Start with what connection pooling is. If the database is already refusing connections, see Postgres too many connections. The PgBouncer configuration reference documents every setting mentioned here.
Key takeaways
- PgBouncer sits between the app and PostgreSQL and lends out a small number of server connections.
- Transaction mode gives the biggest reduction in server connections; session mode only saves connection set-up.
- Transaction mode breaks
SET,LISTEN, held cursors, SQLPREPAREand session advisory locks, and a straySETleaks to other clients. - Pools are per user and database pair; cap the total with
max_db_connectionsand give each workload its own user. - It is single-threaded and can be a single point of failure, so run several processes and more than one instance.
Frequently asked questions
Why use PgBouncer?
Because PostgreSQL uses one server process per connection and slows down when thousands of connections pile up. PgBouncer lets many application processes share a small, fixed number of real connections, avoids the cost of opening new ones, and queues clients when the database is busy instead of overloading it. It uses very little memory per connection and is simple to run.
Should I use PgBouncer?
Use it once your app runs on more than a handful of processes or servers, uses autoscaling, or is near max_connections. You may not need it for a single small server with an in-app pool. Before switching to transaction mode, check your code for session features such as SET, LISTEN, held cursors and session advisory locks. On a managed database, compare it with your provider's built-in pooler or proxy.
What is PgBouncer in PostgreSQL?
PgBouncer is a separate, open-source program, not part of PostgreSQL itself, that acts as a connection pooler in front of a PostgreSQL server. Clients connect to PgBouncer using the normal PostgreSQL protocol, and PgBouncer forwards their queries over a pool of connections it keeps open to the server. It offers session, transaction and statement pooling modes, and an admin console for monitoring pools.