max_connections - AWS Prescriptive Guidance

max_connections

The max_connections parameter sets the maximum number of concurrent connections for your PostgreSQL database.

Note

When you scale down a DB instance, make sure to adjust max_connections to match the new memory limits. Otherwise, PostgreSQL might fail to start or you might encounter issues with the PostgreSQL engine. Document all changes for easier adjustments in the future.

AWS CLI syntax

The following command changes max_connections for a specific DB parameter group. This change applies to all instances or clusters that use the parameter group.

# Modify max_connections on a DB parameter group
aws rds modify-db-parameter-group \
--db-parameter-group-name <parameter_group_name> \
--parameters "ParameterName=max_connections,ParameterValue=<new_value>,ApplyMethod=pending-reboot"

# Modify max_connections on a DB cluster parameter group
aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name <parameter_group_name> \
--parameters "ParameterName=max_connections,ParameterValue=<new_value>,ApplyMethod=pending-reboot"

Type: Static (applying changes requires a reboot)

Default value: LEAST(DBInstanceClassMemory/9531392,5000) connections

To optimize the use of max_connections in Amazon RDS or Aurora and minimize its impact on performance, consider the following best practices:

  • Set the parameter value based on available system resources.

  • Monitor connection usage to prevent reaching the limit quickly.

  • Use connection pooling to reduce the number of connections required.

  • Use Amazon RDS Proxy for connection pooling.

When you tune max_connections in Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible, consider the available instance types and their allocated resources, and focus on memory and CPU capacity. Storage and I/O details are managed by AWS, so you can monitor general workload characteristics and system metrics such as FreeableMemory through Amazon CloudWatch or the Amazon RDS console to confirm that there's enough memory for connections. Monitor high CPUUtilization values, which might indicate that adjustments are needed. Avoid setting max_connections too high, because it can impact memory usage and potentially influence I/O indirectly. Keep in mind that each connection consumes memory. To find the right balance, slowly increase max_connections and see how it affects your system. Watch for signs of slower performance or higher CPU use. Check if your application still works well. Use features such as read replicas in Aurora to distribute read traffic and reduce the load on the primary instance. Review and adjust max_connections regularly based on observed usage patterns to ensure optimal database performance within the given resource constraints.