Configure autonomous tuning

Autonomous tuning can be enabled, disabled, and configured through a set of parameters that control its behavior, such as how often a tuning session should run.

Autonomous tuning depends on the data persisted locally to the azure_sys database by query store. We don't recommend enabling query store on the Burstable pricing tier, due to the performance implications it might have. For the same reason, autonomous tuning isn't recommended for servers using compute from the Burstable tier.

Autonomous tuning is an opt-in feature that isn't enabled by default on a server. It can be enabled or disabled globally for all databases on a given server and can't be turned on or off per database.

Using the Azure portal:

Select your Azure Database for PostgreSQL flexible server instance.
In the resource menu, under Intelligent Performance, select Autonomous tuning.
If either pg_qs.query_capture_mode is set to none or index_tuning.mode is set to off, the Autonomous tuning page gives you the option to enable autonomous tuning. Select on either of the two Enable tuning buttons, to enable autonomous tuning feature and its required query store dependency, if query store is disabled.
Wait for the deployment to complete successfully before considering that the feature is enabled.
After enabling autonomous tuning, allow up to 12 hours for the autonomous tuning engine to analyze the workload collected by query store during that time, and eventually produce recommendations.

Important

When autonomous tuning is enabled through the Enable tuning button, if pg_qs.query_capture_mode is set to none, it's changed to all. If it was already set to either top or all, it's left in its current state.

You can enable autonomous tuning in an existing server via the az postgres flexible-server autonomous-tuning update command.

To enable autonomous tuning, use this command:

az postgres flexible-server autonomous-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled true

If the previous command executes successfully, you should see the following output:

WARNING: Enabling autonomous tuning for the server.
WARNING: Autonomous tuning is enabled for the server.

Steps to disable autonomous tuning

Portal
CLI

Using the Azure portal:

Select your Azure Database for PostgreSQL flexible server instance.
In the resource menu, under Intelligent Performance, select Autonomous tuning.
Select Disable tuning to disable the feature.
Wait for the deployment to complete successfully before considering that the feature is disabled.
Assess whether you want to continue using query store to monitor the performance of your workload and leave it enabled or, if you want to disable it, do so by setting pg_qs.query_capture_mode to none.

Important

When autonomous tuning is disabled through the Disable tuning button, server parameter pg_qs.query_capture_mode isn't set to none, but left as it is configured.

You can disable autonomous tuning in an existing server via the az postgres flexible-server autonomous-tuning update command.

To disable autonomous tuning, use this command:

az postgres flexible-server autonomous-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled false

If the previous command executes successfully, you should see the following output:

WARNING: Disabling autonomous tuning for the server.
WARNING: Autonomous tuning is disabled for the server.

Assess whether you want to continue using query store to monitor the performance of your workload and leave it enabled or, if you want to disable it, by setting pg_qs.query_capture_mode to none.

Important

When autonomous tuning is disabled through the CLI command, server parameter pg_qs.query_capture_mode isn't set to none, but left as it is configured.

Steps to show the state of autonomous tuning

Portal
CLI

Using the Azure portal:

Select your Azure Database for PostgreSQL flexible server instance.
In the resource menu, under Intelligent Performance, select Autonomous tuning.
If autonomous tuning is enabled, the page displays the Disable tuning button.
If autonomous tuning is disabled, the page displays the Enable tuning button.

You can show the state of autonomous tuning in an existing server via the az postgres flexible-server autonomous-tuning show command.

To show the state of autonomous tuning, use this command:

az postgres flexible-server autonomous-tuning show \
  --resource-group <resource_group> \
  --server-name <server>

If autonomous tuning is enabled, you should see the following output:

WARNING: Autonomous tuning is enabled for the server.

If autonomous tuning is disabled, you should see the following output:

WARNING: Autonomous tuning is disabled for the server.

Steps to list autonomous tuning settings

Portal
CLI

Using the Azure portal:

Select your Azure Database for PostgreSQL flexible server instance.
In the resource menu, under Intelligent Performance, select Autonomous tuning.
Select Tuning settings.

You can show the value of a single autonomous tuning setting in an existing server via the az postgres flexible-server autonomous-tuning show-settings command.

For example, to show the value of the autonomous tuning setting called analyze_interval, use this command:

az postgres flexible-server autonomous-tuning show-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval

The command returns all information about the server parameter corresponding to that setting of autonomous tuning, and the output is similar to the following:

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "720"
}

Also, you can show the list of all autonomous tuning settings in an existing server via the az postgres flexible-server autonomous-tuning list-settings command.

To list all autonomous tuning settings, use this command:

az postgres flexible-server autonomous-tuning list-settings \
  --resource-group <resource_group> \
  --server-name <server>

The command returns all server parameters that control the different settings of autonomous tuning, and the output is similar to the following:

[
  {
    "allowedValues": "60-10080",
    "dataType": "Integer",
    "defaultValue": "720",
    "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.analysis_interval",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "minutes",
    "value": "720"
  },
  {
    "allowedValues": "1-10",
    "dataType": "Integer",
    "defaultValue": "2",
    "description": "Maximum number of columns that can be part of the index key for any recommended index.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_columns_per_index",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_columns_per_index",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "2"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each database during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_index_count",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_index_count",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each table.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_indexes_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_indexes_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "5-100",
    "dataType": "Integer",
    "defaultValue": "25",
    "description": "Number of slowest queries per database for which indexes can be recommended.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_queries_per_database",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_queries_per_database",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "25"
  },
  {
    "allowedValues": "0.05-0.2",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Acceptable regression introduced by a recommended index on any of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_regression_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_regression_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-1.0",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Maximum total size, in percentage of total disk space, that all recommended indexes for any given database can use.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_total_size_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_total_size_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-20.0",
    "dataType": "Numeric",
    "defaultValue": "0.2",
    "description": "Cost improvement that a recommended index must provide to at least one of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.min_improvement_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.min_improvement_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.2"
  },
  {
    "allowedValues": "off,report",
    "dataType": "Enumeration",
    "defaultValue": "off",
    "description": "Configures index optimization as disabled ('OFF') or enabled to only emit recommendation. Requires Query Store to be enabled by setting pg_qs.query_capture_mode to 'TOP' or 'ALL'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.mode",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.mode",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "off"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average DML operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_dml_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_dml_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  },
  {
    "allowedValues": "30-720",
    "dataType": "Integer",
    "defaultValue": "35",
    "description": "Minimum number of days the index has not been used, based on system statistics, so that it is considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_min_period",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_min_period",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "days",
    "value": "35"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average read operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_reads_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_reads_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  }
]

Steps to modify autonomous tuning settings

Portal
CLI

Using the Azure portal:

Select your Azure Database for PostgreSQL flexible server instance.
In the resource menu, under Intelligent Performance, select Autonomous tuning.
Select Tuning settings.
Modify the values of as many settings as you want to change, and select Save.
Wait for the deployment to complete successfully before considering that the value of the settings is changed.

You can modify the value of a single autonomous tuning setting in an existing server via the az postgres flexible-server autonomous-tuning set-settings command.

For example, to set the value of the autonomous tuning setting called analyze_interval to 1440, use this command:

az postgres flexible-server autonomous-tuning set-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval \
  --value 1440

The command returns all information about the server parameter corresponding to that setting of autonomous tuning, and the output is similar to the following:

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
  "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "1440"
}

Last updated on 2026-02-12

Configure autonomous tuning

Steps to enable autonomous tuning

Steps to disable autonomous tuning

Steps to show the state of autonomous tuning

Steps to list autonomous tuning settings

Steps to modify autonomous tuning settings

Related content

Additional resources