Connection pooling in database/sql
You're building a web service. A user hits an endpoint, your code queries the database, and you return the result. It works fine with one user. Then you run a load test with a hundred concurrent requests. The response time spikes. The database server logs show "too many connections" errors. Your Go program isn't broken. It's opening a fresh TCP connection for every single query, and the database can't keep up.
Connection pooling solves this by keeping a set of open connections ready to use. Instead of dialing the database server for every query, your program grabs an existing connection from the pool, runs the query, and returns the connection to the pool. The database/sql package handles this automatically. The sql.Open function doesn't open a connection. It creates a pool manager. You configure limits to control how many connections exist, how many stay open when idle, and how long a connection lives before being recycled.
How the pool works
Think of the pool like a rack of rental bikes. You don't build a bike every time you want to ride. You grab one from the rack, ride it, and put it back. If the rack is full, you wait. If a bike is old, you take it to the shop for maintenance.
The *sql.DB object is the rack. It manages a collection of connections to the database. When a query runs, the pool checks for an idle connection. If one exists, it hands it over. If not, and the open limit hasn't been reached, it dials a new connection. If the limit is reached, the query blocks until a connection becomes available. Idle connections sit in the pool until they age out or the pool shrinks below the idle limit.
The pool is safe for concurrent use. Multiple goroutines can call db.Query or db.Exec simultaneously. The pool serializes access to the connection list internally. You don't need mutexes around the *sql.DB object.
Minimal setup
Here's the setup: open the pool, set limits, verify it works.
package main
import (
"database/sql"
"log"
"time"
_ "github.com/lib/pq" // driver import registers the "postgres" driver
)
func main() {
// DSN defines how to connect; pooling happens inside the driver
dsn := "user=postgres dbname=mydb sslmode=disable"
// Open returns a pool handle, not a single connection
db, err := sql.Open("postgres", dsn)
if err != nil {
log.Fatal(err)
}
// Verify the pool can reach the database immediately
if err := db.Ping(); err != nil {
log.Fatal(err)
}
// Limit total connections to prevent overwhelming the database
db.SetMaxOpenConns(25)
// Keep connections warm so the first request doesn't pay the dial cost
db.SetMaxIdleConns(25)
// Recycle connections periodically to handle network changes or server restarts
db.SetConnMaxLifetime(5 * time.Minute)
}
The function is named Open because early versions opened a single connection. The name stuck. The behavior changed. The pool is the reality. Always call Ping after Open. The Open call only parses the DSN and initializes the pool structure. No network traffic occurs until you run a query or call Ping. Skipping Ping means you might not realize the database is down until the first request fails.
Realistic usage
Here's a handler function that queries the pool.
// GetUsers fetches a list of users from the database.
// It uses the pool handle passed from the handler.
func GetUsers(db *sql.DB) ([]User, error) {
// QueryContext respects cancellation; the pool manages the connection lifecycle
rows, err := db.QueryContext(context.Background(), "SELECT id, name FROM users")
if err != nil {
return nil, err
}
// Rows must be closed to return the connection to the pool
defer rows.Close()
var users []User
for rows.Next() {
var u User
// Scan maps columns to struct fields; the pool connection is reused here
if err := rows.Scan(&u.ID, &u.Name); err != nil {
return nil, err
}
users = append(users, u)
}
// Check for errors that occurred during iteration
if err := rows.Err(); err != nil {
return nil, err
}
return users, nil
}
The most common bug is forgetting to close rows. If you call db.Query and don't close the returned *sql.Rows, the connection stays checked out. The pool thinks the connection is in use. Eventually, all connections get stuck in queries that finished long ago. New requests block forever. The compiler won't catch this. You get a silent leak. Always use defer rows.Close() immediately after checking the error.
Go makes error handling explicit. You'll see if err != nil blocks everywhere. This verbosity is a feature. It forces you to handle failures rather than hiding them behind exceptions. The rows.Err() check is another example. Scanning rows can fail midway. rows.Err() captures errors that happen during Next() or Scan(). Always check it after the loop.
Tuning the pool
The default pool settings are often wrong for production. SetMaxOpenConns(0) disables the limit. The pool opens connections without bound. A traffic spike can exhaust the database's connection limit and crash the server. Always set a positive integer.
SetMaxOpenConns is the hard cap. It should be less than the database server's max_connections setting divided by the number of application instances. If you run three instances and the database allows 100 connections, set MaxOpenConns to 30 per instance. Leave room for administrative queries and other services.
SetMaxIdleConns controls the pool size when traffic drops. If you set it lower than SetMaxOpenConns, the pool shrinks by closing idle connections. This saves resources on the database server. However, shrinking the pool means the next request might have to dial a new connection. For stable workloads, set MaxIdleConns equal to MaxOpenConns. The pool stays warm, and you avoid the dial latency on the first request after a quiet period.
SetConnMaxLifetime recycles connections based on age. Load balancers and firewalls sometimes drop idle TCP connections. If your application holds a connection that the network dropped, the next query fails. Recycling connections periodically prevents this. Five minutes is a common default.
SetConnMaxIdleTime closes a connection if it sits unused for too long. This helps when the database server drops idle connections aggressively. If your database closes connections after two minutes of idle time, set SetConnMaxIdleTime to 90 seconds. The pool closes the connection before the database does.
Transactions and context
Transactions use the pool too. db.Begin() checks out a connection. tx.Commit() or tx.Rollback() returns it. The connection stays checked out for the duration of the transaction. Long-running transactions hold connections hostage. Keep transactions short.
Context flows through the query. If the client disconnects, the context cancels, the query aborts, and the connection returns to the pool. Without context, a slow query can hold a connection indefinitely. Use QueryContext and ExecContext instead of Query and Exec. Pass the request context. The pool respects cancellation.
Goroutine leaks happen when a query holds a connection and the context never cancels. Always pass a context with a deadline or cancellation channel. The worst goroutine bug is the one that never logs. A leaked connection blocks new requests silently until the pool is exhausted.
Pitfalls and errors
The compiler rejects missing imports with undefined: sql. At runtime, a query against a closed pool panics with sql: database is closed. Close the database handle only when the application is shutting down. Call db.Close() in a graceful shutdown handler.
Transactions require careful error handling. If Begin fails, there's no transaction to roll back. If Commit fails, the transaction might have succeeded or failed on the server. Check errors at every step.
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
// Rollback if commit fails or if an error occurs during the transaction
defer tx.Rollback()
_, err = tx.ExecContext(ctx, "INSERT INTO users (name) VALUES ($1)", "Alice")
if err != nil {
return err
}
if err := tx.Commit(); err != nil {
return err
}
The defer tx.Rollback() is safe even after Commit. If Commit succeeds, Rollback is a no-op. If Commit fails, Rollback attempts to clean up. This pattern prevents connection leaks in transaction code.
Decision matrix
Use database/sql with a configured pool for almost every production application. The pool handles concurrency, recycling, and limits automatically.
Use db.Ping() at startup to verify connectivity before the application accepts traffic.
Use SetMaxOpenConns to match the database server's connection limits divided by the number of application instances.
Use SetConnMaxLifetime to handle load balancer timeouts or network interface changes.
Use context.Context in every query to allow cancellation and prevent goroutine leaks.
Avoid passing *sql.Conn directly unless you need transaction isolation or connection-specific properties. The pool handle *sql.DB is the standard interface.
The pool is a resource manager. Treat it like one.