## TL;DR
Postgres refuses new connections because the server-wide `max_connections` is exhausted, usually by apps that open a fresh connection per request instead of pooling. Add a connection pooler (PgBouncer) or proper in-app pooling, kill idle offenders, and raise max_connections only as a last resort.

```text
psycopg2 FATAL: sorry, too many clients already
```

## Use this when
- New psycopg2 connections fail with this FATAL
- The error appears under load or as services scale
- An agent opens a new connection per query

## Not for this skill when
- The password is rejected (thats auth)
- Queries are slow but connections succeed (thats performance)
- One app exhausts its own pool (thats the app pool, not the server)

## Steps

1. See who holds the connections:

```sql
SELECT application_name, state, count(*)
FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 3 DESC;
```
Expected output: the top offenders by count and state. Lots of `idle` connections from one app means no pooling on that side.

2. Free the obviously dead ones (carefully):

```sql
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle' AND backend_type = 'client backend'
  AND application_name = 'offending_app';
```
Expected output: the count drops and new connections succeed. This is triage, not a fix; they will come back without pooling.

3. Put PgBouncer (or your cloud's pooler) between the apps and Postgres:

```text
app to pgbouncer (transaction pooling) to postgres
```
Expected output: hundreds of app connections multiplex onto a handful of server connections. This is the real fix for "too many clients" at scale.

4. In the app, use one pooled connection source instead of connect-per-query:

```python
from psycopg2 import pool
pg_pool = pool.ThreadedConnectionPool(2, 20, dsn=DSN)
```
Expected output: the app reuses connections. psycopg2 has no built-in pooling magic; you must add it.

## Variant phrasings

### too many clients after adding a new service
The new service doesnt pool. Every new service needs pooling from day one.

### happens at the same time daily
A cron or batch job opens a connection per row. Batch the work inside one connection.

## Why it happens
Postgres allocates real resources per connection, so `max_connections` (default 100) is a hard ceiling. Modern apps with per-request connections, serverless functions, and agent scripts each holding connections open blow through it fast. The FATAL is server-wide: one leaky app denies connections to everyone, including your admin session (keep a superuser_reserved slot in mind).

## Edge cases
- Raising max_connections without adding memory risks OOM; each connection costs work_mem-sized buffers.
- Serverless platforms need an external pooler (RDS Proxy, PgBouncer) because functions cant share in-process pools.
- `idle_in_transaction` sessions hold locks too; they are worse than plain idle. Find the code that starts transactions and never commits.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8KyRoL7udo7GIdkAksCdWw