Connection pooling - System Design Guide

Overview

A connection pool keeps a set of open connections (to a database, cache, or HTTP service) ready for reuse, instead of opening and closing one per request. Opening a database connection can cost tens of milliseconds (TCP, TLS, authentication, backend process setup), and databases can only handle a limited number of connections at once.

Pools make requests faster and protect the database. The key design question is sizing: too few connections and requests queue; too many and the database spends its time context switching. With many app instances, the total across all pools must stay within what the database can handle, which is why proxies like PgBouncer exist.

Taxi stand

Instead of calling a new taxi every time (slow), a stand keeps a few taxis waiting. Customers take one and return it. Too few taxis create a queue; too many clog the street.

When to use it

Any service talking to a relational database.
High-rate HTTP calls to the same downstream service (keep-alive agents).
Serverless functions that would otherwise open a connection per invocation.
Protecting databases from connection storms during scale-outs.

Where it shows up in interviews

Database protection

Recognize it when: hundreds of app instances hit one database.

Scale a web app on PostgreSQL
Design a serverless backend with a relational DB

Service-to-service efficiency

Recognize it when: high call volume to a dependency.

Design an API gateway
Design a payment service calling a bank API

Where it is used in real software

PgBouncer

A lightweight PostgreSQL proxy that multiplexes thousands of client connections onto a few dozen server connections.

HikariCP

The default Java connection pool in Spring Boot, known for speed and a recommended small pool size.

Amazon RDS Proxy

Pools database connections for Lambda functions, which would otherwise exhaust database connection limits.

Key terms

Pool size: Maximum open connections per instance.
Acquire timeout: How long a request waits for a free connection before failing.
Idle timeout: Close connections unused for a while.
Transaction pooling: PgBouncer mode that assigns a server connection only for a transaction's duration.

How it works, step by step

1
Pool opens connections
Up to a minimum at startup and a maximum under load.
2
Request borrows a connection
Waits up to the acquire timeout if all are busy.
3
Run queries
Keep transactions short so the connection returns quickly.
4
Return the connection
Always, even on errors (finally blocks).
5
Validate and recycle
Test stale connections and replace them periodically.

Sizing pools across a fleet

PostgreSQL max_connections = 500, keep 50 for admin and replication

App instances	Pool size each	Total connections	Result
10	20	200	Fine
40	20	800	Exceeds limit: connection errors
40	10	400	Fits
40 via PgBouncer	20 client each, 60 server total	60 server connections	Fits with lots of headroom

NOWApp instances: 10 | Pool size each: 20 | Total connections: 200 | Result: Fine

Pool size per instance times instance count must stay below the database limit. Auto scaling app servers without a pooler can take down the database.

Implementation

1import { Pool } from "pg";2 3const pool = new Pool({4  connectionString: process.env.DATABASE_URL,5  max: 10,                        // per instance; total = max x instances6  idleTimeoutMillis: 30_000,7  connectionTimeoutMillis: 2_000, // fail fast instead of queueing forever8});9 10export async function transferFunds(from: string, to: string, cents: number) {11  const client = await pool.connect();12  try {13    await client.query("BEGIN");14    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [cents, from]);15    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [cents, to]);16    await client.query("COMMIT");17  } catch (err) {18    await client.query("ROLLBACK");19    throw err;20  } finally {21    client.release(); // always return the connection22  }23}

Complexity and performance

New PostgreSQL connection~5-50 ms

TCP + TLS + auth + process fork.

Borrow from poolmicroseconds

When a connection is free.

Good starting pool size~2 x CPU cores of the DB / instances

Tune with load tests.

Trade-offs

Bigger vs smaller pools

Bigger pools reduce waiting at the app but overload the database; a small pool plus a short queue often gives better total throughput.

Session vs transaction pooling

Transaction pooling scales further but breaks session features (prepared statements, session variables) unless handled.

Variants and related techniques

HTTP keep-alive agents

Reuse TCP/TLS connections to downstream services.

Redis connection multiplexing

Clients like ioredis pipeline many commands over one connection.

Common mistakes

Forgetting to release connections.
Fix: Use finally blocks or helpers; leaks exhaust the pool.
Long transactions holding connections.
Fix: Never call slow external APIs inside a DB transaction.
Scaling app instances without checking DB limits.
Fix: Use a pooler (PgBouncer, RDS Proxy) and cap total connections.

Interview questions

Why use connection pooling?

Creating connections is slow and databases support a limited number. Reusing a fixed set of connections cuts latency and protects the database from overload.

Your database hits max connections after auto scaling. What do you do?

Reduce per-instance pool sizes, add a connection pooler such as PgBouncer or RDS Proxy, and make sure connections are released and transactions are short.

Practice problems

Problem	Difficulty	What it trains
Size pools for 30 instances and a 300-connection DB	Easy	Math.
Design DB access for 10,000 concurrent Lambda functions	Medium	Proxy pooling.