1A serverless application with 500 concurrent Lambda invocations each opens a direct PostgreSQL connection. The database's max_connections is set to 100. What is the best architectural fix?
Vetora📈Scalability
Connection Pooling
Understand how connection pooling reuses database connections across requests to reduce overhead, improve latency, and prevent connection exhaustion in high-traffic applications.
Overview
Connection pooling is a technique that maintains a pool of pre-established database connections that can be reused across multiple requests, rather than opening and closing a new connection for each individual database operation. Establishing a database connection is expensive: it involves a TCP handshake, TLS negotiation (if encrypted), authentication, and session initialization. For a typical PostgreSQL connection, this setup takes 20-50 milliseconds -- an eternity when your target request latency is under 100 milliseconds.
A connection pool pre-creates a configurable number of connections during application startup and keeps them alive. When application code needs to query the database, it borrows a connection from the pool, executes the query, and returns the connection to the pool for reuse by the next request. This amortizes the connection setup cost across thousands of requests instead of paying it once per request.
Connection pooling also solves the connection exhaustion problem. Database servers have a maximum number of concurrent connections they can handle (PostgreSQL's default is 100, though this can be increased). In a horizontally-scaled system with 50 application instances, each opening 10 connections, the database would need to handle 500 concurrent connections. Without pooling, traffic spikes that increase the number of concurrent requests can quickly exhaust the database's connection limit, causing connection errors and request failures. A connection pool with proper limits bounds the number of connections to the database regardless of request volume.
Modern architectures often use connection pooling at multiple levels. Application-level pools (built into ORMs and database drivers) manage connections within a single process. External connection poolers like PgBouncer (for PostgreSQL) or ProxySQL (for MySQL) sit between application instances and the database, aggregating connections from many application instances into a smaller number of database connections. This two-tier approach is especially valuable in serverless and container-based environments where thousands of short-lived processes might each need database access.
Key Points
- 1Opening a new database connection for each request typically takes 20-50ms for the TCP handshake, TLS setup, and authentication. Connection pooling eliminates this per-request overhead by reusing pre-established connections.
- 2Pool sizing is critical: too few connections causes request queuing and increased latency; too many connections can overwhelm the database server with context-switching overhead. A common formula is pool_size = (number_of_cores * 2) + disk_spindles for the database server.
- 3Connection validation (health checks) ensures that borrowed connections are still alive and functional. Stale connections (from network timeouts or database restarts) must be detected and replaced to avoid query failures.
- 4External connection poolers (PgBouncer, ProxySQL) aggregate connections from many application instances, reducing total database connections while supporting high application-level concurrency. This is essential for serverless architectures.
- 5Connection pools should implement timeouts: a checkout timeout (how long to wait for an available connection before failing) and a connection lifetime (maximum age before a connection is recycled to prevent resource leaks).
- 6Transaction-level pooling (PgBouncer's transaction mode) returns connections to the pool after each transaction rather than holding them for the session, dramatically improving connection utilization.
Simple Example
The Taxi Stand Analogy
Think of a busy airport taxi stand. Without pooling, each arriving passenger calls a taxi company and waits 20 minutes for a taxi to arrive from the depot (establishing a new connection). With pooling, a fleet of taxis waits at the stand at all times (the pool). When a passenger arrives, they hop into the next available taxi immediately. When the ride is done, the taxi returns to the stand (connection returned to pool) instead of driving back to the depot. The airport limits the stand to 20 taxis (pool size) to avoid overcrowding the pickup lane (database connection limit). If all taxis are occupied, new passengers wait in a queue (checkout timeout) until one returns.
Real-World Examples
Heroku
Heroku's PostgreSQL managed service enforces strict connection limits (20 connections for the free tier, 500 for premium). Heroku strongly recommends using PgBouncer as a connection pooler because their dyno-based architecture can spin up many short-lived processes. Without PgBouncer, a deployment of 10 web dynos with 4 workers each would consume 40 connections, quickly approaching the limit. PgBouncer multiplexes these into a handful of actual database connections.
GitHub
GitHub uses ProxySQL as a connection pooler in front of their MySQL database clusters. With thousands of application servers and background workers, direct connections would overwhelm the database. ProxySQL aggregates connections and also provides query routing (directing reads to replicas), connection throttling, and automatic failover when a database instance becomes unavailable.
Shopify
Shopify operates one of the largest Ruby on Rails deployments and uses connection pooling extensively in ActiveRecord, Rails' ORM. During Black Friday/Cyber Monday, their application tier scales to thousands of instances. Each instance's connection pool is sized carefully to balance per-instance concurrency against total database connection capacity. They use ProxySQL to manage the connection aggregation layer between application instances and their sharded MySQL databases.
Trade-Offs
| Aspect | Description |
|---|---|
| Pool Size vs Database Load | A larger pool allows more concurrent database operations but increases the number of active connections on the database server. Database servers perform optimally with a moderate number of connections; too many cause excessive context switching and memory consumption. The optimal pool size depends on the database's concurrency capabilities. |
| Connection Reuse vs Isolation | Reusing connections means that session-level settings (search_path, transaction isolation level, temporary tables) from a previous request can leak into subsequent requests. The pool must reset connection state on checkout or use transaction-level pooling to ensure isolation between requests. |
| Checkout Timeout vs Availability | A short checkout timeout fails fast when the pool is exhausted, giving the caller a clear error signal. A long timeout queues requests, increasing latency but potentially serving all requests if connections free up soon. The right choice depends on whether the application prefers fast failure or degraded-but-functional behavior. |
| External Pooler Overhead | External connection poolers (PgBouncer, ProxySQL) add a network hop and processing latency (typically 0.1-0.5ms) to every query. This is negligible for most workloads but can matter for latency-sensitive applications that issue many small queries per request. |
Case Study
Figma's Connection Pooling at Scale with PgBouncer
Scenario
Figma's real-time collaborative design tool generates a high volume of database queries per second. As the user base grew, their horizontally-scaled application tier spawned thousands of processes, each maintaining its own connection pool to PostgreSQL. The total connection count exceeded PostgreSQL's ability to handle concurrent connections efficiently, causing increased query latency and occasional connection refusals during traffic spikes.
Solution
Figma deployed PgBouncer in transaction-level pooling mode between their application tier and PostgreSQL. Instead of each application process holding 5-10 dedicated connections, all processes share a smaller pool of PgBouncer connections. PgBouncer maintains a pool of real PostgreSQL connections (sized to the database's optimal concurrency) and multiplexes application-level connections through them. A connection is assigned to a specific application process only for the duration of a transaction, then returned to the pool for another process to use.
Outcome
The total number of active PostgreSQL connections dropped from over 2,000 to approximately 200, well within PostgreSQL's optimal operating range. Average query latency improved by 15% due to reduced database-level contention. Connection-related errors during traffic spikes were eliminated entirely. The PgBouncer layer added less than 0.3ms of overhead per query, which was negligible compared to the latency savings from better database connection management.
Common Mistakes
- ⚠Setting the connection pool size equal to the maximum concurrent requests. Each request may hold a connection for only a fraction of its total processing time. A pool size of 10-20 can often support hundreds of concurrent requests if queries are fast and connections are returned promptly.
- ⚠Not implementing connection validation. Database connections can become stale due to network timeouts, database restarts, or idle connection cleanup. Borrowing a stale connection results in a failed query. Configure the pool to validate connections on checkout with a lightweight query (SELECT 1).
- ⚠Opening a new pool per request in serverless environments. Lambda functions and similar short-lived processes should use an external connection pooler (RDS Proxy, PgBouncer) rather than creating application-level pools that are destroyed when the process terminates.
- ⚠Holding connections during long-running application logic. If your code borrows a connection, performs a slow API call, then queries the database, the connection is held idle during the API call. Borrow connections only when you are about to execute database operations and return them immediately after.
Related Concepts
See Connection Pooling in action
Explore system design templates that use connection pooling and run traffic simulations to see how these concepts perform under real load.
Browse TemplatesObserve connection pool exhaustion under high concurrency
Metrics to watch
pool_utilization_pctwait_time_mstimeout_rate_pctthroughput_rps
Test Your Understanding
2PgBouncer's transaction-level pooling mode returns connections to the pool after each transaction completes. What PostgreSQL feature becomes unavailable in this mode?