Every Go service that talks to PostgreSQL ends up managing a pool of database connections, and almost every team gets it wrong the first time. The failure modes are sneaky: the service works fine at 50 requests per second, then falls over at 500 with a flood of “context deadline exceeded” errors. Or worse — PostgreSQL starts rejecting new connections because the app opened several thousand of them, and now every other service sharing that database is suffering too.
The confusing part is that opening a TCP connection to Postgres and running SELECT 1 takes maybe 20 milliseconds. How can something so cheap become a production incident? This post walks through what actually happens on both sides of a pooled connection, how to configure pgxpool deliberately instead of by guesswork, and where a proxy like PgBouncer fits — plus the operational details (lifetimes, health checks, statistics) that turn an out-age from a mystery into a five-minute diagnosis.
Why connections are expensive even when they look cheap
A PostgreSQL connection is not just a socket. When a client connects, the server forks (or allocates) a backend process that stays attached for the life of the connection. That backend holds memory for work contexts, catalogs caches, and prepared statement state. Authentication, SSL negotiation, and backend startup add latency on every new connection. Under load, a burst of connection storms can spend more server CPU on connecting than on answering queries.
The server side also has hard limits. The max_connections setting (commonly 100 by default) caps how many backends can exist at once, and each backend costs real memory. When an application “opens a connection per request” without pooling, a traffic spike translates directly into connection exhaustion — and because max_connections is shared across every client of that database, one misbehaving service can take down its neighbors.
Pooling fixes this by keeping a bounded set of connections open and lending them out. The subtlety is in that word bounded. A pool that is too small serializes your queries; a pool that is too big pushes contention into PostgreSQL itself, where every active query competes for CPU, I/O, and locks. Choosing the bound is the actual engineering decision.
The math that actually matters: utilization, not folklore
You’ll find a lot of folklore about pool sizing (“10 per core!”). The more useful framing comes from queueing basics: an actively running query occupies one connection, so the pool needs to cover the number of queries you expect to run simultaneously — not your request rate. A service handling 2,000 requests per second where each request issues one 5 ms query needs only about 10 concurrently-busy connections on average. What kills pools is tail behavior: a slow query (say, one stuck on a lock for 3 seconds) causes in-flight requests to pile up, each holding its connection longer, until every connection is busy and new acquirers queue.
That leads to a few practical rules:
- Size the pool against your worst-case concurrent query count, dominated by your slowest queries. Fixing or bounding slow queries is often worth more than resizing the pool.
- Across a fleet, the sum of all pools must fit under the server’s
max_connectionswith headroom for admin tools, migrations jobs, and replicas routing. Ten services each configured with 100 connections is a scheduled outage. - More connections beyond CPU core count usually reduces throughput on a single Postgres node because of context-switching and lock contention. Scaling means adding replicas or sharding, not raising the number.
One more piece of arithmetic worth internalizing: max_connections is shared. If you run PgBouncer in front of PostgreSQL, the database sees PgBouncer’s server connections, not your app’s client connections — that separation is the whole point of the proxy tier.
Configuring pgxpool with intent
The pgx driver’s pool, pgxpool, exposes the knobs that matter. Its defaults are conservative — MaxConns defaults to the greater of 4 or the number of CPUs, connection lifetime is one hour, and idle connections are reaped after 30 minutes. Those defaults are sane for a small service, but a busy API needs deliberate values. Here is a configuration that makes each decision explicit:
package main
import (
"context"
"time"
"github.com/jackc/pgx/v5/pgxpool"
)
func newPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
cfg, err := pgxpool.ParseConfig(dsn)
if err != nil {
return nil, err
}
// Capacity: worst-case concurrent queries this service will issue.
cfg.MaxConns = 32
// Keep warm connections so a traffic spike doesn't pay
// connection-setup latency at the worst possible moment.
cfg.MinIdleConns = 8
cfg.MinConns = 4
// Retire connections periodically. The jitter staggers expirations
// so the whole pool doesn't rotate at the same instant.
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnLifetimeJitter = 5 * time.Minute
cfg.MaxConnIdleTime = 5 * time.Minute
cfg.HealthCheckPeriod = 30 * time.Second
return pgxpool.NewWithConfig(ctx, cfg)
}Why retire perfectly healthy connections at all? Because a long-lived connection is a liability in disguise. Failovers, DNS changes, load balancer updates, and firewall idle-timeouts all manifest as “this connection died at the worst moment.” Bounding lifetime with jitter spreads reconnections over time so you never rebuild the whole pool in one thundering herd. The jitter field exists precisely because a fleet of identical processes expiring connections simultaneously will starve the pool for the seconds it takes to re-establish them.
MinIdleConns deserves a special mention. The pgx documentation notes it outperforms MinConns for reducing tail latency: it guarantees idle, ready-to-use connections, so a request that arrives during a burst doesn’t wait for a TCP+auth handshake. If your p99 latency budget is tight, this is one of the cheapest wins available.
Read the statistics before you tune
pgxpool exposes runtime counters through pool.Stat(), and these numbers tell you whether your capacity math was right:
func logPoolStats(pool *pgxpool.Pool) {
s := pool.Stat()
// Example export as Prometheus gauges:
// pool_total_conns, pool_idle_conns, pool_acquired_conns,
// pool_acquire_duration_seconds, pool_empty_acquire_count
_ = s.TotalConns()
_ = s.IdleConns()
_ = s.AcquiredConns()
_ = s.AcquireDuration()
_ = s.EmptyAcquireCount()
}Two counters are diagnostic gold. EmptyAcquireCount counts acquisitions that had to wait because the pool was empty — anything above zero on a steady basis means your MaxConns is too low, or a slow query is hogging connections. AcquireDuration is the mean time spent waiting for a connection; watch its trend, because growth here precedes the timeout storm. If TotalConns sits pinned at MaxConns while AcquiredConns is low, your connections are stuck — typically in long transactions or un-timed-out queries.
The most common real-world pathology is a transaction that held a connection while making a slow HTTP call. Nothing in the pool can protect you from that; you need code review discipline (never hold a pgx.Tx across network I/O) and a query timeout so a stuck transaction eventually releases its connection:
func handler(ctx context.Context, pool *pgxpool.Pool) error {
// Bound the whole unit of work so the connection is always returned.
ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
defer cancel()
tx, err := pool.Begin(ctx)
if err != nil {
return err
}
defer tx.Rollback(ctx)
if _, err := tx.Exec(ctx,
"UPDATE accounts SET balance = balance - 100 WHERE id = $1", 42); err != nil {
return err
}
return tx.Commit(ctx)
}Where PgBouncer fits — and its transaction-pooling trap
Client-side pools scale per process. Run twenty replicas of your service and you have twenty independent pools; the database sees their sum. PgBouncer solves the fleet-level problem: it sits between your app and PostgreSQL, holding many cheap client connections and multiplexing them onto a smaller number of real server connections.
PgBouncer offers three pooling modes, and the choice is architectural. In session mode (the default), a client owns its server connection until it disconnects — multiplexing only happens across client lifetimes, which doesn’t help much for long-lived app connections. In transaction mode, the server connection is reassigned after every transaction commits, so a few dozen real connections can serve thousands of clients. In statement mode, connections are reassigned after every statement, which forbids multi-statement transactions entirely.
Transaction mode is where the win is — and where the trap lives. When the connection under you can change between transactions, any session state you rely on becomes a landmine:
- Prepared statements are session state. Historically this forced drivers into workaround modes; modern PgBouncer (1.21+) supports protocol-level prepared statements in transaction mode, and pgx handles this correctly, but old configurations and old proxies still bite.
SETandset_configcalls leak across clients. Settingsearch_path, timezones, or role variables on “your” connection silently affects whoever gets it next. Use fully qualified names and per-transactionSET LOCALinstead.- Advisory locks and
LISTEN/NOTIFYare tied to the session and don’t survive reassignment. - Temp tables vanish when you lose the session between transactions.
The discipline that makes transaction mode safe: assume every transaction may run on a different physical connection. Keep session state in your application, set what you need with SET LOCAL inside the transaction, and let the pool own the wire.
Failure modes worth rehearsing
- Leaked connections. Code paths that acquire without a guaranteed release (early returns, panics) bleed the pool dry. With pgxpool, prefer
pool.Exec/pool.Queryshortcuts that handle acquire/release internally, and wrap explicitAcquirein functions that release viadeferon every path. - Pool exhaustion masquerading as database outage. When acquires queue, the symptom is request latency and timeouts across all queries — which looks exactly like Postgres being down. The
EmptyAcquireCountcounter distinguishes them in seconds. - Thundering-herd reconnection. No jitter on connection lifetime plus a shared restart (deploy, network blip) means the whole pool reconnects at once. Set
MaxConnLifetimeJitter; also letMinIdleConnsrebuild capacity in the background rather than in the request path. - Connection storms after failover. Dead connections + eager retry = a burst of new connections hitting a Postgres that is still recovering. Bound retries with backoff, and keep per-process
MaxConnsmodest so the storm is bounded too. - Idle reaping mismatch. If your app’s
MaxConnIdleTimeis longer than a firewall’s idle timeout, you’ll accumulate connections that are dead but not yet noticed. Keep app-side idle timeouts shorter than any middlebox’s, and let pgxpool’s health checks ping idle connections before reuse.
A sizing walkthrough
Put it together for a concrete case. Suppose one service: p99 query time of 50 ms, peak 400 requests/second, each request doing at most 2 sequential queries. Average concurrent queries ≈ 400 × 2 × 0.05 = 40. Set MaxConns to roughly 1.5–2× that (say 64) to absorb bursts, MinIdleConns to 10 so bursts start warm. With 10 replicas, that’s 640 potential connections — over a default max_connections of 100. Now the options become concrete: either shrink per-instance pools and accept queuing in the app, or put PgBouncer in transaction mode in front of the database and let it own, say, 80 server connections for the whole fleet. The second option is usually right at this scale, and it’s why most serious Postgres deployments run a pooling tier even when every client pools correctly on its own.
Wrapping up
Connection pooling is one of those subsystems where the defaults are fine until they very suddenly aren’t. The durable practice: size pools from concurrent-query math, export pgxpool statistics and alert on EmptyAcquireCount and acquire duration, bound connection lifetimes with jitter, keep transactions short and free of network calls, and when a fleet of services shares one database, put a transaction-mode PgBouncer tier in front and treat session state as forbidden. Do those five things and the pool stops being a source of 3 a.m. pages — it becomes boring infrastructure, which is exactly what you want from it.