Db Optimization

---
name: db-optimization
description: Use when the bottleneck is database-shaped — slow queries, lock timeouts, connection exhaustion, N+1 patterns, index questions, schema migrations on hot tables. Evidence-first DB playbook for PostgreSQL, MySQL/MariaDB, MongoDB, and Redis backing the db-performance-tuner agent.
---

# DB Optimization — EXPLAIN before you touch anything

Playbook for the DB half of the Hydraia perf route. The `db-performance-tuner` agent does the diagnosis; this skill sets the rules the route runs under.

<HARD-RULES>
- Read-first: no change is proposed without its EXPLAIN plan, stat, or code evidence. Live DB unavailable → code/schema inference only, tagged UNVERIFIED, with the confirming commands listed for the human.
- All write-shaped operations (create index, schema change, config change) are PLAN TASKS — reviewed, gated, never executed by a diagnosis agent.
- Destructive or blocking operations (table rewrites, long locks) are flagged for explicit human approval in the plan; prefer and name the online variant per engine: `CREATE INDEX CONCURRENTLY` (Postgres), `ALGORITHM=INPLACE, LOCK=NONE` (MySQL), rolling index builds (Mongo).
- Schema changes on live tables follow EXPAND-CONTRACT, no exceptions.
- Redact literal data values from any plan/log excerpt that lands in a committed artifact.
</HARD-RULES>

## Expand-contract migration protocol

1. **Expand:** add the new column/table/index alongside the old — additive, backward-compatible, online.
2. **Dual-write + backfill:** application writes both shapes; backfill history in batches (bounded, throttled, resumable).
3. **Switch reads** to the new shape behind a verified flag; watch metrics.
4. **Contract:** only after a full bake period, remove the old shape in a later release. Contract is its own plan task, never bundled with expand.

## Index design guidance

- Composite order: equality columns first, then range, then sort — match the query, not intuition.
- Covering indexes to eliminate lookups for hot read paths; weigh write amplification (every index taxes every write).
- Partial/filtered indexes for skewed predicates (`WHERE deleted_at IS NULL`).
- Hunt UNUSED indexes too (`pg_stat_user_indexes`, `sys.schema_unused_indexes`) — dropping one is also an optimization.
- The index is not always the answer: sometimes it is the query shape, the schema, or the caller (N+1, missing cache).

## N+1 patterns per ORM family

- Detection: a loop in code + per-iteration query in the log/profile. The agent cites the code location.
- Fix families: eager loading (`include`/`select_related`/`prefetch_related`/`preload`), query batching (`IN` lists), dataloader pattern (GraphQL), denormalized read models for hot aggregates.

## Pooling guidance

- Pool size starts near `connections = cores * 2 + effective_spindles` (HikariCP heuristic) — not hundreds; queueing beats connection thrash.
- Always set: acquisition timeout, idle timeout, max lifetime. Connection churn (open/close per request) is itself a finding.

## Per-engine evidence commands

See the db-performance-tuner agent definition for the full read-only evidence list per engine (EXPLAIN variants, stat views, lock views, SLOWLOG). This skill and that agent must stay in sync — update both or neither.