Connection Pooling

The Problem: A New Connection for Every Request

A database connection is an open line between your app and the database. Opening one is expensive:

1Open a connection
2  → TCP handshake (set up the network link)
3  → TLS handshake (set up encryption)
4  → login and authentication
5  → PostgreSQL starts a new process just for this connection
6≈ 20–50 ms, plus several MB of memory on the DB server
7 
8Run the actual query ≈ 1–5 ms

If our food delivery app opens a new connection for every request, most of the time is spent connecting, not querying. At dinner rush, thousands of requests open thousands of connections, and PostgreSQL refuses new ones after its limit (max_connections, 100 by default).

What a Connection Pool Is

A connection pool keeps a fixed set of connections open and reuses them. A request borrows a connection, runs its query, and returns it to the pool for the next request.

Think of shared bicycles at a station. Nobody builds a new bicycle for each ride. You take one, ride, and park it back for the next person.

1                 ┌──────────── Pool (max 10) ────────────┐
2Request 1 ──→    │ [conn 1: busy] [conn 2: busy] ...     │ ──→ Database
3Request 2 ──→    │ [conn 3: idle] [conn 4: idle] ...     │
4Request 3 ──→    └───────────────────────────────────────┘
5                   borrow → run query → return

The 20–50 ms connection cost is paid once, when the pool starts, not on every request.

How Many Connections Do You Need?

Fewer than you'd think. A connection is busy only while a query runs:

1Requests per second:  2,000
2Time each query holds a connection: 5 ms = 0.005 s
3 
4Connections busy at once = 2,000 × 0.005 = 10

So 10 connections can serve 2,000 queries per second, if queries are fast.

More connections isn't faster. The database has a limited number of CPU cores. Hundreds of connections all running at once just fight for the same cores and memory. A common starting point for PostgreSQL is about (CPU cores × 2) + number of disks, then adjust by measuring.

Watch out when you scale app servers:

110 app instances × pool of 20 = 200 connections
2PostgreSQL max_connections = 100
3→ new connections fail

When many app instances or serverless functions share one database, put a connection pooler like PgBouncer in between. Thousands of app connections share a small number of real database connections.

1App instances (many) → PgBouncer → PostgreSQL (few real connections)

Setting Up a Pool

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

const pool = new Pool({
  host: 'db-primary',
  max: 10,                        // max connections in the pool (pg default is 10)
  idleTimeoutMillis: 30000,       // close connections idle for 30 s
  connectionTimeoutMillis: 2000   // fail if no connection is free within 2 s
});

// pool.query borrows a connection and returns it automatically
const result = await pool.query('SELECT * FROM orders WHERE user_id = $1', [42]);

In Spring Boot, the same settings go in application.yml:

1spring:
2  datasource:
3    url: jdbc:postgresql://db-primary:5432/food
4    hikari:
5      maximum-pool-size: 10
6      minimum-idle: 5
7      connection-timeout: 2000

When the Pool Runs Out

If every connection is busy, new requests wait. If they wait longer than the timeout, they fail.

1Pool max = 10
210 slow queries each take 3 seconds
3→ request 11 waits
4→ after 2 s (connectionTimeout) → error: "timeout acquiring a connection"
5→ users see errors even though the database itself is fine

Common causes:

A leak is the most common bug. With pool.query(), the pg library returns the connection for you. With pool.connect() (needed for transactions), you must return it yourself:

// LEAK: if a query throws, release() never runs
const client = await pool.connect();
await client.query('UPDATE wallets SET balance = balance - 300 WHERE user_id = 42');
client.release();

// SAFE: release() always runs
const client2 = await pool.connect();
try {
  await client2.query('UPDATE wallets SET balance = balance - 300 WHERE user_id = 42');
} finally {
  client2.release();
}

Ten leaks, and a pool of 10 is permanently empty. The app hangs until it restarts.

Interview Angle

Common mistakes:

• Thinking a bigger pool always means more throughput.
• Forgetting that pool size is per app instance.
• Running a transaction with pool.query() calls in pg, which can use a different connection for each query.

Summary

1Connection     → expensive to open (~20–50 ms + memory on DB)
2Pool           → keep a few open, borrow → query → return
3Size           → busy connections ≈ requests/sec × query time; small is fine
4Total          → app instances × pool size must stay under max_connections
5Many instances → PgBouncer in front of PostgreSQL
6Exhausted      → requests wait, then time out
7Causes         → slow queries, leaks, long transactions
8Rule           → always release in finally / try-with-resources

A pool keeps connections cheap, but a page can still send far too many queries through them. Next, the N+1 query problem shows how one page load can turn into 101 queries.