Database connection pool exhaustion under load
Peak-traffic 500s that looked like a pooling problem, but were really a query problem wearing a pooling costume.
The symptom
API endpoints started returning 500s during peak traffic, with Postgres reporting that remaining connection slots were reserved. The obvious read was "we need a bigger pool."
Root cause
Several polymorphic queries had no supporting indexes, so under load they fell back to full table scans. Each of those scans held its connection open while it waited on disk I/O — so the pool wasn't undersized, it was being occupied by slow queries for far longer than it should have been.
The fix
Added compound indexes on the actual hot query paths, which cut per-query time enough that connections stopped being held open under load.
Introduced PgBouncer in front of Postgres for proper connection pooling, and fixed the N+1 queries underneath the slow endpoints with targeted eager-loading, so each request stopped issuing far more queries than it needed to.
Takeaway
Connection exhaustion is rarely a pooling problem at the root — it's usually slow queries holding connections longer than they should. Fix the query first. If the pool is still the bottleneck after that, then size the pool.