To make sure that you are running your Azure Database for PostgreSQL at its best capabilities, please review the following recommendations and best practices.
1. Server Resource Utilization
- Make sure that your Azure Database for PostgreSQL instance does not run under a high utilization which can cause query execution latencies. Please visit the Azure Portal and navigate to your Azure Database for PostgreSQL Instance and view the Metrics Tab, as shown in figure 1 below.
You need to consider checking the following metrics:
a. CPU utilization
Please remember that maxing out CPU make cause slowness and connection drops. Check your long running queries to understand what is causing high processing time and update your database
tables Statistics regularly by using the “Analyze” command. If you maxed out CPU, scaling up your server by increasing the number of vCores is a good approach to enlarge your resources to
be able to accommodate your workload.
Enable Azure PostgreSQL Query Store to be able to view your long running queries: https://docs.microsoft.com/en-us/azure/postgresql/concepts-query-store
b. IOPS throughput
Please remember that the server has 3 IOPS per 1 GB of Storage. If your application requires higher IOPs, then it is recommended that you scale up you Azure Database for PostgreSQL server
storage size to get more IOPS so that your application performance is not impacted by storage throttling.
c. IO waits
If IO waits are observed from PostgreSQL performance troubleshooting, then increasing the storage size should be considered for higher IO throughput.
Check the wait queries using the portal: https://docs.microsoft.com/en-
d. Active connections
Check the number of Active connections, this limitation is related to the number of vCores that you have provisioned, the full list is provided here: https://docs.microsoft.com/en-us/azure/postgresql/concepts-limits
Note: Setup Azure Alerts to get notified before issues happen
Full tutorial on how to use the Azure portal to set up alerts on metrics for Azure Database for PostgreSQL can be found here: https://docs.microsoft.com/en-us/azure/postgresql/howto-alert-on-metric
2. Optimizing client applications
- (Reference Performance updates and tuning best practices for using Azure Database for PostgreSQL)
We recommend having the application server/client machine in the same region and resource group in Azure to reduce network latencies between the client/application server and the database.
a. Network latency: Check the network latency between the client and the database service instance. You can check the network latency by running simple query as ‘SELECT 1’
b. Connection pooling: Use Connection pooling to reduce the latency caused by connection establishment along with reducing the chance of reaching the server limit of active connections.
c. Accelerated Networking
Use Accelerated Networking on your Azure Web Apps as a best practice.
d. TCP_NODELAY: TCP_NODELAY is a client-side setting that should be considered on a client Virtual Machine (VM). Applications that benefit from the TCP_NODELAY option typically tend to do smaller infrequent writes and are particularly sensitive to latency. As an example, latency can be reduced from 15-40 ms to 2-3 ms with this setting.
e. CPU exhaustion: Single-threaded applications can result in CPU exhaustion of one CPU while the other CPUs are under-utilized. Consider parallelizing your workload to take advantage of all the vCores available
f. Pg_stat_statements: is a PostgreSQL extension that is enabled by default and provides a means to track execution statistics of all SQL statements executed by a server. This module hooks into every query execution and comes with a non-trivial performance cost. Enabling pg_stat_statements forces query text writes to files on disk.
On some customer workloads we have seen up to a 50 percent performance improvement by disabling ps_stat_statements. However, the tradeoff one makes by disabling pg_stat_statements is the inability to troubleshoot performance issues.