Database connection pooling is a critical optimization for any application that uses a relational database. Without pooling, every database query requires a new TCP connection (3-way handshake + TLS handshake + authentication = 50-200ms overhead). A pool of pre-established connections eliminates this overhead, improving response times and reducing database load. This guide covers why pooling matters, how to size pools correctly, and production tools — essential knowledge for backend engineering interviews.
Why Connection Pooling Matters
Without pooling: each web request opens a new database connection (TCP handshake 1-3ms, TLS handshake 5-30ms, PostgreSQL authentication 2-5ms). Total: 10-40ms per connection setup. For a web server handling 1000 requests per second, that is 1000 new connections per second — each consuming a PostgreSQL backend process (10-20MB of memory). PostgreSQL default max_connections is 100. At 1000 requests/second, connections are exhausted in 100ms. Requests queue, latency spikes, and the application becomes unresponsive. With pooling: a pool of 20 pre-established connections is shared across all requests. A request borrows a connection from the pool (~0ms), executes the query, and returns the connection. The 10-40ms connection setup happens once (when the pool initializes), not per request. 20 connections can serve 1000 requests/second because each connection handles ~50 short queries/second (average query time 20ms). Connection reuse is the core value: amortize the expensive connection setup across thousands of requests.
Application-Level Pooling: HikariCP
HikariCP is the fastest Java connection pool (default in Spring Boot). Configuration: (1) maximumPoolSize — the maximum number of connections in the pool. This limits the number of concurrent database sessions. (2) minimumIdle — minimum number of idle connections maintained. Set equal to maximumPoolSize for best performance (pre-create all connections). (3) connectionTimeout — how long a thread waits for a connection from the pool before throwing an exception. Default: 30 seconds. Set lower (5-10 seconds) to fail fast instead of queuing. (4) idleTimeout — how long an idle connection stays in the pool before being closed. Default: 10 minutes. (5) maxLifetime — maximum lifetime of a connection. Set slightly less than the database wait_timeout to prevent the database from closing the connection unexpectedly. Default: 30 minutes. Python: SQLAlchemy uses a built-in connection pool. Configure pool_size, max_overflow, pool_timeout, and pool_recycle. Node.js: the pg library for PostgreSQL supports pooling with max, idleTimeoutMillis, and connectionTimeoutMillis. Go: database/sql has built-in pooling with SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime.
External Pooling: PgBouncer
PgBouncer is a lightweight connection pooler that sits between the application and PostgreSQL. The application connects to PgBouncer (which accepts thousands of connections with minimal memory) and PgBouncer maintains a small pool of actual PostgreSQL connections. Pooling modes: (1) Session pooling — a client gets a dedicated PostgreSQL connection for the entire session (from connect to disconnect). Same as application-level pooling but managed externally. (2) Transaction pooling — a client gets a PostgreSQL connection only for the duration of a transaction. Between transactions, the connection is returned to the pool. This is the most efficient mode: 100 PostgreSQL connections can serve thousands of concurrent application connections because most connections are idle between transactions. (3) Statement pooling — a client gets a connection for each individual SQL statement. Most aggressive but breaks multi-statement transactions and prepared statements. Why PgBouncer over application-level pooling: (1) Multiple application instances share the same PostgreSQL connection pool. Without PgBouncer: 10 application instances with pool_size=20 each open 200 PostgreSQL connections. With PgBouncer: 10 instances connect to PgBouncer, which maintains only 30-50 PostgreSQL connections. (2) Kubernetes pod scaling: as pods scale up/down, PgBouncer absorbs the connection churn without spiking PostgreSQL connection count.
Pool Sizing: How Many Connections?
The most common mistake: making the pool too large. More connections does not mean better performance. PostgreSQL performance degrades with too many active connections due to: context switching overhead (OS scheduling hundreds of processes), lock contention (more processes competing for the same locks), and shared buffer contention (more processes evicting each other cached pages). Formula: pool_size = 2 * number_of_CPU_cores + number_of_disk_spindles. For a server with 8 cores and SSD: pool_size = 2 * 8 + 1 = 17. Round to 20. This formula accounts for: CPU-bound queries (benefit from parallelism up to the core count) and I/O-bound queries (while one query waits for disk, another uses the CPU). With SSDs (near-zero seek time), the disk spindle term is ~1. Practical guidelines: start with 10-20 connections per database server. Benchmark with realistic load. Increase only if connections are saturated (requests queuing for connections) and the database server CPU and I/O are not saturated. For a read replica serving 5000 queries/second with 5ms average query time: each connection handles 200 queries/second. 5000/200 = 25 connections needed. A pool of 30 provides headroom.
Connection Pool Monitoring
Monitor these metrics to detect pool problems: (1) Active connections — connections currently executing a query. If this equals pool_size, the pool is saturated and requests are queuing. (2) Idle connections — connections waiting for a query. A healthy pool has some idle connections available. Zero idle during peak traffic indicates the pool is too small. (3) Waiting threads — threads/requests waiting for a connection. Non-zero means the pool is exhausted. Increase pool size or optimize slow queries to free connections faster. (4) Connection acquisition time — how long a request waits to get a connection from the pool. Should be near zero (microseconds). If this spikes, the pool is saturated. (5) Connection creation time — how long it takes to establish a new connection. Spikes indicate database overload or network issues. (6) Connection lifetime — how long connections live before being recycled. Very short lifetimes may indicate the database is killing connections (check wait_timeout). PostgreSQL: monitor with pg_stat_activity (active connections, their queries, and wait states). HikariCP exposes pool metrics via JMX or Micrometer. PgBouncer exposes SHOW STATS, SHOW POOLS, and SHOW CLIENTS via the admin console. Alert on: pool saturation (active = max for > 1 minute), connection acquisition timeout, and connection creation failures.