Database Connection Pooling

Every database connection costs memory (5-10 MB in PostgreSQL), a process or thread on the server, and network overhead. Without pooling, 100 application instances each opening 10 connections creates 1,000 database connections — far more than most databases can handle efficiently. Connection pooling reuses connections across requests, keeping database load manageable.

---

Why Connection Pooling

Without Pooling	With Pooling
New connection per request (50-100ms overhead)	Reuse existing connection (< 1ms)
1,000 connections for 100 app instances	50 pooled connections serve 100 instances
Database memory exhausted	Bounded, predictable memory usage
Connection storms on deployment	Graceful ramp-up

---

Pool Sizing Formula

Optimal pool size = ((core_count * 2) + effective_spindle_count)

For a 4-core server with SSD:
Pool size = (4 * 2) + 1 = 9

For most applications:
Start with: connections = (CPU cores × 2) + 1
Then benchmark and adjust.

Key insight: MORE connections ≠ MORE throughput.
Too many connections cause lock contention and
context switching that REDUCES performance.

---

PgBouncer Configuration

[databases]
myapp = host=postgres port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *

; Pool mode
pool_mode = transaction    ; Best for most apps

; Pool sizes
default_pool_size = 20     ; Connections per database/user pair  
max_client_conn = 1000     ; Max client connections to PgBouncer
min_pool_size = 5          ; Keep minimum connections warm
reserve_pool_size = 5      ; Emergency connections

; Timeouts
server_idle_timeout = 600  ; Close idle server connections after 10 min
client_idle_timeout = 0    ; Don't close idle client connections
query_timeout = 30         ; Kill queries running > 30 seconds

; Connection lifetime
server_lifetime = 3600     ; Recycle connections every hour

---

Pool Modes

Mode	Behavior	Best For
Session	Connection held for entire session	Long-lived connections, prepared statements
Transaction	Connection returned after each transaction	Most web applications (recommended)
Statement	Connection returned after each statement	Simple queries, no multi-statement transactions

---

Anti-Patterns

Anti-Pattern	Problem	Fix
No pooling	Connection per request, exhausts DB	PgBouncer or application-level pooling
Pool too large	Lock contention, context switching	Formula-based sizing, benchmark
Pool too small	Requests queue waiting for connections	Monitor wait time, increase if needed
Connection leaks	Pool exhausted, app hangs	Leak detection, connection timeout
Session pooling for web apps	Connections held too long	Transaction pooling mode

---

Checklist

• Connection pooler deployed (PgBouncer for PostgreSQL)
• Pool mode: transaction (for web apps)
• Pool size: formula-based, benchmarked
• Connection leak detection enabled
• Monitoring: active connections, wait time, pool utilization
• Query timeout configured
• Connection lifetime recycling (prevent stale connections)
• Serverless: connection pooler required (Neon, Supabase pooler)

---

:::note[Source] This guide is derived from operational intelligence at Garnet Grid Consulting. For database consulting, visit garnetgrid.com. :::