Connection Pool Tuning

Two incidents reach the on-call channel with the same title — "database issues" — and opposite causes.

Incident A: latency climbs, then requests start failing with database/sql: connection is already in use style pressure and long stalls before errors. db.Stats() says OpenConnections is pinned at the limit and WaitCount grows every second. The pool has no free slots; every new query queues behind someone else. This is exhaustion: the limit is reached because either the limit is too small or — much more often — queries and transactions are holding connections too long.

Incident B: a warm service under steady load starts throwing driver: bad connection, connection reset by peer, and occasional TLS handshake errors, while the database's own CPU looks calm. Connections are being created and destroyed constantly instead of reused. This is churn: idle timeouts, lifetimes, or an intermediary (load balancer, firewall) is killing connections the pool still believes are good.

The two are diagnosed from the same place — the pool's own counters:

s := db.Stats()
log.InfoContext(ctx, "db pool",
	"max_open",    s.MaxOpenConnections,
	"open",        s.OpenConnections, // in use + idle
	"in_use",      s.InUse,
	"idle",        s.Idle,
	"wait_count",  s.WaitCount,   // cumulative queries that had to queue
	"wait_time",   s.WaitDuration, // total time spent queueing
	"idle_closed",   s.MaxIdleClosed,   // idle conns evicted by idle policy
	"lifetime_closed", s.MaxLifetimeClosed, // conns retired at ConnMaxLifetime
)

Reading it is straightforward: WaitCount climbing means exhaustion — requests are queueing for a slot. MaxIdleClosed climbing fast means the idle cache is too small for the traffic pattern, so connections are thrown away moments after being returned. MaxLifetimeClosed clustering means connections are retiring en masse — fine as a slow safety net, suspicious if it lines up with your error spikes.

One structural truth before tuning: the pool is a concurrency limiter for database work, and that is a feature. Ten thousand HTTP goroutines cannot all query at once when MaxOpenConns is 25 — the excess queues at your process, where a context deadline can bound the wait, instead of overwhelming the server. The knobs decide where the backpressure sits; setting them wrong moves the failure rather than removing it. With the symptoms separated, the settings themselves come next.

The entire tuning surface is four methods, and every default is wrong for production in a different way:

func Open(ctx context.Context, dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(25)                // 0 = UNLIMITED — never ship this
	db.SetMaxIdleConns(25)                // default is 2
	db.SetConnMaxIdleTime(5 * time.Minute) // drop idle sockets promptly
	db.SetConnMaxLifetime(30 * time.Minute) // safety net, not a timer
	// ... ping, then return
}

SetMaxOpenConns — the hard ceiling. The zero default means unlimited: every goroutine that reaches the database gets its own connection, and a traffic spike becomes a connection spike that the database answers with FATAL: sorry, too many clients already. Pick the number from the budget, not the app's wishes: Postgres's max_connections (typically 100) is shared by every service, replica, migration job, and psql session pointing at it. Ten instances at 25 each is 250 hopeful connections into a 100-connection server. The starting point many teams land on is a small multiple of CPU cores — connections execute queries, and more than cores' worth mostly adds context switching — then measure before raising it.

SetMaxIdleConns — the reuse cache. The default of 2 means: after a burst of 20 queries, 18 connections are closed rather than kept warm, and the next burst re-dials them. The symptom looks like Incident B while MaxIdleClosed climbs. Set idle equal to (or slightly below) max open: idle connections are the pool working correctly, and an idle socket costs the database almost nothing compared with a handshake.

SetConnMaxIdleTime — polite reaping. Idle connections beyond this age are dropped on return. This is where you retire sockets the load balancer or firewall has already killed server-side — a stale idle connection is a future bad connection error. Rule: idle time comfortably below the intermediary's idle timeout (5 minutes against a 10-minute LB kill), so you close first, on your own terms.

SetConnMaxLifetime — the slow safety net. Connections are retired after this age regardless of health, which handles NAT/LB connection limits and secret rotations. The trap is setting it short: connections created together expire together, and a 5-minute lifetime on a busy pool becomes a synchronized reconnect wave every 5 minutes — Incident B on a metronome. Keep it long (30+ minutes), because idle-time already handles the routine cleanup.

Notice what the four knobs cannot fix: a 3-second query holding a slot makes every other query wait 3 seconds — MaxOpenConns converts slow queries into queueing by design. If WaitCount is climbing, the honest first question is which query is slow (transactions held open across HTTP calls being the usual culprit), before any limit goes up. The numbers are the second conversation; the counters decide whether you are having the first.

Tuning without the counters is folklore — someone doubles MaxOpenConns after a bad week, the incident does not recur for a month, and the number calcifies. Exporting db.Stats() as metrics turns each knob into a hypothesis you can check.

// scrape loop or per-request metrics export
s := db.Stats()
metrics.Gauge("db_pool_open_connections", float64(s.OpenConnections))
metrics.Gauge("db_pool_in_use", float64(s.InUse))
metrics.CounterAdd("db_pool_wait_total", float64(s.WaitCount))
metrics.CounterAdd("db_pool_wait_seconds_total", s.WaitDuration.Seconds())
metrics.CounterAdd("db_pool_idle_closed_total", float64(s.MaxIdleClosed))
metrics.CounterAdd("db_pool_lifetime_closed_total", float64(s.MaxLifetimeClosed))

Because the counters are cumulative, alert on their rate of change and pair each signal with its decision:

The last row matters most: a pool with no waits is not your bottleneck, and every minute spent adjusting it is a minute not spent on the 400 ms query. go tool pprof against your service and the database's own slow-query log answer latency questions that no pool setting can (pprof and profiling).

Then there is the arithmetic that no Go setting escapes: instances × max open must fit in the database's connection budget. Twenty replicas at 50 is 1,000 against Postgres's 100 — and no amount of careful tuning inside one process fixes fleet-level math. When the fleet outgrows the server, pooling moves out of the application: PgBouncer in transaction mode lets every instance hold many client connections while the server sees few. Transaction pooling has a cost — session state (prepared statement names, SET variables, advisory locks tied to a session) cannot survive across uses — so the application must behave accordingly. That is a deliberate architecture change, made when the dashboard shows a healthy app pool and a saturated database accept queue.

The full decision ladder: measure waits → fix slow holders → rebalance idle and lifetime from close counters → raise the ceiling within the database budget → move pooling to a shared proxy when the fleet no longer fits. Each rung is justified by a counter, which is what separates tuning from superstition.