postgresql
Postgres connection pooling with PgBouncer — the patterns we run in production
Transaction mode, session mode, prepared statements, and the cluster topology decisions that determine whether PgBouncer helps or hurts.
21. Mai 2026 · 8 min · von Sudhanshu K.
Postgres connection pooling with PgBouncer — the patterns we run in production
Postgres connections are expensive. A single idle Postgres backend uses 5-15MB of memory and holds OS resources that don't release until the connection closes. Most application frameworks open dozens of connections per instance — Node.js with pg's default pool of 10, Rails with 5 per worker, PHP-FPM workers each holding their own. Multiply by replicas, multiply by deploy environments, and you've quickly got 500-2000 connections to a database that performs best at maybe 100.
This is what PgBouncer is for. We deploy it for almost every managed Postgres customer, and it remains the single highest-leverage piece of Postgres ops tooling. Here's how we run it.
What PgBouncer actually does
PgBouncer sits between your application and Postgres. The application opens many "client" connections to PgBouncer (cheap; PgBouncer is lightweight). PgBouncer maintains a much smaller pool of "server" connections to Postgres. When a client wants to send a query, PgBouncer grabs a free server connection, forwards the query, returns the response, and releases the server connection back to the pool.
Three pooling modes:
- Session pooling. Client gets a dedicated server connection for the duration of their session. PgBouncer is basically a connection broker; no real reduction in server connections. Useful when the application uses session-level features (LISTEN/NOTIFY, prepared statements, temp tables).
- Transaction pooling. Server connection is held only for the duration of a transaction. Most aggressive pooling; lets thousands of clients share dozens of server connections. Restrictions on what the application can do — anything that crosses transaction boundaries (session variables, advisory locks, server-side prepared statements without protocol-level support) is unsafe.
- Statement pooling. Server connection released after every statement. Very restrictive — no transactions at all. Rarely useful.
Default to transaction pooling. Use session pooling only for the specific subset of workloads that need it, ideally on a separate PgBouncer port or pool.
A baseline config
[databases]
app = host=db.internal port=5432 dbname=app
[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 = 5000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digitsThe numbers that matter:
max_client_conn = 5000— how many client connections PgBouncer accepts. Make it generous; clients are cheap on the PgBouncer side.default_pool_size = 25— how many server connections per (database, user) pair. This is your real Postgres connection count. Start at 25 and adjust based on the load test.server_lifetime = 3600— kill server connections after an hour and reopen. Forces Postgres to release memory accumulated by the backend; prevents long-lived backend pathologies.server_reset_query = DISCARD ALL— clean state between transactions so leaks don't accumulate. Essential for transaction pooling.
Sizing the pool
The most common question: "How big should default_pool_size be?"
The honest answer: not as big as you think. Postgres has a sweet spot around 2 * (cores) + spindles for concurrent active queries — typically 16-64 on a modern instance. Beyond that, you're context-switching, not getting work done.
A useful starting heuristic:
default_pool_size = (postgres max_connections - admin reserve) / number of databases / number of users
For a 200-connection Postgres, with one admin reserve of 20, two databases, and one app user per database, that's 90 connections per (db, user) pool. Round down to 50 to leave headroom for the unexpected.
Monitor with SHOW POOLS; during peak load:
$ psql -p 6432 pgbouncer -c "SHOW POOLS"
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested |maxwait
----------+---------+-----------+------------+-----------+---------+---------+-----------+---------
app | appuser | 183 | 2 | 24 | 1 | 0 | 0 | 0
cl_waiting > 0 consistently means the pool is too small. sv_idle = pool_size consistently means the pool is bigger than it needs to be. Tune until both numbers stabilize near zero during peak load.
Prepared statements: the 2024+ story
Historically, PgBouncer in transaction mode broke server-side prepared statements. Each pg backend cached prepared statements; the pool would rotate connections, and clients would suddenly find their prepared statement gone.
PgBouncer 1.21 (released late 2023) added native protocol-level prepared statement support. It tracks prepared statements per client, transparently re-preparing them on whichever server connection the client gets. Enable with:
max_prepared_statements = 100For libraries that use prepared statements heavily (most ORMs, including Hibernate, SQLAlchemy with executemany, Sequelize), this is a meaningful performance win and a meaningful operational simplification. If you've been running PgBouncer with prepare_threshold = 0 in your client to disable preparation, upgrade and remove that workaround.
High availability for PgBouncer itself
PgBouncer is single-threaded and a single point of failure. The patterns:
One PgBouncer per app host. Run PgBouncer on each application server, on localhost:6432. The app connects to local PgBouncer. PgBouncer connects to Postgres. Connection pool sizing is per app host, so default_pool_size = 25 with 10 app hosts means 250 Postgres connections — but only if all 10 hosts are simultaneously busy.
Advantages: lowest latency (local Unix socket), no PgBouncer SPOF, no cross-host traffic for the pool.
Disadvantage: harder to monitor centrally, total Postgres connection count is pool_size × app_count, harder to manage from a deploys/Terraform perspective.
Centralized PgBouncer cluster. Run a small number (2-3) of PgBouncer instances behind a load balancer. App connects to the LB.
Advantages: easier to monitor, central place to read pool stats, central place to manage credentials.
Disadvantage: extra network hop adds 1-2ms latency, LB itself is a (different) SPOF.
For most workloads we ship "one PgBouncer per app host," with a small central pair as a fallback path for tools that need a stable hostname (analytics readers, ad-hoc DBA queries). For DigitalOcean workloads on App Platform where you can't sidecar PgBouncer alongside your app, we use the centralized model.
Auth: don't put plaintext passwords in userlist.txt
Default PgBouncer auth uses a userlist.txt file with hashed passwords. The defaults are too weak in 2026:
auth_type = scram-sha-256
auth_user = pgbouncer
auth_query = SELECT username, password FROM pgbouncer.user_lookup($1)auth_query lets PgBouncer look up password hashes from Postgres itself instead of carrying a local file. Combined with a stored procedure that's SECURITY DEFINER, you can let PgBouncer authenticate without giving it direct access to pg_authid. The hashes never leave Postgres. Credentials can be rotated by ALTER USER ... PASSWORD ... without touching PgBouncer config.
Operational gotchas
A grab-bag of things we've learned the hard way:
- Statement timeouts must be set on the application or at Postgres, not at PgBouncer. PgBouncer has no concept of "this query has been running too long."
LISTEN/NOTIFYdoesn't work over transaction pooling. Use a separate session-pool port for any service that needs it.- Server-side cursors don't survive transaction boundaries. ORMs that use server cursors silently for large result sets will break in transaction mode. Use client-side cursors or session pooling for those workloads.
- The "client cancel" doesn't propagate in some PgBouncer configurations. A query that's running too long on Postgres won't be terminated when the client closes its connection to PgBouncer. Set
query_timeoutandidle_in_transaction_session_timeouton Postgres itself. - PgBouncer's
pg_dumpstory is bad. Don't dump through PgBouncer — connect directly to Postgres. Same forpsql -Eand most ad-hoc DBA work.
What we ship by default
For every managed Postgres deployment on any cloud:
- PgBouncer 1.22+ with transaction pooling
- One PgBouncer per app host (or central pair, depending on deploy model)
auth_queryagainst a SECURITY DEFINER stored procmax_prepared_statements = 100to enable prepared-statement support- A Prometheus exporter scraping
SHOW POOLS/SHOW STATS/SHOW LISTSevery 15 seconds - Alerts on
cl_waiting > 5andpool_fullevents
It's the kind of thing that's easy to set up once, easy to forget about, and quietly saves you 10x your provisioned Postgres connection count. If you're running Postgres at production scale and not running PgBouncer, that's usually the first thing we set up when we onboard a managed Postgres customer on Azure or any other cloud.
Sudhanshu K. is a Senior SRE at EdgeServers (RemotIQ Pty Ltd, ABN 91 682 628 128). She has personally been the human on call for more than one "we ran out of Postgres connections" incident, and would prefer not to add to the list.