Skip to content
EdgeServers
Blog

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.

May 21, 2026 · 8 min · by 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_digits

The 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 = 100

For 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/NOTIFY doesn'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_timeout and idle_in_transaction_session_timeout on Postgres itself.
  • PgBouncer's pg_dump story is bad. Don't dump through PgBouncer — connect directly to Postgres. Same for psql -E and 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_query against a SECURITY DEFINER stored proc
  • max_prepared_statements = 100 to enable prepared-statement support
  • A Prometheus exporter scraping SHOW POOLS / SHOW STATS / SHOW LISTS every 15 seconds
  • Alerts on cl_waiting > 5 and pool_full events

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.