skip to content

What do SetConnMaxLifetime and SetConnMaxIdleTime cap on a *sql.DB, and why set either one?

level: middleimportance: nice to knowfreq 38%

answer

  1. two clocks: age and inactivity
  2. one of them is reset by use
  3. both default to forever
  4. something outside Go kills connections eventually
  5. expiry waits for the query to finish

basics

~20 s

SetConnMaxLifetime caps a connection's total age since it was created; SetConnMaxIdleTime caps how long it may sit unused. Both default to no limit. Lifetime retires connections before something outside Go kills them; idle time lets the pool shrink after a burst.

solid answer

~40 s

`SetConnMaxLifetime(d)` retires a pooled connection once it is older than `d` measured from when it was created, whatever it has been doing. `SetConnMaxIdleTime(d)` retires one that has merely sat unused for longer than `d`, however young it is. Both default to zero, meaning connections are reused forever. A lifetime bound matters because things outside your process quietly invalidate connections — a database restart or failover, a network device or middlebox dropping long-lived sessions, credentials that expire, or a scaled-out cluster that only rebalances when clients reconnect — and a bounded lifetime turns a mysterious mid-request failure into a routine reconnect. Neither setting interrupts a query in flight: expiry is enforced when a connection is idle or returned to the pool. `db.Stats()` reports `MaxLifetimeClosed` and `MaxIdleTimeClosed` so you can see which one is firing.

code

go · 2 lines
go
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

go deeper

for a junior

Recall that both settings govern when a pooled connection stops being reused, that one measures total age and the other measures inactivity, and that neither is set by default.

for a middle

Explain what invalidates a long-lived connection from outside your process — failover, middlebox session drops, cluster rebalancing, credential expiry — and why an age bound turns those into routine reconnects.

for a senior

Show that you would pick a lifetime under the shortest known server-side or network session limit, watch MaxLifetimeClosed for over-aggressive retirement, and use idle time rather than a tiny idle count to shrink after bursts.

for a principal

Take a position for the fleet: which of the network, database and credential lifetimes actually binds you, whether replicas should stagger their retirement, and what reconnect cost you are willing to pay for faster failover healing.

## Two clocks, not one `database/sql` retires pooled connections on two independent clocks. - **`db.SetConnMaxLifetime(d)`** — a connection is not reused once its total age, measured from the moment it was created, exceeds `d`. Being busy does not reset the clock. This is an absolute expiry. - **`db.SetConnMaxIdleTime(d)`** — a connection is not reused if it has been sitting unused for longer than `d`. Using it resets that clock. This is an inactivity expiry. Both default to zero, which means *no limit*: an unconfigured pool will reuse a connection for as long as the process lives. ## Why bound the lifetime A connection is a long-lived object that depends on state you do not own, and plenty of things invalidate it without telling your process: - **Server restarts and failovers.** After a failover the pool may still be holding connections to a node that is gone or has become a replica. The next query on such a connection fails in the middle of a user request. - **Middleboxes and networks.** Load balancers, NAT tables and firewalls commonly drop sessions that have been open a long time, or idle a long time, and do not always send anything the client notices. - **Rebalancing.** If your database is behind a load balancer or a cluster grows a node, existing connections stay where they were pinned. New nodes only get traffic when clients open new connections, so a pool that never retires anything never rebalances. - **Credential rotation.** Short-lived, issued-on-demand database credentials expire; a connection authenticated with an old credential may be terminated by the server. A bounded lifetime converts all of these from a rare, confusing, mid-request failure into a routine background reconnect. It is the cheapest resilience knob on the pool. ## Why bound the idle time Idle time answers a different problem: a burst inflates the pool to its open limit, the burst ends, and the connections sit there consuming a session's worth of memory on the server for the rest of the day. With `SetConnMaxIdleTime` set, connections unused for that long are closed and the pool shrinks back on its own. This is the right way to shrink a pool, and it is why a small `SetMaxIdleConns` is the wrong way. A small idle *count* closes connections on the hot path after almost every query; a bounded idle *time* closes only connections that genuinely have not been needed. ## Expiry never interrupts a query This is the detail that separates a confident answer from a guess. Neither setting cancels work in flight. A connection that crosses its lifetime while executing a statement finishes that statement; the pool declines to hand it out again and closes it once it is back. Separately, the pool runs a background cleaner when either bound is configured, so idle connections that have expired are closed even if nobody asks for a connection again. Note also what these settings are *not*: they are not query timeouts. Nothing here bounds how long a statement may run — that is what a `context.Context` deadline on the call does — and nothing here bounds how long a caller waits for a free connection, which is the same context deadline applied to the acquire. ## Reading the effect `db.Stats()` reports the closures separately: `MaxLifetimeClosed` for age-based retirement, `MaxIdleTimeClosed` for inactivity retirement, and `MaxIdleClosed` for connections closed because the idle *count* was full. All three are cumulative totals, so you compare successive samples. If `MaxLifetimeClosed` is enormous, your lifetime is short relative to your traffic and you are paying for handshakes; if it is zero on a long-running service, you have not set the knob at all. ## Choosing values There is no universal number, but the shape of a sane configuration is: a lifetime comfortably shorter than any known server-side or network-side session limit and short enough that a failover heals within an acceptable window, and an idle time long enough that normal traffic troughs do not cause churn. Deliberately staggering the value slightly between replicas is worth considering, so that a fleet started at the same moment does not retire every connection simultaneously.

  • Which of the two clocks does using a connection reset?
    Only the idle clock. `SetConnMaxIdleTime` measures time since the connection was last used, so activity keeps a connection alive indefinitely under that rule. `SetConnMaxLifetime` measures total age from creation and is unaffected by how busy the connection has been, so a heavily used connection still retires on schedule.
  • Why is a short SetConnMaxLifetime not free?
    Every retirement means the next caller opens a fresh connection and pays a TCP connect, a TLS handshake if encrypted, and database authentication. Set it too short and a busy service spends measurable time and server CPU reconnecting; `db.Stats().MaxLifetimeClosed` climbing steeply relative to your query rate is the signal that you have overdone it.
  • Do these settings put any bound on how long a query may run?
    No. They govern connection reuse only. Bounding statement execution is the job of the `context.Context` you pass to `QueryContext` or `ExecContext`; a connection that crosses its lifetime mid-statement still finishes, and is closed afterwards rather than being handed out again.

saying these in an interview costs you the question

  • Treats SetConnMaxLifetime as a query timeout
  • Thinks activity resets the total lifetime clock
  • Believes an expiring connection aborts the running statement
  • Assumes connections are recycled by default
  • Sets a very short lifetime and ignores the reconnect cost