Intelligent tuning in Azure Database for PostgreSQL flexible server

Azure Database for PostgreSQL flexible server includes an intelligent tuning feature that automatically improves performance and helps prevent problems. Intelligent tuning continuously monitors the status of your Azure Database for PostgreSQL flexible server database and dynamically adapts the database to your workload.

This feature includes two automatic tuning functions:

• Autovacuum tuning: This feature is deprecated and replaced by the newer adaptive autovacuum.
• Writes tuning: This function monitors the volume and patterns of write operations. It modifies parameters that affect write performance. These adjustments enhance both system performance and reliability, and proactively avert potential complications.

Learn how to Configure intelligent tuning.

Why use intelligent tuning?

Tuning write operations within the database is a critical part of maintaining the health and performance of an Azure Database for PostgreSQL flexible server. Database administrators typically perform this task. Constantly monitoring a database and fine-tuning write operations can be challenging and time-consuming. This task becomes increasingly complex when you're dealing with multiple databases.

Intelligent tuning simplifies this process. Instead of manually overseeing and tuning your database, use intelligent tuning to automatically monitor and tune the database. You can then focus on other important tasks.

The writes tuning function observes the quantity and transactional patterns of write operations. It intelligently adjusts parameters such as bgwriter_delay, checkpoint_completion_target, max_wal_size, and min_wal_size. By doing so, it enhances system performance and reliability, even under high write loads.

When you use intelligent tuning, you save valuable time and resources by relying on your Azure Database for PostgreSQL flexible server to maintain the optimal performance of your databases.

How does intelligent tuning work?

Intelligent tuning is an ongoing monitoring and analysis process that learns about the characteristics of your workload. It also tracks your current load and resource usage, such as CPU or IOPS. It doesn't disturb the normal operations of your application workload.

The process allows the database to dynamically adjust to your workload by discerning the current write performance and checkpoint efficiency on your server. By using these insights, intelligent tuning deploys tuning actions that enhance your workload's performance and avoid potential pitfalls.

Writes tuning

Intelligent tuning adjusts four parameters related to writes tuning: bgwriter_delay, checkpoint_completion_target, max_wal_size, and min_wal_size.

The bgwriter_delay parameter determines how often the background writer process wakes up to clean "dirty" buffers (buffers that are new or modified). The background writer process is one of three processes in an Azure Database for PostgreSQL flexible server that handle write operations. The other processes are the checkpointer process and back-end writes (standard client processes, such as application connections).

The background writer process's primary role is to alleviate the load from the main checkpointer process and decrease the strain of back-end writes. The bgwriter_delay parameter governs the frequency of background writer rounds. By adjusting this parameter, you can also optimize the performance of Data Manipulation Language (DML) queries.

The checkpoint_completion_target parameter is part of the second write mechanism that an Azure Database for PostgreSQL flexible server supports, specifically the checkpointer process. Checkpoints occur at constant intervals that checkpoint_timeout defines (unless forced by exceeding the configured space). To avoid overloading the I/O system with a surge of page writes, writing dirty buffers during a checkpoint is spread out over a period of time. The checkpoint_completion_target parameter controls this duration by using checkpoint_timeout to specify the duration as a fraction of the checkpoint interval.

The default value of checkpoint_completion_target is 0.9 (since PostgreSQL 14). This value generally works best, because it spreads the I/O load over the maximum time period. In rare cases, checkpoints might not finish in time because of unexpected fluctuations in the number of needed Write-Ahead Logging (WAL) segments. Potential impact on performance is the reason why checkpoint_completion_target is a target metric for intelligent tuning.

Limitations and known issues

• Intelligent tuning makes optimizations only in specific ranges. It's possible that the feature doesn't make any changes.
• Intelligent tuning doesn't adjust ANALYZE settings.

Related content

• Configure intelligent tuning.
• Troubleshooting for Azure Database for PostgreSQL.
• Autovacuum tuning in Azure Database for PostgreSQL.
• Troubleshoot high IOPS utilization in Azure Database for PostgreSQL.
• Best practices to bulk upload data to Azure Database for PostgreSQL.
• Troubleshoot high CPU utilization in an Azure Database for PostgreSQL.
• Query Performance Insight in Azure Database for PostgreSQL.