skip to content

How do SetMaxIdleConns and SetMaxOpenConns differ on a *sql.DB, and what does a low idle limit cost?

level: middleimportance: must knowfreq 52%

answer

  1. one limits existence, one limits retention
  2. only one of them throttles callers
  3. the idle default is a very small number
  4. returned connections may be closed at once
  5. MaxIdleClosed climbing means churn

basics

~20 s

SetMaxOpenConns caps how many connections may exist at once; SetMaxIdleConns caps how many the pool keeps when they fall idle. With the idle limit far below the open limit, connections are closed and reopened constantly under load, paying a fresh handshake each time.

solid answer

~40 s

`SetMaxOpenConns` is a ceiling on connections that exist at all, in use or not. `SetMaxIdleConns` decides how many the pool retains when callers hand them back instead of closing them; the default is only 2. If you raise the open limit to 50 and leave the idle limit at its default, then under 30-way concurrency the pool keeps opening connections and, as each one is returned and finds the idle slots full, immediately closes it — so a steady workload pays a TCP connect, TLS handshake and database authentication over and over. `db.Stats().MaxIdleClosed` counts exactly those closures, and a fast-climbing value is the tell. The usual fix is to set the idle limit equal to the open limit, and use `SetConnMaxIdleTime` rather than a small idle count to shrink the pool after a burst.

code

go · 3 lines
go
db.SetMaxOpenConns(50)
db.SetMaxIdleConns(50)
db.SetConnMaxIdleTime(5 * time.Minute)

go deeper

for a junior

Know that the two settings are not the same knob: one bounds how many connections can exist, the other how many are kept around when idle, and their defaults are wildly different.

for a middle

Explain the churn mechanism concretely: returned connections beyond the idle limit are closed immediately, so every subsequent caller pays a connect, handshake and authentication round trip.

for a senior

Demonstrate the diagnosis: MaxIdleClosed rising fast on steady traffic points at configuration rather than load, and the fix is matching the limits plus an idle-time setting to shrink after bursts.

for a principal

Frame it as a default worth standardising: a shared pool-configuration helper across services beats every team rediscovering the idle default of 2 through a latency incident.

## Two different questions The two count-based knobs on a `*sql.DB` answer different questions. - `db.SetMaxOpenConns(n)` — *how many connections may exist at once?* It counts in-use and idle together and is a hard ceiling; callers beyond it wait. - `db.SetMaxIdleConns(n)` — *of the connections that exist, how many may sit unused rather than being closed?* It is not a limit on concurrency at all. It is a retention policy. Only the open limit throttles work. The idle limit decides whether a connection that has just finished a query is kept for the next caller or thrown away. ## The defaults, and the trap they set The default open limit is unlimited. The default idle limit is **2**. That asymmetry is the source of the classic misconfiguration: an engineer reads about pool sizing, sets `SetMaxOpenConns(50)` to protect the database, and leaves the idle limit alone. The pool may now hold 50 connections, but the moment concurrency drops below the peak, all but two of the returned connections are closed immediately. Under a sustained workload with, say, 30 concurrent callers, that means the pool is continuously opening and closing connections: each returned connection finds the two idle slots occupied and is closed, and the next caller that finds no idle connection opens a new one. Every one of those new connections pays a TCP handshake, a TLS handshake if the connection is encrypted, and a database authentication round trip — often several milliseconds each, and real work for the database server, which has to fork or allocate a session. Throughput drops and latency becomes noisy for reasons that never show up in query timings. Setting the idle limit to zero or a negative number is worse still: no idle connections are retained at all, so effectively every operation opens a connection. ## The counters that prove it `db.Stats()` distinguishes the three reasons a connection is closed: - `MaxIdleClosed` — closed because the idle limit was already full. - `MaxIdleTimeClosed` — closed because it sat idle longer than `SetConnMaxIdleTime` allows. - `MaxLifetimeClosed` — closed because it exceeded `SetConnMaxLifetime`. A `MaxIdleClosed` that climbs by thousands per minute on a steady workload is churn, and it is a configuration bug rather than a load problem. Alongside it, `Idle` will hover at the idle limit while `OpenConnections` swings. ## The interaction between the two limits The pool will not retain more idle connections than it is allowed to open in total. If you lower the open limit below the current idle limit, the idle limit is reduced to match — the settings are not independent in that direction. In the other direction there is no automatic adjustment: raising the open limit leaves the idle limit exactly where it was, which is precisely how the mismatch above arises. ## What to set For a service with steady traffic, the common and defensible configuration is to set the idle limit **equal to** the open limit. The pool then reuses connections instead of churning, and the open limit remains the only thing bounding how many connections you present to the database. The usual objection — "then my pool never shrinks and holds connections it isn't using" — is real but is answered by a different knob. `SetConnMaxIdleTime` retires a connection that has genuinely been unused for a while, so the pool shrinks after a burst on a timescale you choose, rather than shrinking instantly after every single query because of a small idle count. Using a low idle count as a shrink mechanism trades a rare, slow, timer-driven close for a constant, hot-path close. ## Why this is not generic pool advice The reason this question is asked about `database/sql` specifically is the default of 2. Many pool implementations default their idle floor to the maximum, or have no separate idle count at all. In Go the two knobs are independent, only one of them has a small default, and the resulting churn is invisible unless you look at `MaxIdleClosed`.

  • Does raising SetMaxIdleConns let more queries run concurrently?
    No. Concurrency is bounded only by `SetMaxOpenConns`; the idle limit never gates a caller. Raising it changes whether a finished connection is kept or closed, which affects latency and connection churn but not how many statements can be in flight at once.
  • What does the pool do if you set the open limit below the idle limit?
    The idle limit is reduced to match the new open limit, since the pool cannot retain more idle connections than it may hold in total. The reverse is not true: raising the open limit leaves a small idle limit untouched, which is how the churn misconfiguration usually appears.
  • Which db.Stats fields separate idle-limit churn from lifetime expiry?
    `MaxIdleClosed` counts connections closed because the idle slots were full, `MaxIdleTimeClosed` those closed for sitting idle too long under `SetConnMaxIdleTime`, and `MaxLifetimeClosed` those retired by `SetConnMaxLifetime`. All three are cumulative, so compare successive samples to see which one is actually driving the closes.

saying these in an interview costs you the question

  • Thinks the idle limit caps how many queries can run at once
  • Assumes the idle limit defaults to the open limit
  • Sets a large open limit and never touches the idle limit
  • Sets the idle limit to zero to save database resources
  • Blames the database for latency caused by connection churn