Postgresql connection limit issue on AWS RDS

Max Connections for db.m6i.2xlarge Instance

For a db.m6i.2xlarge instance, the formula for calculating the maximum number of connections is:

max_connections = LEAST({DBInstanceClassMemory / 9531392}, 5000)

Since the db.m6i.2xlarge instance has 32 GiB of RAM (32 * 1024 * 1024 * 1024 bytes), the calculation would be:

max_connections = LEAST((32 * 1024 * 1024 * 1024 / 9531392), 5000) ≈ 3601

This means the instance can support approximately 3601 connections. If you’ve set max_connections to 5000, this exceeds the calculated limit based on memory, which could potentially lead to performance issues if fully utilized.

Increasing Connections Beyond Limits

While you cannot directly increase max_connections beyond what the instance class allows, here are some recommended strategies:

1. Connection Pooling: Tools like PgBouncer or pgpool-II can manage connections efficiently by reusing a smaller number of database connections. This is usually the most cost-effective solution.

2. Vertical Scaling: Upgrading to a larger instance class with more memory will allow for more connections. However, this may be less cost-effective unless required for other performance reasons (CPU, storage).

3. Horizontal Scaling: Consider offloading read-heavy operations by using read replicas, which can help distribute connections.

4. Sharding by Tenants: For large multi-tenant systems, sharding databases across multiple instances can reduce the connection load on a single instance.