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 plan...