Connection Pooling Without the Cargo Cult: pgbouncer in 100 Lines of Config

The Postgres dashboard shows 200 connections used out of 200 max. The application is throwing remaining connection slots are reserved for non-replication superuser connections. Somebody in the chat says “we need to bump max_connections” and somebody else says “we need pgbouncer.” Both are right and both are wrong, and the order matters.

Postgres does not handle thousands of connections gracefully. Each one is a backend process that consumes ~10MB of RAM and a slot in the shared lock table. Past a few hundred concurrent connections, throughput goes down — context-switching dominates, lock contention spikes, and the cure (more connections) is the disease. The actual fix is connection pooling: a layer between your app and Postgres that maintains a small number of real connections and multiplexes thousands of clients across them. pgbouncer is the standard. It is also the most miscopied config in the industry.

The mental model

A pgbouncer process sits between your application and Postgres. Your app opens a TCP connection to pgbouncer (cheap — pgbouncer holds it indefinitely). pgbouncer maintains a pool of real Postgres connections behind the scenes and routes your queries through whichever one is free. The two sides are decoupled.

[ app instance × 50 ]                  [ pgbouncer ]                [ Postgres ]
   2000 client conns  <───────────>   pool_size=20  <────────────>   max 25

Fifty app instances × 40 client connections each = 2000 client-side connections, but pgbouncer holds only 20 real connections to Postgres. As long as no single query stalls everything, throughput is dictated by query latency, not connection count.

The three pool modes — ranked by what they break

This is the setting most config copy-pasters get wrong. pgbouncer has three pool modes, and they trade off compatibility against efficiency.

Session pooling — a client gets a server connection and keeps it for the entire session. Functionally identical to no pooler at all from the application’s point of view, except pgbouncer is the queue when you exceed pool_size. Almost no efficiency gain at high concurrency. Use this only if you genuinely need session-scoped state (e.g., temp tables that survive across statements, advisory session locks, prepared statements pinned to a connection).

Transaction pooling — a client gets a server connection only for the duration of a transaction. After COMMIT or ROLLBACK, the connection goes back to the pool and another client can use it. This is the mode 95% of web apps want. The trade-off: anything that requires connection-level state breaks.

Statement pooling — a client gets a server connection only for the duration of one statement. Multi-statement transactions are not allowed. Use this only for single-shot read-heavy workloads (analytics dashboards, public APIs that always do one SELECT). It is rare in practice.

For a typical Node.js / Rails / Django app: transaction pooling. Anything else is wasting the pooler.

What transaction pooling breaks

This is the part teams skip when they “just turn on pgbouncer” and then debug for a week.

Session-level state is gone. SET search_path = ..., SET timezone = ..., SET application_name = ... — these set a property on a connection. In transaction pooling mode, your next transaction probably runs on a different backend, and the setting is gone. Use SET LOCAL (which is transaction-scoped) or set it via the connection string parameters.

Server-side prepared statements break. When your driver runs PREPARE stmt AS ... once and then EXECUTE stmt later, the EXECUTE may land on a backend that has never seen the PREPARE. Modern drivers handle this — Node pg with pg-pool is fine, libpq with prepareThreshold=0 is fine — but ORMs that aggressively cache prepared statements (some configurations of Sequelize, older versions of Hibernate-style ORMs) need a config tweak. Test it.

LISTEN / NOTIFY does not work. LISTEN is a property of a connection. The next time the client sends NOTIFY, it goes through a different backend and your LISTEN-er never hears it. Connect directly to Postgres for LISTEN-ers (skip pgbouncer for that one connection).

Advisory locks across statements are unsafe. pg_advisory_lock is session-scoped; in transaction mode you must use pg_advisory_xact_lock (transaction-scoped) instead.

Cursors do not survive past COMMIT. Use FETCH inside the same transaction, or rewrite to keyset pagination.

If your app respects these constraints (most do), transaction pooling is invisible.

A pgbouncer config that actually works

Drop this into /etc/pgbouncer/pgbouncer.ini and adjust the host/auth lines. Each block is annotated with the why.

[databases]
; Multiple logical databases routed to one Postgres host.
appdb = host=10.0.0.4 port=5432 dbname=appdb pool_size=25
analytics = host=10.0.0.4 port=5432 dbname=analytics pool_size=10

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; Auth.
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin

; The single most important setting. See the next section for sizing.
pool_mode = transaction

; Maximum total Postgres connections per pgbouncer (sum across pools).
; This must be < Postgres max_connections - reserved superuser slots.
max_db_connections = 100

; Maximum client-side connections to pgbouncer. Set generously — these are cheap.
max_client_conn = 5000

; Reserve some pool slots so urgent queries do not get stuck behind big ones.
reserve_pool_size = 5
reserve_pool_timeout = 3

; A client that has not done anything for this long gets disconnected.
client_idle_timeout = 600

; A pooled server connection that has been idle for this long is closed.
server_idle_timeout = 600

; Defensive — a query that runs longer than this is killed at the pool.
; Should comfortably exceed your slowest legitimate query.
query_timeout = 60
query_wait_timeout = 30

; Logs.
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

userlist.txt holds the SCRAM credentials pgbouncer will accept from clients:

"appuser" "SCRAM-SHA-256$..."

You generate the SCRAM hash with psql’s \password and copy the resulting hash. Avoid storing plain passwords — pgbouncer supports it, but you do not need to.

The pool_size formula that actually holds

The most common pool-size mistake is “set it to a big number.” pgbouncer at pool_size=200 against a Postgres at max_connections=500 does not buy you anything except extra ways to overload the database. The right way to size:

pool_size = number of CPU cores on the Postgres host × 2 to 4

That is it. It is a hardware bound, not an application bound. The database can usefully execute ~2x cores worth of concurrent queries before throughput plateaus and starts dropping. Past that, you are queuing, and queuing should happen at the pgbouncer layer (where it is cheap) rather than at the Postgres backend (where each waiting connection costs RAM).

For an 8-core Postgres host: pool_size between 16 and 32. For a 32-core host: 64 to 128. Most “we need a bigger pool” requests are actually “we have one slow query holding connections too long,” not “we are short on parallelism.”

If your queries are short (single-digit ms), you can run with smaller pools — pgbouncer will multiplex effectively. If your queries are long (hundreds of ms), even a small number of slow queries will starve the pool. The metric to watch is cl_waiting — clients waiting for a server connection — from SHOW POOLS:

psql -h pgbouncer-host -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"

If cl_waiting is consistently > 0, you are pool-starved. Either pool_size is too small or queries are too slow. Usually queries are too slow.

What to put in your application’s connection string

The application also has a connection pool (Node pg’s Pool, Rails’ ActiveRecord::ConnectionPool, Django’s CONN_MAX_AGE). With pgbouncer in front, the application pool is now mostly a worker-side cache; sizing rules change:

• Application pool per process: small (4–10). The app does not need its own deep queue — that is pgbouncer’s job.
• Total application connections = (instances) × (pool per process). This is what hits pgbouncer’s max_client_conn. If you have 50 instances × 10 = 500, set max_client_conn comfortably above that (e.g., 1000–5000).
• Query timeout in the driver should be lower than pgbouncer’s query_timeout so the driver kills slow queries first and pgbouncer is the safety net.

For Node:

import { Pool } from 'pg';
export const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  database: 'appdb',
  user: 'appuser',
  password: process.env.DB_PASSWORD,
  max: 8,                              // small per-process pool
  idleTimeoutMillis: 10_000,
  connectionTimeoutMillis: 5_000,
  statement_timeout: 30_000,           // < pgbouncer query_timeout
});

Monitoring you actually need

Two queries you should hit every minute via your monitoring system. Both run against pgbouncer’s admin DB.

-- 1. Are we pool-starved?
SHOW POOLS;
-- Watch cl_waiting and maxwait_us. If maxwait grows past a few seconds,
-- somebody is being held up.

-- 2. Are queries running long enough to matter?
SHOW STATS;
-- avg_query_time and avg_xact_time give you per-pool latency at the pooler.

Pipe these into Prometheus via pgbouncer_exporter and alert on cl_waiting > 5 for 5 minutes. That single alert catches almost every “the database is slow” incident before users notice.

Also keep an eye on the Postgres side. Even with pgbouncer, you want pg_stat_activity showing roughly pool_size active backends. If you see ~1000 active backends, somebody bypassed pgbouncer (often: a one-off worker process pointed at the Postgres host directly).

When pgbouncer is the wrong tool

Two scenarios.

Per-connection state your app cannot give up. If you genuinely need session-scoped temp tables, you cannot use transaction pooling. Use session pooling and accept that you have not gained much. Or look at PgCat, which adds richer routing.

Sharded reads/writes. pgbouncer routes everything to one Postgres host. If you have a primary + replicas and want SELECTs to go to the replicas, you need application-level routing or a smarter pooler (PgCat, Odyssey).

For 95% of teams: pgbouncer in transaction mode, in front of one Postgres primary, with the config above and a sensible pool_size. The migration from “no pooler” to “pgbouncer” is one of the highest-leverage two-hour tasks in backend infra — the kind of change that takes a Saturday morning and makes the next year of scaling 5× easier.

The takeaway

Connection pooling is not an optimization, it is a precondition for any Postgres database with more than a handful of app instances. The default pool_size formula is cores × 2 to 4. The default pool_mode is transaction, with the four constraints above respected. The default monitoring is cl_waiting and maxwait_us. Anything fancier than that is a refinement, not a starting point.

Most “Postgres is slow” incidents that turn out to be connection-related are solved by these five settings. The next thirty minutes you spend wiring pgbouncer up is worth more than the next thirty hours you spend tuning queries that only get slow when the pool is starving.

---

A note from Yojji

The kind of infrastructure work that turns a “we are out of database connections” incident into a non-event — pooler config, slow-query budget, the right alert thresholds — does not show up in a sprint demo and shows up loudly in an outage. It is the unglamorous backend work Yojji’s teams build into the production systems they ship.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their dedicated teams ship across the JavaScript ecosystem (React, Node.js, TypeScript) and the major cloud platforms (AWS, Azure, GCP), including the database and infra plumbing that decides whether a product still feels good at the next traffic milestone.