Connection pool exhaustion under load

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

This is likely masking a connection leak or poor connection management. Here's why: ## Don't just increase max_connections **Why it's usually wrong:** - Each PostgreSQL connection consumes ~10MB RAM plus overhead - 95/100 usage suggests connections aren't being released properly - Increasing the limit just delays the same problem **What to check first:** 1. **Identify connection sources:** ```sql SELECT client_addr, state, count(*) FROM pg_stat_activity GROUP BY client_addr, state ORDER BY count DESC; ``` 2. **Find long-running idle connections:** ```sql SELECT pid, usename, application_name, state, state_change, now() - state_change AS idle_duration FROM pg_stat_activity WHERE state = 'idle' ORDER BY state_change; ``` 3. **Check for leaked connections in your app:** - Are you closing connections in error handlers? - Using connection pools without limits? - Missing `finally` blocks or defer statements? ## The right fix **Use connection pooling** (if not already): - **Application-level:** PgBouncer or your framework's pool (set max pool size) - **Database-level:** Keep `max_connections` at 100, let pooler multiplex - **Typical setup:** 100-200 pooled connections → ~20-40 actual DB connections **For your app:** - Set connection timeout/max lifetime - Limit pool size to 10-20 per instance (not hundreds) - Always close connections in defer/finally blocks What's your current setup? Are you using a connection pool, and if so, what are its settings?

This is almost certainly a **connection leak** in your application rather than a genuine need for more connections. Here's why: ## Why not to just increase max_connections 1. **100 connections is plenty** for most applications - even high-traffic sites typically need far fewer active connections than you'd expect 2. **PostgreSQL memory overhead** - each connection consumes ~10MB of RAM, so scaling connections has real costs 3. **Masks the real problem** - you'll just hit the new limit eventually if there's a leak ## Diagnose the issue first Run these queries on your database: ```sql -- See what connections are doing SELECT state, COUNT(*) FROM pg_stat_activity WHERE datname = 'your_database_name' GROUP BY state; -- Find idle connections SELECT pid, usename, application_name, client_addr, state, state_change, query FROM pg_stat_activity WHERE state = 'idle' AND state_change < NOW() - INTERVAL '5 minutes' ORDER BY state_change; ``` **Red flags:** - Lots of `idle` connections (not `idle in transaction`) - Connections from the same application piling up - Very old `state_change` timestamps ## Common causes & solutions 1. **No connection pooling** → Use pgBouncer or a connection pool in your app (like HikariCP, pg-pool, SQLAlchemy pooling) 2. **Connections not closed** → Ensure `conn.close()` in finally blocks or use context managers 3. **Poor pool configuration** → Pool size too large or connections never timing out 4. **Long-running transactions** → Transactions holding connections open indefinitely ## The right fix Instead of increasing max_connections: 1. **Add connection pooling** (pgBouncer recommended - can handle 1000s of clients with 100 DB connections) 2. **Set connection timeouts** in your app 3. **Use connection lifecycle management** (context managers, try-finally blocks) 4. **Monitor connection age** and kill leaked connections as a temporary measure Would you like me to help investigate your application code to find where connections might be leaking?

Start by examining `postgresql.backends` against `postgresql.connection.max` and checking `postgresql.percent-usage-connections` to confirm you're actually at the limit. Query pg_stat_activity to break down connections by state (active, idle, idle in transaction). If most connections are 'idle' or 'idle in transaction' for extended periods, you have a connection leak, not a capacity problem. The `connection-exhaustion-max-connections` insight emphasizes monitoring connection state distribution to identify idle or long-running connections before adjusting limits.

Use `postgresql-connections-by-process` to see which applications or processes are holding connections. Look for processes with a high count of idle connections that aren't being released. The `connection-limit-approaching` insight specifically mentions checking for connections in 'idle' state for hours in pg_stat_activity—these are classic connection leaks indicating the application isn't properly closing connections or has misconfigured timeout settings.

Examine `postgresql-active-queries` to see if connections are being held by long-running operations. Query pg_stat_activity for queries with long query_start times. Even if individual queries aren't slow, connection pool exhaustion can occur when legitimate concurrent operations all need connections simultaneously. This helps distinguish between connection leaks (idle) and genuine concurrent load (active).

Before touching PostgreSQL settings, review your application's connection pool configuration. Check pool size limits, connection timeout settings, and whether connections are being properly returned to the pool after use. The `connection-limit-exhaustion` insight notes that each PostgreSQL connection consumes 5-10MB RAM via the fork model before queries even run, so throwing more connections at the problem without fixing application-side leaks will waste memory and degrade performance through excessive context-switching.

For most applications hitting 100 connections, the solution is PgBouncer in transaction mode, not increasing max_connections. The `connection-limit-exhaustion` insight specifically recommends configuring PgBouncer with default_pool_size=20 actual database connections while supporting max_client_conn=1000 application connections. This multiplexes many app connections onto few database connections, avoiding the context-switching overhead that makes PostgreSQL unresponsive at 500+ connections. Only increase max_connections to 200-300 if you have genuine concurrent transaction needs and sufficient RAM (each connection adds 5-10MB overhead).