database/sql Pool and Context

Updated

September 8, 2026

database/sql Pool and Context

Overview

database/sql is a generic pool over driver connections. Most production bugs are pool limits, missing context, or holding *sql.Rows open—not SQL syntax.

Diagram: pool

  db.QueryContext
       │
       v
  sql.DB pool
     ├── idle conn
     └── open new (≤ MaxOpen)
       │
       v
  Rows / Tx  ──Close/Commit──► return to pool

Essential settings

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(time.Hour)
db.SetConnMaxIdleTime(10 * time.Minute)

Knob	If wrong
MaxOpen too low	queueing latency
MaxOpen too high	DB overload
MaxIdle 0	reconnect thrash
No context	hung queries pin conns

Context everywhere

ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, q, args...)

Cancel should abort driver ops (driver-dependent quality).

Rows and transactions

defer rows.Close()   // always
tx.Commit / Rollback // always pair

Leaking rows = leaking conns = pool exhaustion under load.

pgx note

Many services prefer jackc/pgx pool directly; patterns still mirror: max conns, timeouts, close. See web db chapter.

Experiment

// conceptual: open sqlite or pg, set MaxOpenConns(1),
// run two concurrent QueryContext with sleep in SQL,
// observe second waits for conn

What to notice: Pool size becomes a concurrency throttle.

Try next: Add db.Stats() logging of InUse, Idle, WaitCount in a service.