What a connection costs · PostgreSQL

Every capacity conversation about PostgreSQL connections stalls in the same place: somebody quotes a per-connection memory figure from an article, somebody else disputes it, and the discussion ends without a measurement. The figure is workload-dependent and the only useful version of it is the one from your own server.

This lesson breaks the cost into three parts that behave differently, and shows how to measure each.

Establishment

PostgreSQL 18’s granular connection log reports the components directly.

Read-only / Safethe cost of one connection, broken down— Captured from a live 18.6 cluster with log_connections = 'all'.

$ docker logs rbpg-base18

LOG:  connection received: host=[local]
LOG:  connection authenticated: user="postgres" method=trust (.../pg_hba.conf:117)
LOG:  connection authorized: user=postgres database=shop application_name=psql
LOG:  connection ready: setup total=1.498 ms, fork=0.445 ms, authentication=0.127 ms

On an idle local cluster: a fork of 0.445 ms, authentication of 0.127 ms, and 1.498 ms total. The roughly 0.9 ms unaccounted for is the backend attaching to shared memory, registering itself, setting its database and populating its local caches.

Those numbers grow in ways worth predicting. Authentication that consults an external directory — LDAP, or a PAM stack reaching off the host — can take tens of milliseconds and is subject to that system’s availability. Fork cost rises with memory pressure on the host. And setup total rises when the catalogue is large.

When establishment cost matters: an application opening a connection per request. The measured breakdown above is 1.498 ms of setup, of which 0.445 ms is the fork itself. At 2,000 requests per second that is 2,000 forks per second and roughly three seconds of connection-establishment work arriving every second — work that has to be absorbed somewhere, and that a pool removes entirely. That is the shape covered in the connection-storm material later in this part.

When it does not: an application holding a pool. A connection opened once and used for hours has amortised its establishment cost to nothing.

Memory

Read-only / Safeone backend's resident memory, from the operating system— 15 MB for a session that has run one trivial query. Captured from a live container.

$ ps -o pid,ppid,etime,rss,args -p 124

    PID    PPID     ELAPSED   RSS COMMAND
  124       1       00:03 15360 postgres: postgres postgres [local] SELECT

15 MB is the floor for a session that has done almost nothing, and quoting it as the per-connection cost would be wrong in both directions.

It is an overstatement because a large part of that RSS is shared pages inherited from the postmaster through fork() — the buffer pool mapping among them — and counting it once per backend double-counts memory the host allocated once.

It is an understatement because it excludes everything the session has not yet done. A backend grows as it touches catalogue entries, and it grows sharply while executing a query that allocates work_mem for sorts and hashes. Part XI works that arithmetic through in full; the short version is that work_mem is per operation, so one query can allocate it several times over.

Measure on your own server, under your own workload

The honest procedure takes ten minutes and produces a number you can defend.

# Backends, with resident memory, sorted by size
psql -U postgres -tAc \
  "SELECT pid FROM pg_stat_activity WHERE backend_type='client backend'" \
  | xargs -r ps -o pid,rss,args -p 2>/dev/null | sort -k2 -n

# The same, correlated with what each session is doing
psql -U postgres -c \
  "SELECT pid, application_name, state, left(query,40) AS query
     FROM pg_stat_activity WHERE backend_type='client backend'
    ORDER BY backend_start"

Take the measurement at peak, not at rest, and take it while the heaviest queries are running rather than between them. The number that matters for capacity is the size of a backend doing the work you are sizing for.

For a container or a cgroup-limited host, the limit that will actually be enforced is the cgroup’s, not the host’s, and Part XI covers reading it.

Contention

The third cost has no configuration remedy and is the reason the other two are not the whole story.

Every backend that wants a shared resource contends with every other: a buffer in the pool, an entry in the lock table, a slot to insert a WAL record. Below some level of concurrency, adding backends adds throughput. Above it, adding backends adds contention, and total throughput falls.

flowchart LR
    A["Low concurrency\nthroughput rises\nwith each backend"] --> B["Plateau\nadditional backends\nadd no throughput"]
    B --> C["Beyond the plateau\ncontention dominates\ntotal throughput FALLS"]

The position of the plateau is a property of the workload and the hardware, not a number that can be quoted. What is reliable is the shape: it exists, and past it more connections make the database slower.

This is the mechanism behind the single most common wrong response in this part of the course.

Raising max_connections because the application is getting connection errors

The errors stop, so it looks like a fix. What actually happened is that the error was the only thing preventing the system from becoming more overloaded.

If the cluster was already past its concurrency plateau, the additional connections reduce total throughput. Each query takes longer. The application’s own timeouts start firing, so it retries, opening more connections — which is the connection-storm loop, and it converges on an outage rather than away from one.

Memory compounds it. Each new backend can allocate work_mem per operation, so the ceiling you just raised is also a licence for a proportionally larger memory footprint, and the way that resolves is the OOM killer terminating a backend, which forces crash recovery for the whole cluster.

The diagnostic that should precede any change to max_connections is one query:

SELECT state, count(*) FROM pg_stat_activity
 WHERE backend_type = 'client backend' GROUP BY state;

A cluster with 4 active and 190 idle is not short of capacity; it is holding connections it is not using, and a pooler is the answer. A cluster with 190 active is genuinely saturated, and more connections will make it worse rather than better.

Which workloads make connections expensive

Workload shape	Establishment	Memory	Contention
Long-lived pool, few connections	Negligible	Predictable	Low
Connection per HTTP request	Dominant	Low each	Low
Many idle connections from many app replicas	Negligible	Dominant	Low
High concurrency of active queries	Negligible	High	Dominant
Serverless functions scaling on demand	Dominant	Moderate	Spiky

The middle row is the one most estates actually have: dozens of application replicas, each with its own pool, most connections idle most of the time. The cost there is memory and connection slots rather than contention, and the remedy is a pooler that lets many application connections share fewer database connections.

Why an idle connection is not free, and what it does still consume

An idle backend is not executing anything, so it costs no CPU beyond being scheduled occasionally. It is not free.

It holds a process slot and its private memory, which is the resident set the operating system will not reclaim while the process lives. Its catalogue caches persist, so a session that queried a wide schema keeps that cache for as long as it stays connected.

It holds a connection slot, which is the scarce resource max_connections bounds. A cluster with 190 idle connections and a ceiling of 200 has ten slots for everything else, including the administrator trying to investigate.

It holds an entry in the shared backend array, which is scanned by several operations — including computing the cleanup horizon that Part VIII depends on. The scan is over max_connections entries regardless of how many are in use, which is a small reason why setting the ceiling far above the real requirement is not entirely free.

And it holds any locks it acquired and has not released, which for an idle session outside a transaction is normally none, and for an idle session inside a transaction is everything it touched. That distinction is the subject of a later lesson in this part and it is sharper than most material makes it.

What an idle connection does not hold, if it is outside a transaction, is a transaction ID or a snapshot. That is why an idle pool is a memory and slot problem rather than a vacuum problem.

Production discipline

Measure per-backend memory on your own server, at peak. A figure from an article is not evidence about your workload, and RSS at rest understates a backend running a real query.
Read the state breakdown before proposing any connection-count change. Idle-heavy and active-heavy clusters need opposite responses.
Treat the concurrency plateau as real even though its position is unknown. Past it, more connections reduce total throughput.
Cost establishment separately from memory. A connection-per-request application has a problem that a larger ceiling cannot fix and a pooler can.
Remember an idle connection still holds a slot, its memory and its caches, so a pool sized far above what the application uses is a standing cost.

Cross-course references

Linux for Production Sysadmins — Part VI (Processes) covers fork and resident set accounting, and Part XL (Memory performance) covers why summing RSS across processes overstates real consumption.
Kubernetes for Production Sysadmins — Part XII (Resources) covers the per-pod limits that decide how many application replicas exist, which is the input to the connection count.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting the connection state breakdown so the plateau is visible before it is reached.