OnCallReadyStart learning

PostgreSQLKubernetesSRE · 5 min read

PostgreSQL "FATAL: sorry, too many clients already": count, cap, pool

max_connections is a budget that every pod's pool spends. How to get in when it is gone, find who took it, stop the bleeding without a restart, and pool.

The billing worker pages at 21:47: psycopg_pool.PoolTimeout: couldn't get a connection after 30.00 sec. The orders team says they only scaled their API for the evening sale. And when you try to connect, it says no:

terminal
$ sudo -u postgres psql -c "select count(*) from pg_stat_activity"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  sorry, too many clients already

PostgreSQL is fine. It ran out of connection slots, and the cause is almost always arithmetic in the applications.

Two FATALs, one budget

Every connection is a server process, and max_connections caps how many may exist (default 100). A few of those slots are held back:

terminal
$ sudo -u postgres psql -c "select name, setting from pg_settings where name in ('max_connections', 'superuser_reserved_connections', 'reserved_connections')"
              name              | setting
--------------------------------+---------
 superuser_reserved_connections | 3
 max_connections                | 100
 reserved_connections           | 0
(3 rows)

So there are two different errors, in this order:

the client seesmeans
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute97 are taken; only the 3 superuser slots are left
FATAL: sorry, too many clients alreadyall 100 are taken, superuser slots included

Applications usually see the first one, which is good news: there is still a door for an admin.

terminal
$ psql "host=localhost dbname=orders user=orders_app password=app-lab-pw" -c 'select 1'
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

The sorry, too many clients already at the top happened because three admin sessions had already taken the reserved slots. Never let an application connect as a superuser: it eats the slots meant for the person fixing the outage.

Get in and count

Use a reserved slot (connect as postgres, over the local socket) and group the connections by who owns them:

terminal
$ sudo -u postgres psql -c "select usename, application_name, state, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by 4 desc"
  usename   | application_name | state  | count
------------+------------------+--------+-------
 orders_app | orders-api       | idle   |    97
 postgres   | psql             | active |     1
(2 rows)

97 connections, all from one app, all idle. That is not load; that is pools. Each instance of a service opens a pool (HikariCP in Java, psycopg_pool in Python) and by default keeps it open whether it is busy or not. Count the instances:

terminal
$ sudo -u postgres psql -c "select count(distinct client_addr) as pods, count(*) as conns from pg_stat_activity where usename = 'orders_app'"
 pods | conns
------+-------
   12 |    97
(1 row)

Twelve pods with a pool of 10 want 120 connections from a server that has 97 for applications. The first pods took everything, the last ones keep failing, and the small but critical billing worker never gets a single slot. Nobody misconfigured anything on their own: three teams sized three pools without seeing each other's numbers, and the autoscaler multiplied one of them. Rolling updates do the same with maxSurge: during a deploy old and new pods hold pools at the same time.

Stop it without a restart

max_connections only changes with a restart, which in the middle of the sale is a second outage. Two statements fix it live. First, a cap on the greedy role, which applies at its next login:

terminal
$ sudo -u postgres psql -c "alter role orders_app connection limit 80"
ALTER ROLE

Then close the newest idle connections above the cap. The pools reconnect, and now they hit the role limit instead of the server limit:

terminal
$ sudo -u postgres psql -Atc "select count(*) from (select pg_terminate_backend(pid) from pg_stat_activity where usename = 'orders_app' and state = 'idle' order by backend_start desc limit 20) t"
20
$ sudo -u postgres psql -c "select application_name, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1 order by 1"
 application_name | count
------------------+-------
 billing-worker   |     2
 orders-api       |    80
 psql             |     1
(3 rows)

Billing is back. The pods that still try get a different, honest error:

terminal
$ tail -n 1 /var/log/k8s/orders-api.log
orders-api-7d9f8c6b5-r4jnl 2026-09-22T20:00:46.134+00:00  WARN 1 --- [orders-api] [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Cannot acquire connection from data source org.postgresql.ds.PGSimpleDataSource (org.postgresql.util.PSQLException: FATAL: too many connections for role "orders_app")

That is the point of per-role limits: when one service over-scales, it fails alone.

Why not just raise max_connections?

Because the next scale-out hits the new number, and connections are not free: each is a process with its own memory, and every query's snapshot looks at all of them. PostgreSQL is fastest with roughly as many active connections as it has cores, a few times that at most.

The budget to plan is simple:

text
max_connections - superuser_reserved_connections - reserved_connections - headroom
  >= sum over every client of (instances x pool size)

Headroom is for migrations, cron jobs, the metrics exporter and you. Write the sum down per service, with the autoscaler's maximum replicas, not today's.

The real fix: a pooler

PgBouncer sits between the apps and the server. Thousands of client connections share a few dozen server connections; in transaction mode a server connection goes back to the pool at every COMMIT, so idle pods cost nothing on the database:

terminal
$ sudo grep -E "^(orders|pool_mode|default_pool_size|max_client_conn|listen_)" /etc/pgbouncer/pgbouncer.ini
orders = host=127.0.0.1 port=5432 dbname=orders
listen_addr = localhost
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 10

Transaction mode has one rule: nothing may outlive a transaction. Session SETs, advisory locks, LISTEN and temporary tables break; prepared statements need PgBouncer 1.21+ with max_prepared_statements. Managed databases ship the same idea (RDS Proxy, Azure's built-in PgBouncer). A lock queue during a migration looks similar from the app side, pools full and requests timing out, so check wait_event_type before you blame the budget.

Keep it from coming back

  • Alert on count(*) from pg_stat_activity against max_connections at 80%, not at 100%.
  • Give every service its own role with a CONNECTION LIMIT, and make the limits add up to less than the budget.
  • When the disk fills instead of the slots, the outage looks different again: disk full from a replication slot.

Practise it

The lab Incident: "sorry, too many clients" after a scale-out gives you this server with twelve pods holding every slot and billing failing; The connection budget before a scale-out has you do the arithmetic before the autoscaler does, and PgBouncer in front of the apps puts the pooler in and moves the services onto it.

OnCallReady is free, with no ads and no tracking. RSS · All posts