Traffic spike: two hundred active PHP-FPM workers, each opening a new database connection. PostgreSQL hits its limit of one hundred connections — too many connections. Panic: raise the limit to five hundred. Database RAM explodes, swap kicks in, performance worsens.
PgBouncer arrives: two hundred app clients, thirty real PostgreSQL connections. Crisis seems solved — until thirty slow queries block the whole pool for thirty seconds. Mass app timeouts. The pool hid the connection ceiling, not the database's concurrent query capacity.
Connection pooling fixes connection overhead. It does not fix full table scans. Misconfigured, it adds an invisible queue in front of an already choking database.
Where to place the pool in the stack
Tooling changes by environment; the principle stays the same: one layer between app processes and the database.
| Stack | Common tool |
|---|---|
| PHP / Python / Node → PostgreSQL | PgBouncer in front of PostgreSQL |
| MySQL | ProxySQL, MariaDB MaxScale, or app pool |
| Serverless | Mandatory pooler (managed proxy) |
| PHP without pooler | Persistent connections — zombie connection risk |
Common architecture:
[PHP-FPM × N] → [PgBouncer :6432] → [PostgreSQL :5432]
Do not double pool app + PgBouncer blindly — nested pools create latency and deadlocks.
Sizing: start from the database, not the app
For PostgreSQL, optimal active connections are measured under real load — not on a theoretical spreadsheet. In practice, default_pool_size between twenty and fifty often suffices for an SMB; max_client_conn must cover the sum of app processes.
| Symptom | Likely cause |
|---|---|
| Pool queue timeout | Pool too small or queries too long |
| Low DB CPU, app timeouts | Lock-blocked queries |
| "Too many connections" | Pool bypass or misconfig |
Transaction mode: gains and traps
Transaction pooling recycles the PostgreSQL connection after commit. Do not use with session-bound prepared statements, persistent session variables, or features like listen/notify. Session mode is safer for migration; transaction mode after auditing your data access layer.
On shared MySQL hosting, connections are often capped at ten or thirty: an app-side pool is mandatory. On managed PostgreSQL, a pooler is sometimes included — check your plan limits.
The peak: pool moves saturation
Fix order: slow queries → pool → scale database. Reverse the order and you pay for queue plus extra hardware with the same outcome.
Decide and move forward without blind spots
Measure peak PostgreSQL or MySQL connections before any change. Deploy PgBouncer or ProxySQL if app processes far exceed safe max_connections. Set pool_size from real database capacity, not worker count. Monitor waiting clients and average wait time. Analyze heaviest queries in parallel — see PostgreSQL EXPLAIN and PHP-FPM saturation.
Frequently asked questions
Why pool PostgreSQL?
Each connection consumes RAM; pooling multiplexes hundreds of app processes on fewer server connections.
Session vs transaction mode PgBouncer?
Transaction mode is more efficient; session mode stays more compatible with ORMs.
How to size pool_size?
Start from database CPU capacity, measure under real load, not app worker count.
Does pool replace SQL indexes?
No — slow queries stay slow once connected. Optimize queries first.
Connection pooling heals opening — not the query that chokes the database once connected.