How does database/sql connection pooling work? Which settings matter and what causes connection leaks?

*sql.DB is not a connection — it is a concurrency-safe pool, created once with sql.Open (which doesn't even connect; call PingContext). Each query checks out a connection and returns it when done.

• SetMaxOpenConns — default unlimited; under load you can exceed the DB's max_connections. Set it.
• SetMaxIdleConns — default 2; too low causes connection churn. Keep it close to MaxOpen.
• SetConnMaxLifetime / SetConnMaxIdleTime — recycle connections before load balancers/firewalls kill them silently.

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users WHERE active = $1", true)
if err != nil {
	return err
}
defer rows.Close() // returns the conn to the pool
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Name); err != nil {
		return err
	}
	users = append(users, u)
}
return rows.Err() // errors during iteration surface here

Leak causes: not closing *sql.Rows (early return inside the loop), using Query for statements that return no rows (use Exec), unfinished transactions (always defer tx.Rollback(); it's a no-op after Commit). With MaxOpen set, leaks show up as requests hanging forever — monitor db.Stats().WaitCount and WaitDuration.