Connection Pooling
matureSummary
Maintain a pool of reusable database connections shared across application threads, eliminating the per-request cost of establishing new connections.
Problem
Direct per-request database connections become a bottleneck at moderate concurrency: consuming server memory and adding establishment latency before any query runs.
Description
Each new database connection requires a TCP handshake, TLS negotiation, and PostgreSQL authentication: typically 5–50ms of latency and significant server memory. Under high concurrency, these costs accumulate and connection count becomes the bottleneck before query throughput does.
A connection pool (PgBouncer, HikariCP, SQLAlchemy pool) maintains N open connections and routes queries through them. Application requests borrow a connection from the pool, execute, and return it. Pool size is tuned to match the database's effective concurrency limit.
Tradeoffs
Eliminates per-request connection cost entirely
Enables thousands of concurrent clients from a small server pool
Additional process (PgBouncer) to configure and monitor
Transaction-mode pooling breaks per-connection prepared statements
When to use
Application creates database connections per request
Per-request connection cost compounds under concurrency
Database connection count approaches or exceeds 200
PostgreSQL performance degrades above a few hundred direct connections
Application is stateless and horizontally scaled
Each app replica opens its own connections without pooling
When not to use
Application has a single process with persistent long-lived connections
Long-lived connections in a single process don't need pooling
Connection latency is not a bottleneck
Add pooling when connection count is the measured constraint
Operational Requirements
Configure pool_size to match database max_connections minus DBA headroom
Typical: pool_size = max_connections * 0.8
Monitor pool saturation (waiting clients)
Pool saturation causes request queuing and latency spikes
Disable prepared statements in transaction-mode pooling
PgBouncer transaction mode resets session state between transactions
Characteristics
Technologies
Canonical
Alternatives
Relationships
Evolves to
Complements
Basis
Essential, universally applied PostgreSQL pattern with zero ambiguity
Related Architecture Knowledge
Outbound: this entity affects
A connection pool bounds the total database connections an application can open, preventing connection storms during traffic spikes and protecting the database server from exceeding its connection limit.
Tradeoffs
- ·Pooler becomes a new single point of failure if not replicated
- ·Transaction-mode pooling incompatible with LISTEN/NOTIFY or explicit transactions across requests
- ·Pool saturation under extreme load shifts the bottleneck to the pool queue
Inbound: affects this entity
PgBouncer is the standard PostgreSQL connection pooler, implementing the connection pooling pattern by multiplexing many client connections onto a smaller pool of server connections.
Full relationship →Read-heavy APIs generate large numbers of short-lived database connections. Connection pooling reduces per-request connection overhead and allows the database to serve far more concurrent requests than its max_connections limit.
Tradeoffs
- ·Pool idle connections consume database resources even under low load
- ·Under sustained load, pool queue wait times add latency
Used In Architecture Scenarios
Multi-Tenant SaaS
A multi-tenant SaaS architecture where multiple customers are served from a shared deployment, with PostgreSQL row-level security providing logical tenant isolation, Redis delivering per-tenant caching, and connection pooling managing the aggregate connection demand across tenant workloads. Tenant isolation, resource fairness, and operational simplicity are the three competing forces this architecture must balance.
Read-Heavy Application
A standard SaaS API architecture optimized for read-dominant workloads. PostgreSQL serves as the primary data store, Redis provides a caching layer for hot data, connection pooling bounds database concurrency, and read replicas scale read throughput without scaling write capacity.
Realtime Collaboration
An architecture for multi-user document editing where users see each other's changes in near real-time. PostgreSQL provides durable state persistence, Redis coordinates ephemeral session state and pub/sub for live change propagation, and connection pooling protects the database from WebSocket-induced connection churn.
Write-Heavy Application
A high-volume transactional write architecture anchored on PostgreSQL, where write throughput, durability guarantees, and audit completeness must coexist. The outbox pattern ensures reliable event publishing to Kafka without two-phase commit, and WAL-based CDC provides a durable change log that can reconstruct system state. Connection pooling via PgBouncer bounds connection overhead at the database layer.