Opening a database connection isn’t free: a TCP (and often TLS) handshake, authentication, and on PostgreSQL a whole new server process. If every web request opens and closes its own connection, a surprising share of time goes into connection setup — and under load the database drowns in connections. Connection pooling keeps a set of open connections and lends them out.

How a pool works

  1. At startup (or on demand), the pool opens N connections.
  2. When code needs the database, it borrows a connection.
  3. When done, it returns it — the connection stays open for the next request.
  4. If all connections are busy, requests wait (up to a timeout) instead of opening more.

The result: fast queries without per-request setup, and a hard cap on how many connections the database has to handle.

Where pools live

In the application

Most runtimes and ORMs include a pool:

Stack Pool
Node.js pg.Pool, mysql2 pools, Prisma, Knex
Python SQLAlchemy pool, Django CONN_MAX_AGE (persistent connections) or Django 5.1+ native pooling with psycopg
Java HikariCP
Go database/sql built-in pool
Ruby on Rails ActiveRecord pool (pool: in database.yml)
PHP No pool across requests by default (each request is separate); persistent connections or an external pooler

As a proxy

A separate service between apps and the database:

  • PgBouncer — PostgreSQL; lightweight, transaction pooling. See PgBouncer.
  • ProxySQL — MySQL/MariaDB; pooling plus query routing (read/write splitting), caching and failover.
  • pgcat, Odyssey — newer PostgreSQL poolers.

Proxies shine when many processes each want connections (PHP-FPM workers, several app servers, serverless functions), because they multiplex all of them onto a small, shared set of database connections.

Sizing: smaller than you think

More connections don’t mean more throughput. A database can only execute as many queries in parallel as it has CPU cores (and disk bandwidth); beyond that, connections just compete and context-switch.

A widely used starting point for the total number of active connections:

connections ≈ (database CPU cores × 2) + effective disk spindles

For a 4-core database server on SSD, that’s roughly 10–20 active connections. Then:

  • Sum all pools: 3 app servers × pool size 20 = 60 connections. Keep the total below the database’s max_connections with headroom for admin tools, replication and migrations.
  • Prefer a queue in the pool (requests wait briefly) over more connections.
  • Long transactions hold connections; keep transactions short.

Measure: watch pool wait times and database CPU. If CPU is saturated, a bigger pool makes things worse; if the pool waits while the database is idle, the pool is too small.

Pitfalls

Connection leaks — code paths that borrow a connection and never return it (error branches, forgotten release()). The pool runs dry and the app hangs. Use try/finally or framework-managed sessions; enable leak detection where available (HikariCP leakDetectionThreshold).

Stale connections — firewalls, NAT or database restarts close idle connections; the pool hands out a dead one. Enable health checks / pre_ping (SQLAlchemy), max lifetime settings, and TCP keepalives.

Per-process pools multiplying — PM2 cluster mode or Gunicorn workers each create their own pool. 8 workers × pool 10 = 80 connections. Size per process accordingly, or use a proxy pooler.

Session state with transaction pooling — SET commands, temporary tables, advisory locks and some prepared statement setups don’t survive across transactions on PgBouncer transaction mode. Know your pooler’s mode.

Serverless — each function instance may open connections; at scale this overwhelms the database. Use a proxy pooler or a serverless-friendly driver.

Quick configuration examples

Node.js (pg):

import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10, idleTimeoutMillis: 30000 });
const { rows } = await pool.query('SELECT now()');

SQLAlchemy:

engine = create_engine(DATABASE_URL, pool_size=10, max_overflow=5, pool_pre_ping=True, pool_recycle=1800)

Rails (config/database.yml): pool: <%= ENV.fetch("RAILS_MAX_THREADS", 5) %> — match Puma threads.

  • Databases
  • Performance
  • PostgreSQL