# Connection pooling

> Reuse a bounded set of database connections — and size the bound from latency, not guesswork.

Concept · Web services · https://levelcode.org/concepts/connection-pooling

Opening a database connection costs a TCP handshake, usually a [TLS](https://levelcode.org/concepts/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.

```go
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](https://levelcode.org/concepts/littles-law):

```text
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](https://levelcode.org/concepts/context-cancellation) on queries so waiting is bounded.
- **One slow dependency drains the pool for everyone.** Separate pools per workload are [bulkheads](https://levelcode.org/concepts/bulkheads).
- **[N+1 queries](https://levelcode.org/concepts/n-plus-one-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.