Every database access in a real service goes through a connection pool — a fixed set of open TCP connections to the database, handed out on demand. This trace follows a single SELECT from the moment it asks the pool for a connection to the moment it returns it, and what happens when demand exceeds supply.
1. The request asks the pool for a connection
The query layer (psycopg2, pgx, HikariCP, Prisma's pool) calls pool.acquire(). The pool is a data structure with two parts: a free list of idle connections and a count of in-flight ones. The caller wants one of each: a free connection to use, and headroom in the total count.
2. Idle connection is reused
In the common case there is an idle connection in the free list — a keep-alive TCP connection to Postgres that has been waiting since the last query. Checkout is an in-memory operation: pop from the free list, mark in use. Cost: microseconds. This is the entire point of pooling: the 40-120ms connection setup cost from the API trace is paid once at boot, not per request.
3. No idle connection, but room in the pool
The free list is empty but in_flight < pool_size. The pool opens a new connection: TCP handshake + Postgres auth + connection startup — 5-50ms of blocking work the caller cannot avoid. Under a cold start or a burst, this is where the first few requests eat setup latency while the pool warms.
4. The pool is exhausted: the request queues
Here is the math that decides what happens next. Suppose the pool size is 20 and each query holds a connection for 5ms. The pool sustains 20 / 0.005 = 4000 queries/sec. Arrivals beyond that find in_flight == pool_size, and the pool has nothing to hand out. The request does not fail — it waits on a semaphore or a wait queue, first-come-first-served. The queue depth at a given request rate λ and service time S is roughly λ·S − pool_size in steady state: at 6000 req/s with 5ms service, the queue holds about 10 waiters.
5. Acquire timeout fires
Most pools ship a default connectionTimeout / acquireTimeout of 5-30 seconds. A waiter that exceeds it gets an exception (SQLException: Connection is not available, request timed out after 10000ms). Critically, a timeout does not mean the request has been waiting alone — it means the pool has been saturated longer than the timeout for everyone behind it. Timeout exceptions are the smoke alarm; the fire is service time growing, not the pool itself.
6. Connection is checked in
The query completes and the caller calls pool.release() (or the pool releases in a finally). The pool runs a cheap validation — SELECT 1 or a isValid ping, ~0.2ms — then pushes the connection back on the free list and signals one waiting acquirer. The total pool overhead for a normal request: well under 1ms.
7. Eviction cleans up
Meanwhile, the pool's maintenance thread runs on a timer (HikariCP default: 30s). It closes connections that have exceeded maxLifetime (30 min default) or idleTimeout (10 min default), and removes dead ones — a network partition, a restarted database, or a server-side terminate all surface here as broken sockets. Eviction is why pools self-heal after an outage: every connection eventually dies and is rebuilt, so a pool does not need a restart to recover.
The number to watch
The metric that matters is not pool size — it is pool.wait_time and pool.active_connections at steady state. If active connections sit near the cap for sustained minutes, service time is the problem. If wait time trends up while active is flat, you are under-provisioned. Both are visible from the pool's own stats endpoint long before users see errors.