Find the actual connection owners
A web app can run out of database connections even while its pages look simple. Each web process, background worker, scheduled task and admin tool may maintain its own pool. When several copies of the app are started during a release, the total can double briefly. PostgreSQL sets a maximum number of concurrent server connections, and reserves some slots for privileged access. That is why the number shown in one application pool configuration is not the whole budget. First identify every component that can open a connection to the same PostgreSQL instance, including monitoring or maintenance jobs. Record its maximum instances, pool size and whether it holds connections while idle. Then add the possible concurrent demand. This is a worst-case planning calculation, not a claim that all connections are busy at once. The calculation reveals where the app could refuse a customer request during a spike or rollout. It also gives the team a concrete question: should it reduce idle connections, cap workers or place a pooler in front of the server?
Read server limits with their conditions
PostgreSQL documentation says max_connections is the upper bound for concurrent connections, with a typical default of 100, although the actual initialized value can be lower. It also says changing this setting requires a server start and that increasing it allocates more server resources, including shared memory. Do not turn the default into a universal safe target. Read the running server’s value and the relevant reserved slot settings, then leave operating room for maintenance and diagnosis. PostgreSQL distinguishes superuser_reserved_connections from reserved_connections available to roles with the relevant privilege; those settings have their own constraints and can differ by installation. A web app must not assume it can consume emergency slots. Managed database providers may impose additional limits or pooling behaviour, so confirm the deployed service’s terms rather than copying an example from another company. Capacity also depends on memory, query duration and transaction behaviour. More permitted connections can mask slow queries for a while and then make contention worse, so a limit increase needs evidence and a load test.
Build a transparent planning worksheet
Consider an illustrative system with four web instances configured for ten connections each, two worker instances configured for eight each, and a reporting job with a pool of four. The configured maximum is 4 × 10 + 2 × 8 + 4 = 60 application connections before admin access and deployment overlap. If a rolling release briefly runs another two web instances, the planned maximum becomes 80. These numbers are only an example; replace them with the actual autoscaling ceiling, process count and pool settings. A worksheet should show both normal and release-time cases, not just the average observed count. It should also distinguish a connection that is open from a query that is active. If observed server use stays low but queued application requests rise, the bottleneck may be a local pool, a lock or a slow query rather than the PostgreSQL global limit. Conversely, low average use does not prove that a short peak never reaches the ceiling. Record the measurement interval and the incident time when comparing them.
Decide whether a pooler fits the workload
PgBouncer can accept client connections and manage a smaller set of server connections, but its modes are not interchangeable. Its documentation says session pooling releases a server connection after a client disconnects, while transaction pooling releases it when the transaction finishes; statement pooling releases it after each query and does not allow transactions spanning multiple statements. Transaction pooling may suit workloads with short, independent transactions, but application features that rely on session state need careful compatibility testing. Do not promise that installing a pooler alone fixes contention. If a request keeps a transaction open while calling an external service, that transaction can still hold a server connection. If every query is slow, a smaller pool can increase waiting. Check how the framework uses prepared statements, temporary objects and connection-local settings before changing mode. Then test the chosen mode against actual checkout, admin and worker flows, including a failed query and a reconnect. PgBouncer’s per-user and per-database settings mean one default pool size should not be mistaken for a universal instance-wide ceiling.
Measure where requests wait
PostgreSQL documents pg_stat_activity as a view with one row per server process and information about current activity. It can help the team separate active work, idle sessions and transactions left open, subject to the visibility granted to the account used for observation. Capture the relevant counts during normal traffic, a planned release and a controlled load exercise. Pair database observations with application pool metrics: connections in use, available connections, waiters and acquisition time. For a PgBouncer deployment, include its client and server connection counts and waiting clients. The exact metric names depend on the libraries in use, so write down what each measurement represents. When a customer request slows, a timestamped trace should show whether it waited for a local pool slot, a PgBouncer server slot, a database lock or query execution. Avoid recording customer secrets in diagnostic output. Without this separation, a team may raise max_connections when the actual problem is a long transaction or an application pool that is too small for one process.
Prove the limit during a safe rehearsal
Before a production change, rehearse a traffic level and deployment overlap that the business realistically expects. Measure whether requests complete within the service’s own acceptable time, whether queues drain after the peak and whether an administrator can still connect for diagnosis. Deliberately include a worker burst and a restart of one web instance, because a reconnect wave can expose a different limit from steady traffic. Record the exact configuration under test, software versions, observed connection peaks, wait times and failure messages. If the system fails, change one cause at a time: shorten a transaction, reduce an oversized pool, cap worker concurrency, adjust pooler configuration or revise server capacity based on evidence. Roll back a change that worsens waiting or breaks a session-dependent flow. A small business can begin with a spreadsheet and a repeatable test rather than buying a large monitoring platform. The result is a defensible capacity decision tied to its own workload, not to a generic recommended connection number.
Choose a change from the evidence
The worksheet and rehearsal should lead to one specific next action, not an automatic purchase or a bigger numeric limit. If many idle sessions come from web replicas, lower their minimum retained connections or change how the application starts its pools, then observe whether request waits remain acceptable. If workers open too many connections during a batch, limit worker concurrency and check whether the batch still completes within its business deadline. If a few long transactions dominate, inspect those paths before adjusting the global ceiling. If a pooler is appropriate, begin with a compatibility trial on the flows that use session features, then set separate client and server limits and observe queues during the release overlap. Write down the trade-off: fewer server connections may protect PostgreSQL but make clients wait, while larger pools may reduce local waiting yet exhaust the server. The business owner needs the expected effect on checkout or staff work, the test result and a rollback trigger. A connection count is a means of protecting completed work, not a success measure on its own.
Keep a small operating record
Connection capacity is not a one-time sizing exercise. A new background job, a second app replica or a changed pool default can invalidate the previous budget without any database upgrade. Keep the worksheet in the release notes and require each new component to declare its maximum instances and pool setting. During a real incident, record the time, affected customer action, application wait, PostgreSQL activity and any pooler queue together. That makes the next decision more precise than a screenshot of one “too many clients” error. Review the plan after a load change, a provider migration or a framework update that changes connection management. If metrics are unavailable, mark the observation as unavailable and use a controlled test where possible; do not invent a peak or equate missing data with zero demand. The accepted result is a system that can complete its intended business actions while retaining a path for diagnosis when demand rises. The record should remain short enough that the next operator will actually use it.
Before you proceed
- The worksheet covers every application, worker and release overlap.
- Actual PostgreSQL and pooler settings have been read and compatibility tested.
- Load rehearsal shows connection peaks, waits and recovery.
