Managing Database Connections
1. Understanding Connection Lifecycle
Connection Lifecycle
- TCP handshake + TLS negotiation
- Authentication (SASL, scram, cert)
- Session setup (search_path, timezone, role)
- Active queries (transactions)
- Idle / cached in pool
- Close on timeout, error, or shutdown
| Cost | Detail |
|---|---|
| Establishment | 10–100 ms (TLS, auth, fork backend) |
| Memory per backend | ~10 MB (Postgres) |
| CPU at scale | Context switches grow with connections |
2. Configuring Connection Pooling
| Mode | Detail |
|---|---|
| Session pooling | 1 connection per client until disconnect |
| Transaction pooling | Connection assigned per tx (most common) |
| Statement pooling | Per-statement (no txs/prepared stmts) |
| Multiplexing | N clients share K connections |
3. Setting Pool Size Parameters
| Param | Guidance |
|---|---|
| Max pool size | ~ (2 × cores) + spindles for sync DB workloads |
| Min idle | Match steady-state load |
| Acquire timeout | 1–5 seconds; fail fast on saturation |
| Idle timeout | 5–10 minutes to release unused |
| Max lifetime | 30 min to dodge stale TCP |
4. Implementing Connection Timeouts
| Timeout | Purpose |
|---|---|
| connect_timeout | TCP/TLS dial limit |
| statement_timeout | Kill long queries |
| idle_in_transaction_session_timeout | Kill idle txs (Postgres) |
| tcp_keepalives_* | Detect dead peers |
| lock_timeout | Avoid hanging on lock acquisition |
5. Handling Connection Leaks
| Cause | Mitigation |
|---|---|
| Forgot close in error path | try-with-resources / context managers |
| Long txs holding conns | idle_in_transaction_session_timeout |
| Pool leak detection | HikariCP leakDetectionThreshold |
| Observability | Track pool checkout time histogram |
6. Using Connection Validation
| Approach | Detail |
|---|---|
| Test query | SELECT 1 before lease |
| Isolation API | JDBC isValid(timeout) |
| Background validation | Periodic vs per-checkout |
| Retry on failure | Replace stale conn transparently |
7. Managing Connection Credentials
| Mechanism | Detail |
|---|---|
| Secret manager | AWS Secrets Manager, Vault, GCP Secret Manager |
| IAM auth | RDS IAM tokens (15 min) |
| Rotation | Automated, dual-key window for zero-downtime |
| TLS / mTLS | Cert-based authentication |
| Avoid env vars in code | Inject at runtime |
8. Implementing Connection Retry Logic
| Strategy | Detail |
|---|---|
| Retry transient errors | Network blip, primary failover |
| Don't retry on permanent | Auth failure, schema mismatch |
| Exponential backoff + jitter | 50ms, 100ms, 200ms... with jitter |
| Max attempts | 3–5; then fail upstream |
| Circuit breaker | Stop hammering during outage |
9. Monitoring Connection Pool Metrics
| Metric | Watch For |
|---|---|
| Active / idle / waiting | Waiting > 0 → undersized pool |
| Acquire time p99 | Spikes → contention |
| Total connections | vs server max_connections |
| Errors (timeouts) | Alert on rate |
10. Optimizing Connection Pool Performance
| Tip | Detail |
|---|---|
| Right-size | Smaller pools often outperform large ones |
| Use external pooler | PgBouncer in front of Postgres |
| Prepared statement cache | Reuse plans across calls |
| Avoid per-request connections | Critical for serverless DB access |