Configuring SetMaxOpenConns and SetMaxIdleConns

This guide is part of Go database/sql Pool Internals. The Go standard-library pool is governed by four interacting setters — SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime, and SetConnMaxIdleTime — and the most common production failure comes from configuring one in isolation. When MaxIdleConns is smaller than MaxOpenConns, every connection opened above the idle ceiling is closed the instant it is returned to the pool, producing relentless open/close churn that shows up as elevated database connection rate, TLS handshake CPU on the server, and latency spikes that no amount of raising MaxOpenConns fixes.

The churn is invisible in application errors but obvious in DBStats. A connection that is opened, used once, and immediately closed never accumulates in the idle set, so the next request opens another. On the database side this looks like a flood of short-lived backends:

# pg_stat_activity sampled once per second shows constant new PIDs
postgres=# SELECT count(*), max(backend_start) FROM pg_stat_activity WHERE state='idle';
 count |          max
-------+-------------------------------
     2 | 2026-06-20 14:07:42.118+00     # idle set never grows past MaxIdleConns
# while the connection-rate metric climbs:
go_sql_stats_connections_opened_total  18422   (rising ~300/s under steady load)

The same DBStats also surfaces the opposite failure — under-provisioned MaxOpenConns — as nonzero WaitCount and growing WaitDuration, where goroutines block waiting for a free connection. This guide separates the two and gives a sizing procedure that sets all four parameters coherently.

Rapid incident diagnosis

database/sql exposes everything you need through db.Stats(). Sample it on an interval and read these fields in order.

1. WaitCount and WaitDuration. If WaitCount is climbing and WaitDuration grows over time, goroutines are blocking on conn := db.Conn() because all MaxOpenConns slots are in use. This is starvation — raise MaxOpenConns (within database max_connections) or reduce per-request hold time. Acquisition that blocks indefinitely is covered in Understanding Connection Acquisition Timeouts in Go.
2. MaxIdleClosed. A large and rising MaxIdleClosed is the churn fingerprint: connections are being closed on return because the idle set is full. This means MaxIdleConns < MaxOpenConns while the pool routinely runs above the idle ceiling. Raise MaxIdleConns toward MaxOpenConns.
3. MaxLifetimeClosed. A high value here is expected if ConnMaxLifetime is short, but if it dominates the close count and correlates with latency, your lifetime is too aggressive and is recycling hot connections mid-load.
4. MaxIdleTimeClosed. Driven by ConnMaxIdleTime. High values mean idle connections are being reaped quickly — fine for bursty traffic, wasteful if the next burst re-opens them immediately.
5. InUse vs Idle vs OpenConnections. If InUse frequently equals OpenConnections equals MaxOpenConns, the pool is saturated. If Idle stays pinned at MaxIdleConns while OpenConnections is much higher, you are paying the churn tax.

The decision rule: rising WaitCount means too few open connections; rising MaxIdleClosed means the idle ceiling is choking connection reuse. The two are independent and frequently occur together.

Mathematical sizing / parameter formula

Size MaxOpenConns from required throughput and per-borrow hold time using Little’s Law, then set MaxIdleConns to eliminate steady-state churn.

Define:

• λ = required query throughput (queries per second at peak)
• S = mean connection hold time per query (execution + transaction, in seconds)
• L = concurrent connections in use = λ × S (Little’s Law)

The pool must satisfy MaxOpenConns ≥ L, with headroom for variance:

MaxOpenConns = ceil(λ × S × 1.2)        # 20% headroom for latency variance
MaxIdleConns = MaxOpenConns             # eliminate return-time churn at steady state

Setting MaxIdleConns equal to MaxOpenConns is the default recommendation for steady workloads: it lets every opened connection stay resident, so reuse is maximized and MaxIdleClosed stays flat. Lower MaxIdleConns only deliberately, for spiky workloads where you want idle connections reaped between bursts — and then pair it with ConnMaxIdleTime rather than a low idle ceiling.

Worked example. A Go API must sustain λ = 1200 qps. Mean hold time S = 0.018 s (18 ms per query including a short transaction). Concurrency in use:

L = λ × S = 1200 × 0.018 = 21.6 connections in flight
MaxOpenConns = ceil(21.6 × 1.2) = ceil(25.9) = 26
MaxIdleConns = 26          # match open to stop churn

A pool of 26 covers the steady-state concurrency with headroom. If you had left the default MaxIdleConns = 2, then 24 of every 26 connections would close on return — MaxIdleClosed would climb by roughly λ minus the reuse rate, and the database would see a continuous open/close storm. Now compare with max_connections: across, say, 8 application instances that is 8 × 26 = 208 connections plus the database’s own overhead. If max_connections = 200, you are over budget and must either lower per-instance MaxOpenConns, reduce instance count, or front the database with a proxy that multiplexes.

Exact remediation & configuration

Set all four parameters together. MaxIdleConns must never exceed MaxOpenConns (the runtime silently clamps it down), and ConnMaxLifetime should stay under any upstream idle-kill timeout (load balancer, proxy, or database idle_in_transaction_session_timeout).

package main

import (
	"database/sql"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func newPool(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}

	// Sized from Little's Law: ceil(lambda * S * 1.2) = 26
	db.SetMaxOpenConns(26)

	// Match idle to open so returned connections are reused, not closed.
	// This is the single most important line for eliminating churn.
	db.SetMaxIdleConns(26)

	// Recycle below the database/LB idle-kill window to avoid stale sockets.
	// Stagger across the fleet so all instances don't recycle simultaneously.
	db.SetConnMaxLifetime(30 * time.Minute)

	// Reap genuinely cold connections during off-peak troughs.
	db.SetConnMaxIdleTime(5 * time.Minute)

	return db, nil
}

Notes on the interplay:

• SetMaxIdleConns(n) where n > MaxOpenConns is clamped to MaxOpenConns at runtime, so always set open first or set both to the same value.
• SetConnMaxLifetime caps the absolute age of any connection regardless of activity; SetConnMaxIdleTime caps how long a connection may sit unused. They are independent — a busy connection is recycled by lifetime, an idle one by idle-time, whichever fires first.
• For a spiky workload where you intentionally want a small resident idle floor, keep MaxIdleConns lower than MaxOpenConns but set ConnMaxIdleTime to a few minutes so the idle set drains gradually rather than slamming closed on every return.

Apply via rolling deploy. Because MaxIdleConns now matches MaxOpenConns, expect the resident connection count per instance to rise to its steady-state level and stay there; multiply by instance count and confirm against max_connections before rollout.

Validation & verification

Confirm churn is gone and no goroutine is starving.

Application-side: assert DBStats deltas. Expose db.Stats() and watch MaxIdleClosed and WaitCount across a steady-load window. After tuning, MaxIdleClosed should be flat (no new idle-driven closes) and WaitCount should be zero or near-zero.

s := db.Stats()
log.Printf("open=%d inUse=%d idle=%d waitCount=%d waitDur=%s idleClosed=%d lifeClosed=%d",
	s.OpenConnections, s.InUse, s.Idle, s.WaitCount, s.WaitDuration,
	s.MaxIdleClosed, s.MaxLifetimeClosed)

A healthy steady state reads idle close to MaxIdleConns, waitCount not increasing, and idleClosed not increasing.

Database-side: confirm the open/close storm stopped.

SELECT count(*) AS idle_backends,
       min(backend_start) AS oldest_idle
FROM pg_stat_activity
WHERE state = 'idle'
  AND datname = current_database();

After the fix, oldest_idle should advance slowly (connections persist and are reused) rather than every idle backend showing a brand-new backend_start each sample. Stable, long-lived idle backends prove reuse is working.

Load-test assertion. Run a constant-throughput load at peak λ for several minutes and assert that the connection-open counter plateaus:

# generate steady load, then check that opened_total stops climbing per scrape
curl -s localhost:9090/metrics | grep go_sql_stats_connections_opened_total
sleep 30
curl -s localhost:9090/metrics | grep go_sql_stats_connections_opened_total

The two readings should differ only by the small number of lifetime-driven recycles, not by hundreds of new opens. A large delta means churn persists — recheck that MaxIdleConns truly equals MaxOpenConns and was not clamped.

Frequently Asked Questions

Related

• Go database/sql Pool Internals — the parent topic on how the standard-library pool manages connections.
• Understanding Connection Acquisition Timeouts in Go — sibling guide on blocking acquisition and context cancellation.
• Optimizing HikariCP maximumPoolSize for High Concurrency — the same Little’s Law sizing applied to the JVM pool.
• Detecting Connection Pool Saturation — turn WaitCount/WaitDuration into saturation alerts.