Database Connection Pooling Explained

Introduction¶

Establishing a database connection costs a TCP handshake, TLS, authentication, and server-side memory. Doing it per web request wastes time and, worse, lets a traffic spike open thousands of connections and topple the database. A pool keeps a small number of connections open and hands them out.

The sizing math¶

More connections is not faster. A database server does real work on a limited number of CPU cores and disk spindles; past that, connections just queue inside the DB and add context-switching overhead.

A widely-cited starting formula (from PostgreSQL folklore, applies broadly):

pool_size ≈ (core_count * 2) + effective_spindle_count

For a modern 8-core server on SSD: ~16–20 connections handles a surprising amount of load. If that's not enough, the fix is usually faster queries, a read replica, or caching — not a bigger pool.

Then: total connections to DB = pool_size × number_of_app_instances. Ten app pods each with a pool of 20 = 200 connections. Check that against the DB's max_connections (and that each connection's memory fits).

Where the pool lives¶

In the application¶

Every framework has one: HikariCP (Java), database/sql (Go), SQLAlchemy's QueuePool, pg/pg-pool (Node), psycopg_pool (Python). Configure:

• max size — the ceiling (see math above).
• min / idle — keep a few warm.
• acquire timeout — how long a request waits for a connection before erroring (fail fast; don't let requests pile up).
• max lifetime — recycle connections every ~30 min so a DB failover or a load-balancer timeout doesn't leave stale ones.
• validation / test-on-borrow — a cheap SELECT 1 or the driver's keepalive.

A dedicated proxy¶

When you have many app instances (serverless, lots of pods), per-app pools multiply badly. A proxy pools centrally:

• PgBouncer (Postgres) — tiny, single-purpose.
• ProxySQL (MySQL) — pooling plus query routing/rewriting, read/write split.
• RDS Proxy / Cloud SQL built-in — managed equivalents.

Transaction vs session pooling (PgBouncer terms)¶

• session pooling — a client holds its server connection for the whole client session. Safe for everything, but pooling ratio is poor.
• transaction pooling — the server connection is returned to the pool at the end of each transaction. Huge pooling ratio (hundreds of clients → dozens of server conns), but breaks anything relying on session state: prepared statements (pre-PG14 style), SET that outlives a txn, advisory locks, LISTEN/NOTIFY, temp tables across statements.
• statement pooling — even more aggressive, no multi-statement transactions.

Transaction pooling is the usual choice for web apps; make sure your driver is configured for it (disable client-side prepared statement caching, or use protocol-level prepares that PgBouncer 1.21+ supports).

Verification and troubleshooting¶

-- Postgres: current connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;

-- MySQL
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW PROCESSLIST;

• App errors "pool exhausted" / "timeout acquiring connection" — either the pool is too small for concurrency, or connections aren't being returned (a code path that doesn't close/with the connection, a long transaction, an N+1 holding a connection while it loops). Look for idle in transaction.
• FATAL: too many connections at the DB — pool_size × instances exceeds max_connections. Shrink pools, add a proxy, or raise max_connections (and the RAM budget — each Postgres backend is ~5–10 MB).
• idle in transaction connections accumulating — the app began a transaction and didn't commit/rollback (an exception path, a forgotten await). Set idle_in_transaction_session_timeout to kill them; fix the code.
• Latency spikes after a DB failover — stale pooled connections to the old primary. Set max lifetime and validation; PgBouncer server_reset_query.
• Transaction pooling broke prepared statements / SET search_path — move those to every-transaction, or use session pooling for that pool, or a newer PgBouncer.
• Connections keep dropping mid-query — a firewall/LB idle timeout shorter than your max lifetime; TCP keepalives on the connection, or lower the lifetime below the timeout.

Related tools and reading¶

• On-site: Port Checker.
• Related posts: InnoDB buffer pool sizing, Requests vs limits and OOMKilled.