PostgreSQL Connection Pooling With PgBouncer: A Practical Guide

I have lost count of the times a perfectly healthy Node.js application has gone down at noon with FATAL: remaining connection slots are reserved for superuser. The database tells the truth: your ORM opened a connection for every request, the pool grew unbounded, and PostgreSQL — which sizes its max_connections budget around per-backend memory — said no.

The fix is usually not "tune PostgreSQL higher." It's a connection pooler. This article walks through why you need one, how PgBouncer's transaction pooling works, and the three mistakes that make pooling setups stale, broken, or silently slower.

Why PostgreSQL resists lots of connections

Every PostgreSQL connection is a backend process. Each one reserves memory in the shared_buffers budget plus per-connection overhead, so the commonly recommended relationship is roughly max_connections ≈ cores * 4 + shared_buffers / 150MB. A small 2 vCPU instance comfortable with ~100 connections will choke at 500, even with zero active queries.

Meanwhile, a Node.js request that touches 6 tables through an ORM can check out 3 or 4 connections in flight. Multiply by a handful of replicas and a traffic spike, and you need far more connections than an idle pool of 10 could ever justify. The pooler collapses the thousands of logical connections into a small, stable set of physical ones.

What PgBouncer actually does

PgBouncer sits between your app and PostgreSQL as a proxy. Connections are lazily established and kept warm. There are three modes:

• Session pooling — a server connection is assigned to a client for the whole session. Better than nothing, but the connection count still grows with concurrent clients.
• Transaction pooling — a server connection is returned to the pool the moment a transaction finishes. This is the mode that makes 10k concurrent clients usable on 50 physical connections.
• Statement pooling — releases after each statement; almost never what you want.

Transaction pooling is the default answer, but it has hard requirements your app has to respect.

The three mistakes that break pooling

1. Prepared statements without PREPARE tuning

PostgreSQL's prepared statements are session-scoped, and extended protocol prepared statements implicitly are created on whichever connection happens to execute them. Under transaction pooling you may get a different physical connection on the next statement, so the prepared statement is gone — causing a re-prepare per query, or an outright prepared statement "s1" already exists error.

Modern client libraries handle this with client-side prepared statement caching plus the protocol's Sync. If you use Prisma or pg directly, you get this for free. If you hand-roll a driver, do not call PREPARE explicitly under a pooler — treat named statements as poison.

2. Assuming session state survives

Anything outside a transaction — SET statements, temp tables, advisory locks, NOTIFY listeners — lives on the physical connection. Under transaction pooling that connection may belong to a different client five milliseconds later.

-- Fine: scoped to the transaction (third arg = true)
SELECT set_config('app.tenant_id', 'abc-def', true);
 
-- Dangerous under a pooler: leaks into the shared connection
SET app.tenant_id = 'abc-def';

This is exactly why the multi-tenant strategy from my earlier article pairs RLS with transaction-local settings. set_config(..., true) protects you; a bare SET does not.

3. Not monitoring the pool

PgBouncer gives you two killer vantage points — and most people ignore both. SHOW POOLS tells you the per-database saturation; SHOW STATS shows the request/transaction counts. The metric that predicts the outage you'll actually have is maxwait, the age of the oldest waiting client.

psql -h localhost -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
psql -h localhost -p 6432 -U pgbouncer pgbouncer -c "SHOW STATS;"

Alert on maxwait > 2000 and on client_idle_time climbing. The pool is healthy when physical connections stay flat while logical demand jumps.

A production-shaped config

[databases]
app = host=/run/postgresql port=5432 dbname=app
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 40
max_client_conn = 2000
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 300

Two details worth calling out:

• auth_type = md5 means app connections go to PgBouncer with a rotated service password rather than the real PostgreSQL credentials, and the pooler holds the real credentials in userlist.txt. That's one credential to rotate per service.
• default_pool_size = 40 — start conservative and grow based on SHOW POOLS saturation, not Google's default.

When using Prisma, tell it to go through the pooler with an explicit port and give it a sensible pool size:

import { PrismaClient } from "@prisma/client";
 
export const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL }, // postgres://…@127.0.0.1:6432/app
  },
  connection_limit: 20,
});

The decision tree for when you actually need it

• You see remaining connection slots errors during traffic spikes → pooler now.
• SHOW POOLS shows maxwait at 0 with idle server connections → you don't need a bigger pool, you need fewer ORM connections per request.
• Your app holds transactions open across network calls (an anti-pattern) → pooling will surface the bug as impatient clients; fix long transactions first.
• Running serverless functions where cold start + pooled connections collide → use a pooler at the database layer and keep sessions transaction-scoped.

Wrap up

PgBouncer in transaction mode is the highest-leverage, lowest-cost resilience upgrade most Postgres-backed SaaS apps can make after adding an index on their hot tables. The three rules that keep it honest are: keep sessions stateless, keep settings transaction-scoped, and watch maxwait instead of the CPU graph.

Set it up, load-test with a spike of 10x normal traffic, and confirm physical connections stay flat while logical connections climb. When that works, you'll stop losing sleep over max_connections — and your pg_stat_activity, for the first time in months, will look calm.