A *sql.DB is a pool of connections, not a single connection. That distinction matters because every query you run borrows a connection, uses it, and returns it. This chapter covers the four knobs that control the pool, how a query acquires and releases a connection, what happens when the pool runs dry, how to tune the settings for a real workload, and how to read db.Stats() to spot problems before they become outages.

Every call to db.Query, db.QueryRow, db.Exec, or db.BeginTx borrows a connection from the pool. If an idle connection is sitting in the pool, it's reused. If none is idle and the pool is below its open-connection cap, a new one is opened. If the pool is at its cap, the caller blocks until a connection is freed or the context is cancelled. When the query is done (and any *sql.Rows is closed), the connection goes back to the pool for the next caller.

The pool box in the middle is where the four tuning knobs apply. They cap how many connections can exist (MaxOpenConns), how many can sit idle (MaxIdleConns), how long a connection can live (ConnMaxLifetime), and how long it can sit idle before being closed (ConnMaxIdleTime). Nothing about this is automatic in the way you might wish: the defaults are conservative for one knob, unlimited for another, and zero for two more. Get them wrong and you'll see anything from slow startup to unpredictable timeouts to the database refusing new connections during peak traffic.

The *sql.DB type exposes exactly four setter methods that control pool behavior. Here they are at a glance:

Each knob is independent, but they interact in ways that matter.

SetMaxOpenConns

SetMaxOpenConns(n) is the hard ceiling on total open connections, idle or in-use, that the pool will hold. The default of 0 means unlimited, which is rarely what you want. With an unlimited cap, a sudden burst of slow queries can open hundreds of connections, blow past the database's own connection limit, and produce errors like too many connections from MySQL or FATAL: sorry, too many clients already from Postgres.

The following program sets the cap to 1 and watches what happens:

Two goroutines tried to run at the same time, but only one connection was allowed. The second waited until the first finished, which is why total time is 400ms instead of 200ms. That's MaxOpenConns(1) doing exactly what it advertises.

Setting MaxOpenConns too low starves the app: any concurrency above the cap turns into wait time. Setting it too high overloads the database, which has its own per-connection memory cost. The cap should match what the database can comfortably serve.

SetMaxIdleConns

SetMaxIdleConns(n) controls how many connections the pool keeps around when nothing is actively using them. The default is 2. Anything beyond n gets closed when it's returned to the pool. The point of keeping connections idle is to avoid the cost of opening a new one on the next request: a TCP handshake, a TLS handshake for encrypted connections, an authentication exchange, and any session setup.

For a server database (Postgres, MySQL), a low idle count means most requests pay reconnection cost. For SQLite, opening a connection is cheap (it's a file open), so the idle count matters less for latency, though it still affects how many concurrent statements can be in flight.

Most production tuning advice is the same number for both: SetMaxIdleConns(n) equal to SetMaxOpenConns(n). That way, a connection opened during peak load stays around for the next peak instead of being closed and reopened seconds later. There's one constraint: if MaxIdleConns is higher than MaxOpenConns, Go silently clamps it to MaxOpenConns. The high idle setting has no effect because the pool can never have more open connections than the open cap.

The MaxOpenConnections field reflects what was set. The point of the example is that setting MaxIdleConns(10) doesn't change anything when MaxOpenConns is 3. Set both to the same number, and forget about idle being higher.

SetConnMaxLifetime

SetConnMaxLifetime(d) caps the wall-clock age of any one connection. After d since the connection was opened, the pool retires it on its next return and opens a fresh one for the next caller. The default of 0 means connections live forever, which sounds fine until you remember what sits between your app and the database in real deployments.

A typical path looks like app → cloud load balancer → database. Load balancers, NAT gateways, and stateful firewalls drop TCP connections that have been idle for too long (commonly 5 to 15 minutes). When the connection is dropped by an intermediary without telling either side, the next query on that connection returns a broken pipe or EOF error. Setting ConnMaxLifetime to something shorter than the shortest idle timeout in the path forces Go to rotate connections before the intermediaries do, which avoids the problem entirely.

A common starting value is 5 minutes. AWS RDS and similar services use 350 seconds in their published examples; 1 to 5 minutes covers the vast majority of cloud setups.

For SQLite, this knob is mostly cosmetic because there are no load balancers or firewalls in the path. The connection is just a file handle. The point still stands that a long-lived process should rotate connections periodically as a defensive measure.

ConnMaxLifetime of 0 (the default) is the most common cause of intermittent broken pipe errors in production Go services running behind a load balancer. Set it to something shorter than the smallest idle timeout in the network path, usually 5 minutes or less.

SetConnMaxIdleTime

SetConnMaxIdleTime(d) (added in Go 1.15) closes a connection that has sat idle in the pool for longer than d, even if it's still inside its lifetime budget. The default is 0, meaning idle time alone never closes a connection.

Lifetime and idle-time interact: a connection is closed when either timer fires. A connection used continuously stays open until ConnMaxLifetime elapses. A connection that's used once and then forgotten is closed after ConnMaxIdleTime. Setting both gives you the right behavior in both extremes: rotate hot connections to avoid stale-state bugs, and drop cold connections to free up database resources for other clients.

A reasonable starting pair for a server database:

A connection in active use gets rotated every 5 minutes. A connection no one's used for 1 minute gets returned to the OS instead of held in the pool. Both numbers are tunable based on the database's own timeouts and the traffic pattern.

Looking at db.Query from the pool's perspective, every call goes through the same three steps: acquire, use, release. The release step is where most bugs live.

The sequence is clear. Before the query, the pool is empty. The call to db.Query borrows a connection and ties it to the *sql.Rows value. While iterating, the connection is in-use. After rows.Close() (or after rows.Next() returns false and reaches EOF naturally), the connection is returned to the pool as idle.

The non-obvious case is db.QueryRow. It returns a *sql.Row, not *sql.Rows, and the connection is released as soon as Scan is called or the row is discarded. Either way you don't have to call Close on a Row.

Functions that return rows or open transactions all follow the same rule: something has to release the connection. For db.Query, that's rows.Close(). For db.BeginTx, that's tx.Commit() or tx.Rollback(). Miss the close and the connection stays in in-use forever, which is the most common cause of pool exhaustion in real services.

When the pool has hit MaxOpenConns and every connection is in use, the next caller blocks. It waits in an internal queue until one of two things happens: a connection is released, or the caller's context.Context is cancelled. If you used db.Query (no context), the wait is unbounded. If you used db.QueryContext with a deadline, the call returns with context.DeadlineExceeded when the deadline passes.

Using the *Context variants is critical in real code: a slow query holding a connection shouldn't be able to wedge every other goroutine in the service indefinitely.

The second query never reached the database. It sat in the pool's wait queue, and after 50ms its context expired. The error wraps context.DeadlineExceeded, which is the signal that "this didn't fail because the database said no, it failed because we ran out of pool slots".

A call with no context can wait forever on a saturated pool. Always use QueryContext, QueryRowContext, ExecContext, and BeginTx with a context that has a deadline. An appropriate deadline is short enough that the caller doesn't hang silently.

Long transactions are the classic way to exhaust a pool. A transaction holds its connection from BeginTx to Commit or Rollback, no matter how many queries it runs. A pool of 10 with 10 transactions open at once is a pool of zero for everyone else. The fix is either a larger pool (if the database can take it) or shorter transactions (almost always the better answer).

Tuning the pool is mostly about matching three numbers: how many connections the database can serve, how many app instances are sharing that budget, and how long each connection should live before being recycled. A few rules of thumb that hold up in practice.

Start with the database's per-connection limit. Postgres typically defaults to 100, MySQL to 151. Cloud providers expose this as a tier limit (Heroku Postgres hobby-dev allows 20; AWS RDS scales with instance size). That number is the total for all clients of the database, not per app.

Divide by app instances, leave headroom. With a Postgres limit of 100 connections and 4 app instances, a starting point is MaxOpenConns(20) per instance: 80 total, 20 left over for other clients (migration jobs, backup tools, admin sessions). Going higher than the database's cap divided by instances guarantees errors at peak load.

Set `MaxIdleConns` equal to `MaxOpenConns`. A different value almost never helps. With idle equal to open, a connection opened during a burst stays around for the next burst.

Set `ConnMaxLifetime` shorter than the network's idle timeout. Five minutes is a fine starting point. AWS RDS Proxy, GCP Cloud SQL, and most managed load balancers drop idle TCP connections at 5 to 15 minutes. Rotating connections under that ceiling prevents broken pipe errors.

Set `ConnMaxIdleTime` to something modest. One minute works well for variable traffic. The idea is that a connection no one's touched in a while doesn't deserve to keep sitting in the pool. For very bursty workloads, a longer idle time (5 minutes) avoids reconnect storms after each lull.

Concrete example: Postgres with a 100-connection limit, 4 app instances, behind a load balancer with a 350-second idle timeout. A reasonable per-instance config:

For SQLite, the tuning advice is different because the bottleneck isn't connection count, it's the single-writer constraint. SQLite allows many concurrent readers, but only one writer at a time. Setting MaxOpenConns(1) is actually a reasonable choice for a write-heavy SQLite app because it serializes writes in your app rather than relying on SQLite's SQLITE_BUSY retries. For a read-heavy app, a larger pool (5 to 20) is fine. The lifetime and idle-time knobs still apply but have less impact because there's no network in the middle.

The _journal=WAL query parameter switches SQLite to write-ahead logging, which is what lets readers and a writer run concurrently. Without WAL, readers and the writer block each other, which makes a small pool feel even smaller.

db.Stats() returns a sql.DBStats struct that's the only built-in way to see what the pool is doing right now. The fields worth watching:

WaitCount and WaitDuration are the two to graph in production. If WaitCount is increasing fast and WaitDuration is growing, the pool is undersized for current load. If they're flat, the pool has headroom.

The following program prints pool stats once a second while doing some work:

Five workers, three connections. Two workers had to wait, and the total wait time across both is around 400ms (two workers waiting roughly 200ms each). Those two numbers, WaitCount and WaitDuration, are exactly what to alert on in production. Rising fast means the pool is undersized for the load.

For a real service, export these as metrics. The same db.Stats() call drives Prometheus gauges, Datadog metrics, or whatever monitoring backend you use. A time.Tick(15*time.Second) loop that reads stats and reports them is a few dozen lines and pays for itself the first time someone asks "is the pool fine?".

Most pool problems trace to a small set of mistakes. Here's the short catalog:

The connection-leak case (forgetting to close *sql.Rows) is the most common and the easiest to miss because the code "works" until load goes up. From the pool's perspective, every unclosed Rows is a connection that will never come back.

The following program demonstrates the MaxIdle > MaxOpen clamp visibly:

The fix is to think of the two values together. Pick a number for MaxOpenConns, then call SetMaxIdleConns(sameNumber). If you want fewer idle than open (for example, to free idle connections after a burst), set idle lower. Setting it higher accomplishes nothing.