Connection Pooling in Go

Connection Pooling in Go

How database/sql Pooling Works

Go's database/sql package manages a pool of connections internally. When you call db.QueryContext() or db.ExecContext(), the pool:

1. Checks for an available idle connection
2. If none available and under MaxOpenConns, creates a new connection
3. If at MaxOpenConns, blocks until a connection is returned to the pool
4. After the query completes, returns the connection to the idle pool
5. If the idle pool is full (MaxIdleConns), closes the connection instead

This means sql.Open() does not actually open a connection -- it only validates the DSN and prepares the pool. The first real connection happens on the first query or Ping().

Pool Lifecycle

Request arrives
    |
    v
Pool has idle conn? --yes--> Use it --> Return to idle pool
    |                                        |
    no                                  Idle pool full?
    |                                   /          \
    v                                 yes           no
Under MaxOpenConns?                Close conn    Keep idle
    |          |
   yes         no
    |          |
    v          v
Open new     Block until
connection   one is returned

Sizing Guidelines

Formula

MaxOpenConns = (DB max_connections - reserved_connections) / app_instances

Where:

• max_connections is the database server's maximum connection limit (check with SHOW max_connections in PostgreSQL)
• reserved_connections are connections reserved for superuser access, replication, monitoring, and migrations (typically 10-20)
• app_instances is the number of running application replicas

Workload-Based Sizing

Important Considerations

• More connections does not mean more throughput. PostgreSQL performance degrades significantly above ~100 active connections due to lock contention and context switching.
• If your app needs more than ~50 connections per instance, consider using PgBouncer or a similar connection pooler between your app and the database.
• Monitor actual connection usage before tuning. Use db.Stats() to check pool utilization.

pgx Native Pooling

For PostgreSQL-only applications, use pgxpool.Pool instead of database/sql. It provides better performance, native PostgreSQL protocol support, and additional features like health checks.

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func NewPool(ctx context.Context) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
        return nil, fmt.Errorf("parsing db config: %w", err)
    }

    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = 5 * time.Minute
    config.MaxConnIdleTime = 1 * time.Minute
    config.HealthCheckPeriod = 30 * time.Second

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, fmt.Errorf("creating pool: %w", err)
    }

    return pool, nil
}

pgxpool vs database/sql

pgx with database/sql Compatibility

If you need database/sql compatibility (for libraries that require it) but still want pgx as the driver:

import (
    "database/sql"

    _ "github.com/jackc/pgx/v5/stdlib"
)

func NewDB(connStr string) (*sql.DB, error) {
    db, err := sql.Open("pgx", connStr)
    if err != nil {
        return nil, fmt.Errorf("opening db: %w", err)
    }

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(1 * time.Minute)

    return db, nil
}

Health Checks and Connection Validation

pgxpool Health Checks

pgxpool performs automatic health checks on idle connections at the interval set by HealthCheckPeriod. This detects broken connections (network failures, database restarts) before they are used for a real query.

config.HealthCheckPeriod = 30 * time.Second

If a health check fails, the connection is removed from the pool and a new one is created on demand.

Manual Health Check Endpoint

Expose a health check endpoint that verifies database connectivity:

func (s *Server) handleHealth(w http.ResponseWriter, r *http.Request) {
    ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
    defer cancel()

    if err := s.db.PingContext(ctx); err != nil {
        http.Error(w, "database unreachable", http.StatusServiceUnavailable)
        return
    }

    w.WriteHeader(http.StatusOK)
    w.Write([]byte("ok"))
}

Use a short timeout (1-2 seconds) for health check pings. If the database does not respond within that window, the instance should be marked unhealthy.

Monitoring Pool Metrics

database/sql Stats

func (s *Server) handleDBStats(w http.ResponseWriter, r *http.Request) {
    stats := s.db.Stats()

    fmt.Fprintf(w, "Open connections: %d\n", stats.OpenConnections)
    fmt.Fprintf(w, "In use: %d\n", stats.InUse)
    fmt.Fprintf(w, "Idle: %d\n", stats.Idle)
    fmt.Fprintf(w, "Wait count: %d\n", stats.WaitCount)
    fmt.Fprintf(w, "Wait duration: %s\n", stats.WaitDuration)
    fmt.Fprintf(w, "Max idle closed: %d\n", stats.MaxIdleClosed)
    fmt.Fprintf(w, "Max lifetime closed: %d\n", stats.MaxLifetimeClosed)
}

Key Metrics to Watch

If WaitCount is steadily increasing, your application is running out of connections. Either increase MaxOpenConns (if the database can handle it) or reduce query duration.

Prometheus Integration

import "github.com/prometheus/client_golang/prometheus"

func registerDBMetrics(db *sql.DB) {
    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_open_connections",
            Help: "Number of open database connections",
        },
        func() float64 { return float64(db.Stats().OpenConnections) },
    ))

    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_in_use_connections",
            Help: "Number of in-use database connections",
        },
        func() float64 { return float64(db.Stats().InUse) },
    ))

    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_idle_connections",
            Help: "Number of idle database connections",
        },
        func() float64 { return float64(db.Stats().Idle) },
    ))

    prometheus.MustRegister(prometheus.NewCounterFunc(
        prometheus.CounterOpts{
            Name: "db_wait_count_total",
            Help: "Total number of connections waited for",
        },
        func() float64 { return float64(db.Stats().WaitCount) },
    ))
}

pgxpool Stats

func logPoolStats(pool *pgxpool.Pool) {
    stat := pool.Stat()

    slog.Info("pool stats",
        "total_conns", stat.TotalConns(),
        "acquired_conns", stat.AcquiredConns(),
        "idle_conns", stat.IdleConns(),
        "constructing_conns", stat.ConstructingConns(),
        "max_conns", stat.MaxConns(),
        "new_conns_count", stat.NewConnsCount(),
        "max_lifetime_destroy_count", stat.MaxLifetimeDestroyCount(),
        "max_idle_destroy_count", stat.MaxIdleDestroyCount(),
    )
}

Cloud Database Considerations

Connection Limits by Provider

PgBouncer

When using PgBouncer (common in managed PostgreSQL services like Supabase), adjust your application settings:

// With PgBouncer in transaction mode
db.SetMaxOpenConns(50)  // Can be higher -- PgBouncer multiplexes
db.SetMaxIdleConns(5)   // Keep low -- PgBouncer handles idle
db.SetConnMaxLifetime(0) // Disable -- PgBouncer manages lifetime

Important PgBouncer considerations:

• Transaction pooling mode (most common): connections are assigned per transaction, not per session. Prepared statements do not work across transactions.
• Session pooling mode: connections are assigned per session. Prepared statements work normally but you get less multiplexing benefit.
• If using pgx with PgBouncer in transaction mode, disable prepared statements:

config, _ := pgxpool.ParseConfig(connStr)
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

DNS-Based Failover

Cloud databases often use DNS to point to the current primary. Set ConnMaxLifetime to a short value so your application picks up DNS changes after failover:

db.SetConnMaxLifetime(1 * time.Minute) // Short lifetime for fast failover

Without this, long-lived connections may keep pointing to the old primary after a failover event, causing errors.