How does database/sql handle connection pooling?

Question

Accepted Answer

db, err := sql.Open("pgx", connStr)
if err != nil {
    log.Fatal(err)
}

// Pool configuration — tune to match Postgres max_connections
db.SetMaxOpenConns(25)                  // max concurrent connections
db.SetMaxIdleConns(10)                  // keep-alive connections in pool
db.SetConnMaxLifetime(5 * time.Minute)  // recycle connections, avoid stale
db.SetConnMaxIdleTime(1 * time.Minute)  // close idle connections after timeout

// Verify connectivity at startup
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
    log.Fatalf("cannot connect to database: %v", err)
}

// ALWAYS use context variants — propagates cancellation to DB driver
rows, err := db.QueryContext(ctx, "SELECT id, name FROM users WHERE active = $1", true)
if err != nil {
    return nil, fmt.Errorf("query users: %w", err)
}
defer rows.Close() // always close rows

var users []User
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil {
        return nil, err
    }
    users = append(users, u)
}
return users, rows.Err() // check rows.Err() after iteration MaxOpenConns: set to (Postgres max_connections / number_of_service_instances) - some buffer MaxIdleConns: should be less than MaxOpenConns; keep commonly-needed connections alive ConnMaxLifetime: prevents stale connections after DB restarts or network disruptions Always use QueryContext, ExecContext, BeginTx — never the non-context variants in production For high-performance workloads, consider pgxpool from jackc/pgx which has better pool control

How does database/sql handle connection pooling?

sql.DB is a connection pool, not a single connection. Configure pool size, idle connections, and connection lifetime to match your database's max_connections and service traffic patterns.

Scenario Questions

Follow-up Questions