Every PostgreSQL connection is a process on the server. Not a thread, not an entry in a table — a full Unix process with its own heap, its own catalog caches, and its own scheduling overhead. This is why max_connections is not a knob you turn up until the problem goes away: raising it trades memory and scheduler latency for concurrency, and past a few hundred connections you usually get worse throughput, not better.
Twenty pods, each with an application pool of 25 open connections, is 500 connections to a server that tolerates maybe 200 — and most of them idle at any instant, still costing memory and still counting against the limit. Connection pooling is the fix, and it comes in two flavors: pools your application owns, and pools that run as a proxy in front of the database. This post covers why connections are expensive, how to size a pool with arithmetic instead of vibes, how PgBouncer‘s three pooling modes differ, and the prepared-statement trap that catches almost everyone who switches to transaction pooling.
Why connections are expensive
A new PostgreSQL connection involves a TCP handshake, TLS negotiation, authentication, and backend process startup — tens of milliseconds in the happy case, seconds when the server is loaded. But the bigger cost is steady-state: each idle connection pins a backend process that holds cached relation descriptors, prepared-plan hashes, and memory arenas. The default max_connections is 100, and that number is a reflection of what a single machine tolerates, not a hard limit.
Pool sizing: do the arithmetic
Little’s law gives you the floor: in a stable system, the number of connections in use equals the arrival rate multiplied by the time each one holds the connection. If your service issues 200 queries per second and each query holds its connection for 10 milliseconds, you need 200 × 0.010 = 2 connections to sustain that load on average. Even at 2,000 QPS with 5 ms queries, ten connections carry the traffic.
Average is not the whole story, though. Queues form at bursts, so you multiply by a headroom factor — two to four is a common starting range — and you account for work that isn’t queries: a transaction that does application-side processing between statements holds its connection for the whole duration, not the sum of query times.
The counterintuitive part is that oversized pools hurt. Postgres scales with CPU cores and I/O bandwidth, not with connection count. If your pool has 100 connections but the server has 8 cores, you’ve built a scheduler-thrashing machine: every active query runs slower, lock contention rises, and p99 latency climbs across the board. A reasonable ceiling is a small multiple of core count for the whole cluster. Start low — often 2 to 4 times core count — measure throughput, and only raise the number when connections are genuinely the bottleneck, which usually looks like high I/O wait rather than saturated CPU.
In Go, the application-side pool lives on the *sql.DB, and the sizing knobs are explicit:
import (
"database/sql"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
)
func openDB(dsn string) (*sql.DB, error) {
db, err := sql.Open("pgx", dsn)
if err != nil {
return nil, err
}
// Sizing rule of thumb: (target concurrency), not "big number".
db.SetMaxOpenConns(20)
// Keep some warm connections so bursts don't pay the dial cost.
db.SetMaxIdleConns(10)
// Retire connections so DNS changes, failovers, and server-side
// state eventually get picked up by fresh connections.
db.SetConnMaxLifetime(30 * time.Minute)
// Don't hoard idle connections that get killed by firewalls.
db.SetConnMaxIdleTime(5 * time.Minute)
return db, nil
}Two details people miss: SetMaxIdleConns defaults to 2, so a pool that’s tuned for 20 open connections will silently churn connections at load if you don’t raise it. And database/sql pools live per-process — the numbers above are per pod, so multiply by your replica count before comparing against the server’s capacity.
Where the pool lives: application side vs proxy side
An application-side pool (what database/sql or a driver gives you) is simple and adds no hop, but it multiplies badly. Pool size × replicas grows with your deployment, and no single service knows what the others are doing. Fifty microservices each politely limiting themselves to 10 connections is still 500 connections.
A proxy-side pool like PgBouncer inverts this: applications open cheap connections to the proxy, and the proxy holds a bounded set of real server connections. Clients can connect and disconnect freely — the expensive resource is only materialized while work is actually running. This is why most serious Postgres deployments with many distinct clients run PgBouncer (or a cloud equivalent), even when every application also has its own internal pool. The two layers solve different problems: the app pool amortizes dial latency, the proxy enforces a global cap.
PgBouncer’s three modes
PgBouncer’s pool_mode setting determines how aggressively a server connection is recycled:
- Session pooling — a server connection is bound to a client for the client’s whole lifetime. Everything works, but the multiplexing benefit is limited to connection churn: if clients hold connections open for hours, you’ve gained almost nothing.
- Transaction pooling — a server connection is assigned only for the duration of a transaction (autocommit statements included). One server connection can serve dozens of clients per second. This is the mode most deployments want, and it breaks session state.
- Statement pooling — released after each statement; multi-statement transactions are rejected. Useful for autocommit-only workloads, otherwise a footgun.
Transaction pooling works by refusing to promise that two statements from the same client hit the same backend. Anything that lives on the connection between statements stops working: SET/RESET (outside a transaction), LISTEN, WITH HOLD cursors, SQL-level PREPARE/DEALLOCATE, and session-level advisory locks. Startup parameters like timezone and client_encoding are tracked and replayed, so those are safe. Set your session state inside each transaction — SET LOCAL is your friend — or move it out of the database entirely.
The core sizing directives, shown as a tuned starting point with the built-in defaults noted for comparison:
[pgbouncer]
pool_mode = transaction ; built-in default: session
max_client_conn = 100 ; clients PgBouncer will accept (default: 100)
default_pool_size = 20 ; server conns per user/database pair (default: 20)
min_pool_size = 5 ; keep some warm (default: 0)
reserve_pool_size = 5 ; extra conns when a pool stalls (default: 0, disabled)
reserve_pool_timeout = 5 ; ... after waiting this many seconds (default: 5)
max_db_connections = 60 ; hard cap across all pools to one DB (default: 0, unlimited)The invariant to enforce: max_db_connections summed across all PgBouncer instances must fit comfortably under the server’s real capacity — max_connections minus room for superuser and replication connections.
Prepared statements and transaction pooling
Prepared statements were the classic reason transaction pooling “didn’t work.” A named prepared statement lives on the backend that prepared it; when the next statement lands on a different backend, the server answers with “prepared statement does not exist.” Every driver with a client-side statement cache — which is most of them, because prepared statements genuinely speed up hot queries — trips over this.
Modern PgBouncer fixes the protocol-level case: since 1.21, setting max_prepared_statements to a non-zero value makes PgBouncer track named prepared statements and transparently re-prepare them on whichever server connection executes next. SQL-level PREPARE/EXECUTE still isn’t supported in transaction mode.
Go’s pgx driver has its own statement caching and several execution modes, and the right one depends on your pooling setup. Behind PgBouncer in transaction mode with prepared-statement tracking enabled, the default extended-protocol mode works; without it, you either drop to the simple protocol or disable the cache:
import (
"context"
"time"
"github.com/jackc/pgx/v5/pgxpool"
)
func openPgxPool(dsn string) (*pgxpool.Pool, error) {
cfg, err := pgxpool.ParseConfig(dsn)
if err != nil {
return nil, err
}
// Only needed if PgBouncer does NOT track prepared statements:
// import pgx and set:
// cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
cfg.MaxConns = 10 // per pod — small, because PgBouncer caps the total
cfg.MinConns = 2
cfg.MaxConnLifetime = 30 * time.Minute
return pgxpool.NewWithConfig(context.Background(), cfg)
}Note that database/sql‘s db.Prepare behaves differently again: it pins a prepared statement to one pooled connection and silently opens new connections when its pool is exhausted — one more reason to keep app pools small behind a proxy.
Recognizing and fixing pool exhaustion
Exhaustion has a recognizable signature: latency climbs while server CPU stays low. Clients aren’t slow because the database is busy — they’re queued waiting for a connection. In Go, a saturated database/sql pool makes calls block indefinitely (there’s no default acquire timeout), so goroutines pile up and the first symptom is often a timeout far from the database.
On the PgBouncer side, the admin console tells you directly: SHOW POOLS exposes cl_waiting (clients queued) and maxwait (how long the oldest waiter has been waiting). A maxwait that regularly exceeds a few hundred milliseconds means the pool is too small — or that long transactions are hogging server connections. Alert on maxwait; it’s the cleanest proxy for “connection starvation” there is.
The fixes, in the order you should try them:
- Shorten transactions. A transaction that wraps an external API call holds a server connection for the whole round trip. Move non-database work outside the transaction.
- Fix slow queries. By Little’s law, halving average query time halves the connections you need. A missing index shows up here as pool exhaustion, not just slow requests.
- Raise the pool — moderately. Bump
default_pool_sizeor the app’sMaxOpenConnsin small increments and watch server CPU and p99. If throughput doesn’t improve, the constraint is elsewhere. - Add headroom deliberately. Configure
reserve_pool_sizeso bursts borrow extra connections instead of queueing, withreserve_pool_timeoutdeciding how quickly that kicks in.
Connection pooling looks like infrastructure plumbing, but it’s really applied queueing theory: a bounded number of servers (your Postgres backends) serving stochastic arrivals, and every sizing decision is a claim about arrival rates and service times. Do the arithmetic, measure, and let maxwait and p99 latency — not connection counts — tell you whether the numbers are right.