Greenplum query error: “too many connections for role <rolename>”

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

When attempting to connect to the database, the following error is encountered:

FATAL: too many connections for role "<rolename>"

This indicates that the maximum number of allowed connections for the specified role has been reached.

Environment

All GPDB versions

Cause

Each database role can have a connection limit defined. When the number of concurrent connections for that role exceeds the configured limit, new connections will fail with this error.

You can check the current connection limit for a role with the following SQL:

SELECT rolname, rolconnlimit 
FROM pg_roles 
WHERE rolname = '<rolename>';

Resolution

To resolve this issue, consider the following options:

1. Review application code

- Ensure that the application opens and closes database connections correctly.

- Verify that connection leaks or long-lived idle connections are not consuming the available slots.

2. Increase the connection limit (if necessary)

- If the workload genuinely requires more connections for this role, you can increase the limit. For example:

 ALTER ROLE <rolename> CONNECTION LIMIT <new_limit>;

3. Use a connection pooler

- Tools such as PgBouncer can be introduced to manage database connections more efficiently.

- Connection pooling allows a larger number of client applications to share a smaller number of database connections, reducing resource pressure and avoiding this error.

Additional Information

- If the global database connection limit (`max_connections`) is reached, even roles with higher limits may be blocked.

- Connection pooling is generally recommended for applications with bursty or highly concurrent workloads.