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.

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.