Database Connection Pool Exhaustion: The Silent Outage Trigger

I’ve stared at a green dashboard while requests timed out, and it was the pool. Database looks like it’s on vacation (low CPU), but your app is frozen? This is one of the most common culprits: database connection pool exhaustion.

When every connection is busy—waiting on a slow query or stuck in a leaked transaction—new requests don’t get to do any work. They just line up behind the pool until connectionTimeout kicks in and you suddenly have an outage that doesn’t “look like a database problem”.

The first time I really internalized this, I spent six hours staring at a “healthy” database while the app timed out. The culprit was a single slow query holding a connection for ~45 seconds—and the giveaway was the metric we weren’t even graphing: pending connection requests in the pool.

Tested on: PostgreSQL 15, HikariCP 5.0, pgbouncer 1.21

The Problem

How Connection Pools Work

Before diving into the problem, let’s understand why connection pools exist. Creating a database connection is expensive—typically 50-100ms of handshaking, authentication, and session setup. For a web request that should complete in 50ms, spending 100ms just to establish a connection is absurd.

Connection pools solve this by maintaining a set of pre-established connections. When your application needs to query the database, it borrows a connection from the pool, uses it, and returns it. The expensive connection setup happens once when the pool initializes, not on every request.

Without pool:
┌─────────┐                    ┌──────────┐
│ Request │ ──create conn──→  │ Database │
│         │ ←───response────  │          │
│         │ ──close conn───→  │          │
└─────────┘                    └──────────┘
Cost: ~50ms per connection establish

With pool:
┌─────────┐     ┌──────────┐    ┌──────────┐
│ Request │ ──→ │   Pool   │ ─→ │ Database │
│         │ ←── │ (reuse)  │ ←─ │          │
└─────────┘     └──────────┘    └──────────┘
Cost: ~0ms (connection already open)

The pool maintains a fixed number of connections—say, 10. These connections sit open, ready to use. When request 1 arrives, it grabs connection A, runs its query, and returns connection A to the pool. Request 2 might grab the same connection A milliseconds later. This reuse eliminates connection overhead entirely.

When Pool Exhausts

The pool’s fixed size is both its strength and its Achilles’ heel. With 10 connections, you can only have 10 concurrent database operations. Normally this is fine—queries complete quickly, so the same 10 connections can serve hundreds of requests per second.

Problems emerge when queries slow down. If queries normally take 10ms but something causes them to take 500ms, your throughput drops from 1000 queries/second to 20. The other 980 requests per second start queueing, waiting for a connection to become available.

Scenario: Pool size = 10, Request rate = 100/sec

Normal operation (10ms queries):
  10 connections × 100 queries/sec each = 1000 queries/sec ✓

Slow query appears (500ms):
  10 connections × 2 queries/sec each = 20 queries/sec
  80 requests/sec waiting for connection... ✗

Pool exhaustion cascade:
┌─────────────────────────────────────────────────────────────┐
│ T+0s:    Slow query starts on connection 1                  │
│ T+0.5s:  Connections 2-10 also hit slow queries            │
│ T+1s:    ALL connections busy with slow queries            │
│ T+1s+:   New requests WAIT for connection                  │
│ T+30s:   Connection timeout → requests fail                │
│ Result:  Total outage despite database being "fine"        │
└─────────────────────────────────────────────────────────────┘

The cascade effect is what makes this so dangerous. One slow query isn’t a problem—9 other connections keep serving traffic. But if the slow query pattern affects all traffic (like a missing index on a common query path), soon all 10 connections are stuck. Once all connections are busy, every new request queues. The queue grows until connection acquisition timeouts start firing, and then you have an outage.

Connection Leaks: The Slow Poison

Even worse than slow queries are connection leaks. A leak happens when application code borrows a connection from the pool but never returns it. Maybe an exception was thrown before the connection could be closed. Maybe a developer forgot to close the connection in a code path. Maybe a transaction was started but never committed or rolled back.

Leaks are insidious because they cause gradual degradation. If one connection leaks per minute, you won’t notice for the first few minutes. After 10 minutes, your pool is empty and the application hangs. By the time you’re investigating, the leak is long over—you see an exhausted pool but no obvious cause.

I’ve seen teams chase connection pool exhaustion for weeks before realizing it was a slow leak in a rarely-executed code path. The key diagnostic is watching the pool over time. If idle connections slowly decrease even during low traffic periods, you have a leak.

Detecting Pool Exhaustion

Application Metrics

The most important metric is pending—the number of requests waiting for a connection. This should almost always be zero. Any non-zero value means you’re at capacity; sustained non-zero values mean you’re exhausting.

// HikariCP exposes these metrics
hikaricp_connections_active     # Currently in use
hikaricp_connections_idle       # Available
hikaricp_connections_pending    # Waiting for connection

// Alert when:
// pending > 0 for extended time = exhaustion
// active == max for extended time = at capacity

I recommend alerting on pending > 0 for more than 30 seconds. Brief spikes during traffic bursts are normal, but sustained pending counts indicate a problem. Also alert if active equals your max pool size for extended periods—you’re running at the edge of capacity and any slowdown will push you over.

PostgreSQL Side

Database-side visibility complements application metrics. You can see exactly which connections exist and what they’re doing:

-- Check connections per application
SELECT
    application_name,
    state,
    count(*) as connections,
    avg(EXTRACT(EPOCH FROM (now() - query_start))) as avg_query_time
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY application_name, state;

-- Find long-running queries holding connections
SELECT
    pid,
    now() - query_start as duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;

The second query is your best friend during outages. It shows which queries are running long, giving you immediate insight into what’s hogging connections. Look for queries with duration in the seconds or minutes—these are your culprits.

Pay attention to the state column. active means the query is running. idle in transaction is a red flag—it means a transaction is open but not doing work, effectively wasting a connection. Idle-in-transaction connections often indicate application bugs where transactions aren’t properly closed.

Incident Runbook (15 Minutes)

If I’m on-call and suspect pool exhaustion, this is the sequence I follow:

1. Confirm it’s the pool. Check hikaricp_connections_pending and whether active == max.
2. Find the hog. On PostgreSQL, identify long-running queries in pg_stat_activity.
3. Look for leaks. Check for rising idle in transaction counts/durations.
4. Mitigate fast. Cancel the worst offenders (pg_cancel_backend) before killing connections (pg_terminate_backend).
5. Stop the stampede. Ensure clients don’t retry aggressively; fail fast when the pool is exhausted.

-- Cancel long queries first (safer)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '30 seconds';

-- If cancellation doesn't work, terminate (drops connection)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '5 minutes';

One counterintuitive thing: scaling the application up can make the situation worse if each instance has its own pool. In incidents, I prefer to reduce concurrency (or shed load) rather than blindly adding more instances and multiplying the total connection count.

Solutions

1. Proper Pool Sizing

The classic sizing heuristic comes from PostgreSQL’s wiki:

Formula: connections = (core_count * 2) + effective_spindle_count

For SSD:
  connections ≈ core_count * 2

For cloud database (4 vCPUs):
  connections = 4 * 2 = 8 per instance

With 10 app instances:
  Total = 8 * 10 = 80 connections
  PostgreSQL max_connections = 100 (with buffer)

Treat this as a starting point, not a law of physics. The right number depends on your workload, query mix, and latency SLOs. The important insight is that more connections usually make things worse, not better. Each connection consumes memory on the database server. More connections mean more context switching. Beyond a certain point, adding connections reduces throughput as the database spends more time managing connections than executing queries.

# HikariCP configuration
spring:
  datasource:
    hikari:
      maximum-pool-size: 8
      minimum-idle: 2
      connection-timeout: 10000  # 10s max wait
      idle-timeout: 300000       # 5min idle before close
      max-lifetime: 1800000      # 30min max connection age
      leak-detection-threshold: 30000  # 30s leak warning

The leak-detection-threshold is crucial. When a connection isn’t returned within 30 seconds, HikariCP logs a warning with the stack trace of where the connection was borrowed. This makes finding leaks trivial.

2. Connection Leak Detection

Leak detection should be enabled in every environment, not just production. Catching leaks in development prevents them from ever reaching production.

// HikariCP leak detection
@Configuration
public class DataSourceConfig {
    @Bean
    public HikariDataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setLeakDetectionThreshold(30000); // 30 seconds
        // If connection not returned in 30s, log stack trace
        return new HikariDataSource(config);
    }
}

// Common leak patterns to avoid:
// 1. Missing try-with-resources
// 2. Exception before connection.close()
// 3. Forgotten @Transactional rollback

The try-with-resources pattern is your best defense:

// BAD - leak if exception occurs
Connection conn = dataSource.getConnection();
// ... code that might throw ...
conn.close();

// GOOD - always closes, even on exception
try (Connection conn = dataSource.getConnection()) {
    // ... code that might throw ...
}

For Python applications, here’s a leak detection pattern:

# Python SQLAlchemy - detect leaks
from sqlalchemy import event
from sqlalchemy.pool import Pool
import traceback
import time

checkout_times = {}

@event.listens_for(Pool, "checkout")
def on_checkout(dbapi_conn, connection_record, connection_proxy):
    checkout_times[id(dbapi_conn)] = {
        'time': time.time(),
        'stack': traceback.format_stack()
    }

@event.listens_for(Pool, "checkin")
def on_checkin(dbapi_conn, connection_record):
    checkout_info = checkout_times.pop(id(dbapi_conn), None)
    if checkout_info and time.time() - checkout_info['time'] > 30:
        print(f"Long checkout: {checkout_info['stack']}")

This captures the stack trace when connections are borrowed and logs if they’re held too long. The stack trace tells you exactly which code path is holding connections.

3. Query Timeouts

Timeouts prevent queries from running forever and blocking connections indefinitely. Set them at multiple levels for defense in depth.

-- PostgreSQL: Set statement timeout
SET statement_timeout = '30s';

-- Or per role (recommended as a safety net)
ALTER ROLE app_user SET statement_timeout = '30s';

Setting the timeout at the role level ensures it applies even if the application doesn’t set it explicitly. This catches cases where developers forget to configure timeouts.

# HikariCP with query timeout (driver-specific)
spring:
  datasource:
    hikari:
      data-source-properties:
        socketTimeout: 30000
        connectTimeout: 10000

Socket timeout is a last-resort protection—it kills the connection if the database doesn’t respond within the timeout. This handles cases where the database server becomes completely unresponsive.

4. Use PgBouncer for Connection Multiplexing

When your application instances outnumber optimal database connections, PgBouncer provides connection multiplexing:

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

# transaction mode: connection returned after each transaction
# Allows 1000 app connections with only 20 DB connections

PgBouncer sits between your application and the database. Your application opens connections to PgBouncer (up to 1000 in this example), but PgBouncer maintains only 20 actual database connections. When a transaction starts, PgBouncer assigns a real database connection. When the transaction ends, the connection returns to PgBouncer’s pool.

Transaction mode is the most aggressive pooling, but it has restrictions—prepared statements and session-level settings don’t work because you might get a different connection for each transaction. If you need these features, use session mode instead, which binds a client connection to a server connection for the duration of the session.

5. Circuit Breaker on Pool

When the connection pool is exhausted, new requests should fail fast rather than queueing indefinitely. A circuit breaker provides this protection:

// Resilience4j circuit breaker on connection acquisition
@Bean
public CircuitBreaker connectionCircuitBreaker() {
    return CircuitBreaker.of("db-pool", CircuitBreakerConfig.custom()
        .failureRateThreshold(50)
        .waitDurationInOpenState(Duration.ofSeconds(30))
        .slidingWindowSize(10)
        .build());
}

public Connection getConnection() {
    return circuitBreaker.executeSupplier(() -> dataSource.getConnection());
}

When connection acquisition fails more than 50% of the time, the circuit breaker opens. For the next 30 seconds, all connection attempts fail immediately without waiting. This prevents the cascade effect where connection timeouts cause request timeouts which cause more load which causes more timeouts.

After 30 seconds, the circuit breaker enters half-open state and allows a few test requests. If those succeed, normal operation resumes. If they fail, the circuit stays open. This gives the database time to recover without being hammered by retry storms.

Monitoring

Effective monitoring catches pool exhaustion before it causes outages:

# Prometheus alerts
- alert: ConnectionPoolExhausted
  expr: |
    hikaricp_connections_pending > 0
  for: 1m
  annotations:
    summary: "Connection pool has waiting requests"

- alert: ConnectionPoolAtCapacity
  expr: |
    hikaricp_connections_active / hikaricp_connections_max > 0.9
  for: 5m
  annotations:
    summary: "Connection pool at 90%+ capacity"

- alert: LongRunningTransactions
  expr: |
    pg_stat_activity_max_tx_duration{state!="idle"} > 30
  for: 5m
  annotations:
    summary: "PostgreSQL has non-idle transactions running > 30s"

The first alert catches active exhaustion—requests are waiting. The second alert catches impending exhaustion—you’re running at capacity and any spike will cause problems. The third alert helps identify a common root cause: long-running work holding connections. (Metric names/labels depend on your exporter; adjust to what you actually expose.)

I recommend setting up a Grafana dashboard with these metrics displayed together. When exhaustion occurs, you want to see the correlation: pool pending spikes at the same time as query/transaction duration spikes. This correlation immediately points you toward the slow query as the root cause.

Checklist

## Connection Pool Configuration

### Sizing
- [ ] Calculate pool size: (cores × 2) + spindles
- [ ] Set connection-timeout (10-30s)
- [ ] Enable leak detection (30s threshold)

### Timeouts
- [ ] Set statement_timeout in PostgreSQL
- [ ] Configure socket timeout in pool
- [ ] Add circuit breaker for pool acquisition

### Monitoring
- [ ] Export pool metrics (active, idle, pending)
- [ ] Alert on pending > 0
- [ ] Track connection checkout duration

### Architecture
- [ ] Consider PgBouncer for high connection counts
- [ ] Use transaction pool mode where possible
- [ ] Implement query timeouts at app level

Conclusion

Connection pool exhaustion causes silent outages because all the usual database metrics look fine. The problem isn’t in the database—it’s in the interface between your application and the database.

Key takeaways:

1. Size pools correctly - more isn’t better, use a heuristic and validate
2. Detect leaks with threshold monitoring and stack traces
3. Set query timeouts to prevent connections being held indefinitely
4. Use PgBouncer when you need more app connections than database connections
5. Monitor pending connections - this is the canary in the coal mine

Check your pending connections metric now. If it’s ever non-zero, you’re running closer to the edge than you think.

Related Articles

• PostgreSQL Idle in Transaction Playbook - One of the most common leak patterns
• Redis Memory Fragmentation - Database resource issues
• gRPC Deadline Propagation - Timeout patterns

References

• PostgreSQL Wiki – Number of Database Connections: https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
• PostgreSQL Docs – pg_stat_activity: https://www.postgresql.org/docs/current/monitoring-stats.html
• HikariCP: https://github.com/brettwooldridge/HikariCP
• PgBouncer: https://www.pgbouncer.org/
• SQLAlchemy – Pool Events: https://docs.sqlalchemy.org/en/20/core/pooling.html#pool-events
• Resilience4j – CircuitBreaker: https://resilience4j.readme.io/docs/circuitbreaker