database/sql is a pool: knobs, sizing math, and the rows.Close leak that starves it

A pool that dials lazily

sql.DB is not a connection. It is a concurrency-safe pool of connections, meant to be created once and shared by every goroutine for the life of the process — one per database, never one per request. And it is lazy:

db, err := sql.Open("pgx", dsn)
if err != nil {
	return err // only DSN/driver errors arrive here — nothing has been dialed yet
}
db.SetMaxOpenConns(25)                 // hard cap on concurrent connections
db.SetMaxIdleConns(25)                 // how many survive between bursts
db.SetConnMaxLifetime(5 * time.Minute) // recycle: survives failovers and LB drains
db.SetConnMaxIdleTime(2 * time.Minute) // shrink an idle pool without dial thrash

if err := db.PingContext(ctx); err != nil { // the FIRST real dial happens here
	return err // fail fast at startup, not on the first user request
}

Every query borrows a connection from the pool and follows the same path: reuse an idle one if available; dial a new one if the pool is below MaxOpenConns; otherwise block in a wait queue until somebody releases. Each knob exists because its absence is a specific production failure:

• MaxOpenConns unset means unlimited. Every Postgres connection is a forked backend process holding several megabytes before doing any work — and work_mem (4 MB default) can be claimed per sort or hash node on top. A traffic spike with no cap turns into hundreds of backends, server memory pressure, and FATAL: sorry, too many clients already for every other service sharing the database. The cap converts overload into bounded queueing inside your process, where a context deadline can handle it.
• MaxIdleConns too low — and the default is 2. Under a steady 25-concurrent load with 2 idle slots, the pool closes and re-dials connections constantly: TCP plus TLS plus auth is a couple of network round trips, milliseconds added to queries that should take hundreds of microseconds, and visible churn in pg_stat_activity. For steady services, set idle equal to open.
• ConnMaxLifetime unset is the Hook. Established connections never re-resolve DNS, so after a failover or a load-balancer re-target they keep talking to the old node for as long as it answers. A bounded lifetime guarantees every connection redials within minutes — it also rebalances load after you scale a database behind a proxy.
• ConnMaxIdleTime lets a pool sized for the daily peak shrink overnight instead of holding eighty server backends hostage while doing nothing.

The sizing arithmetic

When you size a pool in isolation — “let’s say 25 connections per pod” — you are making a promise the whole fleet has to keep. The budget is fleet-wide, and it is the database that pays. The constraint: pods × MaxOpenConns, plus migration jobs, cron tasks, and your colleagues running psql, must stay under max_connections minus superuser_reserved_connections (3 by default). Postgres ships with max_connections = 100; many managed defaults sit at a few hundred. Now run the spike scenario: 30 pods each capped at 25 connections may legitimately want 750 against a 200-connection server. That is not a capacity plan, it is a self-inflicted denial of service — every dial past the server cap fails with sorry, too many clients already, and the errors land randomly across all services sharing the database. Past roughly ten pods, do the division honestly (200 minus reserve, divided by 60 pods at the HPA max, is 3 connections each) — or accept that a server-side pooler like PgBouncer has become infrastructure, not an optimization.

Rows: the leak and the half-read result

Ask yourself: what holds a connection open between the first result row and the last? The *Rows iterator does — and every early exit without Close is a connection the pool never gets back. This is the single most common way Go services exhaust their pools — one leaked iterator at a time:

rows, err := db.QueryContext(ctx, `SELECT id, email FROM users WHERE org_id = $1`, org)
if err != nil {
	return nil, err
}
defer rows.Close() // returns the connection to the pool; without it, an early return leaks the conn

var users []User
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Email); err != nil {
		return nil, err
	}
	users = append(users, u)
}
return users, rows.Err() // the loop exits the same way on success and on a dropped connection

The leak is sneaky because Next auto-closes the rows when iteration reaches the natural end — so code without defer rows.Close() passes every test that reads all rows. Add one early return or break inside the loop, and that connection never comes back. Nothing crashes; db.Stats().InUse just ratchets up by one, until the day the pool hits MaxOpenConns and every query in the process blocks forever — or until its context deadline, if you gave it one. The pair defer rows.Close() plus rows.Err() is non-negotiable: the for rows.Next() loop terminates identically when the result set ends and when the connection dies mid-stream, and only rows.Err() tells you whether you got everything or half a result you are about to present as complete.

The three verbs divide cleanly. QueryRowContext is for exactly-one-row reads — it defers all errors to Scan, where zero rows arrives as sql.ErrNoRows (check with errors.Is; it is a result, not a failure). QueryContext is for result sets and carries the Close/Err obligations above. ExecContext is for statements without result sets and returns RowsAffected — an UPDATE that matched zero rows is not an error, which your code usually needs to notice. For nullable columns, plain string will fail the Scan: use sql.NullString (explicit, ugly, grep-able) or *string (quieter, one nil-check away from a panic) — either works, but pick one convention per codebase. And use the Context variants everywhere: the plain forms are pre-context legacy, and a query with no deadline holds its connection for exactly as long as a slow server feels like taking.