Performance Recommendations in Azure Database for PostgreSQL - Single Server
APPLIES TO: Azure Database for PostgreSQL - Single Server
Important
Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.
Applies to: Azure Database for PostgreSQL - Single Server versions 9.6, 10, 11
The Performance Recommendations feature analyses your databases to create customized suggestions for improved performance. To produce the recommendations, the analysis looks at various database characteristics including schema. Enable Query Store on your server to fully utilize the Performance Recommendations feature. After implementing any performance recommendation, you should test performance to evaluate the impact of those changes.
Permissions
Owner or Contributor permissions required to run analysis using the Performance Recommendations feature.
Performance recommendations
The Performance Recommendations feature analyzes workloads across your server to identify indexes with the potential to improve performance.
Open Performance Recommendations from the Intelligent Performance section of the menu bar on the Azure portal page for your PostgreSQL server.
Select Analyze and choose a database, which will begin the analysis. Depending on your workload, th analysis may take several minutes to complete. Once the analysis is done, there will be a notification in the portal. Analysis performs a deep examination of your database. We recommend you perform analysis during off-peak periods.
The Recommendations window will show a list of recommendations if any were found.
Recommendations are not automatically applied. To apply the recommendation, copy the query text and run it from your client of choice. Remember to test and monitor to evaluate the recommendation.
Recommendation types
Currently, two types of recommendations are supported: Create Index and Drop Index.
Create Index recommendations
Create Index recommendations suggest new indexes to speed up the most frequently run or time-consuming queries in the workload. This recommendation type requires Query Store to be enabled. Query Store collects query information and provides the detailed query runtime and frequency statistics that the analysis uses to make the recommendation.
Drop Index recommendations
Besides detecting missing indexes, Azure Database for PostgreSQL analyzes the performance of existing indexes. If an index is either rarely used or redundant, the analyzer recommends dropping it.
Considerations
- Performance Recommendations is not available for read replicas.
Next steps
- Learn more about monitoring and tuning in Azure Database for PostgreSQL.