Postgres max_connection setting in EC2

Is there any formula to set the max_connection value in a PostgreSQL 12 EC2 instance with 64 CPUs, 500 GB RAM, and 150k IOPS?

There isn't a universal formula for determining the optimal max_connections value because the ideal number depends on several factors specific to your environment and application.

For a PostgreSQL instance on an EC2 instance with 64 CPUs, 500 GB RAM, and 150k IOPS, you might start with a conservative estimate and then adjust based on your application's specific needs and performance testing results. A common rule of thumb is to allocate about 2-3% of the system's RAM for each connection. In your case, with 500 GB of RAM, this would suggest:

max_connections = (500 GB * 1024 MB/GB) * 0.02 to 0.03 / (memory per connection)

The memory per connection can vary, but a typical value might be around 10 MB. So, you might start with:

max_connections = (500 * 1024) * 0.02 to 0.03 / 10 = 1024 to 1536

Note: This is a rough estimate, and you should adjust it based on your application's actual memory usage and connection patterns.

Additionally, what other parameters need to be changed for a mixed workload database?

1. work_mem: Memory for sort operations and hash tables. Increase for better performance in complex queries, but use cautiously as it's allocated per operation.
2. maintenance_work_mem: Maximum memory for maintenance tasks like vacuuming and indexing. Increase to speed up these operations on large databases.
3. shared_buffers: Memory for shared buffers, typically set to 25%-40% of total system memory.
4. effective_cache_size: Helps estimate the disk cache size available to the database, usually set to 50%-75% of total system memory.
5. checkpoint_segments and checkpoint_completion_target: Control the frequency and spread of WAL checkpoints. Adjust to balance I/O load.
6. random_page_cost: Cost of non-sequential disk page fetches. Adjust to influence query planner's choice of index vs. sequential scans.