Database Connection Pool Management

A database connection is expensive — establishing a TCP connection, completing TLS handshake, authenticating, and allocating server-side memory takes 20-100ms. Connection pooling reuses established connections, amortizing this cost across thousands of queries. But pool misconfiguration is the most common cause of database-related outages: too few connections cause request queueing; too many overwhelm the database.

Connection Pool Sizing

PostgreSQL Connection Formula:

Max connections per database server:
  max_connections = (Available RAM - Shared Buffers - OS overhead) 
                    / per_connection_memory
  
  Typical values:
    Available RAM:        16 GB
    Shared Buffers:       4 GB
    OS overhead:          2 GB
    Per-connection memory: ~10 MB
    
    max_connections = (16 - 4 - 2) * 1024 / 10 = ~1000
    ⚠️ Practical limit: 200-500 (performance degrades at high counts)

Application pool size (per application instance):
  pool_size = (num_cores * 2) + disk_spindles
  
  For modern SSDs with 4-core application:
  pool_size = (4 * 2) + 1 = 9 (round to 10)
  
  Rule of thumb:
    CPU-bound queries: pool = core_count
    IO-bound queries: pool = core_count * 2-4
    Mixed workload: pool = core_count * 2

Total connections across all instances:
  total = pool_size * num_instances
  
  Example:
    pool_size = 10, instances = 20
    total = 200 connections
    
  If total > max_connections → use PgBouncer

PgBouncer Configuration

;; pgbouncer.ini — Connection pooler for PostgreSQL

[databases]
mydb = host=db-primary.internal port=5432 dbname=mydb

[pgbouncer]
; Pool mode: transaction is recommended for most workloads
pool_mode = transaction

; Connections from clients to PgBouncer
max_client_conn = 1000

; Connections from PgBouncer to PostgreSQL
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

; Timeouts
server_idle_timeout = 600        ; Close idle server connections after 10 min
client_idle_timeout = 0          ; Never close idle client connections
server_connect_timeout = 15      ; Fail fast if PostgreSQL is unreachable
query_wait_timeout = 120         ; Max wait time for a connection from pool

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Anti-Patterns

Connection pool management is the intersection of application performance and database reliability. The pool is the pressure valve between your application’s desire for more connections and your database’s finite capacity. Size it correctly, monitor it continuously, and treat pool exhaustion as a critical alert.