| name | optimizing-database-connection-pooling |
| description | Process use when you need to work with connection management.
This skill provides connection pooling and management with comprehensive guidance and automation.
Trigger with phrases like "manage connections", "configure pooling",
or "optimize connection usage".
|
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*) |
| version | 1.23.0 |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| license | MIT |
| tags | ["database","optimizing-database"] |
| compatibility | Designed for Claude Code, also compatible with Codex and OpenClaw |
Database Connection Pooler
Overview
Configure and optimize database connection pooling using external poolers (PgBouncer, ProxySQL, Odyssey) and application-level pool settings to prevent connection exhaustion, reduce connection overhead, and improve database throughput.
Prerequisites
psql or mysql CLI for querying connection metrics- Access to database configuration files (
postgresql.conf, my.cnf) for max_connections settings
- PgBouncer, ProxySQL, or Odyssey installed if using external pooling
- Application connection pool settings accessible (database URL, pool size parameters)
- Server CPU core count and available memory for pool sizing calculations
Instructions
-
Audit current connection usage by querying active connections:
- PostgreSQL:
SELECT count(*) AS total, state, usename FROM pg_stat_activity GROUP BY state, usename ORDER BY total DESC
- MySQL:
SHOW STATUS LIKE 'Threads_connected' and SHOW PROCESSLIST
- Compare against
max_connections setting to determine headroom
-
Calculate the optimal pool size using the formula: pool_size = (core_count * 2) + effective_spindle_count. For SSD-backed databases, use core_count * 2 + 1. A 4-core server with SSD storage should have a pool size of approximately 9. This formula applies per application instance.
-
Configure application-level connection pool parameters:
- minimumIdle: Set to 2-5 for low-traffic periods (avoids cold-start latency)
- maximumPoolSize: Set using the formula from step 2
- connectionTimeout: 5-10 seconds (fail fast rather than queue indefinitely)
- idleTimeout: 10-30 minutes (release idle connections back to pool)
- maxLifetime: 30 minutes (prevent stale connections from accumulating)
- leakDetectionThreshold: 60 seconds (log warning for connections held too long)
-
For PostgreSQL with many application instances, deploy PgBouncer in transaction pooling mode:
- Set
pool_mode = transaction to multiplex connections (one backend connection serves many clients between transactions)
- Set and
