Databaseintermediate

Database Connection Pooling

Maintaining a reusable pool of database connections to avoid the overhead of establishing new connections for every query.

Also known as: connection pool, pool management, PgBouncer

Description

Establishing a database connection is expensive -- it involves TCP handshake, TLS negotiation, authentication, and backend process creation (in PostgreSQL, each connection spawns a separate OS process consuming ~5-10 MB of RAM). Connection pooling maintains a set of pre-established connections that application threads borrow and return, amortizing the setup cost across many requests.

Connection pools operate in several modes. In session pooling, a client gets a dedicated connection for its entire session. In transaction pooling (the most common for web applications), a connection is assigned only for the duration of a transaction and returned immediately after. In statement pooling, connections are rotated after each individual statement. Transaction pooling provides the best utilization for typical request/response workloads but prohibits session-level features like prepared statements and LISTEN/NOTIFY unless the pooler supports them.

Pool sizing is critical. Too few connections cause request queuing and timeouts; too many connections overwhelm the database with context switching and memory pressure. A good starting formula for PostgreSQL is pool_size = (core_count * 2) + effective_spindle_count. For a typical 4-core server, this means 9-10 connections. External poolers like PgBouncer or pgcat can multiplex thousands of application connections onto a small number of database connections, which is essential in serverless or high-concurrency environments.

Prompt Snippet

Deploy PgBouncer in transaction pooling mode between the application and PostgreSQL, with default_pool_size set to max_connections / (number_of_app_instances * 1.5). Configure server_idle_timeout = 600s and client_idle_timeout = 300s to reclaim stale connections. Set server_lifetime = 3600s to rotate connections and prevent memory bloat from long-lived backends. Implement application-level health checks using SELECT 1 and configure retry logic with exponential backoff (100ms initial, 5s max, 3 retries) on connection acquisition failures.