Use Connection Pooling for All Applications

Why This Matters

Postgres connections are expensive - each one consumes 1-3MB of RAM and has significant startup overhead. Without pooling, applications exhaust connections under load, causing crashes and timeouts.

This is especially critical for:

• Serverless functions: Each invocation creates a new connection
• High-traffic APIs: Hundreds of concurrent requests
• Microservices: Multiple services competing for connections

The Problem

-- Each request creates a new connection
-- Application code: db.connect() per request
-- Result: 500 concurrent users = 500 connections = crashed database

-- Check current connections
SELECT count(*) FROM pg_stat_activity;  -- 487 connections!

The Solution

-- Use a pooler like PgBouncer between app and database
-- Application connects to pooler, pooler reuses a small pool to Postgres

-- Configure pool_size based on: (CPU cores * 2) + spindle_count
-- Example for 4 cores: pool_size = 10

-- Result: 500 concurrent users share 10 actual connections
SELECT count(*) FROM pg_stat_activity;  -- 10 connections

Pool Modes

• Transaction mode: Connection returned after each transaction (best for most apps)
• Session mode: Connection held for entire session (needed for prepared statements, temp tables)

Common Mistakes

• Using direct connection in serverless: Always use the pooler URL for Vercel, Netlify, AWS Lambda, etc.
• Pool too large: More connections doesn't mean faster - it increases contention. Start with (cores * 2) + 2.
• Not closing connections: Always return connections to the pool. Use connection lifecycle hooks.