Chapter 34: Connection Pooling and Application Integration
Each database connection consumes memory (~10MB in PostgreSQL). With 100 microservice instances each opening 10 connections, you have 1000 connections consuming 10GB of RAM just for connection overhead.
The Problem
Without pooling:
100 app instances × 10 connections = 1000 connections to PostgreSQL
Each connection = ~10MB RAM = 10GB wasted
PostgreSQL max_connections default = 100 → EXHAUSTED!
With pooling (PgBouncer):
100 app instances → PgBouncer (manages pool of 50 real connections) → PostgreSQL
App sees 1000 "connections" but only 50 are real.
PgBouncer
Lightweight connection pooler that sits between your app and PostgreSQL:
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
pool_mode = transaction # release connection after each transaction
max_client_conn = 1000 # accept up to 1000 app connections
default_pool_size = 50 # only 50 real PostgreSQL connectionsPool Modes
| Mode | Behavior | Use When |
|---|---|---|
| Session | Connection held for entire session | Apps using session features (LISTEN/NOTIFY, temp tables) |
| Transaction | Connection returned after each transaction | Most web apps (recommended) |
| Statement | Connection returned after each statement | Simple autocommit workloads only |
Application-Side Pooling
// Most ORMs/drivers have built-in connection pools:
// Go: sql.DB has built-in pool
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
// Java: HikariCP
// Python: SQLAlchemy pool_size parameter💡 Connection Pool Sizing
Optimal pool size ≈ (number of CPU cores × 2) + number of disks. For a 4-core server with SSD: ~10 connections is often optimal. More connections = more context switching = slower. Counterintuitive but true.
Key Takeaways
- Each DB connection costs ~10MB RAM. Don't open thousands.
- PgBouncer: external pooler, multiplexes many app connections to few DB connections
- Transaction mode is best for most applications
- Optimal pool size is small (cores × 2 + disks), not large
- Always set connection limits and timeouts