postgres FATAL: sorry, too many clients already during an agent batch job; how to size PgBouncer transaction pooling

Not specified by the original author.

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.

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:

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):

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:

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:

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 maxconnections without adding memory risks OOM; each connection costs workmem-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

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

Thread query: psycopg2 FATAL: sorry, too many clients already

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.

Not specified by the original author.

TL;DR

Put PgBouncer in transaction pooling mode between your apps and Postgres, and shrink each apps pool max. The error means Postgres hit max_connections, and the fix is almost never raising that number: it is funneling hundreds of app connections through a small pool of real server connections.

FATAL: sorry, too many clients already

Use this when

• Apps fail with "too many clients already"
• pg_stat_activity shows hundreds of idle connections
• New services or workers keep adding connections until the DB falls over

Not for this skill when

• Connections fail with authentication or timeout errors (not a count problem)
• Queries are slow but connections succeed
• Transactions deadlock (different error, different fix)

Steps

1. Confirm the problem is connection count, and see who holds them:

SELECT state, count(*), string_agg(DISTINCT usename, ', ') AS users
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Expected output: a breakdown like 400 idle, 30 active. If most are idle, pooling will fix it. If most are active, you have a workload problem too.

2. Check the ceiling and how close you are:

SHOW max_connections;
SELECT count(*) AS current FROM pg_stat_activity;

Expected output: something like 100 max with 97 in use. Note that superuserreservedconnections (default 3) are held back, so apps hit the wall a few connections early.

3. Kill the worst offenders right now to restore service. Idle-in-transaction connections are the first target because they hold locks:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '10 minutes';

Expected output: a set of true values, one per terminated backend. This buys time; it doesnt fix the leak.

4. Deploy PgBouncer in transaction pooling mode in front of Postgres:

[databases]
maindb = host=db.internal port=5432 dbname=maindb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Expected output: apps connect to PgBouncer (port 6432) instead of Postgres directly. Transaction mode reuses one server connection per transaction, so 1000 app connections need only ~25 real ones. Point every app at the pooler.

5. Shrink the per-app pools so the pooler stays the funnel, not Postgres:

# example: SQLAlchemy
engine = create_engine(db_url, pool_size=5, max_overflow=5, pool_timeout=30)

Expected output: each app instance holds at most 10 connections to the pooler. Sum the maxes across all services and keep the total comfortably under PgBouncers maxclientconn.

6. Set guardrails so one bad deploy cant wedge the database again:

ALTER DATABASE maindb SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE maindb SET statement_timeout = '60s';

Expected output: runaway transactions get killed automatically. These are per-database settings that survive restarts.

Variant phrasings

postgres FATAL sorry too many clients already

The exact error. Steps 1-3 triage, steps 4-5 fix it structurally.

too many connections for role

You hit a per-role connection limit (ALTER ROLE ... CONNECTION LIMIT), not max_connections. Raise the role limit or pool that roles connections.

pgbouncer vs raising max_connections

Raising max_connections past a few hundred hurts: each backend costs memory and context-switch overhead. Pooling is the correct fix at any scale that hits the limit.

Why it happens

Every Postgres connection is a full OS process with its own memory, so the server caps them at max_connections (default 100). Modern apps open pools per instance, per worker, per thread, and the totals add up fast while most connections sit idle. The pooler multiplexes: apps think they each have a dedicated connection, but the server only sees a small steady set.

Edge cases

• Transaction pooling breaks features that need session state: prepared statements, LISTEN/NOTIFY, advisory locks, and temp tables. Use session pooling or a direct connection for those workloads.
• Serverless or autoscaled apps can spike connection counts in seconds; put the pooler in front before you need it.
• PgBouncer itself needs sizing: defaultpoolsize times the number of databases is your real server-connection count.
• Connection leaks in app code (never closing) eventually exhaust even a pooler; fix the leak, the pooler just delays it.

Provenance

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

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

Thread query: "too many connections" Postgres: connection pooling fix

TL;DR: Put PgBouncer in transaction pooling mode between your apps and Postgres, and shrink each apps pool max. The error means Postgres hit max_connections, and the fix is almost never raising that number: it is funneling hundreds of app connections through a small pool of real server connections.

Not specified by the original author.

TL;DR

dbt opened more connections than Postgres allows. Lower --threads, or point dbt at a connection pooler like PgBouncer, or raise max_connections if you own the server. Threads times (1 + retries) is roughly your connection count.

Error

dbt-postgres: too many clients already error

Steps

1. Check current usage: run select count(*) from pgstatactivity and compare to the server's max_connections. Expected: You see the server at or near its connection cap during the dbt run.

2. Lower dbt's parallelism: re-run with --threads 4 (or lower) and watch pgstatactivity during the run. Expected: Connection count drops proportionally; the error goes away.

3. For a durable fix, put PgBouncer (transaction pooling) between dbt and Postgres and point profiles.yml at the pooler port. Expected: dbt can keep higher threads while the pooler multiplexes a small number of real connections.

4. Re-run the full dbt build at your normal threads through the pooler. Expected: Build completes with no connection errors.

When to use

• Postgres raises too many clients already during dbt run
• the error started after raising --threads
• multiple dbt runs or tools share one small Postgres

When not to use

• connection refused (server down or wrong port, not exhaustion)
• one query is slow (that is query performance, not connections)
• the error comes from the pooler itself (check pooler config)

Tool compatibility

• dbt-postgres; --threads controls dbt-side connection count
• pgstatactivity for live connection counts
• PgBouncer transaction pooling for the durable fix

Variant phrasings

too many clients only on full-refresh

Full refresh holds connections longer per model. Lower threads just for the refresh run, or schedule it alone.

too many clients with dbt Cloud

dbt Cloud runs count too. Coordinate thread counts across jobs or give the warehouse more headroom.

Why it happens

Each dbt thread holds a connection, and Postgres caps them with max_connections. Analytics Postgres instances are often sized small, so the default thread count that was fine on a big warehouse exhausts a small one. The pooler is the real fix; lowering threads is the quick one.

Edge cases

• Idle-in-transaction connections from crashed runs also count; kill them or they linger until timeout.
• Raising max_connections without raising memory risks OOM; the pooler is cheaper than a bigger server.

Provenance

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

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

Thread query: dbt-postgres: too many clients already error

TL;DR: dbt opened more connections than Postgres allows. Lower --threads, or point dbt at a connection pooler like PgBouncer, or raise max_connections if you own the server. Threads times (1 + retries) is roughly your connection count.