Tuning PostgreSQL max_connections and superuser_reserved_connections

This guide is part of PostgreSQL Server-Side Connection Diagnostics. max_connections is the number every pool sizing decision ultimately answers to, and FATAL: sorry, too many clients already is the error that announces you got the arithmetic wrong. This page covers what the setting actually costs, why raising it is usually the wrong first move, and how to guarantee you can still log in when it is reached.

The instinct when connections are refused is to raise the ceiling. That instinct is wrong often enough to be worth stating plainly: max_connections is a safety limit, not a capacity dial. Raising it lets more clients in; it does not give PostgreSQL more CPUs, more disk bandwidth, or more memory. Past the instance’s useful concurrency, additional backends compete for the same resources and total throughput falls while latency rises.

What a connection costs

PostgreSQL uses a process-per-connection model. Each connection is a forked backend process, and the costs are concrete:

Resource Per-connection cost Notes
Process one OS process fork cost on connect, context-switch cost while running
Base memory roughly 5–10 MB resident catalog caches, relation cache, plan cache
work_mem up to work_mem per sort or hash node a single query can use several multiples
temp_buffers up to temp_buffers if temp tables are used default 8 MB, allocated lazily
Shared memory a slot in several fixed-size arrays sized at startup from max_connections
Snapshot bookkeeping participation in every snapshot taken cost grows with connection count

The work_mem row is where memory estimates go badly wrong. work_mem is not per connection — it is per sort, hash join, or hash aggregate node in a plan. A query with three hash joins can use three times work_mem, and a parallel query multiplies again by worker count. Sizing memory as max_connections x work_mem therefore understates the worst case, sometimes by a large factor.

The last row is subtler and matters at scale. Taking a snapshot involves examining the state of every active backend, so snapshot cost grows with connection count regardless of whether those connections are doing anything. This is a large part of why an instance with 5,000 mostly-idle connections performs worse than the same instance with 200 busy ones.

Throughput peaks well below max_connections Two curves against connection count. Throughput rises, plateaus at roughly the instance's parallel capacity, then declines. Latency is flat until that point and then rises steeply. The max_connections ceiling is far to the right of the peak. More connections stop helping long before max_connections concurrent connections doing work throughput latency useful concurrency max_connections The gap between the two dashed lines is exactly what a connection pool exists to absorb.
Throughput peaks at the instance's parallel capacity. The ceiling sits far to the right, and the pool's job is to keep you near the peak.

Choosing a value

Work from the instance’s resources rather than from client demand:

  1. Estimate useful concurrency. As a starting heuristic for a transactional workload, that is roughly cpu_cores x 2 + effective_spindle_count. On a 16-core instance with SSD storage that is somewhere near 35–50 concurrently executing statements. This is the number your pools should sum to, not max_connections.
  2. Add headroom for connections that exist but are not executing. Pooled connections spend most of their time idle. A factor of three to four over useful concurrency is generous for a well-pooled fleet.
  3. Add operational reserve. Migrations, monitoring agents, replication tooling, backup jobs, and a human with psql all need slots. Twenty to thirty is realistic.
  4. Check memory. Multiply the resulting count by base per-backend memory, then confirm there is room left for shared_buffers and the OS page cache. If the arithmetic does not fit, the ceiling is too high for the instance, not the other way round.

For a 16-core, 64 GB instance serving a well-pooled fleet, that reasoning lands around max_connections = 200 — which is why so many production PostgreSQL instances use exactly that number. The default of 100 is a conservative starting point suited to a small instance.

# postgresql.conf
max_connections = 200
superuser_reserved_connections = 5
reserved_connections = 10          # PostgreSQL 16+
work_mem = 16MB
shared_buffers = 16GB

Changing max_connections requires a restart — it sizes fixed shared-memory arrays at startup. Plan for that, and note that raising it also raises PostgreSQL’s shared memory footprint slightly, which on a constrained instance can itself fail to start.

If your client count genuinely exceeds what the instance can support, the answer is a connection proxy, not a higher ceiling. A pooler in transaction mode lets thousands of clients share a few dozen backends — the arithmetic is in configuring PgBouncer pool_size and max_client_conn.

Reserving slots so you can still get in

superuser_reserved_connections carves slots out of max_connections that only superusers may occupy. It defaults to 3, and its entire purpose is the incident where an application has consumed every connection: without it, the database is unreachable exactly when you most need to reach it.

The arithmetic is subtractive, and this catches people. With max_connections = 200 and superuser_reserved_connections = 5, ordinary users can open at most 195 connections. Non-superusers hit FATAL: sorry, too many clients already at 195, not 200. Budget your pools against the effective number, not the headline one.

PostgreSQL 16 added reserved_connections, which reserves slots for roles granted pg_use_reserved_connections rather than requiring superuser. This is materially better practice: your monitoring agent and your on-call runbook can use a reserved slot without holding superuser rights.

-- PostgreSQL 16+: a non-superuser role that can always get in
CREATE ROLE oncall LOGIN;
GRANT pg_use_reserved_connections TO oncall;
GRANT pg_monitor TO oncall;

The effective ceiling for ordinary application roles then becomes max_connections - superuser_reserved_connections - reserved_connections — 185 in the configuration above. Write that number down, because it is the one your pool budget must fit inside.

The ceiling is not what applications get A bar representing max_connections of 200, split into superuser reserved slots, reserved slots for privileged roles, an operational allowance, and the remainder that application pools may consume. Applications never get the headline number max_connections = 200 5 superuser 10 reserved roles 25 migrations, backups, agents 160 — the budget every application pool must fit inside divide by replica count, then by pool count A forty-pod deployment with a pool maximum of twenty needs 800 connections and fits nowhere in this budget. Either the per-pod pool shrinks to four, or a proxy multiplexes the fleet onto a smaller server-side pool. Raising max_connections to 900 is the option that looks easiest and performs worst.
Reserved slots, operational allowance and application budget all come out of the same 200. Only the green band is yours.

Checking the current state

Three queries answer the practical questions. How close to the ceiling are we:

SELECT current_setting('max_connections')::int                     AS ceiling,
       count(*)                                                    AS used,
       current_setting('max_connections')::int - count(*)           AS free
FROM pg_stat_activity;

Who is consuming the slots:

SELECT usename,
       application_name,
       client_addr,
       count(*) FILTER (WHERE state = 'active')             AS active,
       count(*) FILTER (WHERE state = 'idle')               AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_xact,
       count(*)                                              AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY total DESC;

That second query is the one to run first during a too many clients incident. A single application_name holding a disproportionate share names the culprit immediately — which is why setting a distinct application_name in every service’s connection string is worth the five minutes it takes.

And whether individual roles have their own limits:

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1;
SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit <> -1;

Per-role and per-database limits are an underused containment tool. Capping a batch role at 20 connections means a runaway batch job cannot consume the whole ceiling and take the request path down with it:

ALTER ROLE reporting CONNECTION LIMIT 20;
ALTER DATABASE analytics CONNECTION LIMIT 40;

Common failure patterns and remediation

Symptom Underlying cause Remediation
FATAL: sorry, too many clients already Fleet’s total pool maximum exceeds the effective ceiling Shrink per-pod pools, or add a proxy; raising the ceiling is a last resort
Refused at 195 with max_connections = 200 Superuser-reserved slots are subtracted from the total Budget against the effective number
Cannot connect to investigate an incident No reserved slots, or the on-call role is not privileged Set superuser_reserved_connections; grant pg_use_reserved_connections
Out-of-memory kills after raising the ceiling work_mem multiplied across more backends and more plan nodes Lower work_mem, or lower the ceiling; set work_mem per role
Throughput fell after raising the ceiling Past useful concurrency; contention rose Lower the ceiling and pool sizes back toward parallel capacity
PostgreSQL will not start after the change Shared memory request exceeds the OS limit Raise the kernel shared memory limits, or lower the ceiling
One batch job exhausts everything No per-role limit ALTER ROLE ... CONNECTION LIMIT
Slots consumed by connections doing nothing Idle-in-transaction accumulation Set idle_in_transaction_session_timeout
Per-role limits contain a runaway Without per-role connection limits, one misbehaving service can consume the whole ceiling and take every other service down with it. Role-level limits confine the failure to that service. A role-level cap turns a fleet outage into a single-service one no role limits batch job opens 200 connections ceiling consumed web tier cannot connect role limit set batch capped at 20 contained batch fails, web tier unaffected ALTER ROLE ... CONNECTION LIMIT is the only containment that works regardless of client configuration. Give every service its own role so the limit can be set independently.
Per-role connection limits are the only server-side containment that does not depend on client behaviour.

Managed database specifics

On managed services max_connections is often derived rather than set directly, and the derivation is worth knowing before you size a pool against it.

  • Amazon RDS and Aurora (PostgreSQL) default max_connections to a formula based on instance memory — roughly LEAST({DBInstanceClassMemory/9531392}, 5000). Changing it means editing the parameter group and rebooting. Aurora Serverless v2 recomputes it as capacity changes, so the ceiling itself moves; see AWS Aurora Connection Scaling.
  • Cloud SQL for PostgreSQL sets a default based on machine type and allows it as a database flag, with restart required.
  • Azure Database for PostgreSQL Flexible Server publishes a per-tier maximum you cannot exceed.

In all three, a portion of the ceiling is consumed by the provider’s own management and monitoring connections before your application sees any of it. Always measure the effective free count from pg_stat_activity on the running instance rather than trusting the documented number.

Operational boundary

max_connections bounds how many connections may exist. It does nothing about how they are used: a ceiling of 500 with every backend sitting idle in transaction is a worse position than 200 with disciplined transaction scoping. Before changing it, confirm from wait events that the connections you already have are doing useful work — the method is in interpreting pg_stat_activity wait events for pool issues.

The honest summary: raising max_connections is correct when the instance has demonstrable headroom and the connections would be doing real work. It is incorrect — and actively harmful — when it is used to accommodate a fleet that is holding far more connections than it needs.

Frequently Asked Questions

Does raising max_connections require a restart?
Yes. It sizes fixed shared-memory structures allocated at startup, so it cannot be changed with a reload.
How much memory does each connection use?
Roughly 5–10 MB of base backend memory, plus up to work_mem per sort or hash node in whatever query it runs, plus temp_buffers if it creates temporary tables. The work_mem component is per plan node, not per connection, so worst-case estimates must account for multiple nodes.
What is the difference between superuser_reserved_connections and reserved_connections?
superuser_reserved_connections reserves slots usable only by superusers. reserved_connections, added in PostgreSQL 16, reserves slots for roles granted pg_use_reserved_connections — which lets on-call and monitoring roles keep a guaranteed slot without superuser rights.
Why do I hit the limit before reaching max_connections?
Reserved slots are subtracted from the total for ordinary roles. Per-role or per-database CONNECTION LIMIT settings can also bind earlier, as can a proxy’s own server-side cap.
Should I set max_connections very high and let the pool manage it?
No. A high ceiling removes the safety limit that stops a misconfigured fleet from overwhelming the instance, and it increases snapshot and shared-memory costs even for connections that are idle.
How do I stop one service from taking everything?
ALTER ROLE ... CONNECTION LIMIT per service role, and give each service its own role. It is the only server-side containment that works regardless of what the client is configured to do.
What if my client count genuinely exceeds any reasonable ceiling?
Use a proxy in transaction mode. That is precisely the problem it solves — thousands of client connections multiplexed onto a few dozen backends.