Database Connection Pooling: Why and How

Opening a database connection is expensive. A PostgreSQL connection involves a TCP handshake, TLS negotiation (if encrypted), authentication, and forking a new backend process that consumes roughly 5-10 MB of memory. This takes 20-100 milliseconds. For a web request that runs a 2ms query, the connection setup dominates the total latency.

Connection pooling solves this by maintaining a set of pre-established connections that your application borrows and returns. The setup cost is paid once, and every subsequent query reuses an existing connection.

How a pool works

A connection pool sits between your application and the database. It manages three things:

1. Idle connections that are established but not currently running a query.
2. Active connections that are checked out by application code and executing work.
3. Waiting requests that arrive when all connections are busy.

Application threads
    |   |   |   |   |
    v   v   v   v   v
  +-------------------+
  |  Connection Pool   |
  | [idle] [idle]      |
  | [active] [active]  |
  +-------------------+
    |   |   |   |
    v   v   v   v
  PostgreSQL (4 backends)

When your code needs a connection, it calls something like pool.getConnection(). The pool returns an idle connection instantly. When the code is done, it returns the connection to the pool instead of closing it. If all connections are busy, the request waits in a queue until one becomes available or a timeout expires.

Pool sizing

The most common mistake is setting the pool too large. More connections does not mean more throughput. PostgreSQL performance degrades when too many backends compete for CPU, memory, and disk I/O.

A good starting formula from the PostgreSQL wiki:

pool_size = (core_count * 2) + effective_spindle_count

For a server with 4 CPU cores and an SSD (effective spindle count of 1):

pool_size = (4 * 2) + 1 = 9

This seems small, but it works. Nine connections can saturate the database’s ability to execute queries in parallel. Adding more connections means more context switching, more lock contention, and less throughput.

For most applications, a pool size between 10 and 20 is appropriate. If you have 10 application instances each with a pool of 20, that is 200 connections to the database. PostgreSQL’s default max_connections is 100. You will need to either increase it (with the associated memory cost) or use an external pooler.

Application-level pooling

Most database drivers and ORMs include built-in connection pools.

Node.js with pg:

import pg from 'pg';

const pool = new pg.Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  max: 15,                    // maximum connections
  idleTimeoutMillis: 30000,   // close idle connections after 30s
  connectionTimeoutMillis: 5000, // fail if no connection in 5s
});

// Use the pool
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Python with SQLAlchemy:

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://app_user:password@localhost/myapp",
    pool_size=10,           # maintained connections
    max_overflow=5,         # extra connections under load
    pool_timeout=5,         # seconds to wait for a connection
    pool_recycle=1800,      # recycle connections after 30 min
    pool_pre_ping=True,     # verify connections before use
)

Java with HikariCP:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/myapp");
config.setUsername("app_user");
config.setMaximumPoolSize(15);
config.setMinimumIdle(5);
config.setIdleTimeout(600000);       // 10 minutes
config.setConnectionTimeout(5000);   // 5 seconds
config.setMaxLifetime(1800000);      // 30 minutes

HikariDataSource ds = new HikariDataSource(config);

Key settings across all pools:

• max size: Upper limit on connections. Start low (10-15), increase based on monitoring.
• idle timeout: How long an unused connection stays open. 30-60 seconds is typical.
• connection timeout: How long to wait for a connection before throwing an error. 3-5 seconds for web requests.
• max lifetime: Rotate connections periodically to handle DNS changes, database restarts, and memory leaks. 30 minutes is common.

External poolers: PgBouncer

When you have many application instances, each with its own pool, the total connection count can overwhelm the database. PgBouncer sits between your applications and PostgreSQL, multiplexing many client connections onto fewer database connections.

App 1 (pool=15) --\
App 2 (pool=15) ----> PgBouncer (50 server conns) ----> PostgreSQL
App 3 (pool=15) --/

45 application connections map to 50 PgBouncer connections to PostgreSQL. Without PgBouncer, the database would need 45 backend processes. With it, PgBouncer reuses connections across clients.

PgBouncer supports three pooling modes:

• Session mode: A client gets a dedicated server connection for the entire session. Safe but offers minimal multiplexing.
• Transaction mode: A client gets a server connection for the duration of a transaction. Between transactions, the connection is returned to the pool. This is the most common mode.
• Statement mode: Connections are returned after every statement. Incompatible with transactions.

Transaction mode is the default choice. It handles 90% of workloads. The main restriction: session-level features (prepared statements, temporary tables, SET commands) do not work because the underlying connection changes between transactions.

Connection leaks

A connection leak occurs when application code checks out a connection and never returns it. The pool gradually exhausts, and new requests start timing out.

Common causes:

// LEAK: connection is never released if the query throws
const client = await pool.connect();
const result = await client.query('SELECT ...');
// If the line above throws, client.release() never runs

// FIXED: use try/finally
const client = await pool.connect();
try {
  const result = await client.query('SELECT ...');
  return result.rows;
} finally {
  client.release();
}

// BEST: use pool.query() which handles checkout/release automatically
const result = await pool.query('SELECT ...', [params]);

Monitor your pool metrics. If active connections grow steadily without returning to idle, you have a leak. Most pools support event listeners or metrics endpoints that report this.

Health checks and validation

Connections can go stale. The database might restart, a firewall might drop idle connections, or a network partition might sever them silently. When your application tries to use a stale connection, the query fails.

Solutions:

1. Pre-ping / test on borrow: Before handing out a connection, the pool sends a lightweight query (SELECT 1). This adds ~1ms of latency but catches dead connections before your application code sees them.
2. Max lifetime: Rotate connections every 15-30 minutes. This bounds the staleness window.
3. Eviction of idle connections: Close connections that have been idle too long. This also helps reduce server-side memory usage.

Monitoring checklist

Track these metrics in production:

• Pool size: active + idle connections over time
• Wait time: how long requests wait for a connection (p50, p95, p99)
• Timeout count: how often connectionTimeoutMillis is hit
• Connection creation rate: how often new connections are established (should be low after warmup)
• PostgreSQL pg_stat_activity: the server-side view of active connections

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'myapp'
GROUP BY state;

If you see many connections in idle state on the server, your pool may be too large. If you see many in active and your application is reporting timeouts, your pool is too small or your queries are too slow.

Connection pooling is infrastructure that pays for itself from day one. Configure it deliberately, monitor it in production, and resist the urge to set max_connections to 500 when 20 well-managed connections will serve you better.