Database Connection Pooling

When to use

• The user sees per-request overhead from opening a fresh DB connection (TCP handshake, TLS, auth) and wants to reuse connections to cut latency.
• The user hits "Too many connections" errors, or the database is saturated by more client connections than it can serve, and needs to bound and pool them.
• The user is deciding how to reuse connections given their runtime — PHP-FPM, Swoole/RoadRunner workers, or serverless — and wants the right strategy.

When NOT to use

• Do not add pooling or persistent connections to a one-shot CLI script or migration that opens one connection and exits.
• Do not add an external pooler when the app comfortably fits inside max_connections and connection setup is not a measured bottleneck.

Core guidance

• Use persistent connections under PHP-FPM — Under PHP-FPM enable persistent connections (PDO::ATTR_PERSISTENT => true or a p: DSN prefix) so each worker reuses its socket across requests instead of reconnecting on every request.
• Size total connections below the server limit — Persistent connections multiply: FPM workers x servers must stay under the database's max_connections, leaving headroom for admin and replicas. Cap FPM pm.max_children accordingly rather than assuming pooling adds capacity.

Quick recipe

1. Read the database max_connections, subtract headroom for admin/replicas, then divide by the number of app hosts to get a per-host worker budget. (verify: sum of pm.max_children across hosts plus headroom is below max_connections.)
2. Set PDO::ATTR_PERSISTENT => true on the DSN and set pm.max_children to the per-host budget from step 1 so workers reuse sockets without exceeding the limit. (verify: New requests reuse existing connections and Threads_connected stays flat under load.)
3. Keep idle timeout under the server wait_timeout, wrap the first query in a reconnect-once retry for "gone away", and alert on Threads_connected nearing max_connections. (verify: A killed backend connection recovers on the next request and the connection-count alert fires before saturation.)

Best practices

• When many app instances need few concurrent server connections, put an external pooler (ProxySQL for MySQL, PgBouncer for Postgres, RDS Proxy on AWS) in front so hundreds of clients share a small warm backend pool
• Use transaction-level pooling (PgBouncer transaction mode / ProxySQL multiplex) for maximum reuse only if the app avoids session-scoped state — prepared statements pinned to a session, temp tables, SET variables, advisory locks
• Keep the connection idle timeout on the pooler shorter than the database's wait_timeout, and validate or reconnect on a "server has gone away" error so a request never inherits a dead persistent socket
• In Swoole/RoadRunner/Octane workers, hold a real in-process connection pool and return each connection after use, rather than opening a connection per job

Anti-patterns

See references/anti-patterns.md.

Common mistakes

1. Persistent connections turn a scale-out into a connection storm — Enabling persistent connections and then scaling out FPM workers multiplies open connections (workers x hosts), which can blow past max_connections and lock everyone out. Fix: Cap pm.max_children, put an external pooler in front so app scale is decoupled from backend connections, and monitor Threads_connected against the limit.
2. Session state leaks across requests on a persistent connection — A persistent connection carries over server-side state — an uncommitted transaction, SET session variables, temp tables, or the current database — into the next request that reuses it. Fix: Always commit or roll back before the request ends, avoid relying on session variables, and reset connection state (or disable persistence) where isolation matters.
3. Reusing a dead connection throws "server has gone away" — A cached persistent connection can be closed by the server (idle wait_timeout, restart, failover) while the client still believes it is open, so the next query fails with error 2006 "MySQL server has gone away". Fix: Keep idle timeouts below wait_timeout, enable the client's ping/validation, and wrap the first query in a reconnect-once retry after a gone-away error.
4. Server-side prepared statements break under transaction pooling — In PgBouncer transaction mode (or ProxySQL multiplexing), server-side prepared statements, LISTEN/NOTIFY, advisory locks, and temp tables fail because each transaction may land on a different backend connection. Fix: Use client-side/emulated prepares, avoid session-scoped features, or switch that connection to session-mode pooling.

Version notes

• PDO persistent connections work from PHP 7.4 onward (PHP >=7.4) — PDO::ATTR_PERSISTENT and the mysqli p: DSN prefix are available on PHP 7.4 and all later versions; there is no in-language connection pool, so external poolers or worker runtimes remain the way to get true pooling.

Security

• Keep TLS and least-privilege on pooled connections — Terminate the app-to-pooler and pooler-to-database hops over TLS, and give the pooled DB user only the privileges the app needs — do not weaken auth to make pooling easier.

Testing

• Activation and decision behavior are verified in eval/scenarios/databases/connection-pooling.yaml.

Additional resources

• references/anti-patterns.md
• references/core.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Related: databases/mysql-indexing, databases/transactions