Systems
The database did not run out of CPU
The API handles 10,000 req/s. The database handles 10,000 queries/s. At 200 concurrent requests, everything locks. The bottleneck was not CPU, memory, or the network. It was connections.
PostgreSQL makes a server process per connection. Each process eats roughly 5 to 10MB of RAM. A hundred concurrent connections is a gigabyte just to stay open. Default max_connections is 100. The box was not slow. It was full of handshakes.
Without a pool
Each request opens a connection, runs the query, closes it. About 50ms of overhead per new connection. At 200 req/s the database saturates on handshake alone. Your query never gets a fair fight.
With a pool
Twenty persistent connections get reused. Request arrives, takes a free connection, queries, returns it. Zero connection theater. Capacity jumps because you stopped paying the cover charge on every ticket.
How I configure it
1. Pool in the app
With pg-pool, 10 to 20 connections per instance. Four instances: 40 to 80 against the database. Inside the limit. Min 5, max 20, idle timeout 30s, connection timeout 5s. If the pool is full, the request waits up to five seconds. Waiting beats knocking the database over.
2. PgBouncer in front
When you scale instances, app pools add up past max_connections. PgBouncer sits between app and Postgres and multiplexes hundreds of client connections into tens of real ones. Transaction mode: the real connection is yours only for the transaction, then it goes back. Maximum efficiency. Use it when the instance count is the problem, not when you have not pooled yet.
3. Watch the count
SELECT count(*) FROM pg_stat_activity. Alert at 80% of max. Near the ceiling you have a leak in code or a pool that is lying about its size.
4. Kill long queries
statement_timeout of 30 seconds. A query that runs longer is cancelled and the connection is freed. Without that, one stuck query holds a slot forever. Pools do not save you from leaks. They just make leaks more polite until they are not.
Connection pooling is the highest return per line of config I know. Five lines that change what the system can carry. If your dashboard says the database is “at capacity” and CPU is bored, look at connections first.