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 default_pool_size = 20 and max_client_conn = 1000
- Configure server_idle_timeout = 600 to close unused backend connections
- Set server_lifetime = 3600 to periodically refresh connections
For MySQL with many application instances, deploy ProxySQL:
- Configure connection multiplexing in mysql_servers table
- Set max_connections per backend server
- Configure query rules for read/write splitting to replicas
- Enable connection pooling with free_connections_pct = 10
Set max_connections in the database server based on available memory. Each PostgreSQL connection uses approximately 5-10MB of memory. For a server with 8GB RAM: max_connections = (8192MB - 2048MB_for_OS - 2048MB_shared_buffers) / 10MB = ~400. For MySQL, each thread uses approximately 1-4MB.
Implement connection health checks. Configure the pool to validate connections before lending (testOnBorrow or validation-query). Use a lightweight query: SELECT 1 for MySQL or a simple query for PostgreSQL. Set validation interval to avoid excessive overhead.
Monitor connection pool metrics continuously:
- Active connections vs. pool size (saturation indicator)
- Wait time for connection acquisition (queuing indicator)
- Connection creation rate (churn indicator)
- Idle connection count (waste indicator)
- Connection leak warnings (application bug indicator)
Handle connection storms (sudden spike in connection requests) by configuring a connection request queue with a bounded wait time, implementing retry with exponential backoff in the application, and pre-warming the pool during application startup.
Document the connection architecture: application pool size per instance, number of application instances, PgBouncer/ProxySQL settings, database max_connections, and the maximum theoretical connections formula (instances * pool_size_per_instance).

Output

PgBouncer/ProxySQL configuration files with optimized pool settings
Application pool configuration with connection string and pool parameters
Connection sizing worksheet documenting the calculation from cores to pool size
Monitoring queries for connection metrics and health checks
Connection architecture diagram showing application -> pooler -> database flow

Error Handling

Error	Cause	Solution
`FATAL: too many connections for role`	Application pool size exceeds `max_connections` or connection leak	Reduce pool size; fix connection leaks (enable leak detection); add PgBouncer for connection multiplexing
Connection timeout after 5 seconds	Pool exhausted, all connections in use	Increase pool size cautiously; check for long-running transactions holding connections; add connection queue with backpressure
`connection reset by peer` errors	Server-side idle timeout killed the connection	Set pool `maxLifetime` shorter than server `idle_in_transaction_session_timeout`; enable connection validation
PgBouncer `no more connections allowed`	`max_client_conn` exceeded	Increase `max_client_conn`; or reduce client connection demand; check for connection leaks in application
High connection churn (create/destroy rate)	Pool too small for workload or `maxLifetime` too short	Increase pool size; extend `maxLifetime` to 30 minutes; ensure `minimumIdle` is set to avoid constant pool resizing

Examples

Right-sizing a pool for a Spring Boot microservice: 4-core server, SSD storage, 3 microservice instances. Optimal pool per instance: (4 * 2) + 1 = 9. Total connections: 9 * 3 = 27. Database max_connections = 100 with comfortable headroom. Application startup pre-warms 5 connections per instance. Connection leak detection set to 60 seconds catches a missing connection.close() in an error handler.

PgBouncer deployment for a serverless application: Lambda functions create a new database connection per invocation, overwhelming PostgreSQL with 500+ connections. PgBouncer deployed between Lambda and PostgreSQL with pool_mode = transaction, default_pool_size = 25, max_client_conn = 5000. Lambda connects to PgBouncer; PgBouncer multiplexes to 25 backend connections. Connection errors eliminated; database CPU reduced from 95% to 30%.

ProxySQL read/write splitting: A MySQL application sends 80% reads and 20% writes. ProxySQL routes writes to the primary and distributes reads across 2 replicas. Connection pooling reduces backend connections from 300 (direct) to 60 (pooled). Average query latency drops from 8ms to 3ms due to reduced connection overhead.

Resources

PgBouncer documentation: https://www.pgbouncer.org/config.html
ProxySQL documentation: https://proxysql.com/documentation/
HikariCP pool sizing: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
PostgreSQL connection management: https://www.postgresql.org/docs/current/runtime-config-connection.html
Odyssey connection pooler: https://github.com/yandex/odyssey

optimizing-database-connection-pooling