Skip to content 
Pragmatic derivation of pool size
I don't size pools based on gut feeling, but rather on expected parallelism and average query duration. A simple approximation: simultaneous user accesses × average simultaneous database operations per request × safety factor. If an API handles 150 simultaneous requests under load, for example, and there are an average of 0.3 overlapping DB operations per request and a safety factor of 1.5 is selected, I end up with 68 (150 × 0.3 × 1.5) connections as the upper limit per app instance. Shorter queries allow for smaller pools, while long transactions tend to require more buffers. Important: This number must match the sum of all app servers and always leave a reserve for admin and batch jobs. I start conservatively, observe waiting times, and only increase when the pool reaches its limit while the database still has room to breathe.
Driver and framework specifics
Pooling works differently depending on the language. In Java, I often rely on a mature JDBC pool with clear timeouts and max lifetime. In Go, I use SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime to precisely control behavior and recycling. Node.js pools benefit from restrictive sizes because event loop blockages caused by slow queries are particularly painful. Python (e.g., SQLAlchemy) needs clearly defined pool sizes and reconnect strategies, as network flaps can quickly trigger nasty error chains. PHP in a classic FPM setup achieves only limited gains through per-process pooling; here, I plan strict timeouts and often prefer an external pooler with PostgreSQL. In all cases, I check whether the driver handles server-side prepared statements reactively and how it establishes reconnections after restarts.
Prepared statements, transaction modes, and state
Pooling only works reliably if sessions are „clean“ after being returned to the pool. With PostgreSQL plus PgBouncer, I use transaction mode for efficiency without carrying over session state. Prepared statements can be tricky here: they remain in session mode, but not necessarily in transaction mode. I make sure that the framework either avoids repeated preparation or works with transparent fallback. I explicitly clean up session variables, search paths, and temporary tables, or avoid them in application logic. This way, I ensure that the next borrow of a connection does not run into an unforeseen session state and produce subsequent errors.
MySQL-specific subtleties
With MySQL, I make sure to keep the max lifetime of pool connections below wait_timeout or interactive_timeout. This allows me to terminate sessions in a controlled manner instead of being „cut off“ by the server side. A moderate thread_cache_size can also reduce the load on connection establishment and termination when new sessions are needed. I also check whether long transactions (e.g., from batch processes) are monopolizing slots in the pool and separate them into their own pools. If the instance has a strict max_connections value, I deliberately plan for a 10–20 percent reserve for maintenance, replication threads, and emergencies. And: I avoid pushing the app pool directly to its limit – smaller, well-used pools are usually faster than large, sluggish „parking garages.“.
PostgreSQL-specific subtleties with PgBouncer
PostgreSQL scales connections less well than MySQL because each client process binds resources independently. I therefore keep max_connections on the server conservative and shift parallelism to PgBouncer. I set default_pool_size, min_pool_size, and reserve_pool_size so that the expected payload is cushioned under load and reserves are available in case of emergency. A sensible server_idle_timeout cleans up old backends without closing temporarily idle sessions too early. Health checks and server_check_query help to quickly detect defective backends. I achieve the best utilization in transaction mode, but I have to deal with prepared statement behavior consciously. For maintenance, I plan a small admin pool that always has access, regardless of the app.
Network, TLS, and Keepalive
With TLS-secured connections, the handshake is expensive – pooling saves a lot here. I therefore activate useful TCP keepalives in productive environments so that dead connections can be detected more quickly after network outages. However, overly aggressive keepalive intervals lead to unnecessary traffic; I choose practical mean values and test them under real latencies (cloud, cross-region, VPN). On the app side, I ensure that timeouts not only affect the pool „acquire,“ but also at the socket level (read/write timeout). This way, I avoid hanging requests when the network is connected but effectively unresponsive.
Backpressure, fairness, and priorities
A pool must not collect requests indefinitely, otherwise user waiting times become unpredictable. I therefore set clear acquire timeouts, discard overdue requests, and respond in a controlled manner with error messages instead of allowing the queue to continue to grow. For mixed workloads, I define separate pools: read APIs, write APIs, batch jobs, and admin jobs. This prevents a single report from consuming all slots and slowing down checkout. If necessary, I add light rate limiting or token bucket methods per endpoint at the application level. The goal is predictability: important paths remain responsive, while less critical processes are throttled.
Decouple jobs, migration tasks, and long operations
Batch jobs, imports, and schema migrations belong in their own, strictly limited pools. Even at low frequencies, individual, long queries can block the main pool. I set smaller pool sizes and longer timeouts for migration processes—patience is acceptable there, but not in user workflows. For complex reports, I break the work down into smaller chunks and commit more frequently so that slots become available faster. For ETL routes, I plan dedicated time slots or separate replicas so that interactive use remains unburdened. This separation significantly reduces escalation cases and facilitates troubleshooting.
Deployment and restarts without connection chaos
For rolling deployments, I remove instances from the load balancer early on (readiness), wait for the pools to run empty, and only then terminate processes. The pool closes remaining connections in a controlled manner; Max-Lifetime ensures that sessions are rotated regularly anyway. After a DB restart, I force fresh connections on the app side instead of relying on half-dead sockets. I test the entire lifecycle—start, load, error, restart—in staging with realistic timeouts. This ensures that the application remains stable even in turbulent phases.
Operating system and resource limits at a glance
At the system level, I check file descriptor limits and adjust them to the expected number of simultaneous connections. An ulimit that is too low leads to errors that are difficult to trace under load. I also monitor the memory footprint per connection (especially with PostgreSQL) and take into account that higher max_connections on the database side tie up not only CPU but also RAM. At the network level, I pay attention to port utilization, the number of TIME_WAIT sockets, and the configuration of ephemeral ports to avoid exhaustion. All these aspects prevent a properly dimensioned pool from failing at its outer limits.
Measurement methods: from theory to control
In addition to wait time, queue length, and error rate, I evaluate the distribution of query runtimes: P50, P95, and P99 show whether outliers are blocking pool slots for a disproportionately long time. I correlate these values with CPU, IO, and lock metrics on the database. Under PostgreSQL, pooler statistics give me a clear view of utilization, hit/miss, and time behavior. Under MySQL, status variables help to estimate the rate of new connections and the influence of the thread_cache. This combination quickly shows whether the problem lies in the pool, the query, or the database configuration.
Typical anti-patterns and how I avoid them
- Large pools as a panacea: increases latency and shifts bottlenecks instead of resolving them.
- No separation based on workloads: Batch blocks interactive, fairness suffers.
- Missing max lifetime: Sessions survive network failures and behave unpredictably.
- Timeouts without a fallback strategy: Users wait too long or error messages escalate.
- Untested prepared statements: State leaks between borrow/return cause subtle errors.
Design realistic load tests
I simulate not only raw requests per second, but also actual connection behavior: fixed pool sizes per virtual user, realistic think times, and a mix of short and long queries. The test includes warm-up phases, ramp-up, plateau, and ramp-down. I also test failure scenarios: DB restart, network flaps, DNS re-resolution. Only when the pool, driver, and application consistently survive these situations do I consider the configuration to be resilient.
Credential rotation and security
When planning password changes for database users, I coordinate the rotation with the pool: either via a double-user phase or by promptly evicting existing sessions. The pool must be able to establish new connections with valid credentials without abruptly terminating ongoing transactions. In addition, I check that logs do not contain sensitive connection strings and that TLS is correctly enforced when required.
When I deliberately choose smaller pools
If the database is limited by locks, IO, or CPU, a larger pool will not speed things up, but will only lengthen the queue. In that case, I set the pool smaller, ensure fast errors, and optimize queries or indexes. Often, the perceived performance increases because requests fail faster or return immediately instead of hanging for a long time. In practice, this is often the fastest way to achieve stable response times until the actual cause is resolved.
Briefly summarized
Efficient pooling saves money Overhead, reduces timeouts, and makes controlled use of your database. I rely on conservative pool sizes, sensible timeouts, and consistent recycling to keep sessions fresh. MySQL benefits from solid app-based pools, while PostgreSQL benefits from lean poolers such as PgBouncer. Observation beats gut feeling: measurements of wait times, queue lengths, and error rates show whether limits are effective. If you take these points to heart, you'll get fast response times, smooth peaks, and an architecture that scales reliably.
