Connection Pooling

Connection pooling keeps a set of open database connections and lends them to requests. Without a pool, every request opens a connection, performs a handshake, and closes it. That cost is small when idle but expensive when hundreds of requests arrive at once.

Why connections are expensive

MySQL, MariaDB, and PostgreSQL limit the number of connections. Each connection uses memory and a process or thread. If the app opens connections faster than the database closes them, requests fail with errors such as “too many connections” or “remaining connection slots are reserved”.

The symptom often appears as Error 500 or Error 503 under load. Raising max_connections without adding RAM only moves the problem.

How a pool works

The app asks the pool for a connection. If an idle connection exists, it is reused. If the pool is full, the request waits until one is returned or times out. When done, the connection returns to the pool instead of closing.

The pool does not need to be large. Start with 5 to 20 connections per app, measure wait time and database usage, then adjust. The rough formula pool_size = core_count * 2 + effective_spindle_count is a starting point, but the real workload matters more.

Configuration

PgBouncer for PostgreSQL can use transaction mode for efficiency. However, session-level features such as some prepared statements, SET, or advisory locks can break. Test with your driver.

In Prisma, set connection_limit in the URL, for example ?connection_limit=10&pool_timeout=20. For serverless, use a pooler because each invocation may open a new connection.

In MySQL, monitor SHOW STATUS LIKE 'Threads_connected'; and SHOW VARIABLES LIKE 'max_connections';. In PHP-FPM, each worker can hold one connection. Fifty workers with a pool of ten can mean hundreds of connections if left unbounded.

Common mistakes

A pool that is too large increases contention and database memory. Connections not closed in a finally block leak until the pool is empty. A pool also does not fix slow queries. An unindexed query stays slow.

Do not rely on session state in a shared connection. Session variables can leak between requests.

Connection pooling complements Redis for caching and PHP-FPM for workers. If queries are slow, start with MySQL Indexing.

FAQ