Connection limit is the maximum number of concurrent connections that a user is allowed to have against a particular redshift database.
Redshift allows connection limit to be specified both at the Database level as well as at the User level.
Connection Limit of a Database
You can determine the connection limit of a database by running the below query:
select datname, datconnlimit from pg_database_info;
Use the below command to modify the connection limit of a redshift database where db_name is the name of the database and value is the number of concurrent connections allowed. You can specify either a ‘numeric value’ or ‘UNLIMITED’ for the value parameter.
ALTER DATABASE db_name CONNECTION LIMIT value;
Examples:
ALTER DATABASE db_name CONNECTION LIMIT 500; ALTER DATABASE db_name CONNECTION LIMIT UNLIMITED;
Connection Limit of a User
You can determine the connection limit of a user by running the below query:
select * from pg_user_info;
Use the below command to modify the connection limit of a redshift database user where username is the name of the user and value is the number of concurrent connections allowed. Once again, you can specify either a ‘numeric value’ or ‘UNLIMITED’ for the value parameter.
ALTER USER username CONNECTION LIMIT value;
Examples:
ALTER USER username CONNECTION LIMIT 100; ALTER USER username CONNECTION LIMIT UNLIMITED;
If both user connection limit and database connection limit are set, user will be able to establish a database connection only if open connection slots are available within the allowed limits.
Scenario:
If the connection limit of a database is set as 500 and the connection limit of a user as 100, the user will be able to make only a maximum of 100 concurrent connections. To allow more, let’s say 200 concurrent connections by the user, you should increase the user connection limit to 200. You do not have to change database connection limit – as 200 is within the 500 concurrent connections allowed by the database.