PgBouncer From First Principles: Why a Busy Primary Needs a Pooler, and Every Line of Its Config

TL;DR: PostgreSQL doesn't use threads — every connection is a fork()ed backend with its own memory and its own slot in max_connections, and the client side decides how many there are (ORM pool × tenants). No server-side setting can lower the count, because pools treat a dropped connection as a fault and immediately reconnect. The fix is a pooler: a proxy that ends thousands of client sockets in one event loop and lends a handful of real backends out only while a statement or transaction is in flight. In transaction mode an idle client holds no backend at all, which is the entire win — and the entire hazard, because anything that lives on a backend (SET, LISTEN, advisory locks, temp tables, named prepared statements) silently breaks across transactions. PgBouncer can even authenticate clients with SCRAM pass-through so the plaintext password never exists on disk, and enforce its own pg_hba because Docker publishes ports past your firewall. Measured result on a busy shared primary: peak 550+ backends → ~35. Every line of the config below is there because a specific failure required it.

Let me start with the number that started all of this: on a busy shared primary, 550+ connections were open at the busiest moment — and ~540 of them, over 97%, were doing nothing. Fewer than 6% were actually executing a query. Every single one of those 540 idle sockets was a forked PostgreSQL backend, eating 4–10 MB of RAM and one slot out of max_connections that a migrating tenant would eventually need.

The obvious first move — tell PostgreSQL to kill idle sessions — failed in a way that taught me how connection counts actually work. This post is that lesson, the three-parts wire-level understanding that follows from it, and the exact deployment that fixed it.

Why a connection costs anything at all

PostgreSQL does not have threads. When a client connects, the postmaster forks, and the child is a backend — a whole operating-system process dedicated to that one connection for its entire lifetime. Everything downstream is a consequence of that one fact:

• Private memory. Every backend carries its own catalogue caches, plan cache, and a slice of work_mem — typically 4–10 MB resident even while it does nothing.
• Shared memory sized at boot. The lock table and process array are allocated at startup from max_connections, whether or not the slots are ever used. A generous max_connections reserves hundreds of MB for connections that never exist.
• A slot. Each backend consumes one max_connections. When they run out, new clients are refused outright — including the one that's about to fail a migration.

Applications make this worse by design. An ORM keeps a client-side pool of open connections so it never pays the connect cost twice. Prisma's default pool size is num_cpus × 2 + 1 — 9 on a 4-core app server, 17 on an 8-core one. Multiply that by every tenant database each app process touches, and the connection count has nothing to do with load; it scales with product surface.

Measured on the production primary (numbers from real observation):

Why a server-side idle timeout does not help

The natural first fix: let PostgreSQL kill idle sessions. With idle_session_timeout = '15min', the 95th-percentile idle age sat at exactly 900 seconds — and the count stayed just as high during quiet hours.

The reason is a feedback loop. A client pool treats a dropped connection as a fault and immediately reconnects to restore its minimum size. Killing idle backends doesn't lower the steady state; it just converts idle time into fork() churn — pools re-establishing exactly what the timeout took away.

The principle: the number of backends is set by the client side — pool sizes multiplied by pool count. Nothing the server does alone can lower it. Either the pools shrink, or something between the pools and the server shares backends among them.

What a pooler actually does

PgBouncer is a proxy that speaks the PostgreSQL wire protocol on both sides. Clients connect to it believing it is PostgreSQL. It holds a small number of real backend connections and lends them out.

DIRECT                                          POOLED
app  ──socket──  postgres backend               app  ──socket── pgbouncer ──backend── postgres
 c1  ──────────  B1   (forked process)            c1 ┐
 c2  ──────────  B2   (forked process)            c2 │  one process, one event loop,
 c3  ──────────  B3   (forked process)            c3 │  thousands of client sockets,
 c4  ──────────  B4   (forked process)            c4 │  100+ dbs share a few dozen real backends
 c5  ──────────  B5   (forked process)            c5 ┘
 c6  ──────────  B6   (forked process)

The six client connections, two ways. Direct, every open socket is a forked backend regardless of what it's doing. Pooled, the six sockets terminate in one PgBouncer process, and only the two that are mid-query hold a real backend.

The insight that makes this work: a backend is only needed while a statement or transaction is in flight. The rest of the time a client connection is just an open socket waiting for the application to say something — and an open socket in one event loop costs a file descriptor and a few kilobytes, not a process.

Two words matter from here on. A client connection is app-to-PgBouncer: cheap; PgBouncer holds thousands. A server connection is PgBouncer-to-PostgreSQL: a real backend, expensive, and the whole point of the pool is to ration it. Almost every setting in the config answers one question: when is a server connection handed out, and when is it taken back?

Transaction mode, and what it breaks

That decision — when a server connection returns to the pool — is the pool mode. It is the most consequential line in the file.

c1:  BEGIN T1 ... COMMIT          idle           BEGIN T3 ... COMMIT
     └── S1 lent ─┘               └─ idle ─┘     └── S1 lent ──┘
c2:                     BEGIN T2 ... COMMIT
                        └─ S1 handed to c2 ─┘

One server connection, three transactions, two clients. S1 is lent out at BEGIN and taken back at COMMIT. Anything a client left on the connection between transactions has been handed to someone else or discarded.

That gap is the whole hazard. Anything that lives on the server connection rather than inside a transaction silently stops working:

• SET outside a transaction — the next statement runs on a connection that never saw it.
• LISTEN / NOTIFY — the listener is a backend, and yours has been lent to someone else.
• Advisory locks — held by a backend, released to whoever gets it next.
• Temporary tables, WITH HOLD cursors, and named prepared statements — all per-backend state.

Compatibility check, run before cutover: no advisory locks held, no LISTEN sessions, no prepared (two-phase) transactions, no transactions open longer than 60 s, no idle-in-transaction sessions. A workload that passes this is clean for transaction pooling. The one thing it may still lean on heavily is named prepared statements — next part.

Prepared statements: the Prisma and JDBC problem

Prisma and the PostgreSQL JDBC driver both use the extended query protocol. Instead of sending SQL text each time, the client sends Parse once to compile a statement under a name like s0, then Bind + Execute on later calls with only the parameters. It's faster — and the name s0 lives on exactly one backend.

Left unhandled, transaction pooling breaks it:

Parse "s0" on S1 ─► txn 1 COMMIT            Parse "s0"? never → error 26P01
                  txn 2 routed to S2 ──►    "prepared statement s0 does not exist"

PgBouncer 1.21+ solves this by tracking each client's Parse messages and replaying them on whichever server connection it hands out next — which is why max_prepared_statements = 200 is mandatory in a workload that uses extended protocol, and why the image digest in the deploy section (not merely :latest) is a correctness requirement, not a tidy habit.

Prisma additionally needs ?pgbouncer=true on its connection string. That flag turns off Prisma's own statement caching, which makes it belt-and-braces alongside PgBouncer's tracking — keep it for the first migration, and only experiment with removing it once everything is stable.

Two hops: identity, access, transport

Every hard problem in this deployment had the same shape: PgBouncer is, by construction, a man-in-the-middle. It has to authenticate the client and authenticate itself to PostgreSQL as that client. It decides who may connect to it, and PostgreSQL decides whether to accept it. It can offer TLS to the client and separately decide what to do toward the server.

client (public internet)                 server (docker bridge)
        │ hop 1: pgbouncer's rules           │ hop 2: postgres' pg_hba
        ▼                                    ▼
   ───────── pgbouncer : 6432 ───────────────► primary : 5432
        TLS (allow)                      TLS / plaintext (per connection)

Hop 1 crosses the public internet and is guarded by PgBouncer's own rules. Hop 2 crosses a private Docker bridge and is guarded by PostgreSQL's. Each row below is one decision made twice — once per hop.

Identity: why there is no password on disk

The usual PgBouncer setup writes the plaintext password into userlist.txt, because PgBouncer must be able to log in to PostgreSQL as the user. For a superuser-adjacent credential on a public port, that's a poor trade.

SCRAM-SHA-256 stores a verifier — a salted, iterated derivation the server can use to check a password without holding it. It cannot be replayed to log in; that's its entire design. So copying the verifier out of pg_shadow gives PgBouncer enough to authenticate the client, but on its own not enough to reach the server.

SCRAM pass-through closes that gap. When the client proves itself to PgBouncer, PgBouncer keeps the proof and reuses the same exchange toward PostgreSQL. The plaintext never exists — not in the config, not on disk, and not in the setup conversation, because the setup script reads pg_shadow on the server and writes the file there.

Constraint this imposes: pass-through has no credential of its own. PgBouncer cannot open a server connection until some client has authenticated, so min_pool_size must be 0. Raise it, and PgBouncer will try to pre-open connections it cannot log in with.

Access: why PgBouncer needs its own HBA

PgBouncer does not read pg_hba.conf, and at defaults has no IP rules of its own. Port 5432 was already IP-restricted. Publishing 6432 without an equivalent would have been a downgrade — a superuser-capable port with only a password between it and the internet.

And the port really is on the internet: Docker's published ports are DNAT'd through its own FORWARD rules and bypass the host's INPUT chain, so host iptables ACCEPT lines for 5432 are decorative. Confirmed twice: 6432 was reachable from outside before any firewall change, and during SSL work a probe for a user from an external IP appeared in the log — correctly rejected by pg_hba, but a reminder that the HBA is the only guard.

auth_type = hba makes PgBouncer enforce a file of the same shape. Rather than maintain a second allowlist, its template is expanded by the same script, from the same allowlist variable, as the database's own HBA — so the two ports cannot drift apart. Two additional rules mirror ones PostgreSQL already has: 127.0.0.1/32 for the healthcheck and admin console inside the container, and the Docker bridge subnet for sibling containers.

Proof, from an IP not in the allowlist:

$ psql -h <primary-ip> -p 6432 -U <admin> …
FATAL:  no authentication method is found
$ psql -h <primary-ip> -p 5432 -U <admin> …
FATAL:  no pg_hba.conf entry for host "<client-ip>", user "<admin>"

Transport: making the two ports interchangeable

When the server had ssl = off, almost all connections switched to plaintext with zero errors — and then a client with strict SSL expectations failed with The server does not support SSL connections. The difference is the client's sslmode: libpq's default prefer falls back silently, while require hard-fails. A service whose pool defaults SSL on is effectively require.

That's why 6432 has TLS. If PgBouncer were plaintext-only, every connection string would need a different sslmode per port — and the same failure would return the day an app moved. allow is the client-side equivalent of ssl = on: TLS when asked for, plaintext still served.

What broke — twice — both fixed, verified live.

1. Key permission mismatch. PgBouncer's image runs as a non-root uid; PostgreSQL's server.key is 0600 owned by the database's uid. It couldn't read it — and loosening the database's key was the wrong fix. The setup script keeps a second copy of the same material in a pgbouncer-owned directory, key still 0600.
2. Directory ownership. The script set umask 077 to write userlist.txt, and that umask was still in effect when it created the TLS directory — as 0700 root:root. PgBouncer crash-looped with failed to load certificate file ".../server.crt": (null), which names the file, not the directory that denied it. The script now sets ownership and mode on the directory explicitly. Re-running it is idempotent: the directory stays 0755 owned by the pgbouncer uid, the key inside stays 0600.

Sizing the pool

A pool exists per (database, user) pair. With one role and many databases that's up to one pool per database, and the ceiling on server connections is the sum across all of them. The budget has to fit under PostgreSQL's max_connections while direct 5432 clients are still connected, because the migration is gradual.

# config/pgbouncer/pgbouncer.ini — pooling
pool_mode = transaction
max_prepared_statements = 200
 
max_client_conn = 2000        ; client sockets are cheap
default_pool_size = 3         ; server conns per (db, user) at steady state
min_pool_size = 0             ; MUST be 0 — SCRAM pass-through, see Two hops
reserve_pool_size = 2         ; extra under pressure, after reserve_pool_timeout
reserve_pool_timeout = 3
max_db_connections = 5        ; hard cap per database
 
server_idle_timeout = 120     ; THIS is what removes the idle connections
server_lifetime = 3600

Three per database looks small until you remember that under transaction pooling a server connection serves a whole transaction and is free again in milliseconds. A few dozen concurrent queries across the entire instance is the busiest this shared primary has ever been.

server_idle_timeout = 120 deserves the emphasis in its comment. It closes a server connection that has gone unused for two minutes. That is the mechanism that finally lowers the backend count — and it works where PostgreSQL's idle_session_timeout could not, because the thing being closed is PgBouncer's own link, not a client's. The client never notices; its socket to PgBouncer stays open.

What broke — fixed. PgBouncer is a single process multiplexing everything through one event loop, so it needs one file descriptor per client socket plus one per server link. On first start it logged kernel file descriptor limit: 1024 … max expected fd use: 2012. Near 1,000 clients it would have stopped accepting connections with no obvious cause. The compose sets ulimits: nofile well above the default.

How it's deployed

PgBouncer sits beside PostgreSQL, not in front of it. The database keeps publishing 5432 and a separate container publishes 6432. Clients choose per connection string — anything transaction pooling can't carry still has a working path, and services that need session state keep their own pool.

# compose — pgbouncer service
image: pgbouncer/pgbouncer@sha256:<digest>   # pinned by digest: image publishes only :latest
ports: ["6432:6432"]
volumes:
  - ./config/pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
  - ./config/pgbouncer/pg_hba.conf:/etc/pgbouncer/pg_hba.conf:ro
  - ./config/pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt:ro
  - ./ssl/pgbouncer:/etc/pgbouncer/ssl:ro
ulimits:   { nofile: { soft: 8192, hard: 16384 } }
healthcheck: nc -z 127.0.0.1 6432        # liveness with no auth dependency
depends_on: pg-primary: service_healthy
restart: unless-stopped
deploy.resources.limits: { memory: 256M, cpus: "0.50" }
logging: json-file, max-size 50m, max-file 3

A few of those lines encode a lesson rather than a preference:

• The digest. The image publishes only :latest. A digest is the only immutable reference, and it matters because max_prepared_statements needs PgBouncer 1.21 or newer.
• The mounted .ini wins. The image's entrypoint generates a config from environment variables — but only if [ ! -f "$PG_CONFIG_FILE" ]. Mount your own and it's left alone.
• The pre-created userlist.txt. If a bind-mounted file doesn't exist, Docker silently creates a directory at that path and PgBouncer fails confusingly. The file is created empty at 0600 before the first up (fails closed), and the setup script overwrites it.
• --no-deps on every up. Because of depends_on, a plain docker compose up -d pgbouncer would also recreate the database container. That is the one thing that must not happen during business hours.
• log_connections = 0. The primary was once found with a multi-20-GB unrotated log — a large chunk of the root filesystem — from exactly this kind of per-connection logging at 80,000+ sessions a day.

Restart tolerance

Tested by restarting PgBouncer itself, then by restarting PostgreSQL underneath it. In both directions it came back healthy within seconds and served queries without intervention. On a host reboot the policy starts containers independently, so PgBouncer may come up before PostgreSQL and log retries — but it never exits, and it reconnects on its own. All config is on bind mounts and survives recreation.

For the PostgreSQL side of a restart: its STOPSIGNAL is SIGINT, a fast shutdown that disconnects clients and checkpoints. Docker's default 10-second grace can elapse mid-checkpoint on a busy instance, after which it sends SIGKILL and the next boot does crash recovery. The database service carries stop_grace_period: 120s.

Moving the applications

Prisma has a first-class answer to "queries here, migrations there":

// schema.prisma — in the datastore repo, not the runtime app repo
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")          // 6432 → PgBouncer · findMany, create, $transaction…
  directUrl = env("DIRECT_DATABASE_URL")   // 5432 → direct · migrate, db push, db pull, studio
}

DATABASE_URL="postgresql://<app_user>:<password>@<primary-ip>:6432/?pgbouncer=true"
DIRECT_DATABASE_URL="postgresql://<app_user>:<password>@<primary-ip>:5432/"

Everything the Prisma client does at runtime goes through url. Everything the Prisma CLI does goes through directUrl — because migrate and db push take a session-level advisory lock, which is precisely the kind of state that evaporates between transactions.

Where the schema lives. The runtime app repo contains no schema.prisma for tenant databases and runs no DDL. Schema handoff is file-only and goes through the datastore repo's git push, where the datastore box pulls the branch and runs migrate. So directUrl belongs in the datastore repo's schema and DIRECT_DATABASE_URL in the datastore box's environment — the runtime app only ever needs DATABASE_URL → 6432. The process doing CRUD and the process doing migrations being different machines makes this safer than the usual setup.

What stays on 5432

• Workflow engine services with their own JDBC pool — workflow engines take advisory locks under load even when none are held at rest.
• Standalone admin/tenant scripts that pass DATABASE_URL explicitly and don't read directUrl.
• Scheduled jobs, backups, maintenance — including whatever uses the maintenance password.
• Your own psql.

Rollout

1. Move one low-traffic tenant to 6432 with ?pgbouncer=true. Watch SHOW POOLS for a day.
2. Batch the remaining Prisma apps.
3. Once most traffic is pooled, lower max_connections toward ~300.

Keep Prisma's own connection_limit modest (3–5). Prisma pooling and PgBouncer pooling stack: each runtime process holds N client connections against a server pool of three per database. That's fine — client sockets are cheap — but a large N per process against max_client_conn = 2000 is worth knowing about.

Expect this on strict-TLS connections. Now that the server offers TLS again, the next failure those connections will show is self-signed certificate in certificate chain. The cert is signed by a private CA, and the client passes rejectUnauthorized: true unless the app opts into trusting it. Add that opt-in to the connection records.

Watching it

PgBouncer exposes a virtual database named pgbouncer to anyone in admin_users or stats_users, so the credential already in environment secrets can observe it:

docker exec pgbouncer psql -h 127.0.0.1 -p 6432 -U <admin> -d pgbouncer -c "SHOW POOLS"

SHOW STATS gives per-database transaction and query counts with average durations; SHOW CLIENTS and SHOW SERVERS list individual connections. A non-zero cl_waiting or rising maxwait on one database is the signal to raise its pool — per database, in [databases], not globally.

On the PostgreSQL side the same story reads from pg_stat_activity: after cutover, backends from the bridge address range are PgBouncer's, everything else is still direct — and the total should fall toward the steady state as tenants move.

Rolling to production

Everything above is reproduced by one script, which is why it exists. The staging deployment was built by hand first and the script made to match it exactly — its --verify passes against what's running.

# on the production primary
 
# 1. ship the three config files and the script
config/pgbouncer/pgbouncer.ini
config/pgbouncer/pg_hba.conf.template
scripts/setup-pgbouncer.sh
 
# 2. generate HBA + userlist (from pg_shadow) + TLS copy — nothing secret is printed
sudo ./scripts/setup-pgbouncer.sh
 
# 3. start ONLY pgbouncer
docker compose -f docker-compose.primary.yml up -d --no-deps pgbouncer
 
# 4. prove all three paths before touching an app
PGSSLMODE=require psql -h <primary-ip> -p 6432 …     # TLS through the pooler
PGSSLMODE=disable psql -h <primary-ip> -p 6432 …     # plaintext still served
psql … -p 6432 -d pgbouncer -c "SHOW POOLS"           # admin console

Before step 2, check what the script can't check for you:

• PostgreSQL's pg_hba.conf allows the bridge subnet with scram-sha-256.
• server.crt and server.key exist and aren't expired. The script copies them and warns on expiry, but can't create them.
• max_connections on the primary has headroom for #databases × max_db_connections on top of whatever direct clients remain during the migration.
• The host's firewall situation: Docker publishes 6432 to the world regardless of host INPUT rules. The HBA is the guard, and the cloud security list is the right place to narrow it further.

Nothing in this rollout restarts PostgreSQL. Nothing changes what's on 5432. If it goes wrong, docker compose stop pgbouncer returns the system to exactly what it was.

The lessons, condensed

1. Backend count is a client-side property. Pool size × pool count decides it; a server-side idle timeout just converts idle time into reconnect churn and changes nothing.
2. A pooler in transaction mode is the one mechanism that actually lowers it, because the thing it reclaims (the backend) is distinct from the thing the client sees (a socket).
3. Transaction pooling buys capacity by discarding per-backend state — and that's also the entire compatibility surface: SET, LISTEN, advisory locks, temp tables, and named prepared statements. Check the workload against that list before cutover, not after.
4. Named prepared statements were the one real surprise, fixed only by PgBouncer ≥ 1.21 (statement tracking) plus ?pgbouncer=true on the client — which is why the image is pinned by digest.
5. Two hops means every decision is made twice. Plaintext passwords on disk were removed via SCRAM pass-through (min_pool_size must then be 0). Docker's DNAT makes host iptables decorative, so PgBouncer gets its own generated HBA that can't drift from PostgreSQL's. TLS on 6432 with allow was required so the two ports stay interchangeable for both prefer and require clients.
6. It's an additive, reversible migration. 5432 stays exactly as it was; apps opt in per connection string; one tenant first, then a batch, then tune max_connections down. A broken rollout is docker compose stop pgbouncer.
7. Four things broke and each put a line in the config: the ULIMIT, the file-descriptor budget, server_idle_timeout (the actual fix), the private-key permission, the umask-created TLS directory, the pre-created userlist.txt, --no-deps, and log_connections = 0. Every section above is a failure that became least-surprise-by-default.

Related posts from the same operating theme: why VACUUM, VACUUM FULL, and pg_repack each handle disk differently, and a PostgreSQL 17 migration postmortem (the false economy of conservative server configs).

Find me on GitHub — corrections and connection-pooling war stories welcome.