| 1 | ### Understand the alert |
| 2 | |
| 3 | This alert monitors the total `connection utilization` of a PostgreSQL database. If you receive this alert, it means that your `PostgreSQL` database is experiencing a high demand for connections. This can lead to performance degradation and, in extreme cases, could potentially prevent new connections from being established. |
| 4 | |
| 5 | ### What does connection utilization mean? |
| 6 | |
| 7 | `Connection utilization` refers to the percentage of `database connections` currently in use compared to the maximum number of connections allowed by the PostgreSQL server. A high connection utilization implies that the server is handling a large number of concurrent connections, and its resources may be strained, leading to decreased performance. |
| 8 | |
| 9 | ### Troubleshoot the alert |
| 10 | |
| 11 | 1. Check the current connections to the PostgreSQL database: |
| 12 | |
| 13 | You can use the following SQL query to check the number of active connections for each database: |
| 14 | |
| 15 | ``` |
| 16 | SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname; |
| 17 | ``` |
| 18 | |
| 19 | or use the following command to check the total connections to all databases: |
| 20 | |
| 21 | ``` |
| 22 | SELECT count(*) FROM pg_stat_activity; |
| 23 | ``` |
| 24 | |
| 25 | 2. Identify the source of increased connections: |
| 26 | |
| 27 | To find out which user or application is responsible for the high connection count, you can use the following SQL query: |
| 28 | |
| 29 | ``` |
| 30 | SELECT usename, application_name, count(*) FROM pg_stat_activity GROUP BY usename, application_name; |
| 31 | ``` |
| 32 | |
| 33 | This query shows the number of connections per user and application, which can help you identify the source of the increased connection demand. |
| 34 | |
| 35 | 3. Optimize connection pooling: |
| 36 | |
| 37 | If you are using an application server, such as `pgBouncer`, that supports connection pooling, consider adjusting the connection pool settings to better manage the available connections. This can help mitigate high connection utilization. |
| 38 | |
| 39 | 4. Increase the maximum connections limit: |
| 40 | |
| 41 | If your server has the necessary resources, you may consider increasing the maximum number of connections allowed by the PostgreSQL server. To do this, modify the `max_connections` configuration parameter in the `postgresql.conf` file and then restart the PostgreSQL service. |
| 42 | |
| 43 | ### Useful resources |
| 44 | |
| 45 | 1. [PostgreSQL: max_connections](https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS) |