analyze-db-performance

Analyze Database Performance

Analyze PostgreSQL database performance using pg_stat_statements, slow query logs, and system metrics.

Process

1. Validate Input: Check if argument is:

   • Database connection string (postgres://...)
   • Path to slow query log file
   • None (will look for local log files)

2. Gather Performance Data: Launch 3 parallel Haiku agents to collect database metrics concurrently:

   Agent #1: Slow Query Analyzer

   • If connection string: Execute query against pg_stat_statements

     SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time
     FROM pg_stat_statements
     ORDER BY mean_exec_time DESC
     LIMIT 20;
     

   • If log file: Parse slow query log and extract patterns
   • Return: list of top 20 slowest queries with execution stats

   Agent #2: Table Size Analyzer

   • If connection string: Execute table size query

     SELECT schemaname, tablename,
            pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
     FROM pg_tables
     ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
     

   • Return: list of tables sorted by size

   Agent #3: Index Usage Analyzer

   • If connection string: Execute index usage query

     SELECT schemaname, tablename, indexname, idx_scan
     FROM pg_stat_user_indexes
     WHERE idx_scan = 0
     ORDER BY pg_relation_size(indexrelid) DESC;
     

   • Return: list of unused indexes (candidates for removal)

3. Launch 2 parallel Sonnet agents to analyze performance data:

   Agent #4: PostgreSQL Specialist (postgresql-specialist agent)

   • Analyze slow queries from Agent #1
   • Review table sizes from Agent #2
   • Evaluate index usage from Agent #3
   • Generate optimization recommendations specific to PostgreSQL
   • Return: PostgreSQL-specific findings (indexes, query rewrites, config tuning)

   Agent #5: General Database Specialist (database-specialist agent)

   • Perform cross-platform analysis of performance patterns
   • Identify anti-patterns (N+1 queries, missing indexes, bloat)
   • Suggest architectural improvements
   • Return: general database optimization recommendations