Database connections

Alert: database-connections. Severity 2.

Production runs GP_Standard_D2ds_v4, whose max_connections is 859. Postgres holds 15 of those back, so the application may open 844 and this fires above 675.

The denominator is not max_connections

Postgres refuses an ordinary role once the free slots fall to reserved_connections plus superuser_reserved_connections, which are 5 and 10 on every SKU this project allows. The application is deliberately not a member of pg_use_reserved_connections, so what it actually gets is max_connections - 15.

This matters most on the small SKU, where the gap decides whether the alert can fire at all. A B_Standard_B1ms reports max_connections = 50 and hands the application 35. A threshold set at 80 percent of 50 is 40, and 40 is above 35: the rule would have sat green while the server was already answering

remaining connection slots are reserved for roles with privileges of the
"pg_use_reserved_connections" role

That is what staging did, and it is why the threshold is computed from usable_connections in infra/terraform/modules/control-plane/database.tf rather than from max_connections. The same value bounds the application’s own pool at plan time, so the alert and the ceiling cannot drift apart.

Confirm the SKU’s number against the running server if you add one to the table. If it is wrong, this alert is quietly measuring the wrong fraction and nothing else will ever say so:

az postgres flexible-server parameter show \
  -g af-cp-prod-centralus -s afcpprod-pg -n max_connections \
  --query "{value:value,default:defaultValue}" -o json

It reads the peak, not the average

The criterion is Maximum over fifteen minutes. Connection exhaustion here is a burst: every replica runs the same five minute housekeeping sweep, so they all reach for the pool at once and let go again. Staging’s own numbers while it was refusing connections were 6 to 11 for four minutes out of every five and 33 to 39 in the fifth. An Average reads that as 12 and stays green through every one of the spikes that took the service down.

Where the connections come from

SELECT usename, state, host(client_addr), count(*)
FROM pg_stat_activity
WHERE client_addr IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Count the distinct client addresses first. One address per replica, so this is the number of control plane processes talking to the database, and it is the number that is wrong most often. If it is larger than the replicas the app is supposed to be running, the connections are coming from revisions nobody is serving traffic from:

az containerapp revision list -n "$APP" -g "$RG" \
  --query "[?properties.active].{name:name,replicas:properties.replicas,traffic:properties.trafficWeight}" -o table

A revision at zero traffic is not idle. In Multiple revision mode it keeps min_replicas running, and each of those is a whole control plane process holding a pool and sweeping the database every five minutes. Forty six deploys to staging left forty six of them. deploy/cd/deploy.sh now deactivates superseded revisions after each release and fails the run if what remains does not fit, but a revision reactivated by hand for a rollback and left there will do the same thing again.

af_app with hundreds of connections and the expected number of addresses means replicas scaled out under load. Six replicas at a pool of ten is 60, which is nowhere near production’s ceiling.

af_migrator with more than one is a person or a script holding a privileged session open. That role can drop the policies that isolate tenants, so an unexplained one is a security question and not a capacity one.

Anything in idle in transaction holds locks and holds back autovacuum, and it is why a connection count climbs without traffic climbing.

What not to do

Do not raise max_connections. Each connection is a process with its own memory, and a server that is out of connections is usually about to be out of memory. The fix is on the client side: fewer active revisions, fewer replicas, a smaller pool_max, or a transaction that stops being held open.

Do not deactivate the revision that is serving traffic. Read the trafficWeight column above before deactivating anything.