The Day Django Killed a Production Database
In 2019, a Python Django service at a mid-size fintech handled 200 requests per second. Every request opened a new PostgreSQL connection, ran a query, and closed it. At 200 req/s, that was 200 TCP handshakes, 200 TLS negotiations, 200 authentication rounds - every single second. The database's max_connections was set to 100. When traffic hit 300 req/s during a marketing campaign, PostgreSQL started refusing connections. The entire platform went down for 45 minutes.
The fix took 10 minutes: add a connection pool with 20 connections. Those 20 pooled connections handled 3,000 req/s with room to spare. The team later said it was the highest ROI change they ever made.
The True Cost of a New Connection
What happens when you open a new database connection:
App Server Database Server
| |
|--- TCP SYN --------------------------->| }
|<-- TCP SYN-ACK -----------------------| } TCP 3-way handshake
|--- TCP ACK --------------------------->| } ~1 RTT (0.5-5ms)
| |
|--- TLS ClientHello ------------------->| }
|<-- TLS ServerHello + Cert ------------| } TLS negotiation
|--- TLS Key Exchange ------------------>| } ~2 RTTs (1-10ms)
|<-- TLS Finished ----------------------| }
| |
|--- Auth (username/password) ---------->| }
|<-- Auth OK + session params -----------| } ~1 RTT (0.5-5ms)
| |
|--- SELECT * FROM users WHERE id=42 --->| } Actual query
|<-- Result row ------------------------| } ~2ms
| |
Total: 15-25ms overhead for a 2ms query!
At 1000 req/s = 15,000-25,000ms wasted PER SECONDHow Connection Pooling Works
Without Pool (per-request connections):
Request 1: [Connect 15ms] [Query 2ms] [Close] = 17ms
Request 2: [Connect 15ms] [Query 2ms] [Close] = 17ms
Request 3: [Connect 15ms] [Query 2ms] [Close] = 17ms
Total: 51ms, 3 connections created and destroyed
With Pool (reused connections):
Startup: [Connect 15ms] [Connect 15ms] Pool ready (2 conns)
Request 1: [Borrow <0.1ms] [Query 2ms] [Return] = 2.1ms
Request 2: [Borrow <0.1ms] [Query 2ms] [Return] = 2.1ms
Request 3: [Borrow <0.1ms] [Query 2ms] [Return] = 2.1ms
Total: 6.3ms, 2 connections reused forever
Performance gain: 8x faster, 0 connection overhead per requestPool Sizing: The Most Misunderstood Setting
Most developers set pool size = expected concurrent users. This is wrong. The PostgreSQL wiki formula: optimal connections = (2 x CPU cores) + number_of_disks. For a 4-core database with 1 SSD, that is 9 connections. Not 100. Not 500. Nine.
Pool Sizing Guide:
Database Server Cores Disks Optimal Pool Common Mistake
--------------- ----- ----- ------------ ---------------
Small (t3.medium) 2 1 5 50
Medium (m5.xlarge) 4 1 9 100
Large (r5.2xlarge) 8 1 17 200
XL (r5.4xlarge) 16 1 33 500
With 5 app instances sharing 1 database:
Per-instance pool = Optimal Pool / Number of Instances
Example: 17 total / 5 instances = 3-4 per instance
Why fewer is better:
- Each connection = ~10MB RAM on PostgreSQL
- 500 connections = 5GB just for connection overhead
- More connections = more CPU context switching
- More lock contention on shared data structures
- HikariCP benchmarks: 50 connections performed WORSE than 10Connection Pool Architecture
Application-Level Pool (HikariCP, SQLAlchemy, c3p0):
App Instance 1 App Instance 2
[Pool: 5 conns] [Pool: 5 conns]
||||| |||||
vvvvv vvvvv
============== Database (max_connections=100) ==============
Problem: 20 app instances x 5 conns = 100 connections
Add instance 21 = connection refused!
External Pool (PgBouncer, ProxySQL, RDS Proxy):
App Instance 1 App Instance 2 App Instance 3
[5 conns] [5 conns] [5 conns]
\\\\\ ||||| /////
\\\\\ ||||| /////
=============================================
| PgBouncer (multiplexer) |
| Client connections: 500 (lightweight) |
| Server connections: 20 (real PostgreSQL)|
=============================================
||||||||||||||||||||
============== Database (max_connections=100) ==============
PgBouncer modes:
- Session: 1:1 mapping (least benefit)
- Transaction: conn returned after each txn (best for most apps)
- Statement: conn returned after each statement (breaks multi-stmt txns)Connection Pool Settings Explained
min_size (2-5): minimum connections kept alive even when idle. Avoids cold start latency.
max_size (CPU formula): maximum connections. Never exceed database max_connections / number_of_app_instances.
idle_timeout (300s): close idle connections after this long. Prevents stale connections.
max_lifetime (1800s): close connections after this age regardless. Prevents DNS caching issues and memory leaks.
checkout_timeout (5s): how long to wait for a free connection. Fail fast rather than queue indefinitely.
validation_query ('SELECT 1'): verify connection is alive before handing it out. Prevents 'connection reset' errors.
Interview Tip
Interviewers love asking 'how would you handle 10K concurrent users with one database?' The answer involves connection pooling with the PostgreSQL formula (2 x cores + disks), plus an external pooler like PgBouncer in transaction mode for multiplexing. Mention that more connections actually hurt performance due to context switching - cite the HikariCP benchmark showing 50 connections being slower than 10. If asked about microservices, explain why PgBouncer is essential: 20 services x 10 connections = 200 connections vs PgBouncer multiplexing 200 app connections into 20 database connections.
Key Takeaway
Connection pooling eliminates the repeated cost of establishing connections. Size your pool based on database capacity (not request volume) using the formula: 2 x CPU cores + disks. A few well-managed connections outperform hundreds of poorly managed ones. Use PgBouncer for multi-service architectures. Every production application should use connection pooling.