optimizing-database-connection-pooling

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 <[e-mail]>
license	MIT
tags	["database","optimizing-database"]
compatibility	Designed for Claude Code

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

1. 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

2. 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.

3. 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)

4. 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