Enable PostgreSQL Query Performance Insight and Autonomous Tuning via Terraform

[ootneim]

Description

Add Terraform configuration for Azure Database for PostgreSQL Flexible Server to enable better insight into database workload and provide automatic performance improvement recommendations.

The implementation should enable:

Query Performance Insight

• Identify long-running and resource-intensive queries.
• View query execution trends.
• View wait statistics.
• Identify queries with high execution count, I/O, data usage, and temporary file usage.
• Make the required Query Store information available through Azure Monitor / Log Analytics.

Autonomous Tuning

• Analyze the workload captured by Query Store.
• Generate recommendations for:
  • Creating indexes.
  • Removing duplicate indexes.
  • Removing unused indexes.
  • Reindexing invalid indexes.
  • Running ANALYZE on tables with missing/outdated statistics.
  • Running VACUUM on bloated tables.

Autonomous Tuning should initially operate in report/recommendation mode only.

Recommendations must be reviewed before they are applied.

---

Terraform implementation

Enable Query Store

Configure Query Store for the PostgreSQL Flexible Server.

    resource "azurerm_postgresql_flexible_server_configuration" "query_store" {
      name      = "pg_qs.query_capture_mode"
      server_id = azurerm_postgresql_flexible_server.this.id
      value     = "top"
    }

top should initially be used to capture top-level statements while limiting unnecessary Query Store overhead.

Enable Query Store wait sampling

    resource "azurerm_postgresql_flexible_server_configuration" "query_store_wait_sampling" {
      name      = "pgms_wait_sampling.query_capture_mode"
      server_id = azurerm_postgresql_flexible_server.this.id
      value     = "all"
    }

Wait sampling is required to provide wait statistics in Query Performance Insight.

Enable parameter capture

Enable parameter sampling so Autonomous Tuning can analyze parameterized queries.

resource "azurerm_postgresql_flexible_server_configuration" "query_store_parameters" {
  name      = "pg_qs.parameters_capture_mode"
  server_id = azurerm_postgresql_flexible_server.this.id
  value     = "capture_first_sample"
}

This is particularly important for workloads using prepared statements or the PostgreSQL extended query protocol.

Enable Autonomous Tuning

    resource "azurerm_postgresql_flexible_server_configuration" "autonomous_tuning" {
      name      = "index_tuning.mode"
      server_id = azurerm_postgresql_flexible_server.this.id
      value     = "report"
    }

report enables workload analysis and recommendation generation without automatically applying recommendations.

The default analysis interval should initially be retained unless there is a reason to override it.

If the interval should be explicitly managed through Terraform:

    resource "azurerm_postgresql_flexible_server_configuration" "autonomous_tuning_interval" {
      name      = "index_tuning.analysis_interval"
      server_id = azurerm_postgresql_flexible_server.this.id
      value     = "720"
    }

---

Query Performance Insight diagnostic settings

Query Performance Insight requires Query Store data to be sent to a Log Analytics workspace.

Ensure that the existing PostgreSQL diagnostic settings include:

• PostgreSQLFlexSessions
• PostgreSQLFlexQueryStoreRuntime
• PostgreSQLFlexQueryStoreWaitStats

Example:

    resource "azurerm_monitor_diagnostic_setting" "postgresql_query_performance" {
      name                       = "postgresql-query-performance"
      target_resource_id         = azurerm_postgresql_flexible_server.this.id
      log_analytics_workspace_id = var.log_analytics_workspace_id

      log_analytics_destination_type = "Dedicated"

      enabled_log {
        category = "PostgreSQLFlexSessions"
      }

      enabled_log {
        category = "PostgreSQLFlexQueryStoreRuntime"
      }

      enabled_log {
        category = "PostgreSQLFlexQueryStoreWaitStats"
      }
    }

If the PostgreSQL Terraform module already manages an azurerm_monitor_diagnostic_setting, extend the existing resource rather than creating an additional diagnostic setting.

---

Suggested module implementation

The functionality should preferably be configurable at module level.

    variable "query_performance_insight_enabled" {
      description = "Enable PostgreSQL Query Performance Insight."
      type        = bool
      default     = true
    }

    variable "autonomous_tuning_enabled" {
      description = "Enable PostgreSQL Autonomous Tuning in report mode."
      type        = bool
      default     = true
    }

A consolidated implementation could use `for_each`:

    locals {
      query_performance_configurations = var.query_performance_insight_enabled ? {
        "pg_qs.query_capture_mode"              = "top"
        "pgms_wait_sampling.query_capture_mode" = "all"
        "pg_qs.parameters_capture_mode"         = "capture_first_sample"
      } : {}

      autonomous_tuning_configurations = var.autonomous_tuning_enabled ? {
        "index_tuning.mode" = "report"
      } : {}

      postgresql_performance_configurations = merge(
        local.query_performance_configurations,
        local.autonomous_tuning_configurations
      )
    }

    resource "azurerm_postgresql_flexible_server_configuration" "performance" {
      for_each = local.postgresql_performance_configurations

      name      = each.key
      server_id = azurerm_postgresql_flexible_server.this.id
      value     = each.value
    }

---

Considerations

• Query Store is enabled at PostgreSQL Flexible Server level and applies to the databases on the server.
• Evaluate Query Store overhead before enabling it on Burstable servers.
• Autonomous Tuning requires a supported PostgreSQL Flexible Server configuration.
• Autonomous Tuning should initially only be enabled on suitable servers with at least 4 vCores.
• Autonomous Tuning should run in report mode only.
• Recommendations must not be automatically applied.
• Recommendations may include:
  • CREATE INDEX
  • DROP INDEX
  • REINDEX
  • ANALYZE
  • VACUUM
• Query text may contain sensitive data and access to detailed query information should follow existing access-control requirements.
• Existing diagnostic settings should be reused where possible.

---

Acceptance criteria

• Query Store is enabled through Terraform.
• Query Store wait sampling is enabled through Terraform.
• Parameter capture is enabled using capture_first_sample.
• Autonomous Tuning is enabled using index_tuning.mode = report.
• Required PostgreSQL diagnostic categories are sent to the existing Log Analytics workspace.
• Query Performance Insight displays workload information for the PostgreSQL Flexible Server.
• Query Performance Insight displays query wait statistics.
• Autonomous Tuning performs workload analysis.
• Autonomous Tuning recommendations can be reviewed.
• Recommendations are not automatically applied.
• Recommendations can include index, ANALYZE, and VACUUM improvements.
• Query Performance Insight can be enabled/disabled through the PostgreSQL Terraform module.
• Autonomous Tuning can be enabled/disabled through the PostgreSQL Terraform module.
• Existing diagnostic settings are extended rather than duplicated.
• Terraform plan does not cause an unexpected PostgreSQL Flexible Server replacement.
• Documentation describes where Query Performance Insight can be viewed.
• Documentation describes where Autonomous Tuning recommendations can be reviewed.