Index tuning is generally available in Azure Database for PostgreSQL

This smart and user-friendly feature provides insightful recommendations for your indexes, helping you enhance the performance of your workloads which in turn optimizes resource usage which translates to reduction in costs.

Adding indexes to address performance issues is a double-edged sword. On one side, it can improve performance of some queries. But on the other side, it can slow down some other. However, maintaining more indexes than those strictly necessary to improve the performance of any given workload adds latency to the data writing part in the execution of INSERT, UPDATE and DELETE statements.

In the same way, dropping indexes which you don’t know if are critical for certain queries in your workload to run with the required performance, is extremely dangerous.

Back on May, we announced the availability of automated index tuning, in Public Preview, on Azure Database for PostgreSQL Flexible Server. Index tuning is a built-in feature that automatically analyzes the tracked queries, and provides index recommendations that aim to improve the performance of your workload. It recommends indexes than should be created, indexes that should be dropped and indexes that should be rebuilt.

A variety of customer workloads have benefited from this optimization feature and we’ve made additional improvements since the public preview in the following areas:

Increased accuracy and reliability of recommendations: we’ve been able to adjust our recommendation algorithms, so that we minimize the chance to introduce regressions when any of our recommendations are applied. We provide several settings through which you can control the behavior of the optimization algorithm.

Investment where developers and DBAs don’t invest: During the preview phase of our new feature, we discovered an intriguing trend: database administrators and developers often create indexes reactively, only when a performance issue arises. However, they rarely invest time and effort into identifying duplicate indexes or those that are barely used. These redundant indexes can act as dead weight, hindering the performance of applications that rely on the data model. They not only increase storage costs but also demand more CPU and RAM to keep them updated.

Helping you further to maintain Tier 1 database environments: Especially in enterprise databases, on which ability to access the data 24/7 is of utmost importance, we’ve observed that users tend to leverage the CONCURRENTLY clause supported by PostgreSQL to build new indexes or re-build existing indexes without taking any locks that prevent concurrent inserts, updates, or deletes on the underlying table. Although that approach is great to support concurrency of writes on the table on which the index is being built, it has several caveats which are documented in Building Indexes Concurrently. This way of building indexes can easily leave invalid indexes behind. Indexes marked as invalid are ignored for querying purposes because they might be incomplete; however, they will still consume update overhead for any Data Modification Language statement that targets columns referenced by those indexes. On numerous servers where users were employing automation to maintain their indexes, we found a substantial number of invalid indexes. To address this, we recommend either dropping these indexes or reindexing them, depending on whether they are duplicates or not. This proactive approach will help improve the performance and efficiency of your database systems.

Tirelessly work to optimize the performance of your workload: Users have liked the fact that index tuning can perform this analysis 24 hours a day, seven days a week, always adjusting the recommendations it emits to the workload it has observed in the recent past.

Continually expanding the scope: Since its inception, we’ve continued to increase the range of scenarios for which we can produce index recommendations. For example, when a query is executed, we now capture the configuration of search_path so that we can properly bind the objects referred in the statement analyzed, in case objects are not fully qualified.

Getting started

 To learn more about index tuning feature, how it works, how it simplifies your life as a Database Administrator or Database developer, and how it helps you saving costs, visit our documentation below:

• Index tuning in Azure Database for PostgreSQL – Flexible Server.
• Configure index tuning in Azure Database for PostgreSQL - Flexible Server.
• Use index recommendations produced by index tuning in Azure Database for PostgreSQL - Flexible Server.
• To learn even more about our Flexible Server managed service, see the Azure Database for PostgreSQL Flexible Server.
• You can always find the latest features added to Flexible server in this release notes page.
• We are eager to hear all the great scenarios this new feature helps you optimize, and look forward to receiving your feedback at https://aka.ms/PGfeedback.