Size a Postgres connection pool against the memory the server actually has. The dominant term is work_mem, which is charged per sort node rather than per connection, and that is the multiplier that turns a comfortable margin into an OOM.
Worked setups you can load into the form above. Each one is a decision the generator makes differently, and the reason it makes it.
400 connections at 8 MB looks like 3.2 GB. With four sort nodes in the heaviest query it is 12.8 GB, and that is the number the server actually has to survive.
Pools open their maximum under load, so the newest instances get FATAL: sorry, too many clients already. It gets worse as you scale out, which is the opposite of what scaling out is for.
A connection is a process. Past a few per core the server is switching between backends that are all waiting, so throughput falls while latency rises.
Transaction mode decouples client count from backend count. The price is that session state does not survive, which has to be decided before the switch rather than during the incident that forced it.
These are the ones that fail silently. The config is accepted, nothing raises an error, and the consequence arrives later.
`work_mem` is allocated per sort or hash NODE. A query with three sorts takes it three times, and a parallel query takes one per worker on top, so the real worst case is several times the figure that gets budgeted.
Instead:Multiply by the number of sort and hash nodes in your heaviest plan. Count them in `EXPLAIN`.
A connection is a process. Past a few per core the server is switching between backends that are all waiting, so throughput falls and latency rises. This reliably makes the outage worse.
Instead:Put a pooler in front and keep `max_connections` as a safety limit rather than as a capacity setting.
Twenty connections per instance across ten instances is two hundred, opened eagerly under load. Past `max_connections` the newest instances get `FATAL: sorry, too many clients already`, so the failure moves around and worsens as you scale out.
Instead:Multiply instances by pool size and compare against `max_connections` before scaling out.
A client gets a different backend per transaction, so `SET` without `LOCAL`, advisory locks, `LISTEN`, temporary tables and server-side prepared statements all break or leak between clients.
Instead:Use `SET LOCAL`, take advisory locks transaction-scoped, and keep a small session-mode pool for anything that genuinely needs session state.
Postgres relies on the operating system page cache as a second tier, so a very large `shared_buffers` caches the same pages twice and starves the filesystem cache. It also lengthens checkpoints.
Instead:25% of RAM as a starting point, up to about 40% on a dedicated server with a well understood workload.
The arithmetic that decides whether a Postgres server survives a busy afternoon has one term everybody gets wrong, and getting it wrong understates the worst case by whatever the number of sorts in your heaviest query happens to be.
It is allocated per sort or hash node in the plan, not per connection and not per query. A query with two sorts and a hash join takes three allocations, a parallel query takes one per worker on top, and nothing anywhere enforces a total. So the worst case is max_connections times work_mem times nodes per query, and a server sized on max_connections times work_mem has a comfortable-looking margin that does not exist.
200 connections, work_mem 4MB
sized as 200 x 4MB = 800 MB looks fine
really 200 x 4MB x 3 nodes = 2,400 MB is what happensPostgres forks a backend per connection, several megabytes each before any query work. Past roughly two to four active connections per core the server is not doing more work, it is switching between processes that are all waiting on the same resources, and throughput falls while latency climbs. Raising max_connections in response to connection errors is the reflex, and it reliably makes the outage worse.
A pool of twenty per instance across ten instances is two hundred connections, opened eagerly under load. When that exceeds max_connections the last instances to start get FATAL: sorry, too many clients already, so the failure moves around and gets worse as you scale out. That is the point at which a pooler stops being optional.
Transaction mode is what makes hundreds of clients share a few dozen backends, because a client holds one only for the length of a transaction. The price is that session state does not survive: SET without LOCAL, advisory locks, LISTEN and NOTIFY, temporary tables, and server-side prepared statements on clients that cannot reuse them. Decide those before switching, not during the incident that made you switch.
It is arithmetic on the numbers you enter, not a measurement of your server. The per-backend figure is a representative 7 MB and real usage varies with extensions, prepared statements and the connection's history. The sort-node count is the input that moves the answer most and the one most often guessed: get it from EXPLAIN on your heaviest query rather than estimating. It also does not model parallel workers, which multiply work_mem again, so treat the worst case here as a floor.