Connection pooling

Opening a database connection costs a TCP handshake, usually a TLS handshake, authentication, and on PostgreSQL a whole new server process. That can easily take 20–50 ms, often longer than the query itself. A connection pool opens connections once and lends them out to requests: borrow, run a query, return.

In Go, *sql.DB and *pgxpool.Pool are already pools. Create one at startup and share it. Never open a pool per request.

db, _ := sql.Open("pgx", dsn)
db.SetMaxOpenConns(20)                  // hard cap: the 21st query waits
db.SetMaxIdleConns(20)                  // keep them warm instead of closing
db.SetConnMaxLifetime(30 * time.Minute) // recycle, so failovers and LB changes take effect

#Sizing it

More connections isn't faster. The database has a fixed number of CPU cores and disks, and past a point, extra connections just queue inside it and add lock contention. Size the pool with Little's law:

connections needed ≈ queries per second × average query time
                   = 400 q/s × 0.010 s = 4 busy connections

Add headroom for spikes, say 10–20 per instance. Then multiply by the number of instances. Fifty pods with 20 connections each is 1,000 connections, which is more than a default PostgreSQL max_connections of 100 will accept. That's why fleets put a pooler such as PgBouncer between the app and the database, or lower each pod's maximum.

#Failure modes

• Pool exhaustion. Every connection is busy and new requests wait. It usually means slow queries, a connection leaked by a rows you never closed, or a transaction held open across a slow external call. Always defer rows.Close(), and put a context deadline on queries so waiting is bounded.
• One slow dependency drains the pool for everyone. Separate pools per workload are bulkheads.
• N+1 queries borrow and return a connection dozens of times per request.

#Watch it

db.Stats() reports InUse, Idle, WaitCount and WaitDuration. A rising WaitDuration tells you requests are queuing for connections before any query is slow. Export it as a metric.