Connection Pooling

Connection pooling reuses a set of pre-established database connections instead of creating a new connection for every query, dramatically reducing latency and resource consumption.

What It Really Means

Opening a database connection is surprisingly expensive. A PostgreSQL connection involves TCP handshake (1 round-trip), TLS handshake (2 round-trips), authentication (1 round-trip), and process forking on the server side. That is 50-200ms of setup time and 5-10MB of server memory per connection.

If your application opens a new connection for every HTTP request, and you handle 1,000 requests per second, you are creating 1,000 connections per second. The database server spends more time managing connections than executing queries. This is why connection pooling exists — maintain a pool of open connections and check them out when needed.

Connection pooling is one of the most impactful and least understood performance optimizations. Misconfigured pools cause connection exhaustion, deadlocks, and mysterious timeouts in production. Getting the pool size right is critical.

How It Works in Practice

Connection Lifecycle Without Pooling

Connection Lifecycle With Pooling

Pool Sizing Formula

The optimal pool size is not "as many as possible." PostgreSQL's default max_connections is 100 for good reason — each connection consumes memory and CPU for process scheduling.

A widely cited formula from the HikariCP documentation:

For a 4-core server with SSDs (spindle count is effectively 1):

This seems small, but a pool of 9 connections can handle thousands of requests per second if queries are fast (5-50ms each). With 9 connections and 10ms average query time, throughput is 900 queries/second.

External Connection Pooler: PgBouncer

In serverless or high-connection environments (AWS Lambda, Kubernetes with many pods), each application instance might need its own pool. 50 pods with 10 connections each = 500 connections. PgBouncer sits between application instances and the database, multiplexing hundreds of application connections into a smaller pool of database connections.

Implementation

Application-level pooling (Python with psycopg2):

PgBouncer configuration:

Trade-offs

Pool modes (PgBouncer):

Benefits:

• Eliminates connection setup overhead (50-200ms saved per request)
• Limits database server resource usage
• Handles connection spikes gracefully (queue requests instead of crashing)

Risks:

• Pool exhaustion: All connections checked out, new requests block or timeout
• Connection leaks: Application fails to return connection to pool (use try/finally)
• Stale connections: Connections in pool may be closed by the server (configure health checks)

Common Misconceptions

• "Bigger pool = better performance" — A pool of 200 connections to a 4-core database will cause context switching overhead and actually reduce throughput compared to a pool of 10.
• "Connection pooling is only for high-traffic apps" — Even a low-traffic app benefits from avoiding the 50-200ms connection setup cost on every request.
• "The ORM handles pooling automatically" — Some ORMs use connection pooling by default (SQLAlchemy, HikariCP). Others do not. Verify your ORM's connection management.
• "PgBouncer replaces application-level pooling" — Use both. Application-level pools manage local connection reuse. PgBouncer manages aggregate connections across all application instances.

How This Appears in Interviews

1. "Your application occasionally times out connecting to the database" — Pool exhaustion. Check for connection leaks, increase pool size cautiously, add connection timeout monitoring.
2. "How do you handle database connections in a serverless environment?" — External pooler like PgBouncer or RDS Proxy. Serverless functions spin up and down too frequently for connection pools.
3. "Your database has 500 active connections but only 4 cores" — Over-provisioned pools across application instances. Use an external pooler to multiplex.
4. "How do you size a connection pool?" — Explain the formula, why smaller is often better, and how to benchmark.

Related Concepts

• Read Replicas — connection pools route to primary vs replica
• Database Transactions — pool mode determines transaction behavior
• Database Partitioning — sharded databases need pools per shard
• Tail Latency — pool exhaustion is a common cause of p99 latency spikes
• System Design Interview Guide
• Algoroq Pricing — access all concept deep-dives