Wombat

WAF-PERF-120 – Database workloads MUST undergo regular index and query performance reviews

Pillar: Efficiancy | Severity: 🟡 Medium | Category: Logging Monitoring | Automatable: Medium

Description

All production databases MUST enable query performance insights, slow-query logging, or execution plan analysis. Slow queries exceeding defined latency thresholds MUST be reviewed monthly and optimized. Indexes MUST be justified by query patterns and revisited as access patterns evolve. Query review results MUST be documented in a performance register.

Rationale

Risk Description

Risk

Unreviewed slow queries accumulate until they become critical latency or availability incidents.

Risk

Missing indexes force full table scans, increasing cost and degrading user experience.

Requirements

  • All production databases MUST enable query performance insights, slow-query logging, or execution plan analysis.

  • Slow queries exceeding defined latency thresholds MUST be reviewed monthly and optimized.

  • Indexes MUST be justified by query patterns and revisited as access patterns evolve.

  • Query review results MUST be documented in a performance register.

Implementation Guidance

  1. Document the requirement in a version-controlled policy, ADR, or standard.

  2. Assign ownership and establish a review cadence (e.g., quarterly).

  3. Implement the required configuration as code in Terraform or equivalent IaC.

Maturity Levels

Level Name Criteria

L1

Ad hoc – control not consistently applied

* Die Anforderung ist nicht einheitlich umgesetzt; Nachweis existiert nur punktuell oder fehlt.

L2

Standardised – policy defined and communicated

* Eine dokumentierte Policy oder ein Standard beschreibt die Anforderung.

L3

Integrated – automated validation in CI/CD

* Terraform-Checks oder IaC-Gates prüfen die Anforderung automatisch in CI/CD.

L4

Auditable – metrics and exceptions tracked

* Abdeckung, Ausnahmen und Qualitätsmetriken werden quartalsweise berichtet.

L5

Optimised – continuous improvement based on data

* Die Effektivität wird kontinuierlich gemessen und automatisch optimiert.

Checks

waf-perf-120.tf.aws.rds-performance-insights

Engine: terraform | Provider: aws | Automated: Yes

AWS RDS instances must enable Performance Insights

AWS RDS instances must enable Performance Insights

Scope: resource — aws_db_instance, aws_rds_cluster_instance

Assertions: * performance_insights_enabled is_true — RDS must have Performance Insights enabled for query review.

Remediation: AWS RDS instances must enable Performance Insights

Compliant
# Example compliant with waf-perf-120.tf.aws.rds-performance-insights
Non-Compliant
# Example non-compliant with waf-perf-120.tf.aws.rds-performance-insights

waf-perf-120.config.query-review-register

Engine: config | Provider: any | Automated: No

Database query review register must exist

Database query review register must exist

Scope: file — docs/performance/db-query-review.md

Assertions: * exists attribute_exists — Database query review register must document review cadence and owners.

Remediation: Database query review register must exist

Compliant
# Example compliant with waf-perf-120.config.query-review-register
Non-Compliant
# Example non-compliant with waf-perf-120.config.query-review-register

Evidence

  • Terraform configuration implementing the requirement.

  • Policy, ADR, or standard documenting the requirement.

  • Optional cloud console or API output confirming runtime state.

Related Controls