Optimize performance using Azure Database for PostgreSQL Recommendations

As a developer today, you have a lot of things to worry about. There are new technologies, frameworks, and deployment models that you need to keep up with so that you can build game-changing apps for customers. At the end of the day though, one thing that remains unchanged is the need to focus on developing features that add value while ensuring that your database remains functional and performant. In the past, a DBA would take care of your database performance, but today that responsibility can be shared across many roles.

You no longer have to be a database expert to optimize your database. Make your job easier and start taking advantage of Azure Database for PostgreSQL Recommendation for Azure Database for PostgreSQL today.

Analyzing workloads + making recommendations = heart

By analyzing the workloads on your server, the recommendations feature gives you daily insights about the Azure Database for PostgreSQL resources that you can optimize for performance. These recommendations are tightly integrated with Azure Advisor to provide you with best practices directly within the Azure portal. Azure Advisor is a personalized cloud consultant that helps you follow guidelines to optimize your Azure deployments.

If you’re running your Postgres application on Azure today and you want to see the recommendations we’ve already made to help you optimize your Azure Database for PostgreSQL resources, it’s easy! Just check out the performance recommendations tab in the Azure Advisor.

Types of settings we make recommendations about

The recommendation feature continuously monitors and analyzes your database servers' telemetry to determine if your workload performance can be improved by configuring one or more of the resource settings. Currently, we monitor, analyze, and make recommendations for four different types of settings for your Azure Database for PostgreSQL deployment.

◈ CPU bottlenecks – Very high utilization of the CPU over an extended period can cause slow query performance for your workload. Increasing the CPU size is likely to help in optimizing the runtime of the database queries and improve overall performance. We will identify servers with a high CPU utilization that are likely running CPU constrained workloads and recommend scaling your compute.

◈ Connection constraints – Each new connection to Postgres occupies some memory. The database server's performance degrades if connections to your server are failing because of an upper limit in memory. We will identify servers running with many connection failures and recommend upgrading your server's connections limits to provide more memory to your server by scaling up compute or using Memory Optimized SKUs, which have more memory per core.

◈ Memory constraints – A low cache hit ratio can result in slower query performance and increased IOPS. This could be due to a bad query plan or running a memory intensive workload. Fixing the query plan or increasing the memory of your server will help optimize the execution of the database workload. Azure Database for PostgreSQL Recommendation identifies servers affected due to this high buffer pool churn and recommends either fixing the query plan, moving to a higher SKU with more memory, or increasing storage size to get more IOPS.

◈ Read-intensive workloads – The new Azure Database for PostgreSQL recommendation feature leverages workload-based heuristics such as the ratio of reads to writes on the server over the past seven days. Your PostgreSQL resource with a very high read/writes ratio can result in CPU and/or memory contentions leading to slow query performance. Adding a replica will help in scaling out reads to the replica server, preventing CPU and/or memory constraints on the primary server. Azure Database for PostgreSQL Recommendation will identify servers with such high read-intensive workloads and recommend adding a read replica to offload some of the read workloads.

Getting started with the Recommendation feature

The only prerequisite for getting started with Azure Database for PostgreSQL Recommendation is that you have an instance of Azure Database for PostgreSQL running with some load.

Access Azure Database for PostgreSQL Recommendation in the Azure portal

You can access the recommendations feature by default with no additional cost. The recommendations provided for your server are accessible either via the Azure Advisors page or the server’s overview page, as shown in Figures 1 and 2 below.

Fig 1: Azure Database for PostgreSQL Recommendation on the Azure Advisor’s blade on the portal

Fig 2: Azure Database for PostgreSQL Recommendation from the overview blade of your PostgreSQL resource

Go deeper with the recommendation on Azure portal

After accessing the type of recommendation, you need to select a specific recommendation. Based on the recommendation type, you can take specific actions to address the performance constraints for the server. You can see the recommendation details as you select the recommendation type (Label 1 in Fig 3). This provides the details you need to optimize your Azure resource. Additionally, you can act on a recommendation by selecting recommended actions (Label 2 in Figure 3).

Fig 3: Gather further recommendation details and take actions

Azure Advisor provides inline actions, a convenient way to select and implement recommendations without leaving the Azure Advisor portal.

If you don’t intend to act immediately, you can postpone a recommendation for a period. You can also dismiss a recommendation to ensure that it no longer appears. If you do not want to receive recommendations for a specific subscription or resource group, you can configure Azure Advisor via the portal or by using the CLI to generate recommendations only for those that you specify. 

After implementing any recommendation, be sure to evaluate performance to measure the impact of the changes you made.