dbt-postgres: too many clients already error

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.

Not specified by the original author.

TL;DR

Postgres hit max_connections and the only free slots are reserved for superusers, so dbt regular connections are refused. Reduce dbt threads, close idle connections from other tools, or put a pooler such as PgBouncer in front. The durable fix is matching total client connections (threads times jobs plus other tools) to the server limit.

dbt-postgres: remaining connection slots are reserved for superuser

Use this when

• dbt run fails connecting with remaining connection slots are reserved for superuser
• failures start partway into a parallel run or when several jobs overlap
• the Postgres instance is small (low max_connections) or shared with BI tools

Not for this skill when

• the server is down entirely, connections time out rather than being refused for slots
• one user hit a per-role connection limit, adjust that role limit instead of server settings
• you can raise max_connections cheaply and nothing else competes, a server setting change may be the simplest fix

Steps

1. Count demand: note dbt threads in the profile, how many dbt jobs run at once, and what else connects (BI tools, notebooks). Sum the peak connections. Verify: you have a peak connection estimate to compare with the server limit.

2. Check the server side: query pgsettings for maxconnections and pgstatactivity grouped by state and application to see who holds connections, including idle ones. Verify: you know the limit and which clients hold idle connections.

3. Cut dbt demand first: lower threads for the job (for example to 4), and close idle sessions from abandoned tools. If the platform supports it, route dbt through PgBouncer in transaction pooling mode. Verify: dbt peak connections fit within the non-reserved slots.

4. Rerun the job at normal overlap times and monitor pgstatactivity during the run. Verify: the run completes and peak connections stay below the limit with headroom.

Variant phrasings

only in CI

CI may run several dbt jobs in parallel against one dev database. Stagger jobs, give each its own database, or lower CI threads.

idle connections from crashed runs

Killed dbt processes can leave server-side sessions until timeout. Set sensible idle session timeouts on the role or server.

Compatibility: dbt-postgres (psycopg) against Postgres 12 and later; PgBouncer transaction mode works for dbt if prepared statements settings are handled per adapter guidance. Superuser reserved slots come from superuserreservedconnections.

Why it happens

Postgres preallocates a fixed connection pool at startup. When regular connections consume every non-reserved slot, new non-superuser connections get this exact refusal. dbt is connection hungry because each thread keeps its own connection, and parallel jobs multiply that, so dbt often trips a limit that single-threaded tools never approach.

Edge cases / pitfalls

• Each dbt thread plus control connections means real usage is threads plus a small overhead; leave headroom, do not plan to the exact limit.
• Raising max_connections costs memory per connection on Postgres; pooling is usually safer than a big raise.
• Managed Postgres tiers cap connections by instance size; check your tier before promising a number to the team.

Provenance

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

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

Thread query: dbt-postgres: remaining connection slots are reserved for superuser

TL;DR: Postgres hit max_connections and the only free slots are reserved for superusers, so dbt regular connections are refused. Reduce dbt threads, close idle connections from other tools, or put a pooler such as PgBouncer in front. The durable fix is matching total client connections (threads times jobs plus other tools) to the server limit.

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.