Automated DB Index Health Monitoring and Recommendation Engine

[JamesEjembi]

Problem Statement

Database performance degrades over time as data grows and query patterns change, often due to missing or unused indexes. An index health monitoring service must be built that analyzes PostgreSQL index usage statistics (pg_stat_user_indexes, pg_stat_all_tables), identifies unused indexes (candidates for removal), recommends missing indexes based on sequential scan patterns, and generates monthly optimization reports.

Technical Bounds

• Analysis interval: daily cron (off-peak hours).
• Indexes analyzed: idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes.
• Unused threshold: idx_scan < 10 in 30 days -> candidate for removal; notify DBA.
• Missing index detection: find queries with sequential scans on large tables (> 10K pages) and suggest indexes on filter columns.
• Report: monthly PDF/email with index usage summary, unused candidates, and recommended new indexes.
• Safety: recommended index DDL is reviewed; never auto-execute DROP INDEX.

Steps

1. Implement analyzer: query pg_stat_user_indexes, compute scan frequency, identify unused indexes.
2. Implement missing index detector: analyze pg_stat_statements for sequential scans on large tables; extract filter columns.
3. Store results in index_health_reports table: (table_name, index_name, scans_30d, size_mb, recommendation).
4. Generate monthly report: PDF with tables and recommendations.
5. Add API: GET /api/v1/db/index-health returns current recommendations.