The Connection Pool

Every statement needs a connection to the database. Building one is slow: a TCP connection, often TLS, then a login. So *sql.DB keeps the connections it has built and hands them out again. That store is the connection pool.

By default it has no size limit. Under load it opens a connection for every goroutine that asks. A database accepts only so many: Postgres refuses anything past max_connections. Four replicas of an unlimited service can lock every other client out, including your own migrations. Four settings prevent that, and they are always set together.

The settings

• SetMaxOpenConns(n) caps connections in use and idle together. Past the cap, a query waits for one to come free, and it waits until its context is cancelled. The default is 0, meaning no limit.
• SetMaxIdleConns(n) is how many free connections are kept rather than closed. The default is 2. If it is below the open limit, a busy service spends its time closing and reopening connections. Set it equal to the open limit. The package also clamps it down to the open limit for you.
• SetConnMaxLifetime(d) retires a connection once it has lived that long, which lets a database restart or a failover drain away. The default is 0, meaning connections are never retired by age.
• SetConnMaxIdleTime(d), added in Go 1.15, closes a connection that has sat unused that long, so a quiet service shrinks back.

Picking numbers

Add the open limit up across every replica and keep the total below the server's own limit, leaving room for migrations and a human with a psql session. Then read db.Stats(): rising WaitCount and WaitDuration mean the cap is too low, and errors about too many connections mean it is too high.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Set all four together, right after Open, before any query runs.
	db.SetMaxOpenConns(10)                  // at most 10 connections exist
	db.SetMaxIdleConns(10)                  // keep all 10 instead of closing them
	db.SetConnMaxLifetime(30 * time.Minute) // retire a connection after 30 minutes
	db.SetConnMaxIdleTime(5 * time.Minute)  // close one unused for 5 minutes

	var n int
	if err := db.QueryRow("SELECT 1").Scan(&n); err != nil {
		log.Fatal(err)
	}

	// Stats is what you export to monitoring.
	s := db.Stats()
	fmt.Println("result:", n)
	fmt.Println("limit:", s.MaxOpenConnections)
	fmt.Println("in use:", s.InUse) // the query finished, so the connection went back
	fmt.Println("waited for a connection:", s.WaitCount, "times")
}

// Output:
// result: 1
// limit: 10
// in use: 0
// waited for a connection: 0 times

package storage

import (
	"database/sql"
	"time"
)

// Tune applies pool limits. maxConns is the number this replica may hold, so
// maxConns multiplied by the replica count must stay under the database limit.
func Tune(db *sql.DB, maxConns int) {
	db.SetMaxOpenConns(maxConns)
	db.SetMaxIdleConns(maxConns) // idle matches open: never rebuild a connection we just had
	db.SetConnMaxLifetime(30 * time.Minute)
	db.SetConnMaxIdleTime(5 * time.Minute)
}

package storage

import (
	"database/sql"
	"log/slog"
)

// LogStats writes one line describing the pool. Call it on a timer, or wire the
// same fields into whatever metrics system you use.
func LogStats(db *sql.DB, log *slog.Logger) {
	s := db.Stats()
	log.Info("db pool",
		"open", s.OpenConnections, // in use plus idle
		"in_use", s.InUse,
		"idle", s.Idle,
		"wait_count", s.WaitCount, // queries that queued for a connection
		"wait_total", s.WaitDuration, // how long they queued in total
		"closed_max_lifetime", s.MaxLifetimeClosed,
	)
}

package storage

import (
	"context"
	"database/sql"
	"time"
)

func CountUsers(ctx context.Context, db *sql.DB) (int, error) {
	// The pool is full: this wait is part of the 2 seconds, not on top of it.
	ctx, cancel := context.WithTimeout(ctx, 2*time.Second)
	defer cancel()

	var n int
	err := db.QueryRowContext(ctx, "SELECT count(*) FROM users").Scan(&n)
	return n, err
}