# Performance Review

> [Debugging] Use when a workflow step or the user asks for performance bottleneck analysis and validated fixes: queries, memory, network, rendering and concurrency.

- Skill: `duc01226/performance-review-3` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx get-skill add duc01226/performance-review-3`
- Raw SKILL.md: https://api.skillmd.com/api/skills/duc01226/performance-review-3/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Productivity
- Author: duc01226 (https://skillmd.com/u/duc01226)
- Updated: 2026-11-14
- Page: https://skillmd.com/skills/duc01226/performance-review-3

---


> Codex compatibility note:
> - Invoke `$skill-name`: load the registered `.agents/skills/<name>/SKILL.md` and execute it with Codex tools; no separate `Skill` tool or Claude mode switch is needed. When a workflow step or another skill names a skill, read that file in full yourself (every line, with no line limit; in parts only when output is cut off): a skill whose implicit invocation is off is never loaded for you. Author `.claude/**`; mirrors are generated.
> - Track steps before execution and keep progress/evidence current. Explicit skill requests execute their protocol. Use the native question tool for required questions.
> - Map foreign tool names to available host capabilities. A name mismatch needs no approval; an actually unavailable required capability stops with its step/evidence and a user question before adaptation.
> - Subagent authorization: when a skill is user-invoked or AI-detected and its protocol requires subagents, that skill activation authorizes use of the required `spawn_agent` subagent(s) for that task.
> - Preserve protocol order unless the user approves a deviation. Workflows use `$start-workflow`: gates stay fixed; other steps may flex with logged reasons and evidence.
## Quick Summary

> **Review modes:** For review/audit work, standalone defaults to `--review-only` (`--report-only` alias); `--fix-loop` enables review → validate → authorized fix → fresh re-review, default cap 3. `--fix-loop --loop-owner=caller` returns a read-only pass to the caller that owns fixes/re-review. Create triage, review, validation and re-review todo tasks first; apply the carried `review-policy` contract for LOW deferral and user-approved bounded extensions. Non-review modes and terminal findings validation keep their own dispatch.

**Goal:** Ensure every shipped performance fix removes a measured or static-risk-labeled bottleneck across data access, compute, runtime/GC, network, client delivery, and concurrency/resilience; calibrate every number against a known anchor, preserve behavior/authorization/semantics, prove before/after evidence, pass `$why-review` before any fix, and complete a clean full Phase-0 re-review — never hide waste or break correctness.

**Summary:**

- **Main path:** detect scope → discover local patterns → measure or label static risk → review all applicable performance dimensions → calibrate severity → plan → validate findings → fix at the owning layer → prove before/after → run a fresh full re-review.
- **Gates:** count rows before row size; bound memory, queues and concurrency; preserve authorization and semantics. Round 1 clears all validated findings, with a LOW-only fix set reviewed after the fix (LOW deferral). Round 2 clears CRITICAL/HIGH/MEDIUM and defers LOW; failed binary gates always block.
- **`--report-only`:** read-only leaf mode for a caller that owns every fix — Phases 0–6 only, no nested sub-agents, no writer beyond the report; see [Report-Only Mode](#report-only-mode---report-only).

**Workflow:** Detect scope → discover local patterns → measure or label static risk → run 12 serial dimension passes → findings and severity → plan → validate findings → fix → run a full re-review.

**Key Rules:** Evidence beats intuition; calibrate numbers against the local performance reference; bound rows, memory, queues, and concurrency; Round 1 fixes every open validated severity (LOW deferral), while Round 2 fixes only CRITICAL/HIGH/MEDIUM and defers LOW-only findings; failed binary gates always block.

> **[IMPORTANT]** MANDATORY MUST ATTENTION stay project-generic: discover local stack, conventions, query APIs, index definitions, metrics, and report paths before judging.
>
> **AI surface?** Only if the change creates or changes a model call, prompt, agent, tool/MCP, retrieval or eval (see `node .claude/scripts/ai-signal-scan.cjs`): apply `AE-4`, `AE-5` (context budget, caching layout, timeouts, cost caps; keep the `AE-4.4` gate) from `.claude/skills/shared/protocols/ai-engineering-gate.md` and route depth to `$ai-engineering-review`; otherwise skip this line.
> **[IMPORTANT]** MANDATORY MUST ATTENTION prove every performance claim with measurement or static evidence: `file:line`, query text/shape, row counts, query plan/explain output, trace, profile, or logs.
> **[IMPORTANT]** MANDATORY MUST ATTENTION review performance one dimension at a time — ALL **12**: (1) query shape/over-fetching, (2) index/access path/data topology, (3) N+1 fan-out, (4) aggregation/join shape, (5) materialization/memory, (6) write path/locks/transactions, (7) caching, (8) API payload/frontend delivery/Core Web Vitals, (9) in-process compute/algorithmic complexity, (10) network/protocol round trips, (11) runtime/memory/GC pauses, (12) distributed resilience/load management (timeouts, retries, queue bounds). NEVER stop at 9 — 10-12 are the layers a code-only reading habitually never opens.
> **[IMPORTANT]** MANDATORY MUST ATTENTION include in-process compute, not just I/O: flag O(n²)+ nested scans, linear membership lookups inside loops, ReDoS-prone regex, and per-iteration serialize/clone — CPU bottlenecks need the same evidence rigor as queries.
> **[IMPORTANT]** MANDATORY MUST ATTENTION when an operation is fast but p95/p99 is high, investigate saturation: measure pool/thread acquire-wait and queue depth. Little's Law estimates average demand at the measured pool boundary; validate burst/wait behavior and the sum of per-instance connection budgets against shared-backend limits.
> **[IMPORTANT]** MANDATORY MUST ATTENTION calibrate every number against a known anchor before assigning severity — latency ladder, utilization knee, Core Web Vitals thresholds, hit-ratio math (`references/performance-knowledge.md`); a breached anchor is a HYPOTHESIS to verify with local evidence, NEVER a finding on its own.

> **[PERFORMANCE-FIRST PRINCIPLES — three non-negotiable checks on every hot path, OOM first]**
>
> 1. **[MOST IMPORTANT] Hunt every OOM / out-of-memory bad practice.** Unbounded read-all / `SELECT *` / no page bound, full materialization before paging/filtering, buffering a whole export/report instead of streaming/chunking, loading blobs / large JSON / tracked entities for list views, accidental multiple enumeration, unbounded caches / accumulators / queues / in-memory joins. Triage row **COUNT before row SIZE**, reduce rows **AT THE SOURCE** — a fast query pulling millions of rows still OOMs the process. Bound EVERY result set with a page/limit/cursor or proven business invariant.
> 2. **Right data structure & algorithm for the stack.** Match the structure to the access pattern via the runtime's efficient primitive — O(1) `Set`/`Map`/dict/hash lookup instead of a linear `find`/`includes`/`contains`/`in list` scan inside a loop; no O(n²) where O(n log n) / O(n) / O(1) exists; single-pass min/max/partition instead of redundant re-sort. Prove the complexity class at worst-case N, never by intuition.
> 3. **Batch once, or parallelize — never serial fan-out.** Collapse per-item query / API / cache calls into ONE batched call (`IN` / bulk / aggregate / prefetch dictionary); where independent calls remain, run bounded-parallel with a fresh safe resource per worker instead of sequential awaits — always preserving ordering, authorization, idempotency.

> **[BLOCKING] Query Scan Gate — no search or query scans rows on large data.** Every query, search or filter the change adds or alters on data that can grow large (no proven small, invariant-bounded size) reaches its rows through an index-backed, sargable access path and returns a bounded result (page, limit or cursor). A full table or collection scan, a leading-wildcard `LIKE`, unanchored regex or `contains` search, a non-sargable predicate (dimension 2), or an unindexed filter, join, sort or group key on such data is at least a HIGH finding and blocks the verdict; free-text search needs a full-text or search index, not a scan. Evidence: the serving index (`file:line` in the schema or migration) and, when available, plan/explain output at realistic volume. A scan stays only when the data is provably bounded (state the bound) or the plan is shown cheap at production volume. This gate sets the severity floor for these patterns; the scale-evidence rule under severity governs every other finding.

> **Performance Knowledge (calibration constants & domain laws)** — the anchors severity depends on:
>
> - **Latency ladder** `1 ns → 100 ns → 100 µs → 10 ms → 100 ms` (L1 → RAM → SSD → disk seek → intercontinental), each rung ~100-1000×; **~1 ms RTT per 100 km of fiber is a hard floor** no code fix beats.
> - **Measure the local SLO knee** — M/M/1 illustrates mean queue wait `service_time × ρ/(1−ρ)`: 80%→4×, 90%→9×, 95%→19× under its assumptions, not a universal CPU limit. **Little's Law** `L = λ × W` estimates average in-flight work, not a pool-size floor. **Tail amplification** — fan-out to 100 backends hits a p99 ~63% of the time, so a backend p99 becomes the user's median.
> - **Core Web Vitals** LCP ≤2.5 s · INP ≤200 ms · CLS ≤0.1 · TTFB ≤800 ms, measured at **p75 of real users** (field), never a lab score alone.
> - **Cache hit-ratio math** — 90%→99% cuts origin load **10×**; percentiles are NEVER averageable.
>
> **MANDATORY MUST ATTENTION [BLOCKING at the severity/anchor moment]** READ `references/performance-knowledge.md` — full ladder, universal laws, symptom→cause triage matrix, and deep tables for network/protocol, DB engine + isolation + sharding, caching, web/CWV, memory/GC, distributed resilience, measurement rigor. The read is REQUIRED — never optional — before you **assign a severity** or **quote/compare any anchor constant**; NEVER assign a severity or cite an anchor from memory or from the 4-bullet digest above. A scope-narrowed review that assigns no severity and quotes no constant may proceed on the digest alone. — why: the digest orders hypotheses but only the body carries the thresholds severity depends on, and quoting a constant without measuring THIS system is the guess-as-fact failure this skill exists to prevent.

<!-- PROTOCOL-GUIDES:START -->

> **Protocol guides** — Apply each protocol only when its read-when condition holds. If a protocol's text is not in your context, read its file below before you act on it.

- `category-review-thinking` — Derive review concerns per category of changed files from domain knowledge; reviewing a changeset that spans several file categories → .claude/skills/shared/protocols/category-review-thinking.md
- `goal-contract-satisfaction-loop` — Save the goal in a file and loop until every saved criterion passes; executing work against a user goal → .claude/skills/shared/protocols/goal-contract-satisfaction-loop.md
- `graph-assisted-investigation` — Optional hint: a code-graph query can add callers and dependents when grep may miss a high-risk blast radius, and it can be stale; a high-risk change where grep and reading alone may miss the blast radius → .claude/skills/shared/protocols/graph-assisted-investigation.md
- `measured-capacity-engineering` — Model demand, reduce measured work safely and prove capacity before scaling; planning, building, testing or reviewing hot paths, caches or capacity → .claude/skills/shared/protocols/measured-capacity-engineering.md
- `review-decision-autonomy` — Choose supported review decisions and ask before extending the round budget; running any review or audit skill or mode → .claude/skills/shared/protocols/review-decision-autonomy.md
- `review-policy` — Review-only or a three-round fix loop with explicit extension and fresh post-fix evidence; deciding round eligibility, blocking findings or review state → .claude/skills/shared/protocols/review-policy.md
- `review-principle-awareness` — Classify the change context first, then apply the current principles that fit it; starting any review → .claude/skills/shared/protocols/review-principle-awareness.md
- `scenario-stress-eval` — Judge the system under concrete failure and load scenarios; evaluating resilience or production readiness → .claude/skills/shared/protocols/scenario-stress-eval.md
- `severity-rubric` — One consequence-based Critical, High, Medium, Low scale for every finding and gate; classifying a finding or deciding whether a review round passes → .claude/skills/shared/protocols/severity-rubric.md
- `systematic-review-batching` — Triage all files and plan adaptive review with complete coverage and no fixed size caps; choosing review assignments or handling working-set overflow → .claude/skills/shared/protocols/systematic-review-batching.md
- `trade-off-interrogation-gate` — Three trade-off questions before any verdict, score or recommendation; rendering a verdict or recommending an option → .claude/skills/shared/protocols/trade-off-interrogation-gate.md

<!-- PROTOCOL-GUIDES:END -->

<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:START -->

> **[BLOCKING]** Execute skill steps in declared order. NEVER skip, reorder, or merge steps without explicit user approval. Inside a workflow run, `$start-workflow` → Workflow-step mode bounds this rule.
> **[BLOCKING]** Before each step/sub-skill call, update todo tracking: set `in_progress` when step starts, `completed` when step ends.
> **[BLOCKING]** Every completed/skipped step MUST include brief evidence or explicit skip reason.
> **[BLOCKING]** If task tools unavailable, maintain equivalent step-by-step tracker with synchronized statuses.

<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:END -->

## Contents

- [Quick Summary](#quick-summary)
- [Detailed Summary](#detailed-summary)
- [Report-Only Mode (`--report-only`)](#report-only-mode---report-only)
- [Phase 0: Detect Scope](#phase-0-detect-scope)
- [Architecture-Altitude Performance Review](#architecture-altitude-performance-review)
- [Phase 1: Discover Local Context](#phase-1-discover-local-context)
- [Phase 2: Baseline Evidence](#phase-2-baseline-evidence)
- [Phase 3: Serial Dimension Passes](#phase-3-serial-dimension-passes)
- [Phase 4: Findings And Severity](#phase-4-findings-and-severity)
- [Phase 5: Optimize Plan](#phase-5-optimize-plan)
- [Sub-Agent Routing](#sub-agent-routing)
- [Phase 6: Why-Review Findings Validation Gate (MANDATORY when findings exist)](#phase-6-why-review-findings-validation-gate-mandatory-when-findings-exist)
- [Phase 7: Validated Fix + Full Performance Re-Review Loop (MANDATORY when validated findings remain)](#phase-7-validated-fix--full-performance-re-review-loop-mandatory-when-validated-findings-remain)
- [Output](#output)
- [Closing Reminders](#closing-reminders)

## Detailed Summary

Read [Architecture-Altitude Performance Review](#architecture-altitude-performance-review) when judging a design; apply the same evidence gate before recommendations. Before recommending caching, understand query shape, indexes, pagination, batching and data volume; completion requires measured hit ratio and a bound.

<target>$ARGUMENTS</target>

## Report-Only Mode (`--report-only`)

> **Use when** a caller runs this skill as a read-only leaf — e.g. a workflow parallel review barrier over a plan or design, or a review batch — and another step owns every fix. `--report-only` in `$ARGUMENTS` selects it; review-only is the default; standalone `--fix-loop` alone runs repair/restart phases.
>
> **MANDATORY — when `--report-only` is passed, read `.claude/skills/workflow-review-changes/references/caller-mode.md` § `--report-only` in full FIRST.** It holds the rules every read-only leaf shares (no fix or restart, scope from the caller's brief, no nested fan-out, no user questions, write only the report, return contract); the rules below are this skill's own.
>
> 1. **Run Phases 0–6 only.** A plan or design target uses the Architecture-Altitude lens with `static risk` labels. Phase 5 becomes the report's recommended optimization plan — no code, plan, or artifact edit. Phase 6 `$why-review --validate-findings` still validates every finding. **Phase 7 does not run:** return the validated report; the caller owns fixes and any re-review. — why: two writers of one artifact inside a barrier race each other.
> 2. **No nested fan-out.** Skip Sub-Agent Routing and size-capped batching; review sequentially in this context.
>
> For this mode the declared step order ends at Phase 6; stopping there is the mode's contract, not a skipped step.

---

## Phase 0: Detect Scope

Classify before analysis. Detection drives dimensions, evidence, sub-agent choice.

| Scope               | Signals                                                                                         | Primary evidence                                                                                |
| ------------------- | ----------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- |
| DB read             | slow query, full scan, sort spill, high rows examined                                           | query text/ORM expression, row count, plan/explain, indexes                                     |
| DB write            | slow save, lock waits, per-row updates, transaction bloat                                       | write loop, batch size, lock/deadlock logs, transaction scope                                   |
| N+1/fan-out         | loop with query/API call, lazy loading, per-item lookup                                         | caller trace, query count, loop source                                                          |
| API latency         | high p95/p99, timeout, slow endpoint/job                                                        | trace/profile/logs, call chain                                                                  |
| Saturation/Queueing | high p99 while the operation itself is fast, pool exhausted/timeout, threads blocked on acquire | pool active/idle/pending, acquire-wait time, threads/workers vs pool size, replica count × pool |
| Memory/OOM          | large materialization, blobs, no paging, buffering                                              | allocation profile, result size, collection loads                                               |
| Frontend            | slow render, huge bundle, repeated fetch, DOM churn                                             | browser profile, network waterfall, component/render trace                                      |
| Distributed         | message lag, cross-service waterfall, retry storm                                               | trace spans, queue metrics, consumer/producer chain                                             |
| Compute/CPU         | hot loop, nested iteration, quadratic scaling, regex stall, heavy serialize/clone               | input N, operation count vs N, profiler/flame-graph sample, microbench                          |
| Network/protocol    | chatty call count, per-request handshake, no keep-alive, large payload, cross-region hop        | call count × RTT, connection reuse state, TLS/DNS timing, payload size, HTTP version            |
| Runtime/GC          | latency spikes uncorrelated with load, pauses, RSS growth, blocked event loop                    | GC log/pause histogram, allocation rate, RSS vs heap, thread states, event-loop lag             |
| Resilience/load     | retry storm, no timeout, unbounded queue, cold-start blip, one tenant degrades all               | timeout/retry config, queue depth AND age, breaker state, per-tenant rate limits                |

Check applicability for all 12 dimensions; skip a dimension when project configuration and inspected paths prove its surface absent or outside the selected scope. Record the evidence; do not invent dependencies or setup to fill an inapplicable dimension.

**Triage accelerator (symptom → usual cause).** MUST ATTENTION use the symptom→cause matrix in `references/performance-knowledge.md` §3 to pick the FIRST evidence to pull — it maps signatures AI habitually misreads, e.g. `p99 bad + p50 fine` → GC pause / lock contention / fan-out tail / cold cache (NOT a slow query); `latency scales with result size` → N+1; `sudden cliff at some load` → utilization knee or pool exhaustion; `degrades over days, fine after restart` → leak/bloat/connection leak; `slow for one tenant only` → hot key/partition skew. NEVER let the matrix replace evidence — it orders the hypotheses, Phase 2 proves one.

---

## Architecture-Altitude Performance Review

> **When to apply:** design/architecture reviews (e.g. `architect` agent) — judge performance as a **structural property of the design** BEFORE it ships, not a tactical query fix after a bottleneck appears. Dimension passes stay the tactical tool; this section is the design-level lens.

Evaluate the **layer model** as a design concern, not a symptom site:

```
Performance as architecture
├── Database  — data access shape baked into the model (projection, paging, N+1 surface, index strategy, partition/shard key)
├── API       — serialization/processing cost, batched vs per-item queries, response-DTO contracts
├── Network   — payload size & call-count designed into the contract (batch endpoints vs chatty waterfalls), endpoint placement vs RTT budget
├── Frontend  — bundle/lazy-load topology, change-detection/list-keying/virtual-scroll as default architecture
├── Runtime   — allocation profile & collector choice, event-loop discipline, pool sizing, working-set target
└── Background jobs — bounded parallelism (local concurrency-limited primitive) + bulk write (local batch API) as the shape, not an afterthought
```

Architecture-altitude rules (decide at design time — cheapest to fix here):

- **Bound every result set and project only needed columns/fields in the contract itself** — never design an unbounded read-all or `SELECT *` endpoint; unbounded reads spike memory/latency under real data volume.
- **Design out N+1 at the boundary** — eager-load / batch-fetch is the default access pattern; per-item lookups are a design smell, not a tuning detail.
- **Caching is a design decision, not a patch** — choose request-scope memoization vs bounded shared cache up front, with key dimensions (tenant/user/auth/version), TTL/invalidation, size limits, privacy constraints specified; never cache to hide an unbounded query.
- **Async I/O is structural** — never design a path blocking threads with `.Result`; bounded parallelism for fan-out is part of the design, with a fresh safe scope/context per worker.
- **Make the cost visible** — design slow-operation + query logging in from the start so regressions are observable in production.
- **Size pools and parallelism, never default them** — estimate average in-use demand using Little's Law (arrival-rate × resource hold-time at the same boundary), then validate burst/wait behavior and dependency limits. Shorten unnecessary _hold-time_ before growing the pool; validate fleet-wide connection budgets (sum of per-instance pools) against shared-backend capacity — local tuning can overload a shared dependency.
- **Budget the round trips and the geography in the contract** — count `call count × RTT` for every designed interaction and place the endpoint (edge/region/replica) against the latency budget; ~1 ms RTT per 100 km and a 2-RTT TCP+TLS handshake are floors no later optimization removes, so a chatty contract or a distant endpoint is a permanent design cost, not a tuning detail.
- **Design the load-management controls in, not on** — a decreasing timeout budget per hop, backoff + full jitter + retry budget + idempotency keys, breaker/bulkhead/shedding, and a BOUND on every queue belong in the design; leave them out and the system amplifies its own partial failures. Reserve headroom below the measured SLO boundary and validate scaling indicators against the limiting resource and warmup delay; queue depth/concurrency can lead aggregate CPU.
- **Choose the runtime cost profile deliberately** — allocation rate and collector choice set the tail (GC pauses are correlated fleet-wide and invisible in the mean); an event-loop runtime must keep CPU work off the loop by design; state the working-set target so the RAM/page-cache cliff is a known bound, not a surprise.

DB index strategy at design time → dimension 2 below (composite key order, covering/partial indexes, write-cost analysis). The tactical evidence gate (measure baseline, prove with plan/explain) still applies to every recommendation at this altitude.

---

## Phase 1: Discover Local Context

MANDATORY discovery before findings (MUST ATTENTION):

- ALWAYS search local standards: `performance`, `index`, `query`, `pagination`, `projection`, `database`, `profiling`, `cache`, `timeout`, `retry`, `pool`, `contributing`, `style guide`.
- search 3+ similar local query/API patterns before proposing a fix.
- read target code and index/migration/schema files controlling the queried data.
- map callers and frequency using available graph/call-trace/profiler tools; if none exist, use grep/import/call hierarchy. Optional: when the hot-path fan-out is hard to see by grep and `.code-graph/graph.db` exists, a graph blast-radius pass (`trace --direction downstream` on the hot path) can hint at the fan-out (it may be stale — verify by reading) — see the Graph-Assisted Investigation advice below.
- identify data shape: tenant/security-review filters, cardinality, expected max rows, selected columns/fields, sort, joins, aggregation/grouping, cache keys, partition/shard key, primary vs replica routing.
- ALWAYS discover the local **SLA/budget** (latency target, page-size cap, throughput/SLO) before judging any number — the local budget outranks every anchor in `references/performance-knowledge.md`.
- Read the local configuration used by the inspected path: runtime/memory settings and, where present, HTTP/connection pools, timeout/retry/breaker policy, queue/consumer bounds, rate limits, container limits, CDN/assets or field metrics. Record absent surfaces as N/A; a CPU-only utility need not acquire service or database configuration.
- NEVER hardcode project names, repository paths, ID formats, DB engines, ORMs, runtime/GC flags, HTTP clients, or framework defaults; derive every one from discovered files.

---

## Phase 2: Baseline Evidence

For capacity/load or cache-placement work, apply `SYNC:measured-capacity-engineering`: disclose workload and offered/achieved demand, attribute resource pressure, preserve cache correctness, and verify cold-state/overload recovery. Read `references/performance-knowledge.md` §10.1 and §6.1 when designing those experiments.

Prefer runtime proof. If unavailable, label finding `static risk` and include exact command/query needed to verify.

MANDATORY baseline for DB findings:

- ALWAYS capture query source: `file:line` and generated SQL/query/ORM expression when available
- ALWAYS capture volume: input size, rows matched, rows returned, rows examined/scanned, page size/limit
- ALWAYS capture access path: query plan/explain, used index, sort/group strategy, join method when available
- ALWAYS capture timing: p50/p95/p99, elapsed query time, query count, allocation or response size
- ALWAYS capture context: endpoint/job/consumer frequency and worst-case fan-out

MANDATORY baseline for compute/CPU findings:

- ALWAYS capture input size N and the growth assumption (expected and worst-case N)
- ALWAYS capture operation count vs N (constant / linear / quadratic+) and the nested-loop or repeated-scan source `file:line`
- ALWAYS capture timing: microbench / `console.time` / profiler or flame-graph sample at representative AND worst-case N

MANDATORY baseline for saturation/pooling findings:

- ALWAYS capture offered concurrency and arrival rate (RPS / worker count / threads.max)
- ALWAYS capture resource hold-time vs total request time (a connection/lock/permit is held only for the fraction it is actually used, not the whole request)
- ALWAYS capture pool state: size, active/idle/pending, and acquire-wait time / queue depth at the pool entrance
- ALWAYS capture aggregate demand on shared dependencies: replica count × per-instance pool → total connections/cores the shared backend must serve

MANDATORY calibration + measurement rigor on EVERY baseline (`references/performance-knowledge.md` §1-2, §10):

- ALWAYS state which anchor the number violates (ladder rung, utilization knee, CWV threshold, hit-ratio target) — a raw number with no anchor cannot carry a severity.
- Report sample count, repetitions and meaningful distributions; do not hide tails behind a mean. Segment by the workload dimensions that exist (such as endpoint/tenant/region in a service). NEVER average percentiles across instances or windows — aggregate histograms instead; do not invent segments for a CPU-only benchmark.
- For concurrent request/load measurements, disclose the load model: open arrival-rate versus closed fixed-user demand, offered/achieved work and possible coordinated omission. For a compute microbenchmark, disclose input shape, iteration/repetition and timing method instead; do not present it as system capacity.
- State input/data volume and relevant cache state. For growth comparisons use representative and worst-case sizes with realistic shape/skew; a roughly 10× comparison can expose super-linear work. Persistent-data tests reuse the project’s supported seed/fixture owner; compute-only tests generate valid inputs without a database/seeder. Capacity/leak claims require the applicable cold-state or soak evidence; toy or warm-only data cannot establish those claims.
- ALWAYS warm up (JIT + caches), measure steady state, repeat, and name the environment before comparing to a baseline; NEVER present a microbenchmark as system behavior.
- NEVER quote an anchor from the reference as a project requirement — local SLA/spec/config wins; the anchor calibrates, it does not govern.

Confidence:

| Confidence | Action                                                |
| ---------- | ----------------------------------------------------- |
| 95%+       | Recommend fix freely.                                 |
| 80-94%     | Recommend with caveats and verification command.      |
| 70-79%     | List unknowns first; gather more evidence before fix. |
| <70%       | STOP. Do not recommend.                               |

---

## Phase 3: Serial Dimension Passes

MANDATORY apply one focused pass per dimension. NEVER scan all dimensions at once. **12 dimensions** — 1-9 are the in-process/data-access core, 10-12 cover the layers a code-only reading habitually skips (network round trips, runtime/GC, resilience under load). `references/performance-knowledge.md` carries deep tables for network/protocol (§4), database (§5), caching (§6), web/CWV (§7), memory/GC (§8), and distributed resilience (§9); the remaining dimensions calibrate against the ladder, universal laws, and triage matrix (§1-3) instead of a dedicated table.

### 1. Query Shape And Data Minimization

**Think:** Which rows/columns load? Are filters, projection, sorting, and limits executed by data source before materialization?

MUST ATTENTION find:

- unbounded list/read-all APIs without page, limit, cursor, or bounded business invariant
- filter after materialization (`ToList`/array/load-all before `Where`/filter)
- projection after materialization; full entity/document loaded for list/summary view
- unused includes/joins/lookup data; large text/blob/json fields in list queries
- client-side sort/group/distinct; offset pagination on very deep pages where cursor/keyset fits better
- missing tenant/auth/status/date filters in hot-path queries

Prefer fixes: push predicates to data source, select only needed fields, bound result set, use cursor/keyset for deep sequential access, keep reusable predicates near domain/query-owner layer discovered locally.

### 2. Index, Access Path And Data Topology

**Think:** Can existing indexes satisfy equality/range filters, joins, sort, grouping, and projection in the actual query order? **Sargability first:** for EVERY filter/join predicate, is the indexed COLUMN left bare, or is it wrapped in a function/transformation that the DB must compute per row (killing the index)? Then: does the query reach the data through the right partition/shard/replica?

> **MUST ATTENTION — Non-sargable predicate spot-check (any ORM/SQL).** A function, cast, concatenation, date extraction, or leading-wildcard predicate on a column can prevent an ordinary index seek; expression indexes and optimizer support depend on the actual database. Inspect the translated query and actual collation/normalization rules before proposing a rewrite. For case-insensitive comparisons, use a database-supported collation/operator or a normalized indexed column/expression index only after proving equivalent results. Moving normalization to the parameter alone or listing a few casing variants does not preserve arbitrary mixed-case or Unicode matches. Preserve tenant/auth filters, null behavior, locale, Unicode normalization and result sets with representative fixtures before measuring performance. Verify improvement separately with `EXPLAIN`/the query plan and representative load; a sequential scan can be optimal for low selectivity and is not by itself proof of a defect.

Find:

- no index for high-cardinality filters, joins, foreign keys, sort columns, or frequent group keys
- composite index field order mismatched with equality -> range -> sort access pattern
- **non-sargable predicate: an indexed column wrapped in a function/cast/concat/date-part/transformation** (see spot-check above) — the single most common silent index-loss; also incompatible type/collation, leading wildcard, broad `OR`, negative predicate, or low selectivity
- sort spill/filesort because index order does not match filter + order by
- covering/partial/filtered index opportunity for hot narrow query
- index bloat from adding every field without write-cost analysis
- **leftmost-prefix violation** — a query filtering only on the SECOND column of a composite index gets no seek from it
- **selectivity not established** — "add an index" proposed without the selectivity number; above ~5-20% selectivity a sequential scan legitimately beats random index lookups
- **stale statistics** — plan/explain shows estimated rows far from actual rows; the plan is wrong for a reason no rewrite fixes (refresh stats/analyze first)
- **partition pruning lost** — partitioned table queried without the partition key, so every partition is scanned
- **shard/partition key skew** — monotonic (timestamp/auto-increment) or low-cardinality key creating a hot shard/partition; per-partition throughput ceilings hit by one key
- **replica read correctness-vs-lag** — read-your-writes broken by replication lag, or a lag-sensitive read pointed at a replica
- random-UUID primary key destroying index locality and inflating index size (time-ordered UUIDv7/ULID fits)

Prefer fixes: add/adjust smallest useful index, reorder composite keys to match query, rewrite predicate to be sargable, refresh statistics, carry the partition/shard key into the predicate, salt or re-key a hot partition, route lag-sensitive reads to primary (or a sticky/LSN-aware window), verify with plan/explain before/after, include write-cost risk. **Escalate in order — tune query/index → cache → vertical → read replicas → partition → shard**; NEVER propose sharding before the earlier rungs are proven exhausted (why: resharding and cross-shard joins are the most expensive reversal in the ladder).

### 3. N+1 And Fan-Out

**Think:** Does work scale with item count instead of request/job count?

Find:

- query/API/cache call inside loop, map, serializer, resolver, template/render loop, event handler loop
- per-item existence/count lookup; per-item lazy-loaded relation
- repeated same lookup with different IDs that could be one `IN`/batch/group query
- nested fan-out across services, queues, jobs, or retries
- sequential awaits where independent calls can batch or run bounded parallel with separate safe resources

Prefer fixes: batch IDs once, join/include only needed fields, prefetch dictionaries, aggregate counts in one query, use bounded concurrency, preserve ordering/authorization semantics.

### 4. Aggregation, Join, And Pipeline Shape

**Think:** Does the pipeline reduce data before expensive join/unwind/group/sort/window stages?

Find:

- join/unwind/group before selective filter
- cartesian joins or duplicate expansion not collapsed
- grouping/sorting without pre-filter or supporting index
- aggregation loads all related rows/documents when only existence/count/min/max needed
- repeated post-processing that database can compute safely

Prefer fixes: filter early, project early, aggregate at source, reduce join cardinality, use existence/count queries, repeat necessary post-expansion filters when array/child semantics require it.

### 5. Materialization And Memory

**Think:** What enters memory? Is it bounded, streamed, and tracking-free when read-only?

Find:

- large collection materialized before paging/filtering
- read-only queries tracking entities/objects unnecessarily
- blob/file/large JSON fields loaded for lightweight responses
- buffering entire export/report when streaming/chunking fits
- accidental multiple enumeration re-running query

Prefer fixes: page/chunk/stream, use no-tracking/read-only mode when local stack supports it, project lightweight DTOs, move filter before load, memoize intentionally.

### 6. Write Path, Locks, And Transactions

**Think:** Does write work batch safely and keep locks/transactions small?

Find:

- per-row save/update/delete inside loop
- long transaction wrapping remote calls or heavy reads
- unnecessary unique checks per row instead of bulk validation
- lock escalation/hot-row contention/counter updates without batching
- parallel writes sharing unsafe session/context/unit-of-work
- **long-running or idle-in-transaction connection** — under MVCC it pins old row versions and drives bloat/vacuum pressure fleet-wide (a slow-motion outage, not a local slowdown)
- **isolation level mismatched to the invariant** — lost update at Read Committed, or write skew at Snapshot/Repeatable Read where Serializable (or an explicit lock/version column) is required; read-modify-write done in application code instead of one atomic `UPDATE`
- inconsistent lock acquisition ORDER across code paths (deadlock source), or no retry on the deadlock error
- schema/migration change taking a blocking lock proportional to table size instead of an online pattern (nullable add → batched backfill → `NOT VALID` constraint → validate; concurrent index build; expand/contract)
- durability setting silently traded for throughput without the trade named (fsync/commit-sync relaxation)

Prefer fixes: bulk write, chunk, shorten transaction, move remote calls outside transaction, use idempotent commands, create fresh safe scope/context per parallel worker, pick the isolation level the invariant needs (or an explicit `FOR UPDATE`/version column), make write conflicts atomic in one statement, order lock acquisition consistently and retry deadlocks, use the online migration pattern for large tables.

### 7. Cache And Reuse

**Think:** Is repeated expensive work stable, safe to reuse, and invalidated correctly?

Find:

- same lookup repeated within request/job
- hot reference data fetched every request
- cache key missing tenant/user/auth/filter/version dimensions
- cache hides unbounded query or stale security-sensitive data
- **no cache-value evidence** — measure hit rate and work avoided: at fixed demand, 90%→99% reduces miss traffic 10×. Value also depends on miss cost, key cardinality, memory and consistency cost.
- **stampede/thundering-herd exposure** — hot key expiring sends every request to origin at once; no single-flight/request-coalescing, no per-key lease, no TTL jitter, or a whole key class expiring simultaneously
- **cold-start blindness** — post-depl

…(truncated)
