Connection pools prevent your database from melting
You are running a Go service. Traffic is low, queries return in milliseconds, and everything feels snappy. Then a marketing campaign goes live. Requests flood in. Your database starts rejecting connections with "too many clients" errors. Your app hangs. Every request waits for a connection that never comes. You didn't write bad SQL. You just forgot to tell Go how many connections to keep open.
Without a connection pool, your application opens a new TCP connection for every query and closes it immediately. TCP handshakes are expensive. Database authentication is expensive. Doing this thousands of times per second burns CPU, fills up file descriptors, and overwhelms the database server. A connection pool solves this by keeping a set of connections alive and handing them out to requests as needed.
The pool is a cache, not a factory
A connection pool is a cache of open database connections. Instead of manufacturing a new connection for every query, you keep a handful of connections ready. A request grabs a connection, runs its query, and returns the connection to the pool. If the pool is full, the request waits until a connection becomes available. If the pool is empty, the pool opens a new connection, up to a configured limit.
Think of it like a library of rental bikes. You don't manufacture a new bike every time someone wants to ride. You keep a rack of bikes ready. A rider grabs one, rides, and returns it to the rack. If the rack is full, the rider waits. If the rack is empty, you might build a new one, but only up to the number of parking spots you have. The pool manages the rack. Your code just asks for a bike.
Minimal setup
The standard library package database/sql includes a connection pool built in. You configure the pool using methods on the *sql.DB handle. The handle is safe for concurrent use by multiple goroutines. You create one handle and share it across your application.
Here's the standard way to initialize a pool with database/sql.
package main
import (
"database/sql"
"time"
)
// NewDB creates a database handle with pool configuration.
// It validates the DSN and verifies the database is reachable.
func NewDB(dsn string) (*sql.DB, error) {
// sql.Open validates the DSN but does not open a connection.
// It returns a handle that manages a pool of connections.
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, err
}
// SetMaxOpenConns limits the total number of connections to the database.
// This protects the database from being overwhelmed by your application.
db.SetMaxOpenConns(25)
// SetMaxIdleConns sets the maximum number of connections in the idle pool.
// Connections in the idle pool are kept open even when not in use.
db.SetMaxIdleConns(10)
// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
// Old connections are closed and replaced to handle network changes.
db.SetConnMaxLifetime(5 * time.Minute)
// Ping verifies the connection pool is actually reachable.
// Without this, you might not know the database is down until the first query.
if err := db.Ping(); err != nil {
return nil, err
}
return db, nil
}
sql.Open is deceptive. It doesn't open a connection. It creates a *sql.DB object that holds the configuration and a pool of connections. The pool starts empty. The call returns immediately unless the DSN is invalid. You must call db.Ping() to verify the database is reachable. If you skip the ping, your app might start successfully and then fail on the first request when the database is down.
Convention aside: gofmt formats this code automatically. Run gofmt -w main.go to apply standard formatting. Most editors run gofmt on save. Don't argue about indentation; let the tool decide.
sql.Open builds the pool manager, not the connection. Always Ping to verify reachability.
How the pool behaves
When you call db.Query or db.Exec, the pool checks for an idle connection. If one exists, it hands it out. If not, and MaxOpenConns hasn't been reached, it opens a new one. If the limit is hit, the caller blocks until a connection becomes available. The pool tracks active connections, idle connections, and wait counts.
SetMaxOpenConns controls the upper bound. If you set this to 25, the pool will never open more than 25 connections to the database. If 26 goroutines try to query simultaneously, the 26th goroutine blocks. This protects the database from connection storms. The default is 0, which means unlimited. Never leave it at 0 in production.
SetMaxIdleConns controls the lower bound. If you set this to 10, the pool keeps at least 10 connections open even when no queries are running. This reduces latency for the next request because it doesn't need to open a new connection. The default is 2. If your app has background workers or expects burst traffic, increase this value.
SetConnMaxLifetime controls connection age. If you set this to 5 minutes, connections older than 5 minutes are closed and replaced. This handles network changes, load balancer resets, and credential rotation. The default is 0, which means connections live forever. Forever is usually too long. Set this to a value slightly less than your infrastructure's timeout.
Convention aside: if err != nil { return err } is verbose by design. The community accepts the boilerplate because it makes the unhappy path visible. Don't hide errors. Return them or wrap them with fmt.Errorf("context: %w", err).
Realistic usage with context
Database queries can take time. You need a way to cancel a query if the client disconnects or a timeout expires. Go uses context.Context for this. Pass the context to the query method. The driver cancels the query when the context is done.
Here's a handler that fetches a user using context and proper error handling.
package main
import (
"context"
"database/sql"
"fmt"
)
// User represents a row in the users table.
type User struct {
ID int
Name string
}
// GetUser retrieves a user by ID using the provided context.
// The context controls the lifetime of the database query.
func GetUser(ctx context.Context, db *sql.DB, id int) (User, error) {
var u User
// QueryRowContext passes the context to the driver.
// If the context is cancelled, the query is aborted.
err := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id).
Scan(&u.ID, &u.Name)
// Handle specific database errors.
// sql.ErrNoRows indicates the query returned no results.
if err == sql.ErrNoRows {
return User{}, fmt.Errorf("user %d not found", id)
}
if err != nil {
return User{}, fmt.Errorf("query user: %w", err)
}
return u, nil
}
The context.Context parameter always goes first. The convention is to name it ctx. Functions that take a context should respect cancellation and deadlines. If the context expires, the query stops, and the connection returns to the pool immediately. This prevents hanging goroutines from consuming connections.
Convention aside: Public names start with a capital letter. Private names start lowercase. GetUser is public because it's exported. u is private because it's a local variable. No keywords like public or private. Capitalization controls visibility.
Context is plumbing. Run it through every long-lived call site.
Tuning and monitoring
A connection pool needs tuning. The right values depend on your database, your traffic, and your infrastructure. Use db.Stats() to inspect the pool at runtime. The Stats struct contains OpenConnections, InUse, Idle, WaitCount, and WaitDuration.
WaitCount is the most important metric. It counts how many times a goroutine had to wait for a connection. If WaitCount is rising, you need more connections or faster queries. If WaitCount is zero, you might be over-provisioned.
Tune SetMaxOpenConns based on your database's max_connections. If your database allows 100 connections and you have 4 replicas, don't set MaxOpen to 100 per replica. You'll hit 400 connections and crash the database. Set MaxOpen to 20 per replica. Leave room for administrative connections and other services.
Tune SetMaxIdleConns based on your baseline concurrency. If you have 5 background workers that run continuously, set MaxIdle to 5. This ensures the workers always have a connection ready without paying the connection cost. If your app is purely request-driven with no background work, set MaxIdle to match your expected concurrent requests during quiet periods.
Tune SetConnMaxLifetime based on your infrastructure timeouts. If your load balancer resets connections after 3 minutes, set the lifetime to 2 minutes. If your database rotates credentials every hour, set the lifetime to 55 minutes. The goal is to close connections before the infrastructure kills them.
Convention aside: The receiver name is usually one or two letters matching the type. If you define a method on a custom wrapper, use (p *Pool) Stats(), not (this *Pool) or (self *Pool). Keep receiver names short and consistent.
Tune the pool to match your infrastructure, not your hopes.
Pitfalls and errors
Connection pools introduce subtle bugs. The most common is leaking connections. If you call db.Query and forget to close the *sql.Rows, the connection stays in use and never returns to the pool. The pool fills up, and your app deadlocks. The compiler won't catch this. You get a runtime hang.
Always close rows and statements. Use defer rows.Close() immediately after checking the error from Query. If the query fails, rows might be nil, so check for nil before deferring, or use a helper function that handles the nil case.
If you pass a *sql.DB to a function expecting a driver interface, the compiler rejects it with cannot use db (variable of type *sql.DB) as Driver value in argument. *sql.DB is a pool manager, not a driver. Drivers implement driver.Driver. The pool manages the drivers.
If you set SetMaxIdleConns(0), idle connections close immediately. This causes latency spikes on burst traffic because new connections must be established. Only use 0 if you want to minimize database load at the cost of latency.
If you forget to import a package and you get undefined: pkg from the compiler. Forget to use one and you get imported and not used. Go enforces clean imports. Remove unused imports to keep the codebase tidy.
The worst goroutine bug is the one that never logs. A leaked connection looks like a silent deadlock.
When to use what
Use database/sql with SetMaxOpenConns when you need a standard, concurrent-safe pool for relational databases.
Use a connection pool library like pgxpool when you need advanced features like row description or prepared statement caching that the standard library doesn't provide.
Use a single connection when you are running a migration script or a one-off tool that doesn't need concurrency.
Use SetMaxIdleConns equal to SetMaxOpenConns when you want to avoid the latency of opening new connections and your database can handle the load.
Use SetConnMaxLifetime when your infrastructure rotates credentials or changes network routes periodically.
Use db.Stats() to monitor WaitCount and adjust pool size based on actual traffic patterns.
Use db.Close() during graceful shutdown to release all connections and wait for active queries to finish.