PostgreSQL uses a process-per-connection architecture, so connection growth is a capacity and workload question, not merely an application preference. A high maximum connection setting can postpone an error while increasing context switching, memory demand and the number of concurrent queries competing for the same CPU and I/O resources.
Count states, not just sessions
Separate active sessions from idle, idle in transaction and waiting sessions. A pool can have many idle connections without stressing the database; hundreds of active queries can. Idle-in-transaction sessions deserve special attention because they may retain snapshots and locks even when they are doing no useful work.
Understand the memory model
Shared buffers are a global cache, while work memory can be used by sorts and hash operations in individual plan nodes. It is not a simple per-connection reservation, but concurrent complex queries can multiply consumption. Do not raise work memory globally because one query spills; first inspect the plan, row estimates and concurrency at the time of the spill.
A safer capacity sequence
- Measure connection creation rate and the client or pool responsible.
- Set pool limits that reflect database capacity and protect critical traffic.
- Remove idle-in-transaction behaviour and unnecessary retries.
- Tune the expensive query before widening global memory limits.
- Load test the planned concurrency with realistic SQL mixes.
The aim is predictable throughput and latency. A smaller, well-managed pool often produces better results than allowing every application worker to open an unrestricted database connection.
Separate pool saturation from server exhaustion
Record whether the error comes from application pool acquisition, database connection establishment or statement execution. A pool timeout with unused database connections points to the application boundary. A database limit error points to the total connection budget. Long execution times with many active backends may indicate overload even when neither limit has been reached.
SELECT application_name, state, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY sessions DESC;
Use this as a current inventory, then compare it with each application's configured pool and replica count. Label connections with application_name so the next incident does not require reverse-engineering ownership from client addresses. Include migrations, monitoring and background jobs in the budget.
Example: a reporting change exhausts memory
Assume a report contains several sorts and hash operations, and twenty workers begin running it simultaneously. Its memory demand cannot be forecast as twenty times a single work_mem value: multiple plan nodes, hash memory settings and parallel execution affect the result. An apparently modest setting can therefore produce substantial aggregate pressure during the overlap.
Inspect the plan's sorts, hash operations, spill behaviour and row estimates. If only one report benefits from more workspace, test a transaction-scoped or session-scoped adjustment in the reporting workflow. Confirm how the connection pool resets session settings before returning a connection to another user. A setting intended for one job should not silently affect the transactional service.
Pooling has application compatibility requirements
Transaction pooling can reduce server connection demand, but applications using session-level state must be reviewed for compatibility. Temporary state, prepared-statement behaviour and connection affinity need testing against the specific pooler and configuration. Treat pooling as an application architecture change, not just a database parameter replacement.
Measure the outcome at both boundaries: application queue time and database execution time. A lower backend count with a much longer acquisition queue is not automatically an improvement. Define acceptable throughput, p95 end-to-end latency, memory headroom and failure rates before the load test. Repeat the test during the report overlap that exposed the issue, not only during a quiet transactional period.
Keep the evidence for the next incident
Mini DBA PostgreSQL monitoring provides query and metric history alongside session, lock, vacuum and WAL diagnostics, helping teams compare an incident with normal operation. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading