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

     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

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

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

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

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

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

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

FATAL: too many connections for role

max_connections

connection reset by peer

maxLifetime

idle_in_transaction_session_timeout

no more connections allowed

max_client_conn

max_client_conn

maxLifetime

maxLifetime

minimumIdle

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

9 * 3 = 27

max_connections = 100

connection.close()

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

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