PostgreSQL Connection Pooling in Go: What I Actually Tune in Production

#go #postgres #backend #database

In my last post, lesson 3 was "your database is the bottleneck, not Go" — use pgx, size the pool deliberately. A few of you asked what "deliberately" actually means. Fair question.

Here's every pgxpool setting I touch in production, what it does, and the number I actually use. No theory — just the config running behind real traffic.

1. The pool is the choke point, not your code

Go will happily spin up 10,000 goroutines to serve requests. If your pool holds 20 connections, 9,980 of those goroutines are standing in line doing nothing.

Every performance problem I've debugged in a Go API eventually led here. Not the handler, not the JSON serialization — the pool. It's the narrowest pipe in your system, and the defaults are not your friend. Every setting below is about controlling that pipe.

2. MaxConns: do the math, per instance

Each Postgres connection costs the server a backend process — a few MB of memory plus CPU spent context-switching. Past a few hundred connections, Postgres spends more time managing connections than running queries. More connections is not more throughput; it's more overhead.

The formula I use, per API instance:

max_conns ≈ (2 × CPU cores) + 4

A 4-core box gets 12. Not 100. Not unlimited. 12.

Why 2×? Web queries are I/O-bound — while one connection waits on disk, another uses the CPU. Two per core keeps the CPU busy without drowning Postgres in backends. The +4 is headroom for migrations, admin queries, and that one endpoint that fans out.

The part everyone misses: multiply by instance count. Three replicas × 12 = 36 connections hitting Postgres. Five services each with a pool of 20 = 100 backends before a single query runs. I size the pool backwards, from the database's perspective: database connection budget ÷ number of clients = per-instance MaxConns.

config, err := pgxpool.ParseConfig(connStr)
if err != nil {
    log.Fatal(err)
}
// sized: (2 × 4 cores) + 4. Database budget ÷ instances.
config.MaxConns = 12

3. MinConns: idle is not free

MinConns keeps warm connections ready so a traffic burst doesn't pay connection-setup latency. Sounds great until you realize every idle connection still holds a Postgres backend process. MinConns = 10 across ten instances is 100 backends doing absolutely nothing at 3 AM.

I keep it small — 2 to 5. Enough to absorb a burst, cheap enough to forget about. pgx ramps up to MaxConns on demand anyway, and the ramp is fast. Warm is good; hoarding is not.

config.MinConns = 2

4. The two settings everyone ignores: lifetimes

Long-lived connections go stale. Load balancers silently drop idle TCP connections. DNS changes don't propagate to connections that are already open. A statement_timeout you changed on the server only applies to new connections.

config.MaxConnLifetime = time.Hour        // recycle every connection hourly
config.MaxConnIdleTime = 30 * time.Minute // reap connections idle this long

MaxConnLifetime forces rotation — no connection lives long enough to go quietly stale. MaxConnIdleTime reaps connections that are just sitting there holding a backend for no reason.

One gotcha: don't set MaxConnLifetime too aggressively. One minute on a busy pool means constant churn — TLS handshake and auth on every cycle. An hour is boring. Boring is good.

5. HealthCheckPeriod: trust, but verify

pgx validates idle connections before handing them out, but only as often as HealthCheckPeriod allows. The default is one minute, which is fine for most setups.

If your network is flaky — cloud load balancers love silently killing idle TCP — drop it to 30 seconds. It's a cheap ping. A dead connection handed to a live request is an expensive 500.

config.HealthCheckPeriod = time.Minute

6. Fail fast: the context deadline is your acquire timeout

This is the setting that saves you during an outage. When the pool is exhausted, what should the 21st request do — wait forever, or fail fast?

pgx v5 has no separate acquire-timeout knob. Connection acquisition respects the context deadline. Your context timeout is your acquire timeout. Use it:

ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
defer cancel()

var name string
err := pool.QueryRow(ctx, "SELECT name FROM users WHERE id = $1", id).Scan(&name)

Two seconds, then the request fails with a clear error instead of piling up behind a dead database. This is the difference between "the API is slow" and "the API is down for everyone, including the health check." Fail fast, return 503, let the load balancer route around you.

7. Watch the pool: `pool.Stat()`

If you're not watching the pool, you're guessing. pgx exposes everything:

s := pool.Stat()
log.Printf("pool: total=%d idle=%d acquired=%d max=%d",
    s.TotalConns(), s.IdleConns(), s.AcquiredConns(), s.MaxConns())

I export these as Prometheus gauges and alert on exactly one thing: AcquiredConns sitting at MaxConns. That means the pool is saturated — every new request is queueing. It tells me to scale instances or find the slow query holding connections hostage, long before users notice.

Two patterns I watch in dashboards:

IdleConns near zero with TotalConns at MaxConns — the pool is too small, or something is leaking connections.
AcquireCount climbing far faster than actual queries — something acquires and never releases. Nine times out of ten, it's a forgotten rows.Close().

8. Mistakes I've actually made

A pool per request. Early on I called pgxpool.New inside a handler "to be safe." Every request built a pool, every pool opened connections. The database fell over in minutes. One pool per process, created at startup, passed down. Ever.

Forgetting pool.Close(). On shutdown, in-flight queries get killed mid-flight and the database logs a wall of errors. Close the pool in your graceful shutdown path — after srv.Shutdown, before exit. (See lesson 6 of my last post.)

Prepared statements behind PgBouncer in transaction mode. pgx prepares statements by default; PgBouncer in transaction-pooling mode can't handle named prepared statements across transactions, and you'll get prepared statement already exists errors at the worst possible time. The fix is one line:

config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

Only needed if you run PgBouncer in transaction mode. If you don't know what that means, you probably don't — skip it.

9. The config I actually ship

package platform

import (
    "context"
    "log"
    "time"

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

func NewPool(ctx context.Context, connStr string) *pgxpool.Pool {
    config, err := pgxpool.ParseConfig(connStr)
    if err != nil {
        log.Fatalf("parse db config: %v", err)
    }

    config.MaxConns = 12
    config.MinConns = 2
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute
    config.HealthCheckPeriod = time.Minute

    // Only if you run PgBouncer in transaction mode:
    // config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        log.Fatalf("create db pool: %v", err)
    }

    // Fail fast at startup if the database is unreachable.
    pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
    defer cancel()
    if err := pool.Ping(pingCtx); err != nil {
        log.Fatalf("ping database: %v", err)
    }

    return pool
}

Twelve connections. Two idle minimum. Hourly rotation. And a ping at startup, so a bad connection string fails the deploy — not the first request at 2 AM.

Connection pooling is unglamorous work. Nobody demos a pool config. But it's the difference between an API that degrades gracefully and one that falls over the first time traffic spikes.

What's your MaxConns, and how did you arrive at it? I'm curious whether anyone actually measures this or just copies 20 from a tutorial.

I write about backend engineering, Go, and the unglamorous work of keeping systems alive. Follow for more.