backend-db-performance

# Database Optimization Skill Systematic approach to identifying and fixing database performance issues. ## When to Use - Slow page loads (database bottleneck) - Query timeout errors - N+1 query problems - Schema design review - Index optimization - Migration planning ## CRITICAL: Check Existing First **Before ANY optimization, verify current state:** 1. **Check existing indexes:** ```sql SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'your_table'; ``` 2. **Check existing migrations:** ```bash ls -la supabase/migrations/ | grep -i "index\|optim\|perf" ``` 3. **Check if index already exists:** ```sql SELECT 1 FROM pg_indexes WHERE indexname = 'your_proposed_index'; ``` 4. **Check Supabase advisors for current issues:** - Use `get_advisors` MCP tool for performance/security - Don't re-fix already addressed issues **Why:** Duplicate indexes waste storage and slow writes. Always verify before adding. ## Performance Investigation ### 1. Identify Slow Queries **Prisma - Enable query logging:** ```typescript // lib/db.ts import { PrismaClient } from '@prisma/client' export const db = new PrismaClient({ log: [ { emit: 'event', level: 'query' }, ], }) db.$on('query', (e) => { if (e.duration > 100) { // Log queries > 100ms console.log(`Slow query (${e.duration}ms):`, e.query) } }) ``` **Supabase - Query analysis:** ```sql -- Enable query stats CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Find slow queries SELECT q...