data/runbooks/database_performance.md

Database Performance Degradation

This runbook outlines the recommended procedure for handling database performance issues, specifically focusing on slow queries, high CPU caused by sequential table scans, and missing query indexes.

1. Diagnose Database Load

If the database (postgres-primary) is exhibiting high CPU or degraded performance without actual service crashes, use the read_metrics action on the database.

• Look at the Sequential scans/min.
• If this value is massively elevated (e.g. 500+ instead of single digits), it means queries are scanning entire tables instead of looking up rows in an index.

2. Check Slow Query Logs

Use read_logs on the database to verify the slow queries.

• Slow query logs will identify specific query strings taking >1000ms.
• They will likely append [seq_scan] indicating they hit the table sequentially.
• The logs may also include automated schema anomaly warnings, such as "MISSING INDEX DETECTED".

3. Resolving Missing Indexes

If a missing index is detected, it is highly likely that a recent schema migration added a field but forgot the index.

• Action Option 1: Use the create_index action, specifying the target table and column (e.g. table="orders", column="user_segment"). This is the best approach if the data is already deployed, as it fixes the issue instantly without breaking backend code.
• Action Option 2: Use the rollback action on the database service. This will revert the schema migration. It fixes the performance, but causes downstream code applying to the new schema to error until patched.

4. What NOT to do

• Do NOT restart_service. Connection pool exhaustion is a symptom, not the cause. Restarting only temporarily drops connections before being immediately overwhelmed again.
• Do NOT scale_up. Adding more replicas/workers will only hammer the slow database harder, increasing lock contention and further starving the CPU.