Go’s database/sql package manages a connection pool for you. The *sql.DB returned by sql.Open is a long-lived, concurrency-safe handle to that pool, not a single database connection.

The defaults are deliberately general. Once a service is under real load, it helps to understand what each setting controls and which metrics show whether the pool is helping or getting in the way.

The two pools: open and idle

sql.DB tracks two categories of connections:

Open connections are all connections currently in use or sitting idle. This is the total count. Idle connections are open connections not assigned to any query or transaction. They sit in a free list, waiting to be reused.

When you call db.QueryContext or db.ExecContext, the pool first tries to reuse an idle connection. If none is available and the open-connection limit has not been reached, it can open another one. Once the limit is reached, the call waits for a connection to be returned or for its context to be cancelled.

This distinction matters. SetMaxIdleConns controls how many connections survive between bursts of traffic. SetMaxOpenConns caps the total number of connections your application holds against the database.

The four knobs

package main

import (
	"database/sql"
	"time"

	_ "github.com/lib/pq"
)

func main() {
	db, err := sql.Open("postgres", "postgres://user:pass@localhost/mydb?sslmode=disable")
	if err != nil {
		panic(err)
	}

	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(10)
	db.SetConnMaxLifetime(30 * time.Minute)
	db.SetConnMaxIdleTime(5 * time.Minute)
}

Here’s what each one does:

SetMaxOpenConns(n int) caps total open connections (active + idle). The default is 0, meaning no package-level limit. A deliberate limit is useful when the database has a fixed connection budget, but setting it too low can turn the pool into a bottleneck or even contribute to a deadlock.

SetMaxIdleConns(n int) controls how many idle connections are retained for reuse. The current default is 2. Raising it can reduce reconnect churn in a service with significant parallelism; lowering it gives unused connections back to the database sooner.

SetConnMaxLifetime(d time.Duration) limits how long a connection may be reused. Expired connections may be closed lazily before reuse. Rotation can be useful when a database proxy or load balancer imposes its own connection lifetime. A value of zero disables age-based closure.

SetConnMaxIdleTime(d time.Duration) limits how long a connection may remain idle. It is useful for shedding connections after a traffic burst. With the default value of zero, connections are not closed because of idle age, though they can still be closed for other reasons.

What happens when the pool is full

When all allowed connections are in use, the next database call waits. It resumes when a connection becomes available or returns early if its context is cancelled.

That makes cancellation and sensible deadlines important:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	_ "github.com/lib/pq"
)

func getUser(ctx context.Context, db *sql.DB, id int) (string, error) {
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	var name string
	err := db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = $1", id).Scan(&name)
	if err != nil {
		return "", fmt.Errorf("query user %d: %w", id, err)
	}
	return name, nil
}

If the pool is still exhausted after three seconds, this call returns an error instead of waiting indefinitely. A request-scoped context may already carry a suitable deadline; otherwise, add one at the boundary where you know how long the operation is allowed to take.

For more on why context should be threaded through your call stack, see Context Should Probably be the First Argument of your Go Functions.

Transaction ownership

When you call db.BeginTx, the pool assigns one connection to that transaction. That connection is reserved until the transaction commits or rolls back. If the context passed to BeginTx is cancelled, database/sql rolls the transaction back automatically.

Long-running transactions therefore reduce the capacity available to other work. If MaxOpenConns is 25 and 20 goroutines are inside transactions, at most five connections remain for other operations.

Always defer your rollback to avoid leaking connections on error paths:

func transferFunds(ctx context.Context, db *sql.DB, from, to int, amount float64) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // no-op if Commit succeeds

	_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from)
	if err != nil {
		return err
	}

	_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to)
	if err != nil {
		return err
	}

	return tx.Commit()
}

If an error path returns without Commit or Rollback, the transaction can keep its connection checked out. There is no garbage-collector guarantee that repairs an abandoned Tx, so make cleanup explicit. The deferred rollback is safe: after a successful commit it returns sql.ErrTxDone and has no effect.

Pool statistics

sql.DB exposes a Stats() method that returns a sql.DBStats struct. This is your window into what the pool is doing:

• OpenConnections — current total open connections
• InUse — connections currently checked out
• Idle — connections sitting in the free list
• WaitCount — total number of times a caller had to wait for a connection
• WaitDuration — total time spent waiting across all callers
• MaxIdleClosed — connections closed because MaxIdleConns was exceeded
• MaxLifetimeClosed — connections closed because ConnMaxLifetime expired
• MaxIdleTimeClosed — connections closed because ConnMaxIdleTime expired

Export these as metrics. The first three are gauges; the rest are cumulative counters, so graph their rates or deltas. Rising wait time means callers are contending for connections, but it does not identify the cause by itself: the limit may be too low, queries may be slow, or transactions may be holding connections for too long. If you’re using Prometheus with Go, these values map naturally to gauges and counters.

Production tuning guidelines

There is no universal configuration. Start from the database’s connection budget, then tune from measurements.

Budget MaxOpenConns across every replica. If a database permits 100 connections and four application replicas share it, do not give all four a limit of 100. Reserve capacity for migrations, administration, failover overlap, and other services.

Size MaxIdleConns for normal concurrency. Too few idle connections can cause reconnect churn; too many keep database resources allocated during quiet periods. Compare connection-open rates and steady-state InUse before changing it.

Coordinate ConnMaxLifetime with your infrastructure. If a proxy, load balancer, or database enforces a connection lifetime, choose a shorter application lifetime and add jitter outside database/sql if synchronized reconnects would be a problem. Otherwise, do not add a lifetime merely because a generic example uses one.

Use ConnMaxIdleTime to shrink after bursts. Choose a duration that reflects the service’s traffic pattern and the cost of reconnecting.

Propagate cancellation and use deadlines where appropriate. This bounds both time spent waiting for the pool and time spent executing through drivers that support cancellation.

Monitor db.Stats(). Look at wait rates and latency alongside query duration, transaction duration, database saturation, and connection-open rates. Pool metrics make more sense in that context.

Common pitfalls

Ignoring the connection budget. The default MaxOpenConns value is unlimited. Under enough concurrency, one process can consume more connections than the database or the rest of the system can afford.

Setting MaxIdleConns higher than MaxOpenConns. The pool silently reduces idle to match open. It won’t error, but it means you’ve misconfigured something.

Leaving sql.Rows unfinished. Rows closes automatically when Next reaches the end and there are no more result sets. If you stop iterating early, call rows.Close()—usually with defer immediately after checking the query error—and always check rows.Err() after the loop.

Assuming sql.Open proves connectivity. sql.Open may only validate its arguments; it usually does not establish a connection, and DSN validation depends on the driver. Use db.PingContext(ctx) when startup needs to verify that the database is reachable.

Treat the pool as a shared resource with a budget, not as a bag of magic numbers. Return connections promptly, measure contention, and change one setting at a time. The right values come from the workload and the database behind it.