What do I do if excessive connections are established to an ApsaraDB RDS for PostgreSQL instance? - ApsaraDB RDS

When connections to an ApsaraDB RDS for PostgreSQL instance reach the maximum limit, new clients cannot connect. This article explains how to diagnose the issue, free up connections, and prevent recurrence.

Symptom

Clients fail to connect to the RDS instance with the following error:

FATAL: remaining connection slots are reserved for non-replication superuser connections

This error indicates that all available connection slots are occupied. PostgreSQL reserves a small number of slots for superuser access, so regular users are rejected first.

Common causes

PostgreSQL uses a process-per-connection model. Each connection, whether active or idle, spawns a dedicated backend process that consumes memory and CPU. Connections typically run out due to:

Idle connections that are never closed. Applications that open connections without releasing them gradually consume all slots.
Slow queries holding connections open. Long-running queries prevent their connections from being reused.
Connection spikes without pooling. Short-lived application processes that each open a direct database connection create bursts that exceed the limit.

Diagnose the issue

Before closing connections, identify what is consuming them. Connect to the database as a privileged user and run the following queries.

Check current and maximum connection counts

SELECT count(*) AS current_connections,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections;

View connections by state

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

Key states to look for:

State	Meaning
`active`	Executing a query
`idle`	Connected but doing nothing
`idle in transaction`	Inside a transaction but not actively querying
`idle in transaction (aborted)`	Transaction errored and was not rolled back

A large number of idle or idle in transaction connections typically means applications are not releasing connections properly.

Find connections by user and database

SELECT usename, datname, count(*)
FROM pg_stat_activity
GROUP BY usename, datname
ORDER BY count DESC;

Identify long-idle connections

SELECT pid, usename, datname, state,
       now() - state_change AS idle_duration,
       query
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_duration DESC
LIMIT 20;

Identify long-running active queries

SELECT pid, usename, datname, state,
       now() - query_start AS query_duration,
       left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY query_duration DESC
LIMIT 20;

Free up connections immediately

Terminate idle or unnecessary sessions through the ApsaraDB RDS console to free up connection slots.

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
Click the Session Management tab.
Review the active sessions. Select and close unnecessary sessions. For details, see Use the session management feature.

Root cause solutions

After you restore connectivity, address the underlying cause to prevent recurrence.

Optimize slow queries

Slow queries hold connections open longer than necessary. Identify and optimize time-consuming queries to reduce system load and release resources sooner. For step-by-step instructions, see Use the slow query log analysis feature.

Enable connection pooling

Connection pooling reduces the overhead of frequent connection creation and teardown. A pool of shared connections serves multiple clients, which lowers total connection count and stabilizes resource usage.

To enable the built-in PgBouncer-based connection pooling feature, see Enable or disable the connection pooling feature.

Key PgBouncer parameters:

Parameter	Description
`pool_mode`	Pooling granularity. The default mode is `transaction`, which returns connections to the pool after each transaction.
`default_pool_size`	Number of server connections per user/database pair.
`max_client_conn`	Maximum number of client connections that PgBouncer accepts.

Warning

On ApsaraDB RDS for PostgreSQL, max_connections is determined by your instance type and cannot be adjusted independently. Upgrading to a larger instance type solely to increase max_connections does not address the root cause. Each additional connection consumes memory. Use connection pooling instead.

Use the database proxy feature

The database proxy simplifies connection management and provides high availability, high performance, and maintainability. It is suitable when:

The primary RDS instance is overloaded.
Read/write splitting is required.
A large number of connections are established.
Isolation requirements must be met.

For details, see What are database proxies?

Upgrade the instance type

If connection pooling and the database proxy do not meet your requirements due to business growth, upgrade to an instance type that supports more connections.

For the maximum number of connections per instance type, see Instance types for primary ApsaraDB RDS for PostgreSQL instances.
For upgrade instructions, see Change instance specifications.

Note

View the current instance specifications in the Configuration Information section of the Basic Information page.

Prevention best practices

Enable connection pooling by default. Set up PgBouncer or the database proxy before connection issues arise.
Set connection timeouts in applications. Close idle connections after a defined period instead of keeping them open indefinitely.
Monitor connection usage. Track connection counts and set alerts at 80% of max_connections to catch issues early.
Close connections properly. Use try/finally blocks or connection pool libraries to make sure connections are returned or closed after use.

Applies to

ApsaraDB RDS for PostgreSQL