Azure Database for PostgreSQL Blog

9 MIN READ

Unleash the power of automated index tuning in Azure Database for PostgreSQL Flexible Server

nachoalonsoportillo

Microsoft

May 21, 2024

Azure Database for PostgreSQL Flexible Server offers a smart and easy way to tune your indexes and consequently improve the performance of your workloads and reduce your Azure costs.

Why index tuning matters

Indexes are data structures that help your database find and retrieve data faster. They are essential for improving the performance of your queries, especially when you have large tables with millions of rows. However, creating and maintaining indexes is not a trivial task. It requires a deep understanding of your data, your queries, and your database engine.

There are many factors that affect the efficiency of your indexes, such as:

The size and distribution of your data.
The frequency and complexity of your queries.
The type and configuration of your indexes.
The changes and updates in your data and queries over time.

If you don't tune your indexes properly, you may end up with:

Slow and inefficient queries that consume more resources (CPU, RAM & IOPS) and time.
Unused or redundant indexes that waste disk space and maintenance overhead.
Missing or outdated indexes that cause table scans and performance degradation.
Higher Azure bills because of the additional CPU, RAM or IOPS required to execute your workload in reasonable time, on databases without proper and optimal indexes.

Therefore, index tuning is a vital part of database optimization. It involves creating, dropping, or modifying indexes to match the needs of your data and queries. However, index tuning is not a one-time activity. It is an ongoing process that requires constant monitoring and analysis of your database performance and workload.

The challenges and difficulties of index tuning

Index tuning is not an easy task. It requires a lot of time, effort, and expertise from DBAs and developers. Some of the common challenges and difficulties of index tuning are:

Finding the optimal set of indexes for your database. There is no one-size-fits-all solution for index tuning. You need to consider the trade-offs between the benefits and costs of each index. For example, adding more indexes may speed up your queries, but it may also slow down your inserts, updates, and deletes. You also need to balance the disk space and maintenance overhead of your indexes.
Keeping up with the changes in your data and queries. Your data and queries may change over time due to various reasons, such as business requirements, user behavior, or data growth. These changes may affect the performance and relevance of your indexes. You need to monitor and analyze your database workload regularly and adjust your indexes accordingly.
Testing and validating the impact of your index changes. Before you apply any index changes to your production database, you need to measure the before and after performance of your queries and ensure that your index changes do not cause any major regressions, and the benefit they provide is significant enough. You also need to execute your index changes in a way that maximizes the ability to continue accessing your data, while the indexes are being created or maintained.
Considering the optimal indexes for the whole workload. Figuring out what are the best indexes for an individual query, without considering the rest of the workload, might not be too difficult. However, having to consider the optimal indexes for the whole workload is way more complicated. You need to take into account the frequency, importance, and execution time of each query, as well as the interactions and conflicts between different indexes. This is a challenging task that requires a lot of analysis and experience.

As you can see, index tuning is a complex and tedious process that requires a lot of skills and resources. It can be overwhelming and frustrating for many DBAs and developers, especially if they have to deal with multiple databases and applications.

The relief of having an automated assistant for index tuning

What if you could have an automated assistant that could help you with index tuning? What if you could delegate some of the tasks and decisions of index tuning to a smart and reliable tool that could analyze your database and suggest the best index actions for you? That would be a huge relief, right?

That's exactly what the index tuning feature in Azure Database for PostgreSQL Flexible Server can do for you. It is a built-in service that can automatically monitor, analyze, and optimize your indexes based on your database workload, analyzed holistically rather than each query individually. It can help you with the following aspects of index tuning:

Recommendations. It can provide you with a list of index actions that can improve your query performance, such as creating, or dropping indexes. It can also show you:
- For each index whose creation is suggested, the impact it has on the queries analyzed in your databases, and the size these indexes are estimated to have when created.
- For each index recommended to delete, the amount of disk space released when dropped.
Execution. It provides the SQL statement you can copy/paste into your favorite PostgreSQL client, to implement the recommendation.

With the index tuning feature in Azure Database for PostgreSQL Flexible Server, you can save a lot of time and effort in tuning the indexes in your database to improve the performance of your actual workload. You can rely on a smart and easy to use tool that can help you optimize your database performance and reduce your operational costs. You can also focus more on your core business logic and application development, rather than worrying about your database indexes.

Test driving the feature

For test driving the feature, we setup an instance of Azure Database for PostgreSQL Flexible Server, configured with Query Store enabled, index tuning feature enabled, an Azure Log Analytics workspace to which we send logs and metrics from the configured instance, so that we can later use Query Performance Insight's dashboard to visualize different performance aspects of the server before and after index recommendations are applied.

The benchmark used in this exercise is the well-known TPCH with the following data volumes:

Table name	Number of rows
region	5
nation	25
supplier	10000
customer	150000
part	200000
partsupp	800000
orders	1500000
lineitem	6001215

The sample workload executed against that model consists of 22 SELECT queries of diverse complexity, all of which aim to resolve typical questions in a decission making system.

Once tables are loaded with data, we run ANALYZE on all tables to make sure that the query planner has up-to-date statistics so that it can better estimate the number of rows retrieved by a query, and therefore is able to make good choices of query plans.

We also create a workbook to monitor the performance of the system while running the benchmark. With the help of the workbook, we will be able to visually see the benefit provided when the index reommendations are applied.

The benchmark consists of 22 queries which are executed in parallel for 12 hours. When the execution of any given query finishes, the exact same query is enqueued to run again. With such scheduling mechanism and given that some queries might complete in seconds while some others might take hours to complete, the ones that finish faster will execute significantly more times than those whose average duration is among the highest.

After first index tuning session analyzes the workload executed in the past 12 hours, it produces some recommendations which we review and, based on the knowledge we have about:

How repetitive the pattern of the exercised workload is;
The estimated improvement offered by each recommended index;
How much disk space we want to dedicate to new indexes;
When is the best time window to create the chosen recommendations;

We decide which ones we want to implement and when.

With the recommended indexes created, we continue running the workload and continue plotting collected metrics in our workbook, to compare impact of the recommendations.

Configure index tuning

Simplest way to enable the index tuning feature is by using the resource menu of your instance of Azure Database for PostgreSQL flexible server. Under Intelligent Performance section, select Index tuning (preview).

If the feature is not enabled yet, you can use either of the two Enable index tuning buttons in that page, and it will automatically configure it for you with default values.

Once successfully enabled, the page will inform of the current status and frequency at which it will run.

Inspect and apply index recommendations

Once index tuning analyzes our workload and produces some recommendations, the Index tuning (preview) page will show a summarization card and some aggregated information about the recommendations produced, like the timestamp of the most recent tuning session that has emitted recommendations, the total number of recommendations of each type, or the average improvement those recommendations can provide to our workload.

Selecting the View index recommendations summarization card takes us to a page showing the list of recommendations available, which we can read, filter, or sort in the way that best suits our needs. This list only shows a limited amount of details about each recommendation.

To see all the attributes associated to a particular recommendation, you can select the link placed on top of the values of the leftmost column (Index recommendation), and it will open the Index recommendation details panel to the right of the screen, and expose every single detail, including the SQL script that you can copy and run from your preferred PostgreSQL client application to apply the recommendation.

The page on which all index recommendations are listed, also provides a shortcut Copy SQL script menu option which you can use to fetch the SQL statement you must run from the SQL client application of your preference to apply the recommendation.

With the script at hand, you can use your preferred PostgreSQL client application and, using the credentials of a user with enough permissions to create indexes on the target object, connect to the database of your instance for which the recommendation was produced, paste the SQL script and run it.

For this exercise we will create all indexes recommended, and will later measure if they're really improve or worsen the performance of our workload, which will continue running.

Compare performance of workload before and after applying recommendations

The following graphs show various metrics collected during almost 3 hours, extending from 1.5 hours before the index recommendations had been applied to 1.5 hours after.

These graphs show that the performance of the workload improved significantly after creating the recommended indexes.

Main evidences of that improvement are the reduction of CPU percent, temporary files size, average query duration, and total temp blks read and written, and the increase of queries executed.

The performance improvement is attributed to the fact that the indexes reduced the need for sequential scans, sorting, and hashing operations, which are CPU and I/O intensive.

Detailed evidence of performance improvement

CPU percent: The graph shows that the CPU percent dropped from around 80% to around 20% after creating the indexes. This indicates that the workload run required less CPU resources to process the queries.
Temporary files: The graph shows that the total size of temporary files dropped from around 22 GB to around 2.3 GB after creating the indexes. This indicates that the workload run required less temporary disk space to store intermediate results of queries.
Queries executed: The graph shows that the number of queries executed on any given time increased from around 20 to around 40 after creating the indexes. This indicates that the workload run was able to process more queries in the same time period.
Average query duration: The graph shows that the average query duration dropped from around 5 seconds to around 1 second after creating the indexes. This indicates that the workload run was able to complete each query faster.
Total temp blks read and written: The graphs show that the total temp blks read and written dropped from around 500,000 and 400,000 to around 50,000 and 40,000, respectively, after creating the indexes. This indicates that the workload run required less I/O operations to read and write temporary data.

Conclusion

Index tuning is an important but challenging part of database optimization. It can have a significant impact on your query performance and database efficiency. However, it can also be a complex and tedious process that requires a lot of time, effort, and expertise from DBAs and developers.

That's why Azure Database for PostgreSQL Flexible Server offers a smart and easy way to tune your indexes with the index tuning feature. It is an automated assistant that can monitor, analyze, and propose optimizations to your indexes, based on your database workload. It can save you a lot of time and effort in index tuning and help you focus more on your core business logic and application development.

If you want to optimize your database performance and reduce your operational costs, try out the index tuning feature in Azure Database for PostgreSQL Flexible Server today. You can create a new instance or migrate your existing PostgreSQL database to Azure and enjoy the benefits of a smart and easy index tuning functionality.

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: