Connection Pooling — pgBouncer & ProxySQL
debt(d5/e5/b5/t5)
Closest to 'specialist tool catches it' (d5). Detection requires monitoring tools like pgbouncer stats, proxysql admin interface, mysql-processlist, or APM tools like Datadog to observe connection saturation, 'Too many connections' errors, or PHP-FPM worker counts exceeding safe DB connection ratios. Standard linters won't catch missing pooler infrastructure.
Closest to 'touches multiple files / significant refactor in one component' (e5). Adding PgBouncer or ProxySQL requires infrastructure changes: installing the pooler, configuring pool_mode, updating application DSNs, and tuning pool sizes. While the quick_fix suggests it's conceptually simple, the actual deployment touches infrastructure config, application database configuration, and requires testing transaction behavior.
Closest to 'persistent productivity tax' (b5). Once deployed, the external pooler becomes load-bearing infrastructure that affects all database-connected code paths (web and queue-worker contexts per applies_to). Developers must understand transaction pooling semantics, pool saturation monitoring, and connection lifecycle. However, it's infrastructure-level burden rather than code-pervasive, so not quite b7.
Closest to 'notable trap' (t5). The misconception explicitly states developers wrongly believe PHP persistent connections provide the same benefits as external poolers. This is a documented gotcha most devs eventually learn — the difference between per-process connection reuse and true connection multiplexing. Additional traps include pool_mode confusion (statement vs transaction mode) per common_mistakes.
Also Known As
TL;DR
Explanation
PHP-FPM creates a new database connection per worker. With 100 FPM workers × 10 servers, the database sees 1,000 connections — far above what PostgreSQL or MySQL handle efficiently. pgBouncer (PostgreSQL) and ProxySQL (MySQL) are lightweight proxies that multiplex many client connections onto a smaller pool of real server connections. pgBouncer modes: session pooling (one real connection per client session), transaction pooling (most efficient — real connection held only during a transaction), and statement pooling (PostgreSQL only). ProxySQL also adds query routing, read/write splitting, and query caching. Configure PHP's PDO DSN to point to the pooler rather than the database directly — no application code changes required.
Common Misconception
Why It Matters
Common Mistakes
- Not using PgBouncer or ProxySQL in transaction mode — session mode holds a server connection for the full request duration.
- Not configuring pool_mode correctly — statement mode breaks transactions; transaction mode is usually correct.
- Assuming PHP PDO persistent connections equal a proper pooler — they are per-process and do not multiplex.
- Not monitoring pool saturation — queries queue silently when all pool connections are in use.
Code Examples
# Without PgBouncer — 500 PHP workers exhaust Postgres max_connections=100:
# php-fpm pm.max_children = 500
# Each worker holds 1 connection = 500 needed, but max is 100 — connection errors
# With PgBouncer (transaction mode):
# PHP connects to PgBouncer (up to 500), PgBouncer holds 100 server connections
# Connections multiplexed — no exhaustion// PDO persistent connections — reuse DB connections across PHP-FPM workers
\$pdo = new PDO(
'mysql:host=localhost;dbname=app;charset=utf8mb4',
DB_USER, DB_PASS,
[PDO::ATTR_PERSISTENT => true] // reuse connection in same FPM worker process
);
// Caveat: persistent connections can hold transactions open — use with care
// Better for high-traffic: PgBouncer (PostgreSQL) or ProxySQL (MySQL)
// They pool connections at the network level, transparent to PHP
// ProxySQL — sits between PHP-FPM and MySQL:
// PHP connects to ProxySQL on port 6033
// ProxySQL maintains a pool of real MySQL connections
// Reduces MySQL max_connections pressure dramatically