PostgreSQL Connection Readiness Review

How to use this review

Quarterly, and whenever an instance count changes. Most of it is arithmetic that somebody has to do once and then keep current.

The arithmetic

instances x pool_max  ≤  max_connections - superuser_reserved - reserved

The change that has already been made twice

The three refusals

Measured on PostgreSQL 18.6 with max_connections=15, superuser_reserved_connections=3, reserved_connections=2:

The ordinary ceiling is max_connections minus both reserves. Here that is 10, and the eleventh ordinary connection was refused with five slots free.

A pool that grows on latency is an amplifier

Past the knee of the throughput curve, more connections buy negative throughput. Measured on a four-core cluster:

A pool configured to add connections when checkouts wait will, on seeing latency, move the workload further right on that curve — which produces more latency. In one incident a four-second storage stall started that loop, and only an application restart broke it, two hours after storage had recovered.

Cap the pool below the knee, and make it queue rather than grow.

If you deploy a pooler, deploy it for the right reason

Measured at 400 clients on a 1.4 ms query: 465 server backends direct against 65 pooled, with the pooled run marginally faster.

Measured at 80 clients on a sub-millisecond query: 5124 tps direct against 2627 pooled — and at 400 clients on that workload, three times slower, because a single-threaded proxy becomes the bottleneck.

The consistent product across all of them is the backend count. That is what a pooler is for.

Where the numbers come from

The connection count comes from pg_stat_activity sampled over time, not read once, because the number that matters is the peak and the shape rather than the instant. max_connections and the three reserved-slot settings come from pg_settings with their source.

Per-backend memory comes from a measurement on a host running the real workload, not from a figure quoted in a lesson, because a backend executing real queries with real work_mem allocations is substantially larger than an idle one.

Pooler figures come from the pooler’s own statistics — client connections, server connections, and maxwait, which is the one that says whether the pool is too small.

Access this needs

A role holding pg_monitor for pg_stat_activity, the connection counters and the reserved-slot settings. Read access to each application’s own connection configuration — its pool size, its timeouts, its retry behaviour — which usually means access the database team does not have and has to ask for.

If a pooler is deployed, read access to its statistics and its configuration, and to the auth file’s existence and permissions, not its contents.

Nothing here requires the authority to terminate a session. If an item cannot be completed without doing so, record that and stop.

What the review produces

A dated record naming the reviewer, the measured peak connection count against max_connections, the per-backend memory measurement, and the disposition of every item. Attach the arithmetic: peak connections times per-backend memory, plus shared_buffers, against the host’s memory.

An application that opens a connection per request, and a pool that grows in response to latency, are both findings that go to that application’s owner, because neither can be fixed on the database side.

Sign-off

• Reviewer: ________________ Date: ___________
• Database owner: ___________ Date: ___________
• Service owner: ____________ Date: ___________

Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint: record the date, the reviewer, the disposition of every item that did not pass, and the name of whoever accepted the residual risk.