Pattern: Connection pooling for database efficiency

Creating a new database connection per request is expensive (TCP handshake, TLS, auth). Under load, this causes connection exhaustion and slow response times.

Use connection pooling to reuse database connections:

# Python with psycopg pool:
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
conninfo='postgresql://user:pass@localhost/db',
min_size=5, # Keep 5 connections warm
max_size=20, # Never exceed 20
max_idle=300, # Close idle connections after 5 min
max_lifetime=3600, # Recycle connections after 1 hour
)

# Usage:
with pool.connection() as conn:
with conn.cursor() as cur:
cur.execute('SELECT * FROM users WHERE id = %s', (user_id,))
return cur.fetchone()
# Connection automatically returned to pool

Sizing guidelines:

• min_size: Number of concurrent DB operations during quiet periods
• max_size: connections = (core_count * 2) + disk_spindles

(For SSD: core_count * 2 + 1 is a good start)

• PostgreSQL max_connections default is 100
• Each pool per service instance counts against max_connections
• If 5 service instances with max_size=20: need 100+ max_connections

For Node.js (pg):
const { Pool } = require('pg');
const pool = new Pool({ max: 20 });
const result = await pool.query('SELECT...', [params]);

Connection pooling eliminates the overhead of creating connections per request and limits total connections to prevent database overload.