The waiting room problem
A new web service launches. It handles user requests, queries a PostgreSQL database, and returns JSON. The first hundred requests fly through. The database responds in milliseconds. Then traffic doubles. The server starts hanging. Requests queue up. The database logs show a flood of connection attempts. The application crashes with out-of-memory errors. The problem is not the database. The problem is that every request opens a fresh TCP connection, authenticates, and tears it down when done. TCP handshakes and database authentication are expensive. Doing them per-request turns a fast system into a bottleneck.
Go solves this with a built-in connection pool. You do not need a third-party library. The standard library handles the heavy lifting. You just need to understand what the pool tracks, how to tune it, and where the defaults will bite you.
What the pool actually manages
A connection pool is a managed cache of open database sessions. Instead of creating a connection for every query and destroying it afterward, the application borrows a connection from the pool, runs the query, and returns it. The pool keeps idle connections alive so the next request can grab one immediately. It also enforces limits so the application never exhausts database resources or its own memory.
Think of it like a restaurant kitchen. The chefs are the database connections. If every customer demands a private chef, the kitchen collapses. Instead, the restaurant keeps a fixed number of chefs on duty. Customers wait in line for a free chef, get their meal, and the chef returns to the pool. The manager sets rules: maximum chefs allowed, how long a chef can sit idle before going home, and how long a customer can wait before being turned away.
Go's database/sql package implements this pattern automatically. The pool tracks three numbers: how many connections are currently open, how many are sitting idle, and how long a connection has lived. You configure these numbers with simple setter methods. The pool runs a background goroutine that cleans up stale connections and respects your limits.
The minimal setup
Here is the simplest way to initialize a pooled database client. The code sets up the pool, applies safe defaults, and verifies the connection works.
package main
import (
"database/sql"
"fmt"
"log"
"time"
)
// NewDBPool creates a configured database pool and verifies connectivity.
func NewDBPool(dsn string) (*sql.DB, error) {
// sql.Open does not open a connection. It only validates the DSN and creates the pool struct.
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, fmt.Errorf("failed to initialize pool: %w", err)
}
// Cap total connections to prevent overwhelming the database under load.
db.SetMaxOpenConns(25)
// Keep a baseline of idle connections so the first request does not pay the handshake cost.
db.SetMaxIdleConns(10)
// Recycle connections after 30 minutes to avoid stale network routes or expired credentials.
db.SetConnMaxLifetime(30 * time.Minute)
// Verify the pool can actually reach the database before handing it to the application.
if err := db.Ping(); err != nil {
return nil, fmt.Errorf("database unreachable: %w", err)
}
return db, nil
}
The sql.Open function is a misnomer. It does not dial the database. It only parses the connection string and returns a *sql.DB struct that represents the pool. The first query triggers the actual network connection. Calling Ping forces that first connection immediately, which catches configuration errors at startup rather than during the first user request.
How the pool moves data at runtime
When a query runs, the pool checks its internal state. If an idle connection exists, it hands it out instantly. If no idle connections exist but the total open count is below MaxOpenConns, the pool dials a new connection. If the limit is reached, the calling goroutine blocks until a connection returns to the pool or the context deadline expires.
After the query finishes, the connection does not close. It returns to the idle queue. The pool tracks idle time. If a connection sits longer than MaxIdleConns allows, the background cleanup goroutine closes it. The ConnMaxLifetime setting works independently. Even if a connection is actively used, the pool will refuse to hand it out once it ages past the lifetime threshold. This prevents issues like expired TLS certificates, rotated database passwords, or stale routing tables.
The pool is safe for concurrent use. Multiple goroutines can call db.Query or db.Exec simultaneously. The underlying mutex ensures that connection handouts and returns never race. You do not need to wrap database calls in your own locks. The standard library handles the synchronization.
One convention worth noting: always pass a context.Context as the first argument to query methods. The pool respects context cancellation. If a request times out, the context signals the pool to abort the waiting goroutine and return the connection to the pool instead of leaking it. Functions that accept a context should check ctx.Err() before proceeding and propagate cancellation cleanly.
Production tuning and context
Real applications need more than defaults. Traffic patterns vary. Some endpoints run heavy analytical queries. Others fire rapid micro-lookups. Tuning the pool means matching the numbers to your workload and your database limits.
Here is a production-ready initialization that adds health checks and explicit context handling.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
)
// ConfigurePool applies production-safe limits and verifies readiness.
func ConfigurePool(db *sql.DB) error {
// Match the database server's max_connections setting divided by the number of app instances.
db.SetMaxOpenConns(20)
// Idle connections stay open for 5 minutes before the pool closes them.
db.SetConnMaxIdleTime(5 * time.Minute)
// Connections older than 45 minutes are retired to avoid credential rotation surprises.
db.SetConnMaxLifetime(45 * time.Minute)
// Keep enough idle connections to cover baseline traffic without cold starts.
db.SetMaxIdleConns(5)
// Verify connectivity with a short timeout so startup fails fast if the DB is down.
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
return fmt.Errorf("startup health check failed: %w", err)
}
return nil
}
The SetConnMaxIdleTime method was added in Go 1.15. It controls how long an idle connection survives before the pool closes it. This is different from MaxIdleConns, which controls how many idle connections the pool keeps at once. Both matter. High idle limits waste memory. Low idle limits force frequent reconnections.
When you pass a context to QueryContext or ExecContext, the pool attaches the deadline to the underlying network call. If the context expires, the driver cancels the query, the connection returns to the pool, and your handler can return a timeout error to the client. This pattern keeps long-running queries from blocking the entire pool.
A quick convention aside: Go functions that perform I/O should accept context.Context as their first parameter, conventionally named ctx. The community expects this signature. It makes cancellation, tracing, and deadlines consistent across your codebase. Wrap errors with %w so callers can unwrap them later. The verbose if err != nil { return err } pattern is intentional. It keeps the failure path visible and prevents silent drops.
Where things go wrong
Connection pools fail silently until they do not. The most common mistake is relying on defaults. If you never call SetMaxOpenConns, the pool allows unlimited connections. A traffic spike will open hundreds of connections, exhaust the database's max_connections, and crash both the app and the database. The runtime will panic with too many connections or the driver will return pq: remaining connection slots are reserved for non-replication superuser connections.
Another trap is forgetting to close rows or statements. If you call db.Query and never call rows.Close(), the connection stays borrowed. The pool thinks it is in use. Eventually the pool hits its limit and blocks. The compiler will not catch this. You get a runtime goroutine leak. The worst pool bug is the one that never logs. Always defer rows.Close() immediately after querying.
Context misuse causes deadlocks. If you pass context.Background() to a long-running batch job, the pool will wait forever for a connection. If you pass a request-scoped context to a background worker, the worker dies when the HTTP request finishes. Match the context lifetime to the actual work. If a job outlives the request, create a new context with an appropriate deadline.
Driver compatibility matters too. Some older drivers ignore ConnMaxLifetime or mishandle idle timeouts. Check your driver's documentation. The database/sql interface is stable, but driver implementations vary. If you see sql: connection is already closed errors during normal queries, the driver is likely dropping connections prematurely. Upgrade the driver or adjust the idle timeout.
Choosing your connection strategy
Use the standard database/sql pool when you need a simple, concurrent-safe cache for relational databases. Use a dedicated single connection when you are running a one-off migration script or a background job that requires serial execution. Use a connection proxy like PgBouncer or ProxySQL when you run dozens of application instances and need centralized connection multiplexing. Use http.Transport pooling when you are making outbound HTTP calls and want to reuse TCP connections across requests instead of opening new ones for every API call. Use a connection pool when you have independent I/O calls that can run while others wait. Use plain sequential code when you don't need concurrency: the simplest thing that works is usually the right thing.