DEV Community

Philip McClarence
Philip McClarence

Posted on

Postgres max_connections: The Real Memory Math

I watched someone go from max_connections = 100 to 2000 at 4pm and take the box down the same night. The change itself took ten seconds. The 2am crash recovery took considerably longer.

📖 Read the full guide: max_connections in Postgres: A Memory Decision in Disguise

Postgres max_connections: The Real Memory Math

postgres max_connections isn't a knob that means "allow more clients." It resizes shared memory at startup and multiplies your worst-case per-backend memory exposure by a bigger number. Here's how to check your real usage, do the memory math, and decide whether a new value actually fits — before you touch it.

Quick answer

  • Run SHOW max_connections, then count only backend_type = 'client backend' in pg_stat_activity. Autovacuum workers and walsenders aren't your app.
  • Sample that count over hours, not once. A single count(*) tells you nothing about peak load.
  • Compute the ceiling first: shared_buffers + (per-backend private memory × max_connections) + concurrent work_mem allocations + maintenance_work_mem × autovacuum workers + OS headroom.
  • For most teams, a connection pooler solves this faster than a config change. PgBouncer in transaction mode lets 2000 clients share 40 server connections.
  • If you genuinely need to raise it: ALTER SYSTEM SET, check pending_restart, pre-flight shared_memory_size, raise standbys first, restart in a window with logs tailing.

What max_connections actually reserves

Postgres forks a dedicated backend process per client connection. Each connection is an OS process with its own address space and catalog caches before it runs a single query. There's no thread pool underneath — one process per connection, full stop.

max_connections has a postmaster context, meaning a reload won't apply it:

SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 100     | postmaster | f
Enter fullscreen mode Exit fullscreen mode

It needs a restart because the value sizes fixed shared memory structures at startup. The lock table is sized from max_locks_per_transaction × (max_connections + max_prepared_transactions). The predicate lock table works the same way. Those are allocated once, up front.

Since PG 15 you can see the total without restarting, and even run this against a stopped or separate data directory to pre-flight a value:

$ postgres -C shared_memory_size -D $PGDATA
4489
$ postgres -C shared_memory_size_in_huge_pages -D $PGDATA
2245
Enter fullscreen mode Exit fullscreen mode

Since PG 13, pg_shmem_allocations shows where it went:

SELECT name, pg_size_pretty(allocated_size) FROM pg_shmem_allocations
ORDER BY allocated_size DESC LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

Postgres connection limit check: find out what you're actually using 

SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('superuser_reserved_connections')::int AS su_reserved,
  count(*) FILTER (WHERE backend_type = 'client backend') AS client_backends,
  current_setting('max_connections')::int
    - current_setting('superuser_reserved_connections')::int
    - count(*) FILTER (WHERE backend_type = 'client backend') AS available
FROM pg_stat_activity;
Enter fullscreen mode Exit fullscreen mode

superuser_reserved_connections defaults to 3 and comes off the top for ordinary users. PG 16 added reserved_connections (default 0) for roles granted pg_use_reserved_connections. Since PG 12, max_wal_senders no longer counts against max_connections, so replication slots aren't eating your budget.

Now break it down by type. backend_type was added in PostgreSQL 10 so you stop conflating autovacuum workers and walsenders with real client load:

SELECT backend_type, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2 ORDER BY 3 DESC;

   backend_type    |        state        | count
-------------------+---------------------+-------
 client backend    | idle                |    61
 client backend    | active              |     9
 client backend    | idle in transaction |     7
 autovacuum worker | active              |     3
 walsender         | active              |     2
 background writer |                     |     1
Enter fullscreen mode Exit fullscreen mode

Seventy client backends, and 61 are doing nothing. Run this on a cron every 30 seconds into a table for a day before you decide anything. One sample at 11am isn't a capacity plan.

If you're not superuser, query and state come back NULL for other roles unless you have pg_read_all_stats or pg_monitor.

Why you're seeing "sorry, too many clients already"

Symptom Diagnostic Fix that isn't "raise the number"
Idle-in-transaction leak state = 'idle in transaction' with old xact_start idle_in_transaction_session_timeout plus fix the app's transaction scope
App pool × pod count pool size × replica count > max_connections Shrink per-pod pool; 5 per pod × 40 pods is 200
No pooler Connection count tracks request rate exactly PgBouncer
Long analytics queries holding slots now() - query_start > '5 min' Separate read replica, statement_timeout
Deploy-time storms Spike at rollout, settles in 60s Staggered rollout, pooler absorbs it 
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

idle_in_transaction_session_timeout defaults to 0 (disabled). Setting it to '5min' is almost always a better first move than a restart — and it applies with a reload, no downtime.

The work_mem memory calculation before you raise anything

Concrete box: 16 GB RAM, dedicated Postgres, shared_buffers = 4GB, work_mem = 16MB, hash_mem_multiplier = 2.0 (the PG 15 default), maintenance_work_mem = 512MB, three autovacuum workers.

First, measure real per-backend private memory. Don't use ps RSS — it counts shared_buffers pages the backend has touched and will tell you every backend uses 900 MB. smaps_rollup gives you the honest private footprint:

$ for p in $(pgrep -f "postgres: app"); do \
    grep -H Private_ /proc/$p/smaps_rollup 2>/dev/null | \
    awk -F: '{s+=$3} END {print s/1024" MB"}'; done | sort -rn | head -3
41.2 MB
12.8 MB
9.6 MB
Enter fullscreen mode Exit fullscreen mode

Call it 10 MB typical. Now the arithmetic at 100 vs 400:

At max_connections = 100
  shared_buffers                4.0 GB
  private (100 × 10 MB)         1.0 GB
  work_mem: 25 active × 2 nodes × 16 MB × 2.0   1.6 GB
  maintenance (3 × 512 MB)      1.5 GB
  OS + page cache headroom      1.0 GB
                              --------
                                9.1 GB   fits in 16 GB

At max_connections = 400 (same active ratio)
  shared_buffers                4.0 GB
  private (400 × 10 MB)         4.0 GB
  work_mem: 100 active × 2 × 16 MB × 2.0        6.4 GB
  maintenance                   1.5 GB
  headroom                      1.0 GB
                              --------
                               16.9 GB   over budget
Enter fullscreen mode Exit fullscreen mode

work_mem is a per-node limit, not per-connection. One query with two sorts and a hash join can allocate several multiples of it, and every parallel worker gets its own allowance. That 2-node assumption is conservative for OLTP and wildly optimistic for a reporting workload.

The 400 number doesn't fit — and that's before anyone runs a bad report.

PgBouncer vs raising max_connections

For most people reading this, PgBouncer in transaction mode ends the conversation. A server connection is held only for the duration of a transaction, so idle clients cost nothing on the database side.

[databases]
app = host=10.0.1.20 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
server_idle_timeout = 60
Enter fullscreen mode Exit fullscreen mode

Defaults are max_client_conn = 100 and default_pool_size = 20, so both need raising deliberately, tuned against the real usage numbers from the connection check above — not guesses.

What transaction mode breaks:

Feature Works in transaction mode?
Session-level SET / RESET No
LISTEN / NOTIFY No
WITH HOLD cursors No
Session-level advisory locks No
Temporary tables No
Named prepared statements Only from PgBouncer 1.21+
Transaction-scoped advisory locks Yes

PgBouncer publishes a full SQL feature map by pooling mode. Read it before you flip the switch. If your app leans on any "No" row, the honest options are session pooling (which gives back most of the connection savings) or a genuinely larger max_connections, not a workaround.

One operational detail: PgBouncer is a single-threaded event loop. To use more than one core you run multiple instances behind SO_REUSEPORT, supported since 1.19.

When raising max_connections is still the right call: your app depends on LISTEN/NOTIFY or session advisory locks and can't be refactored; a fleet of stateless services each needs a handful of direct connections and a pooler adds latency you can't afford; you already run a pooler and the server-side pool needs to be bigger under genuinely measured load. PG 14's snapshot scalability work in GetSnapshotData cut the overhead idle connections impose on active ones, but an idle connection still costs a process slot and its private memory.

How to increase max_connections in PostgreSQL safely 

ALTER SYSTEM SET max_connections = 200;

SELECT name, setting, pending_restart FROM pg_settings
WHERE name = 'max_connections';

      name       | setting | pending_restart
-----------------+---------+-----------------
 max_connections | 100     | t
Enter fullscreen mode Exit fullscreen mode

Pre-flight the new shared memory size before restarting:

$ postgres -C shared_memory_size -D $PGDATA
5761
Enter fullscreen mode Exit fullscreen mode

If huge_pages = on, recount now:

$ postgres -C shared_memory_size_in_huge_pages -D $PGDATA
2881
$ sysctl -w vm.nr_hugepages=3000
$ echo "vm.nr_hugepages = 3000" >> /etc/sysctl.d/99-postgres.conf
Enter fullscreen mode Exit fullscreen mode

Raise every standby before the primary. A hot standby refuses to continue recovery if max_connections, max_worker_processes, max_prepared_transactions, or max_locks_per_transaction are lower than on the primary. Standby first, primary second, no exceptions.

A healthy restart looks like this:

LOG:  database system was shut down at 2026-08-04 02:14:07 UTC
LOG:  database system is ready to accept connections
Enter fullscreen mode Exit fullscreen mode

Three ways this bites you after restart

Huge pages shortfall. You bumped the setting but forgot vm.nr_hugepages:

FATAL:  could not map anonymous shared memory: Cannot allocate memory
Enter fullscreen mode Exit fullscreen mode

Recount as above, or set huge_pages = try so it falls back to normal pages instead of refusing to start — safer during a change window even if it costs some TLB efficiency.

Standby refuses recovery. You did the primary first:

FATAL:  hot standby is not possible because max_connections = 100 is a lower
        setting than on the primary server (its value was 200)
Enter fullscreen mode Exit fullscreen mode

Raise and restart the standby, ordering as above.

OOM killer. The box fits at idle and doesn't fit under load:

LOG:  server process (PID 28841) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
Enter fullscreen mode Exit fullscreen mode

Postgres treats an OOM kill as a crash and forces every other session into crash recovery, so one killed backend costs you the whole cluster's availability for the recovery window. Mitigations: PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE to protect the postmaster while leaving backends killable, and vm.overcommit_memory = 2 with a tuned overcommit_ratio on dedicated hosts so the kernel refuses the overcommit instead of killing you later.

And the two errors clients will actually see at the ceiling:

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication
        superuser connections
Enter fullscreen mode Exit fullscreen mode

Managed Postgres (RDS, Aurora, Cloud SQL)

On RDS and Aurora PostgreSQL the default is LEAST({DBInstanceClassMemory/9531392}, 5000), derived from instance memory. It's a static parameter, so changing it in the parameter group requires a DB instance reboot. There's no postgresql.conf to edit and no ALTER SYSTEM. Cloud SQL follows the same memory-derived, reboot-to-apply pattern. For pooling, RDS Proxy is the AWS-native option; on Supabase it's Supavisor. Same tradeoffs as PgBouncer transaction mode apply.

The order of fixes that actually works

  1. idle_in_transaction_session_timeout = '5min'. Costs nothing, ships as a reload, catches the most common cause.
  2. Fix app-side pool sizing. Per-pod pool × replica count is the real number, and most teams have never multiplied it out.
  3. PgBouncer in transaction mode. This is the correct answer for the large majority of connection-exhaustion tickets.
  4. Only then, max_connections — with the memory arithmetic written down and the standby restarted first.

If you want a second opinion on where your instance sits today, MyDBA's free health check looks at connection distribution and memory settings together, which is the pairing that matters here.

The value in postgresql.conf is easy to change. The RAM on the box is not.

Top comments (0)