Problem
Your production application starts throwing "connection pool exhausted" errors under moderate load. The database server shows only 50 active connections, but the application's connection pool (configured for 20 connections) reports all connections are in use. Response times spike from 100ms to 10+ seconds, and eventually requests start failing with timeout errors.
The Scenario
Your Node.js application uses a PostgreSQL connection pool (via Prisma, pg-pool, or Knex). Under normal load (100 req/s), everything works fine. When traffic increases to 300 req/s, the connection pool is exhausted within minutes.
Investigation reveals these symptoms:
- Pool reports 20/20 connections "checked out"
- PostgreSQL
pg_stat_activity shows many connections in "idle in transaction" state
- Application logs show database queries completing in 5-20ms
- Some API endpoints call multiple database queries sequentially
- The application uses middleware that opens a database transaction for every request
Your Task
- Diagnose the root causes of pool exhaustion (there are multiple).
- Identify the specific code patterns causing connection leaks.
- Fix the issues with concrete code changes.
- Prevent pool exhaustion from recurring.
Constraints
- You cannot simply increase the pool size (the database has a 100-connection limit shared by multiple services).
- The fix should not require restructuring the entire application.
- Solutions must handle both normal and error code paths (connections must be returned on errors too).
What to Deliver
- Explain the connection pool lifecycle and what "idle in transaction" means
- Identify at least 3 code patterns that cause pool exhaustion
- Provide fixes for each pattern
- Recommend monitoring and alerting strategies