backend-db-performance

Database Optimization Skill

Degree of freedom: MIXED. Which query/index/N+1 to fix [HIGH freedom]; existing-index probes and EXPLAIN ANALYZE [LOW freedom — run exactly].

How to reason

1. Observe — EXPLAIN ANALYZE / pg_stat_statements / existing pg_indexes
2. Interpret — seq scan vs N+1 vs over-fetch vs missing pagination
3. Classify — add-index / eager-load / narrow-select / paginate / leave-alone
4. Severity — write-path timeout outranks a 200ms list page

Worked example

Observe: /feed p95 2.4s; Prisma logs 81 queries; pg_indexes has no idx_posts_user_created. Interpret: findMany posts then per-row user.findUnique — N+1; ORDER BY created_at is a seq scan. Classify: eager-load include: { author } + composite index (user_id, created_at DESC). Verify: EXPLAIN ANALYZE → Index Scan; query count 2; p95 < 200ms. Did not add a duplicate index.

Self-critique before reporting

• Existing first — listed pg_indexes / migrations before CREATE INDEX
• EXPLAIN — the claimed winner has ANALYZE output, not intuition
• No duplicate index — the proposed name was queried and absent
• Right owner — schema consistency → audit-db-schema; RLS access → plan-rls-audit

Systematic approach to identifying and fixing database performance issues.

When to Use

• Slow page loads (database bottleneck)
• Query timeout errors
• N+1 queries
• Schema design review
• Index optimization
• Migration planning

CRITICAL: Check Existing First [LOW freedom — run exactly]

Before ANY optimization, verify current state:

1. Check existing indexes:

SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'your_table';

1. Check existing migrations:

ls -la supabase/migrations/ | grep -i "index\|optim\|perf"

1. Check if index already exists:

SELECT 1 FROM pg_indexes WHERE indexname = 'your_proposed_index';

1. Check Supabase advisors for current issues:

• Use get_advisors MCP tool for performance/security
• Don't re-fix already addressed issues