Governance policies for Database Optimization

Domain: performance

Name	Description
Database performance baseline	Enable diagnostics on all databases, create indexes for primary queries in deploy.sh, configure connection pooling, and set up read replicas for production

Description	Instead
Do not use SELECT * in application queries	Select only required columns and use covering indexes with INCLUDE
Do not use cross-partition queries in Cosmos DB for common operations	Design partition keys to align with primary query patterns; use point reads where possible
Do not create database connections in a loop	Use connection pooling with Min/Max Pool Size configured in the connection string
Do not skip indexing for known query patterns	Create nonclustered indexes with INCLUDE columns for all primary query patterns in deploy.sh

• SQL Database performance monitoring
• Cosmos DB partition key design
• PostgreSQL performance tuning
• SQL indexing best practices
• Azure SQL read scale-out

---

Check	Severity	Description
WAF-PERF-DB-001	Required	Define SQL indexing strategy — create indexes in deploy.sh post-deployment script for primary query patterns
WAF-PERF-DB-002	Required	Design Cosmos DB partition keys based on query patterns — use high-cardinality fields that align with read and write access patterns
WAF-PERF-DB-003	Required	Configure connection pooling for all database connections — exact connection string patterns for SQL, Cosmos, and PostgreSQL
WAF-PERF-DB-004	Recommended	Configure read replicas for SQL and PostgreSQL to offload read traffic from the primary
WAF-PERF-DB-005	Required	Enable Query Performance Insight and diagnostic settings for database performance monitoring

---

Define SQL indexing strategy — create indexes in deploy.sh post-deployment script for primary query patterns

Severity: Required
Rationale: Missing indexes cause full table scans; proper indexing can improve query performance by 100-1000x. Indexes are created post-deployment via T-SQL
Agents: terraform-agent, bicep-agent, cloud-architect, app-developer, csharp-developer, python-developer

• Microsoft.Sql/servers/databases
• Microsoft.DocumentDB/databaseAccounts
• Microsoft.DBforPostgreSQL/flexibleServers
• Microsoft.Cache/redis

---

Design Cosmos DB partition keys based on query patterns — use high-cardinality fields that align with read and write access patterns

Severity: Required
Rationale: Partition key choice is the single most important Cosmos DB design decision; bad keys cause hot partitions, throttling, and cross-partition queries
Agents: cloud-architect, app-developer, csharp-developer, python-developer, terraform-agent, bicep-agent

• Microsoft.Sql/servers/databases
• Microsoft.DocumentDB/databaseAccounts
• Microsoft.DBforPostgreSQL/flexibleServers
• Microsoft.Cache/redis

---

Configure connection pooling for all database connections — exact connection string patterns for SQL, Cosmos, and PostgreSQL

Severity: Required
Rationale: Connection creation takes 20-100ms; pooling reuses connections, reducing latency and preventing connection exhaustion under load
Agents: app-developer, csharp-developer, python-developer, cloud-architect, terraform-agent, bicep-agent

• Microsoft.Sql/servers/databases
• Microsoft.DocumentDB/databaseAccounts
• Microsoft.DBforPostgreSQL/flexibleServers
• Microsoft.Cache/redis

---

Configure read replicas for SQL and PostgreSQL to offload read traffic from the primary

Severity: Recommended
Rationale: Read replicas handle 50-80% of typical application traffic (reads); offloading reduces primary load and improves read latency
Agents: terraform-agent, bicep-agent, cloud-architect

• Microsoft.Sql/servers/databases
• Microsoft.DocumentDB/databaseAccounts
• Microsoft.DBforPostgreSQL/flexibleServers
• Microsoft.Cache/redis

---

Enable Query Performance Insight and diagnostic settings for database performance monitoring

Severity: Required
Rationale: Without query monitoring, slow queries go undetected until they cause user-visible performance degradation
Agents: terraform-agent, bicep-agent, cloud-architect, monitoring-agent

• Microsoft.Sql/servers/databases
• Microsoft.DocumentDB/databaseAccounts
• Microsoft.DBforPostgreSQL/flexibleServers
• Microsoft.Cache/redis

---