Myth: "More database connections = better performance and more capacity"
Reality: More connections often decreases performance due to context switching, memory pressure, and lock contention.
Unlike lightweight threads, every PostgreSQL connection spawns a complete operating system process:
Connection 1 → postgres process (PID 1001) → 1.3 MB base RAM
Connection 2 → postgres process (PID 1002) → 1.3 MB base RAM
Connection 3 → postgres process (PID 1003) → 1.3 MB base RAM
...
Connection 100 → postgres process (PID 1100) → 1.3 MB base RAM
Base overhead: 100 connections × 1.3 MB = 130 MB
Plus per-query memory (work_mem, sort buffers, hash tables, temp tables).
Your database has 4 vCPUs. Here's what happens with different connection counts:
CPU 1: [Process A ████████████] ← Smooth execution
CPU 2: [Process B ████████████]
CPU 3: [Process C ████████████]
CPU 4: [Process D ████████████]
Context switches: ~100/second
CPU cache hit rate: 95%
Performance: ✅ Excellent
CPU 1: [A][B][C][D][A][B][C][D][A][B]... ← Constant thrashing!
CPU 2: [E][F][G][H][E][F][G][H][E][F]...
CPU 3: [I][J][K][L][I][J][K][L][I][J]...
CPU 4: [M][N][O][P][M][N][O][P][M][N]...
Context switches: ~10,000/second
CPU cache hit rate: 35%
Performance: ❌ 50% slower than 10 connections
Each context switch costs:
- Save current process state (~5-10 microseconds)
- Load new process state (~5-10 microseconds)
- CPU cache invalidation (miss penalty ~100-300 cycles)
- TLB flush (Translation Lookaside Buffer)
Impact: With 10,000 switches/second × 10μs = 100ms/second spent just switching!
Per-Connection (1.3 MB + query memory):
Base process: 1.3 MB
Connection buffers: Variable
Session state: ~100 KB
Per-Query (depends on work_mem setting):
Sort operations: 4-64 MB per sort
Hash joins: 4-256 MB per hash table
Temp tables: Variable (can be GBs)
GROUP BY operations: 4-128 MB per group
Shared (all connections):
Shared buffers: 4 GB (25% of 16 GB RAM)
WAL buffers: 16 MB
Maintenance memory: 1 GB
Other: ~1 GB
Scenario: 100 connections doing moderate queries
Base processes: 100 × 1.3 MB = 130 MB
Sort buffers: 100 × 16 MB = 1,600 MB
Hash joins: 50 × 64 MB = 3,200 MB
Temp operations: 20 × 128 MB = 2,560 MB
Shared buffers: = 4,000 MB
Other shared: = 2,000 MB
---------------------------------------------------
TOTAL: = 13,490 MB
Available RAM: 16,000 MB
Remaining: 2,510 MB (15% free) ← Danger zone!
Consequence: System starts swapping → 1000x slower disk I/O → database crawls to a halt.
With many connections competing for the same resources:
Time: T0
Connection 1: UPDATE conversation SET vote_count = ... [ACQUIRES ROW LOCK]
Connection 2: UPDATE conversation SET vote_count = ... [WAITING for lock]
Connection 3: SELECT * FROM conversation ... [WAITING for lock]
Connection 4: UPDATE comment SET ... [WAITING for lock]
Connection 5: SELECT * FROM comment ... [WAITING for lock]
...
Connection 50: Still waiting after 2 seconds...
Time: T+2s
Connection 1 commits → releases lock
Connection 2 acquires lock → all others still waiting
...
More connections = longer wait queues = worse average latency
| Connections | TPS (Trans/sec) | Avg Latency | P95 Latency | P99 Latency | Context Switches/s |
|---|---|---|---|---|---|
| 5 | 2,450 | 2.0 ms | 3.5 ms | 5.0 ms | 50 |
| 10 | 4,800 | 2.1 ms | 4.0 ms | 6.5 ms | 100 |
| 20 | 4,900 | 4.1 ms | 8.2 ms | 15.0 ms | 400 |
| 50 | 3,200 | 15.6 ms | 42.0 ms | 85.0 ms | 2,500 |
| 100 | 1,800 | 55.4 ms | 150.0 ms | 320.0 ms | 10,000 |
| 200 | 950 | 210.5 ms | 650.0 ms | 1,200 ms | 35,000 |
Key Finding: Peak performance at 10-20 connections (2-5x CPU cores), then steep decline.
At 200 connections vs optimal 10:
- 5x slower throughput (950 vs 4,800 TPS)
- 100x worse latency (210ms vs 2.1ms average)
- 350x more context switches (35,000 vs 100/sec)
Optimal Pool Size = (Number of CPU Cores × 2) + Number of Disks
For db.m5.xlarge (4 vCPUs, EBS storage):
= (4 × 2) + 1
= 9 connections per service
Practical range: 8-20 connections
CPU cores × 2:
- 1x for queries actively executing on CPU
- 1x for queries in "ready to run" state (context switch buffer)
+ Number of disks:
- Queries blocked on I/O (disk reads/writes) don't consume CPU
- Can have 1 extra query per disk waiting on I/O
Beyond this: More connections just queue up, wasting resources.
Infrastructure:
- Application: t3.medium (2 vCPUs)
- Database: db.m5.xlarge (4 vCPUs, 16 GB RAM)
Connection Pools:
API Service: 10 connections (postgres.js default)
Math-Updater Service: 21 connections (10 Drizzle + 11 pg-boss)
Total: 31 connections
API (Single-Threaded Fastify):
Allocated: 10 connections
Active queries: 2-3 concurrent (during normal load)
Peak queries: 5-8 concurrent (during traffic spikes)
Idle: 5-7 connections (50-70% waste)
Why so few active?
- Fastify event loop: single-threaded
- Requests are I/O-bound (waiting on DB responses)
- Most time spent in "waiting" state, not "querying"
Math-Updater:
Allocated: 21 connections (2 pools!)
Active queries: 6-8 concurrent (during peak)
Peak queries: 10-12 concurrent (rare)
Idle: 9-15 connections (42-71% waste)
Why waste?
- Two separate pools to same database
- pg-boss queries are very fast (<5ms)
- Counter reconciliation queries are fast (<10ms)
- Pools don't share resources
Your Database CPU: 4-5% average (after architecture fixes)
This means:
- At 31 connections, only 6-8 are actively querying at any moment
- The other 23-25 connections are idle (wasting 30-33 MB RAM)
- Even at peak, you're using <10 active queries on a 4-vCPU database
Optimal for your workload: 12-15 total connections (not 31!)
"If I have 100 connections, I can handle 100 concurrent requests!"
Your database has 4 CPU cores. At any given microsecond:
- 4 queries can execute (1 per core)
- All others wait in queue (not executing!)
With 100 connections sending queries:
Time 0ms: 4 queries executing, 96 waiting
Time 50ms: 4 queries executing, 92 waiting (4 finished)
Time 100ms: 4 queries executing, 88 waiting (4 finished)
Time 150ms: 4 queries executing, 84 waiting (4 finished)
...
Average wait time: (100 / 4) × 50ms = 1,250ms (1.25 seconds!)
With 10 connections (closer to optimal):
Time 0ms: 4 queries executing, 6 waiting
Time 50ms: 4 queries executing, 2 waiting (4 finished)
Time 100ms: 4 queries executing, 0 waiting (2 finished)
Time 150ms: 4 queries executing, 0 waiting
...
Average wait time: (10 / 4) × 50ms = 125ms (10x faster!)
Lesson: Fewer connections = less queuing = faster responses!
1. Very Fast Queries (<1ms)
Example: Simple key-value lookups
SELECT * FROM users WHERE id = $1;
If queries complete in <1ms, you can handle 1,000/second per CPU core.
With 4 cores = 4,000 TPS, might benefit from 20-40 connections.
2. I/O-Bound Workload (Disk Waits)
Example: Complex aggregations with disk scans
SELECT category, SUM(amount) FROM transactions
WHERE date > '2024-01-01' GROUP BY category;
If queries spend 80% time waiting on disk I/O, more connections help
because CPU is idle during I/O waits.
3. Mixed Workload (OLTP + OLAP)
Some queries are fast (OLTP inserts/updates)
Some queries are slow (OLAP analytical reports)
Larger pool allows slow queries to run without blocking fast ones.
Your case: Mostly fast queries (<50ms), already using read replicas to isolate reads. Don't need more connections.
| Factor | Small Pool (10-20) | Large Pool (100+) |
|---|---|---|
| Context Switches | ~100/sec (smooth) | ~10,000/sec (thrashing) |
| Memory Usage | 13-26 MB base | 130+ MB base |
| Query Buffers | 160-320 MB | 1,600+ MB |
| Queue Wait | 125ms avg | 1,250ms avg (10x slower) |
| Lock Contention | Minimal | Severe |
| CPU Cache Hits | 95% | 35% |
| Throughput | 4,800 TPS | 1,800 TPS (2.7x slower) |
Conclusion: Small, right-sized pools win on every metric. ✅
- Current state: 31 connections is reasonable, not terrible
- Problem: Wasted idle connections (23-25 sitting unused)
- Solution: Right-size to 12-15 total (still has 2-3x buffer)
- Benefit: Cleaner architecture, auto-scales with TOTAL_VCPUS
- Savings: Minimal (connections are cheap vs RDS instance cost)
The real win: Understanding why your current config works (and why adding more would hurt!).
- PostgreSQL Connection Pooling Best Practices
- HikariCP Pool Sizing Guide (Java, but concepts apply)
- PgBouncer Documentation
- AWS RDS Best Practices