database/sql Connection Pooling & Timeouts

*sql.DB is a pool of connections, not a single long-lived socket.

Tuning SetMaxOpenConns, idle settings, and lifetimes keeps your service from starving the database or wedging goroutines waiting for a free connection.

Pair pool limits with context.Context deadlines so abandoned HTTP requests release pool slots promptly.

Summary

SetMaxOpenConns caps simultaneous connections per process.

SetMaxIdleConns controls how many connections stay warm between bursts.

SetConnMaxLifetime and SetConnMaxIdleTime rotate connections for load balancer and credential hygiene.

QueryContext and friends honor cancellation while waiting for a connection or executing a query.

Recipe

Quick-reference recipe card - copy-paste ready.

func ConfigurePool(db *sql.DB) {
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(30 * time.Minute)
    db.SetConnMaxIdleTime(5 * time.Minute)
}
 
func GetOrder(ctx context.Context, db *sql.DB, id string) (string, error) {
    ctx, cancel := context.WithTimeout(ctx, 300*time.Millisecond)
    defer cancel()
    var status string
    err := db.QueryRowContext(ctx,
        `SELECT status FROM orders WHERE id = $1`, id,
    ).Scan(&status)
    return status, err
}

When to reach for this:

• Any production service using database/sql behind HTTP or gRPC.
• When metrics show WaitCount rising or queries outlive client disconnect.
• After scaling replicas - divide database max_connections by pod count.

Working Example

package main
 
import (
    "context"
    "database/sql"
    "fmt"
    "time"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        panic(err)
    }
    defer db.Close()
 
    db.SetMaxOpenConns(5)
    db.SetMaxIdleConns(2)
    db.SetConnMaxLifetime(time.Hour)
 
    ctx, cancel := context.WithTimeout(context.Background(), 100*time.Millisecond)
    defer cancel()
 
    var one int
    err = db.QueryRowContext(ctx, `SELECT 1`).Scan(&one)
    fmt.Println(one, err)
 
    s := db.Stats()
    fmt.Printf("waitCount=%d waitDuration=%s\n", s.WaitCount, s.WaitDuration)
}

What this demonstrates:

• Pool limits apply before the first query runs.
• QueryRowContext respects the parent deadline.
• db.Stats() exposes WaitCount and WaitDuration for saturation signals.
• SQLite in-memory still follows the same API as networked drivers.

Deep Dive

How It Works

• sql.Open creates a pool manager; connections are created lazily up to MaxOpenConns.
• When all connections are busy, new QueryContext calls block until a connection frees or ctx ends.
• Returned connections go to the idle pool if under MaxIdleConns; otherwise they close.
• ConnMaxLifetime forces replacement even if the connection is healthy, which helps behind rotating credentials or PgBouncer.

Pool Settings at a Glance

Context vs SQL Timeouts

ctx, cancel := context.WithTimeout(parent, 200*time.Millisecond)
defer cancel()
_, err := db.ExecContext(ctx, `SET statement_timeout = 150`)

• Context cancel stops waiting for pool slots and driver round trips when supported.
• Postgres statement_timeout protects when drivers finish the current packet slowly.
• Layer both: context for request budget, SQL for driver-agnostic guardrails.

Go Notes

• One *sql.DB per DSN per process is typical; do not open a new pool per request.
• Pass *sql.DB from main into repositories; avoid init() pools without tests.
• Log db.Stats() fields (InUse, Idle, WaitCount) on a timer in production.

Gotchas

• Default unlimited MaxOpenConns - A traffic spike can open thousands of connections and hit Postgres too many connections. Fix: set explicit cap from capacity planning.
• Huge MaxIdleConns equal to MaxOpenConns on tiny DBs - Idle sockets waste DB slots. Fix: idle pool smaller than open cap unless latency data says otherwise.
• No Ping at startup - Bad credentials fail on first user request. Fix: PingContext with boot timeout in main.
• Child timeout longer than handler - Query runs after client left. Fix: derive DB timeout from r.Context() remaining deadline.
• Ignoring WaitCount metrics - Saturation looks like slow queries. Fix: alert on wait duration growth before p99 query time spikes.
• New pool per integration test without Close - CI exhausts ephemeral ports. Fix: one pool per test package or shared test container with teardown.

Alternatives

FAQs

Related

• Databases Basics - Open, Query, Scan fundamentals
• Data Access in Go: database/sql as the Foundation - architectural anchor
• Transactions, Prepared Statements & sql.Tx - short transactions
• context in database/sql & gRPC - cancellation wiring
• Databases & Data Access Best Practices - pool and timeout checklist

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).

WTI5a1pXZDFhV1JsY3k1cGIzeGpaMmx2TkRFMmZESXdNall3Tnc9PQ==