Connection Pooling

Connection pooling reuses database connections to reduce overhead.

The Problem Without Pooling

Without Pooling:

Request 1: Create connection → Use → Close
Request 2: Create connection → Use → Close
Request 3: Create connection → Use → Close

Overhead per request:
- TCP handshake: 20ms
- Authentication: 30ms
- SSL negotiation: 50ms
- Total: 100ms overhead per request!

For 1000 requests/sec:
- 100 seconds of overhead per second
- Wasted resources

Connection Pooling Solution

With Pooling:

Pool: [Conn1, Conn2, Conn3, Conn4, Conn5]

Request 1: Get Conn1 → Use → Return
Request 2: Get Conn2 → Use → Return
Request 3: Get Conn3 → Use → Return

Overhead per request:
- Get from pool: 1ms
- Use connection: 10ms
- Return to pool: 0.5ms
- Total: 11.5ms (10x faster!)

How Connection Pooling Works

1. Pool initializes with min connections
2. Application requests connection
3. Pool returns available connection
4. Application uses connection
5. Application returns connection to pool
6. Connection available for next request

If pool empty:
- Wait for available connection
- Or create new connection (up to max)

Connection Pool Architecture

Application Servers
    │
    ├── Connection Pool
    │   ├── Conn 1 (active)
    │   ├── Conn 2 (active)
    │   ├── Conn 3 (idle)
    │   ├── Conn 4 (idle)
    │   └── Conn 5 (idle)
    │
    └── Database

Benefits of Connection Pooling

Benefit	Description
Reduced overhead	No create/destroy per request
Better performance	10x faster connection acquisition
Resource control	Limit total connections
Connection reuse	Existing connections reused
Health checking	Verify connections before use

When to Use Connection Pooling

Always use connection pooling for:
- Web applications
- API servers
- Microservices
- Any application with multiple requests

Exception:
- Simple scripts with few queries
- One-off operations

Proper pool configuration is critical for performance.

Key Configuration Parameters

1. Min Connections (minIdle)
   - Minimum connections in pool
   - Always available
   - Default: 5-10

2. Max Connections (maxActive)
   - Maximum connections in pool
   - Upper limit
   - Default: 20-30

3. Connection Timeout
   - Max wait for connection
   - Fail fast if pool exhausted
   - Default: 30 seconds

4. Idle Timeout
   - Close idle connections
   - Free resources
   - Default: 10 minutes

5. Max Lifetime
   - Maximum connection age
   - Prevent stale connections
   - Default: 30 minutes

Pool Sizing Formula

Formula: Connections = (Core count × 2) + Effective spindle count

Example:
- 4 CPU cores
- SSD (1 effective spindle)
- Connections = (4 × 2) + 1 = 9

Total pool = connections × number of instances

Configuration Examples

# HikariCP (Java)
maximumPoolSize: 20
minimumIdle: 5
connectionTimeout: 30000
idleTimeout: 600000
maxLifetime: 1800000

# Node.js (pg-pool)
max: 20
min: 5
idleTimeoutMillis: 30000
connectionTimeoutMillis: 2000

# Python (SQLAlchemy)
pool_size: 20
max_overflow: 10
pool_timeout: 30
pool_recycle: 1800

Pool Sizing Guidelines

Too Small:
- Requests wait for connections
- High latency
- Poor throughput

Too Large:
- Database overload
- Memory waste
- Connection overhead

Just Right:
- Minimal waiting
- Good throughput
- Database can handle

Configuration Best Practices

1. Start conservative: Begin with defaults, adjust based on load
2. Monitor utilization: Track active vs idle connections
3. Set timeouts: Prevent hung connections
4. Use connection validation: Verify connections before use
5. Consider database limits: Don't exceed max_connections

Monitoring connection pools prevents issues and optimizes performance.

Key Metrics to Monitor

1. Active Connections
   - Currently in use
   - Should stay below max

2. Idle Connections
   - Available in pool
   - Should stay above min

3. Waiting Threads
   - Threads waiting for connection
   - Should be 0 ideally

4. Connection Creation Rate
   - New connections per second
   - High = pool too small

5. Connection Timeout Rate
   - Timed out requests
   - Should be 0

Monitoring Dashboard

Connection Pool Metrics:

Active:   [████████░░] 80%  (16/20)
Idle:     [██░░░░░░░░] 20%  (4/20)
Waiting:  [░░░░░░░░░░] 0    (0 threads)
Timeouts: [░░░░░░░░░░] 0    (0/sec)

Status: Healthy ✓

Monitoring Tools

1. Application Metrics
   - HikariCP metrics
   - pg-pool stats
   - SQLAlchemy pool status

2. Database Monitoring
   - PostgreSQL: pg_stat_activity
   - MySQL: SHOW PROCESSLIST

3. APM Tools
   - Prometheus + Grafana
   - Datadog
   - New Relic

Monitoring Queries

-- PostgreSQL: Active connections
SELECT count(*) FROM pg_stat_activity;

-- By state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Long running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Alerting Thresholds

Alert When:
- Active connections > 80% of max
- Waiting threads > 0 for > 5 seconds
- Connection timeout rate > 0
- Idle connections < min for > 1 minute

Common Issues and Solutions

Issue	Symptom	Solution
Pool exhaustion	Waiting threads	Increase max or optimize queries
Connection leaks	Active grows, never decreases	Fix code to return connections
Stale connections	Timeout errors	Decrease maxLifetime
Too many connections	Database overload	Decrease max or add replicas

Monitoring Best Practices

1. Monitor continuously: Real-time visibility
2. Set up alerts: Proactive issue detection
3. Track trends: Capacity planning
4. Dashboard visibility: Team can see status
5. Log connection events: Debug issues