Connection Pooling

Opening a database connection is one of those things that sounds trivial but is actually surprisingly expensive. Every time our application opens a connection to PostgreSQL or MySQL, a whole chain of events happens:

1. TCP handshake — three network round-trips just to establish the connection
2. TLS handshake — if encrypted, another 1-2 round-trips for the SSL negotiation
3. Authentication — the database verifies our credentials
4. Process/thread creation — PostgreSQL forks a new process for each connection, MySQL creates a new thread
5. Memory allocation — each connection consumes ~5-10 MB on the server side

All of this can take 20-50 milliseconds. For a single query, that might be fine. But in a web application handling 1,000 requests per second, opening and closing a connection for every request means we’re spending more time connecting than actually querying.

Enter Connection Pooling

Connection pooling solves this by reusing existing connections instead of creating new ones. We maintain a pool of open, ready-to-use connections. When our app needs a database connection, it borrows one from the pool. When it’s done, it returns it to the pool (not closes it). The connection stays open and ready for the next request.

In simple language, instead of 1,000 requests each opening their own connection (which would overwhelm the database), they all share a pool of, say, 20 connections. The database only sees 20 connections, not 1,000.

Pool Sizing — The Goldilocks Problem

Setting the pool size wrong causes real problems:

• Too small — requests wait in line for a free connection. Latency spikes. Timeouts.
• Too large — too many connections overwhelm the database. Context switching, memory pressure, worse performance for everyone.

The Rule of Thumb

A surprisingly common formula from the HikariCP docs:

connections = (core_count * 2) + effective_spindle_count

For a 4-core server with an SSD (spindle count = 0):

• connections = (4 * 2) + 0 = 8 connections

That’s not a typo. Most applications need far fewer connections than people think. A pool of 10-20 connections can handle thousands of requests per second because each connection is only “in use” for the milliseconds it takes to run the query.

Common Poolers by Language

Node.js — pg Pool

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'postgres',
  password: 'secret',
  max: 20,                    // max connections in pool
  idleTimeoutMillis: 30000,   // close idle connections after 30s
  connectionTimeoutMillis: 2000, // fail if can't get connection in 2s
});

// Borrow a connection, use it, return it
const result = await pool.query('SELECT * FROM users WHERE id = $1', [42]);

// Or for transactions — manual checkout
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();  // return to pool — don't forget this!
}

Java — HikariCP

HikariCP is the fastest and most reliable connection pool for Java. It’s the default in Spring Boot.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/myapp");
config.setUsername("postgres");
config.setMaximumPoolSize(10);           // max connections
config.setMinimumIdle(5);                // keep at least 5 idle
config.setIdleTimeout(300000);           // close idle after 5 min
config.setMaxLifetime(1800000);          // recycle connections every 30 min
config.setConnectionTimeout(30000);      // wait max 30s for a connection

HikariDataSource ds = new HikariDataSource(config);

Python — SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://postgres:secret@localhost/myapp",
    pool_size=10,          # max connections to keep open
    max_overflow=5,        # allow 5 extra connections under load
    pool_timeout=30,       # wait max 30s for a connection
    pool_recycle=1800,     # recycle connections after 30 min
)

External Poolers — PgBouncer

Sometimes in-app pooling isn’t enough. If we have 10 application servers, each with a pool of 20 connections, that’s 200 connections to PostgreSQL. With serverless functions (AWS Lambda), it can be even worse — each invocation might open its own connection.

PgBouncer sits between our app and PostgreSQL as a lightweight proxy. All our app servers connect to PgBouncer, and PgBouncer maintains a much smaller pool of actual database connections.

PgBouncer Pool Modes

This is a common interview question.

Transaction mode (most common): A connection is assigned to a client for the duration of a single transaction. Between transactions, the connection goes back to the pool. Very efficient.

Session mode: A connection is assigned to a client for the entire session (until they disconnect). Less efficient but supports all PostgreSQL features (prepared statements, temp tables, etc.).

Statement mode: A connection is assigned per statement. Ultra-efficient but doesn’t support multi-statement transactions.

; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction        ; most common choice
max_client_conn = 1000         ; accept up to 1000 client connections
default_pool_size = 20         ; but only keep 20 real DB connections
min_pool_size = 5
reserve_pool_size = 5          ; extra connections for spikes

Configuration Parameters to Know

Common Mistakes

1. Not returning connections to the pool — forgetting to call release() or close() on a borrowed connection. The pool gradually runs out and everything hangs.
2. Setting the pool too large — 200 connections doesn’t mean 200x performance. Past a point, more connections means more contention and worse performance.
3. No connection validation — if a connection goes stale (DB restarted, network blip), the pool hands out a dead connection. Most pools have a test-on-borrow or validation-query option to check.
4. Ignoring max_lifetime — connections held open for days can accumulate server-side memory leaks, DNS changes, or firewall timeouts.