Part 191: Connection Pooling: PgBouncer, HikariCP & Starvation Prevention
Why opening 5,000 direct database connections crashes your server, and how connection poolers fix it.
1. The Cost of a Database Connection
Each connection in PostgreSQL spawns a dedicated operating system process consuming 10MB to 20MB of RAM: - 1,000 idle connections consume 20GB of RAM just holding sockets open! - Operating system context switching between 1,000 processes collapses CPU cache efficiency.
2. PgBouncer Pooling Modes
PgBouncer acts as a lightweight reverse proxy sitting between application servers and PostgreSQL:
| Mode | Behavior | Best Use Case |
|---|---|---|
| Session Pooling | Allocates a server connection for the entire duration of client socket connection. | Legacy apps relying on temporary tables or SET commands. |
| Transaction Pooling (Recommended) | Allocates a server connection only while a transaction is active. Connection returned to pool immediately upon COMMIT! |
High-scale web applications (allows 10,000 clients on 50 DB connections). |
| Statement Pooling | Allocates connection per single SQL statement. | Multi-statement transactions are not supported. |
3. Sizing Your Connection Pool
The famous HikariCP formula: $$\text{Max Connections} = (\text{Core Count} \times 2) + \text{Effective Spindle Count}$$
On an 8-core CPU server with NVMe SSD storage: $$\text{Max Connections} = (8 \times 2) + 1 = 17 \text{ connections!}$$
Key Takeaways
- Less is more: 25 properly pooled connections process 5x more requests per second than 1,000 unpooled connections!
In Part 192, we explore High Availability Topologies: Galera, Patroni & Multi-Region!