Go — database/sql Pool Exhaustion from Unclosed Rows

Diagnose sql: connection pool exhausted errors in Go, find rows.Next loops without defer rows.Close, and restore pooling with deterministic resource handling.

intermediatedebugginggodatabaseresource-leak

Symptoms

sql: connection pool exhausted after the service runs for a while, with queries that once worked now timing out
Database-side idle connections pile up in "idle in transaction" or hold snapshots longer than expected
Goroutine dumps show many goroutines parked in database/sql.(*DB).conn waiting for a free connection

Possible causes

A rows iterator is not closed on every path, holding its connection out of the pool indefinitely
A helper returns rows to callers, scattering the Close obligation across dozens of call sites
An early return or error branch inside the rows.Next loop skips the deferred close
Long transactions hold connections while doing slow work, shrinking effective pool capacity

Diagnosis steps

Check pool and database state

db.Stats(); SQL: SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Interpretation

WaitCount and WaitDuration in db.Stats quantify queueing; idle-in-transaction sessions on the server mirror rows or transactions held open in the app.

Audit rows lifecycle in code

grep -Rn -A4 "db.Query(\|tx.Query(" src/ | grep -v "rows.Close" | head -20

Interpretation

Query call sites without a nearby defer rows.Close are the leaks; helpers returning *sql.Rows multiply them.

Watch the pool drain live

watch -n5 "curl -s localhost:6060/debug/vars | grep -A5 sqlpool"

Interpretation

OpenConnections climbing to MaxOpenConnections and staying there while WaitCount grows is the exhaustion signature.

Root cause

Each *sql.Rows holds a dedicated connection until Close. Unclosed rows therefore permanently remove connections from the pool; under steady traffic the pool reaches MaxOpenConnections, and every subsequent query queues until Context cancellation or timeout.

Fix

Close rows deterministically with defer rows.Close() immediately after every Query, refactor helpers to fully consume rows and return values instead of *sql.Rows, and set pool limits (MaxOpenConnections, MaxIdleConns, ConnMaxLifetime) deliberately rather than by default.

Risk note

Raising MaxOpenConnections without fixing the leak shifts exhaustion to the database global limit, which is shared with other services.

Verify the fix

Drive a fixed query workload and assert OpenConnections returns to MaxIdleConns after completion and WaitCount stays zero.

Symptom walkthrough

The error arrives from the pool, not the database:

sql: connection pool exhausted

Two telemetry sources make it concrete. On the app side, db.Stats():

stats := db.Stats()
log.Printf("open=%d idle=%d waitCount=%d waitDuration=%s",
    stats.OpenConnections, stats.Idle, stats.WaitCount, stats.WaitDuration)
// open=50 idle=0 waitCount=1421 waitDuration=14m

open pinned at the max with idle at zero and waiters accumulating means connections leave and never return. On the database side, sessions linger idle in transaction, mirroring the open rows iterators. The goroutine dump completes the picture: stacks parked in database/sql.(*DB).conn.

Fix in practice

The canonical loop, with close on every path:

rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
    return err
}
defer rows.Close() // releases the connection on every exit path

for rows.Next() {
    if err := rows.Scan(&id, &name); err != nil {
        return err // deferred close still runs
    }
    // process
}
return rows.Err()

Refactor helpers that return rows — the obligation must live in one place:

// Consume the rows here; callers receive values, not resources.
func ListUsers(ctx context.Context) ([]User, error) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var out []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name); err != nil {
            return nil, err
        }
        out = append(out, u)
    }
    return out, rows.Err()
}

And size the pool as a deliberate decision: SetMaxOpenConns matched to database capacity, SetConnMaxLifetime to survive load balancer and firewall idle cutoffs.