How to Fix PostgreSQL ‘Remaining Connection Slots Reserved’ Error

If your application suddenly fails with an error like:

remaining connection slots are reserved for roles with the SUPERUSER attribute

It usually means your PostgreSQL database has reached its maximum number of allowed connections. Some of these connections may be idle-in-transaction sessions that are holding slots unnecessarily. This guide shows how to safely free up those connections.

Understanding the Problem

PostgreSQL limits the total number of concurrent connections through the max_connections setting. When all normal slots are used, only superusers can connect. Long-running or idle-in-transaction sessions from your app can consume slots and prevent new connections.

Check Current Connections

Before terminating anything, inspect your connections with:

SELECT pid, usename, application_name, state, backend_start
FROM pg_stat_activity
ORDER BY backend_start;

Look for rows where state is idle in transaction for your application user.

Terminate Old Idle-in-Transaction Connections

You can safely terminate old idle connections that are holding slots using:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND usename = 'your_app_user'
  AND now() - backend_start > interval '5 minutes';

Replace your_app_user with the username your app uses. This will free up connections without affecting active queries.

Precautions

• Do not terminate system connections (like postgres or background workers) — Aiven/PostgreSQL protects them with insufficient privileges.
• Do not terminate your own session (pid = pg_backend_pid()).
• Consider implementing connection pooling in your app to prevent future connection exhaustion.

Long-Term Recommendations

• Use a connection pooler like PgBouncer or built-in pooling from Prisma/TypeORM.
• Set idle_in_transaction_session_timeout for your database or user to automatically clean up long idle transactions:

ALTER DATABASE your_db SET idle_in_transaction_session_timeout = '1min';
ALTER ROLE your_app_user SET idle_in_transaction_session_timeout = '1min';

• Monitor pg_stat_activity regularly to catch hanging transactions early.

Summary

By terminating old idle-in-transaction connections, you can quickly free up PostgreSQL connection slots and restore app functionality. For long-term stability, combine this with connection pooling and automatic session timeouts.

Following this approach ensures that your database continues to operate smoothly without unexpected connection errors.

❤️ Support This Blog

---

If this post helped you, you can support my writing with a small donation. Thank you for reading.

---

Coffee ☕ Thanks 🙏 Extra Support ❤️