Error "FATAL: remaining connection slots are reserved" in PostgreSQL during a billing spike

By Kevin Marcondes · Published on Oct 28, 2026 · 8 min read

The "FATAL: remaining connection slots are reserved" error in PostgreSQL during a billing spike means nearly all connection slots are used and the remaining ones are reserved for the superuser. The result is application logins being blocked. Learn how to diagnose in minutes, safely free connections, and apply quick fixes to stabilize the system.

What this error means and why it happens

PostgreSQL reserves a number of connection slots for the superuser. When the other slots are exhausted, regular users receive this error. It typically shows up during spikes, connection storms, or when sessions are stuck in "idle in transaction."

It's not just about max_connections. Lack of a connection pooler, simultaneous restarts of web workers, and parallel jobs make the situation worse. If the problem is general saturation, see the "Too many connections" guide. For continuous heavy load, consider our PostgreSQL hardening and tuning service.

• Normal connections are exhausted; only superuser-reserved slots remain.
• Apps without pooling amplify spikes by opening many short-lived connections.
• "Idle in transaction" sessions keep slots occupied for long periods.

What to check first during the spike

You need immediate visibility: how many slots exist, who is occupying them, and in what state. Prioritize administrative access and reduce network dependency by connecting via the local socket if available.

Run quick queries to get limits, group activity by state/user/application, and list top active or stuck sessions. These results guide low-risk initial actions.

-- Limites atuais
SELECT name, setting FROM pg_settings
WHERE name IN ('max_connections','superuser_reserved_connections');

-- Quem está ocupando mais conexões agora?
SELECT state, application_name, usename, client_addr, count(*) AS qtd
FROM pg_stat_activity
GROUP BY 1,2,3,4
ORDER BY qtd DESC;

-- Top sessões ativas ou presas em transação
SELECT pid, state, xact_start, wait_event_type, wait_event, application_name, query
FROM pg_stat_activity
WHERE state IN ('active','idle in transaction')
ORDER BY xact_start NULLS LAST
LIMIT 20;

How to free connections safely without restarting

Cancel long-running queries before terminating sessions. Terminating forces a rollback; pick low-risk targets and communicate with the business team.

Reduce the rate of new connections from the application: enable or tune the driver pool, temporarily limit web processes, and suspend noncritical background jobs until things stabilize.

• Canceling queries before killing sessions reduces impact.
• Act first on very old "idle in transaction" sessions.
• Throttle new application connections while the situation normalizes.

-- 1) Cancelar consultas em execução (encerra com graça)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND now() - xact_start > interval '30 seconds';

-- 2) Encerrar sessões problemáticas (gera rollback)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - xact_start > interval '2 minutes';

Which quick PostgreSQL adjustments help during the spike?

Some parameters relieve saturation, but changes to max_connections and superuser_reserved_connections require a restart. Plan memory and test before altering them.

The sustainable approach is to keep a reasonable connection limit in PostgreSQL and place a pooler in front. That reduces churn and stabilizes latency under peak load.

# Exemplos no postgresql.conf (requer restart)
max_connections = 300
superuser_reserved_connections = 3

Connection pool: when to use PgBouncer and which modes

Use PgBouncer when the application opens many short connections or many processes start in parallel. Transaction pooling fits most web scenarios by reducing server sessions.

Avoid transaction pooling if you need session state like temporary tables, session-level prepared statements, or LISTEN/NOTIFY. In that case, use session pooling or adapt the application.

• Transaction pooling: ideal for HTTP with short queries and high request rate.
• Session pooling: preserves session state, but scales less efficiently.
• Tune max_client_conn, default_pool_size and reserve_pool_size according to load.

# Exemplo mínimo de PgBouncer
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 5
reserve_pool_size = 20
server_reset_query = DISCARD ALL

Backlog and kernel tuning to withstand connection storms

Bursts of new connections can fill the system accept queue. Adjusting somaxconn and tcp_max_syn_backlog reduces rejections during peaks, without adding more PostgreSQL slots.

The structural fix remains limiting concurrency with a pooler and reusing connections in the app. Backlog tuning is complementary and should be validated in staging. Changes to sysctl affect the whole system.

• Persist adjustments in /etc/sysctl.d/ and version the file.
• Test in staging before rolling to production.
• Monitor new connections per second and handshake failures.

# Ajustes temporários (teste controlado)
sudo sysctl -w net.core.somaxconn=4096
sudo sysctl -w net.ipv4.tcp_max_syn_backlog=8192

# Persistir em /etc/sysctl.d/99-tuning.conf
# net.core.somaxconn=4096
# net.ipv4.tcp_max_syn_backlog=8192
# sudo sysctl --system

How to prevent this from happening again

Set clear limits and maintain continuous visibility. Metrics and alerts for connections, session states and transaction durations prevent repeats during billing spikes.

Create a runbook with diagnostic queries and criteria for cancelling or terminating sessions. Train the team and validate the plan. For migration discipline, see the PostgreSQL migrations & rollbacks guide and implement alerts tied to runbooks, as in our observability & runbooks service.

• Collect metrics: active connections, new connections/sec and "idle in transaction" counts.
• Alert when "idle in transaction" exceeds a defined threshold.
• Simulate spikes with synthetic load to validate configuration.

Frequently asked questions

Does increasing max_connections permanently solve the error?

No. Increasing max_connections helps short-term but can worsen latency and memory usage. The sustainable solution is connection pooling (PgBouncer or driver pooling) and fixing long sessions, combined with a moderate limit increase if needed.

Can I change superuser_reserved_connections without restarting PostgreSQL?

No. superuser_reserved_connections, like max_connections, requires a server restart. Plan a maintenance window and validate memory before changing. For immediate relief, cancel/terminate sessions and throttle new connections via the pooler or application.

How do I quickly identify who is consuming the most connections right now?

Group pg_stat_activity by state, application_name, usename and client_addr. Counts per group reveal which services, users or hosts are saturating. Filter by "idle in transaction" and transaction duration to prioritize low-risk actions.

What features break when using PgBouncer transaction pooling?

Transaction pooling does not preserve session state between commands. Resources like temporary tables, session-scoped prepared statements, SET LOCAL and LISTEN/NOTIFY may fail. Use session pooling or modify the app for short, stateless transactions if you need them.

Is it better to kill sessions or just cancel long queries?

Start with pg_cancel_backend, which tries to stop the query without dropping the session. If that doesn't work, use pg_terminate_backend on low-risk sessions, knowing it triggers a rollback. Prioritize very old "idle in transaction" sessions and clearly stuck queries.

Does kernel backlog tuning increase PostgreSQL's connection capacity?

No. Backlog settings (somaxconn, tcp_max_syn_backlog) only improve the OS accept queue under bursts. The number of concurrent sessions depends on max_connections and the effectiveness of your connection pooler.

See also

• Intermittent 502 Bad Gateway in Docker Compose with Nginx & PHP‑FPM: how to diagnose and fix timeouts and connection overloads
• How to diagnose and capture packets for intermittent network issues between Docker containers on Debian
• How to detect and fix duplicate SKU inconsistencies between WooCommerce and your ERP without losing orders
• How to reduce build time and avoid duplicate artifacts in CI for large PHP monoliths