Tuning ConnMaxLifetime and ConnMaxIdleTime in Go

This guide is part of Go database/sql Pool Internals. It covers SetConnMaxLifetime and SetConnMaxIdleTime — the two expiry controls on *sql.DB — and the specific production failure they exist to prevent: driver: bad connection or unexpected EOF on a connection the pool believed was fine.

Both default to zero, and in Go zero means no limit. A *sql.DB created with defaults will keep a connection forever. That is a reasonable default for a database on the same host and an actively dangerous one behind any load balancer, proxy, or managed database endpoint, all of which reap idle TCP connections on their own schedule without telling the client.

What each setter does

db.SetConnMaxLifetime(5 * time.Minute)  // maximum total age of a connection
db.SetConnMaxIdleTime(2 * time.Minute)  // maximum time a connection may sit unused
db.SetMaxOpenConns(25)                  // ceiling on total connections
db.SetMaxIdleConns(25)                  // how many may be kept idle

ConnMaxLifetime is measured from the moment the connection was created, regardless of use. ConnMaxIdleTime, added in Go 1.15, is measured from the last time the connection was returned to the pool. A connection is discarded when either limit is exceeded — they are independent, and the effective lifetime is the shorter of the two conditions to trigger.

Two behaviours in the implementation are worth stating precisely because they surprise people:

• Expiry is checked on the way out of the pool, not on a timer alone. When a connection is requested, database/sql checks whether the candidate has expired and, if so, closes it and takes the next one. There is also a background connection cleaner goroutine, but it runs on an interval derived from the shorter of the two settings — it is not instantaneous.
• An expired connection is never handed to a caller. The pool does not validate connections with a ping; expiry is its entire staleness defence. This is the structural difference from a JDBC pool that runs a validation query, and it is why setting these values correctly matters more in Go than the equivalent settings do in Java.

Choosing values

The same rule as any pool applies: ConnMaxLifetime must be shorter than the shortest idle or connection-age limit anywhere in the network path.

The last row is the case people miss. In a database deployment where the writer endpoint can move — an Aurora failover, a Kubernetes service whose backing pods are replaced — long-lived connections pin your pool to yesterday’s topology. A short ConnMaxLifetime forces periodic re-resolution of the endpoint, which is how the pool discovers the new writer without a restart. That mechanism is covered further in AWS Aurora Connection Scaling.

Go does not add jitter to connection expiry. If your pool was filled at startup, every connection reaches ConnMaxLifetime within a few milliseconds of the others, and you get a synchronised reconnect burst. For a pool of 25 that is harmless; for hundreds of pods each holding 25 connections it is a thundering herd against the database. The fix is to jitter it yourself:

func jitter(base time.Duration, pct float64) time.Duration {
    // deterministic per-process spread, seeded from the pod identity
    h := fnv.New32a()
    h.Write([]byte(os.Getenv("HOSTNAME")))
    frac := float64(h.Sum32()%1000)/1000.0*pct - pct/2
    return base + time.Duration(float64(base)*frac)
}

db.SetConnMaxLifetime(jitter(5*time.Minute, 0.20)) // ±10%

A complete configuration

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

    // Ceiling: this pod's share of the database connection budget.
    db.SetMaxOpenConns(25)

    // Keep the idle set equal to the ceiling so steady traffic never
    // pays reconnect cost inside a request.
    db.SetMaxIdleConns(25)

    // Below the 350 s load balancer idle timeout, with margin.
    db.SetConnMaxLifetime(4 * time.Minute)

    // Release genuinely unused connections back to the database sooner.
    db.SetConnMaxIdleTime(90 * time.Second)

    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        return nil, fmt.Errorf("ping: %w", err)
    }
    return db, nil
}

sql.Open does not connect. It validates the driver name and returns immediately; the first connection is established lazily on first use. The explicit PingContext is what turns a misconfigured DSN into a startup failure instead of a runtime one.

Note the relationship between SetMaxIdleConns and SetConnMaxIdleTime. MaxIdleConns caps how many connections may be kept idle; anything beyond it is closed immediately on return. ConnMaxIdleTime bounds how long those retained connections may stay idle. Setting MaxIdleConns below MaxOpenConns means every burst above the idle cap closes connections on return and reopens them on the next burst — a common and expensive misconfiguration that shows up as latency spikes rather than errors.

Verifying with DBStats

database/sql exposes counters that make expiry visible. MaxIdleTimeClosed and MaxLifetimeClosed are the two that confirm these settings are actually firing:

s := db.Stats()
log.Printf("open=%d inuse=%d idle=%d wait=%d waitDur=%s lifetimeClosed=%d idleTimeClosed=%d idleClosed=%d",
    s.OpenConnections, s.InUse, s.Idle,
    s.WaitCount, s.WaitDuration,
    s.MaxLifetimeClosed, s.MaxIdleTimeClosed, s.MaxIdleClosed)

Read the three “closed” counters as a ratio against your request rate:

• MaxLifetimeClosed should climb steadily and slowly — roughly open_conns / lifetime per second at steady state. A rate far above that means ConnMaxLifetime is too aggressive and you are paying handshake cost constantly.
• MaxIdleTimeClosed climbing during quiet periods is exactly the intent. Climbing during peak means ConnMaxIdleTime is shorter than the natural gap between requests on a connection.
• MaxIdleClosed climbing at all is a different signal: it means connections were closed because MaxIdleConns was too low, not because of any timer. Raise MaxIdleConns toward MaxOpenConns.

Export these as gauges alongside WaitCount and WaitDuration — the pattern is described in Prometheus and Grafana Pool Metrics.

Common failure patterns and remediation

Operational boundary

These settings bound connection age. They do not detect a connection that broke during an idle interval shorter than the cap — for that you need the retry that database/sql already performs (it retries a query twice on driver.ErrBadConn before surfacing the error) and a context deadline on every query. They also do nothing for the case where the database itself is refusing connections; that is a sizing and capacity question addressed in Connection Pool Sizing Formulas.

If you find yourself lowering ConnMaxLifetime below about a minute to suppress errors, stop and find the reaper. Something in the path is closing connections aggressively, and a one-minute lifetime is a workaround that costs a handshake per connection per minute forever.

Frequently Asked Questions

Related

• Go database/sql Pool Internals — the parent guide on how the pool allocates connections.
• Connection Pool Sizing Formulas — choosing MaxOpenConns in the first place.
• Connection Acquisition Timeout Strategies — bounding the wait when the pool is full.
• AWS Aurora Connection Scaling — why short lifetimes help a pool survive a failover.
• Prometheus and Grafana Pool Metrics — exporting the DBStats counters above.