Module 1 - Data Storage
Connection Pooling
Reuse database connections to avoid the overhead of creating new ones.
1The Phone Line Analogy
Simple Analogy
Without pooling: Every call requires setting up a new phone line, then tearing it down after. Expensive and slow.
With pooling: Keep 10 phone lines open. When someone needs to make a call, grab an available line. When done, put it back for others to use.
With pooling: Keep 10 phone lines open. When someone needs to make a call, grab an available line. When done, put it back for others to use.
2Why Connection Pooling?
Creating a database connection is expensive: TCP handshake, SSL negotiation, authentication, memory allocation. A single connection can take 20-50ms to establish.
Without Pooling
Open connection: ~30ms
Execute query: ~5ms
Close connection: ~5ms
Total: ~40ms per query
With Pooling
Get from pool: ~0.1ms
Execute query: ~5ms
Return to pool: ~0.1ms
Total: ~5.2ms per query
3How It Works
1
Initialize Pool
Create N connections at startup (min pool size)
2
Borrow Connection
Application requests connection, pool provides one
3
Execute Query
Application uses connection for database operations
4
Return Connection
Connection goes back to pool, ready for reuse
4Pool Configuration
Min Pool Size
Connections kept warm at all times
5-10
Max Pool Size
Maximum concurrent connections
20-100
Connection Timeout
How long to wait for available connection
30s
Idle Timeout
Close idle connections after this time
10-30min
Max Lifetime
Recycle connections to prevent stale state
30-60min
Don't set max pool size too high. Databases have connection limits. 100 app servers × 100 connections = 10,000 connections!
5Popular Tools
PgBouncer
Lightweight connection pooler for PostgreSQL. Sits between app and DB.
HikariCP
Fast, production-ready pool for Java applications.
ProxySQL
MySQL proxy with connection pooling and query caching.
Built-in Pools
Most ORMs (Prisma, SQLAlchemy, TypeORM) have built-in pooling.
6Key Takeaways
1Connection pooling reuses connections to avoid creation overhead
2A connection can take 20-50ms to create-pooling eliminates this
3Configure min/max pool size based on workload
4Use external poolers (PgBouncer) for serverless/high-scale
5Watch for connection leaks-always release connections
?Quiz
1. Why is creating a new DB connection expensive?
2. What happens if you set max pool size too high with many app servers?