Production Database Performance Remediation Directive

Role: Staff Database Reliability Engineer responsible for high-throughput OLTP database tuning, indexing strategies, and query performance.

Context

• Recipient engineering group: {{engineering_team}}
• Primary database engine: {{primary_database_type}}
• Service level objective threshold: {{p99_latency_threshold}}
• Remediation review deadline: {{incident_review_date}}
• Identified query bottlenecks: {{slow_query_patterns}}
• Replica tier configuration: {{read_replica_status}}

Task

Author a high-priority engineering notification email to {{engineering_team}} highlighting critical database query latency degradation, prescribing targeted indexing and query refactoring fixes, and establishing a firm remediation cutoff.

Method

1. Highlight the current latency metrics exceeding the {{p99_latency_threshold}} threshold on {{primary_database_type}}.
2. Itemize the top degrading query signatures identified in {{slow_query_patterns}} with their table scan impacts.
3. Analyze missing or redundant indexes contributing to buffer cache churn and lock contention.
4. Recommend query refactoring patterns (e.g., keyset pagination, selective projection, CTE optimization).
5. Explain how to offload analytical or read-heavy traffic using {{read_replica_status}}.
6. Provide exact DDL index creation guidelines with concurrent/online execution flags to prevent table locks.
7. Establish the verification criteria and testing steps required ahead of the {{incident_review_date}} deadline.

Constraints

• MUST specify zero-downtime index creation syntax appropriate for {{primary_database_type}}.
• MUST NOT suggest vertically scaling hardware as a substitute for query optimization.
• MUST include explicit benchmark criteria to confirm query remediation.
• Keep communication strictly technical, constructive, and oriented toward rapid engineering execution.

Output format

• Subject line: Performance Alert: Query Optimization Required for {{primary_database_type}} [Deadline: {{incident_review_date}}]
• Telemetry Summary (2-3 concise sentences on latency spikes)
• Query Analysis & Recommended Fixes (bulleted list per slow query pattern)
• Deployment Safeguards (rules for rolling out index updates)
• Next Steps & Sign-off Deadline (clear checklist)
• Total length: 300-450 words

Self-review

• Ensure the {{p99_latency_threshold}} metric is explicitly tied to customer impact or SLA risk.
• Check that online DDL execution constraints are clearly stated to avoid production lockouts.
• Confirm the review deadline is emphasized in both the header and conclusion.