Calculating maximum connections in PostgreSQL

Calculating the maximum number of connections for a PostgreSQL server involves considering both the available RAM and CPU. Here’s a structured approach to help you determine an appropriate max_connections value based on your system’s resources.

General Guidelines

RAM Consideration:
- PostgreSQL uses memory for each connection, so the available RAM significantly affects the maximum number of connections.
- Each connection consumes memory for work memory, shared buffers, etc.
- A common rule of thumb is to allocate around 100 MB per connection for active connections.
CPU Consideration:
- PostgreSQL can handle many connections, but more connections can lead to context switching overhead.
- As a general guideline, aim for a maximum of 2 – 3 connections per CPU core.

Steps to Calculate Maximum Connections

Determine Available Resources:
- Find your server’s total RAM and the number of CPU cores.
- For example, if you have 16 GB of RAM and 4 CPU cores.
Calculate Maximum Connections Based on RAM:
- Use the formula:
  Max Connections (RAM) = Total RAM(inMB) / 100 MB per connection
- For 16 GB:
  Max Connections (RAM) = 16384 MB / 100 = 163.84 ≈ 163 connections
Calculate Maximum Connections Based on CPU:
- Use the formula:
  Max Connections (CPU) = Number of CPU Cores × 3
- For 4 cores:
  Max Connections (CPU) = 4 × 3 = 12 connections
Determine the Final Maximum Connections:
- The final max connections will be the lower of the two values calculated above:
  Max Connections = min(163,12) = 12

Conclusion

For a server with 16 GB RAM and 4 CPU cores, you’d want to limit your max connections to 12 based on CPU considerations.
Always monitor your system’s performance after making changes and adjust as necessary.
Consider using a connection pooler like PgBouncer if you anticipate needing a higher number of connections than your hardware can support simultaneously.

Sumber asli: https://www.solusidb.com/2024/09/30/calculating-the-maximum-connections-in-postgresql/