I received the error "connection limit 500 exceeded for non-bootstrap users." What are the connection limits for Amazon Redshift? How can I manage my connections in Amazon Redshift?

Amazon Redshift limits the number of connections per cluster and per database user. See Limits in Amazon Redshift for more information. When the limit is reached, subsequent connection attempts will fail with the error "connection limit 500 exceeded for non-bootstrap users." Because the connection limits cannot be raised, we recommend you restrict the number of connections per user and per database. For the best performance, we recommend you use no more than 15 concurrent queries. By default, we allow five concurrent queries, but you can increase this. For more information about concurrent queries, see Concurrency Level. We also recommend keeping as few idle connections as possible to reduce resource usage and simplify troubleshooting user sessions.

You can use connection pool software to limit the number of connections to your Amazon Redshift cluster and validate your connections. We recommend using a light-weight health-check query command, such as SELECT 1, to reduce resource consumption.

Review and manage Amazon Redshift connections

You can manage connection limits for specific databases by using CREATE DATABASE and ALTER DATABASE.

You can manage connection limits for specific users by using CREATE USER and ALTER USER.

Each session corresponds to a connection. You can view information about the active user sessions for Amazon Redshift, or you can check the total number of the connections by using STV_SESSIONS. See the following example:

select count(*) from stv_sessions

You can use the starttime to identify long-running sessions on clusters that do not use enhanced VPC routing, and you can join this with STL_CONNECTION_LOG to see the IP addresses associated with the connection. For example, the following query can check the connection count from different remote IP addresses:

select c.remotehost,count(*) from stv_sessions s left join stl_connection_log c on s.process=c.pid where event='authenticated' and s.user_name<>'rdsdb' group by 1 order by 2 desc;

Note: If the same pid is used for multiple sessions, this query might produce additional records for the previous sessions that used the pid.

Clean up Amazon Redshift connections

You can terminate a process and the corresponding session using PG_TERMINATE_BACKEND. To review previous connections, use STL_CONNECTION_LOG to log authentication attempts, connections, and disconnections.

Note: To manage disk space, all STL log tables, including STL_CONNECTION_LOG, retain only two to five days of log history. If you want to keep your connection logs longer, use Database Audit Logging.

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2017-12-21