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.
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]
- [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.
- 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.
- 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 — 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
[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.
Contents
Detailed Summary
Read 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.
$ARGUMENTS
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.
- 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.
- 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-deploy/failover empty cache indistinguishable from an origin outage; no warming and no LB slow-start
- unbounded cache (a memory leak with a friendly name): no size bound, no entry lifetime, no eviction policy matched to access skew — and cache thrash once the working set exceeds cache size (a cliff, not a slope)
- missing negative caching, so nonexistent keys generate repeated miss-storms
- schema/build version absent from the key, so a deploy can serve poisoned entries
Prefer fixes: request-scope memoization first, then bounded shared cache with explicit key, TTL/invalidation, size limits, privacy constraints, and hit/miss metrics. Add single-flight + TTL jitter for hot keys, stale-while-revalidate where staleness is acceptable, negative caching (or a Bloom filter) for absent keys, a version segment in the key, and an eviction policy matched to the access skew (LRU default, LFU/W-TinyLFU for skewed). NEVER treat "we added a cache" as a completed fix without the measured hit ratio and the bound.
8. API Payload, Frontend Delivery And Rendering
Think: Does the user-perceived time come from payload size, render/interaction work on the main thread, or asset delivery? Judge against the Core
…(truncated)
1---2name: performance-review-43version: 3.6.04description: '[Debugging] Use when a workflow step or the user asks for performance bottleneck analysis and validated fixes: queries, memory, network, rendering and concurrency.'5metadata:6 source: https://skillmd.com/skills/duc01226/performance-review-47---89## Quick Summary1011> **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.1213**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.1415**Summary:**1617- **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.18- **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.19- **`--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).2021**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.2223**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.2425> **[IMPORTANT]** MANDATORY MUST ATTENTION stay project-generic: discover local stack, conventions, query APIs, index definitions, metrics, and report paths before judging.26>27> **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.28> **[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.29> **[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.30> **[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.31> **[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.32> **[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.3334> **[PERFORMANCE-FIRST PRINCIPLES — three non-negotiable checks on every hot path, OOM first]**35>36> 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.37> 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.38> 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.3940> **[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.4142> **Performance Knowledge (calibration constants & domain laws)** — the anchors severity depends on:43>44> - **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.45> - **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.46> - **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.47> - **Cache hit-ratio math** — 90%→99% cuts origin load **10×**; percentiles are NEVER averageable.48>49> **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.5051<!-- PROTOCOL-GUIDES:START -->5253> **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.5455- `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.md56- `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.md57- `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.md58- `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.md59- `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.md60- `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.md61- `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.md62- `scenario-stress-eval` — Judge the system under concrete failure and load scenarios; evaluating resilience or production readiness → .claude/skills/shared/protocols/scenario-stress-eval.md63- `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.md64- `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.md65- `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.md6667<!-- PROTOCOL-GUIDES:END -->6869<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:START -->7071> **[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.72> **[BLOCKING]** Before each step/sub-skill call, update todo tracking: set `in_progress` when step starts, `completed` when step ends.73> **[BLOCKING]** Every completed/skipped step MUST include brief evidence or explicit skip reason.74> **[BLOCKING]** If task tools unavailable, maintain equivalent step-by-step tracker with synchronized statuses.7576<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:END -->7778## Contents7980- [Quick Summary](#quick-summary)81- [Detailed Summary](#detailed-summary)82- [Report-Only Mode (`--report-only`)](#report-only-mode---report-only)83- [Phase 0: Detect Scope](#phase-0-detect-scope)84- [Architecture-Altitude Performance Review](#architecture-altitude-performance-review)85- [Phase 1: Discover Local Context](#phase-1-discover-local-context)86- [Phase 2: Baseline Evidence](#phase-2-baseline-evidence)87- [Phase 3: Serial Dimension Passes](#phase-3-serial-dimension-passes)88- [Phase 4: Findings And Severity](#phase-4-findings-and-severity)89- [Phase 5: Optimize Plan](#phase-5-optimize-plan)90- [Sub-Agent Routing](#sub-agent-routing)91- [Phase 6: Why-Review Findings Validation Gate (MANDATORY when findings exist)](#phase-6-why-review-findings-validation-gate-mandatory-when-findings-exist)92- [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)93- [Output](#output)94- [Closing Reminders](#closing-reminders)9596## Detailed Summary9798Read [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.99100<target>$ARGUMENTS</target>101102## Report-Only Mode (`--report-only`)103104> **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.105>106> **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.107>108> 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.109> 2. **No nested fan-out.** Skip Sub-Agent Routing and size-capped batching; review sequentially in this context.110>111> For this mode the declared step order ends at Phase 6; stopping there is the mode's contract, not a skipped step.112113---114115## Phase 0: Detect Scope116117Classify before analysis. Detection drives dimensions, evidence, sub-agent choice.118119| Scope | Signals | Primary evidence |120| ------------------- | ----------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- |121| DB read | slow query, full scan, sort spill, high rows examined | query text/ORM expression, row count, plan/explain, indexes |122| DB write | slow save, lock waits, per-row updates, transaction bloat | write loop, batch size, lock/deadlock logs, transaction scope |123| N+1/fan-out | loop with query/API call, lazy loading, per-item lookup | caller trace, query count, loop source |124| API latency | high p95/p99, timeout, slow endpoint/job | trace/profile/logs, call chain |125| 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 |126| Memory/OOM | large materialization, blobs, no paging, buffering | allocation profile, result size, collection loads |127| Frontend | slow render, huge bundle, repeated fetch, DOM churn | browser profile, network waterfall, component/render trace |128| Distributed | message lag, cross-service waterfall, retry storm | trace spans, queue metrics, consumer/producer chain |129| Compute/CPU | hot loop, nested iteration, quadratic scaling, regex stall, heavy serialize/clone | input N, operation count vs N, profiler/flame-graph sample, microbench |130| 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 |131| 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 |132| 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 |133134Check 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.135136**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.137138---139140## Architecture-Altitude Performance Review141142> **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.143144Evaluate the **layer model** as a design concern, not a symptom site:145146```147Performance as architecture148├── Database — data access shape baked into the model (projection, paging, N+1 surface, index strategy, partition/shard key)149├── API — serialization/processing cost, batched vs per-item queries, response-DTO contracts150├── Network — payload size & call-count designed into the contract (batch endpoints vs chatty waterfalls), endpoint placement vs RTT budget151├── Frontend — bundle/lazy-load topology, change-detection/list-keying/virtual-scroll as default architecture152├── Runtime — allocation profile & collector choice, event-loop discipline, pool sizing, working-set target153└── Background jobs — bounded parallelism (local concurrency-limited primitive) + bulk write (local batch API) as the shape, not an afterthought154```155156Architecture-altitude rules (decide at design time — cheapest to fix here):157158- **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.159- **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.160- **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.161- **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.162- **Make the cost visible** — design slow-operation + query logging in from the start so regressions are observable in production.163- **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.164- **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.165- **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.166- **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.167168DB 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.169170---171172## Phase 1: Discover Local Context173174MANDATORY discovery before findings (MUST ATTENTION):175176- ALWAYS search local standards: `performance`, `index`, `query`, `pagination`, `projection`, `database`, `profiling`, `cache`, `timeout`, `retry`, `pool`, `contributing`, `style guide`.177- search 3+ similar local query/API patterns before proposing a fix.178- read target code and index/migration/schema files controlling the queried data.179- 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.180- 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.181- 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`.182- 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.183- 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.184185---186187## Phase 2: Baseline Evidence188189For 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.190191Prefer runtime proof. If unavailable, label finding `static risk` and include exact command/query needed to verify.192193MANDATORY baseline for DB findings:194195- ALWAYS capture query source: `file:line` and generated SQL/query/ORM expression when available196- ALWAYS capture volume: input size, rows matched, rows returned, rows examined/scanned, page size/limit197- ALWAYS capture access path: query plan/explain, used index, sort/group strategy, join method when available198- ALWAYS capture timing: p50/p95/p99, elapsed query time, query count, allocation or response size199- ALWAYS capture context: endpoint/job/consumer frequency and worst-case fan-out200201MANDATORY baseline for compute/CPU findings:202203- ALWAYS capture input size N and the growth assumption (expected and worst-case N)204- ALWAYS capture operation count vs N (constant / linear / quadratic+) and the nested-loop or repeated-scan source `file:line`205- ALWAYS capture timing: microbench / `console.time` / profiler or flame-graph sample at representative AND worst-case N206207MANDATORY baseline for saturation/pooling findings:208209- ALWAYS capture offered concurrency and arrival rate (RPS / worker count / threads.max)210- 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)211- ALWAYS capture pool state: size, active/idle/pending, and acquire-wait time / queue depth at the pool entrance212- ALWAYS capture aggregate demand on shared dependencies: replica count × per-instance pool → total connections/cores the shared backend must serve213214MANDATORY calibration + measurement rigor on EVERY baseline (`references/performance-knowledge.md` §1-2, §10):215216- 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.217- 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.218- 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.219- 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.220- 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.221- NEVER quote an anchor from the reference as a project requirement — local SLA/spec/config wins; the anchor calibrates, it does not govern.222223Confidence:224225| Confidence | Action |226| ---------- | ----------------------------------------------------- |227| 95%+ | Recommend fix freely. |228| 80-94% | Recommend with caveats and verification command. |229| 70-79% | List unknowns first; gather more evidence before fix. |230| <70% | STOP. Do not recommend. |231232---233234## Phase 3: Serial Dimension Passes235236MANDATORY 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.237238### 1. Query Shape And Data Minimization239240**Think:** Which rows/columns load? Are filters, projection, sorting, and limits executed by data source before materialization?241242MUST ATTENTION find:243244- unbounded list/read-all APIs without page, limit, cursor, or bounded business invariant245- filter after materialization (`ToList`/array/load-all before `Where`/filter)246- projection after materialization; full entity/document loaded for list/summary view247- unused includes/joins/lookup data; large text/blob/json fields in list queries248- client-side sort/group/distinct; offset pagination on very deep pages where cursor/keyset fits better249- missing tenant/auth/status/date filters in hot-path queries250251Prefer 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.252253### 2. Index, Access Path And Data Topology254255**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?256257> **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.258259Find:260261- no index for high-cardinality filters, joins, foreign keys, sort columns, or frequent group keys262- composite index field order mismatched with equality -> range -> sort access pattern263- **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 selectivity264- sort spill/filesort because index order does not match filter + order by265- covering/partial/filtered index opportunity for hot narrow query266- index bloat from adding every field without write-cost analysis267- **leftmost-prefix violation** — a query filtering only on the SECOND column of a composite index gets no seek from it268- **selectivity not established** — "add an index" proposed without the selectivity number; above ~5-20% selectivity a sequential scan legitimately beats random index lookups269- **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)270- **partition pruning lost** — partitioned table queried without the partition key, so every partition is scanned271- **shard/partition key skew** — monotonic (timestamp/auto-increment) or low-cardinality key creating a hot shard/partition; per-partition throughput ceilings hit by one key272- **replica read correctness-vs-lag** — read-your-writes broken by replication lag, or a lag-sensitive read pointed at a replica273- random-UUID primary key destroying index locality and inflating index size (time-ordered UUIDv7/ULID fits)274275Prefer 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).276277### 3. N+1 And Fan-Out278279**Think:** Does work scale with item count instead of request/job count?280281Find:282283- query/API/cache call inside loop, map, serializer, resolver, template/render loop, event handler loop284- per-item existence/count lookup; per-item lazy-loaded relation285- repeated same lookup with different IDs that could be one `IN`/batch/group query286- nested fan-out across services, queues, jobs, or retries287- sequential awaits where independent calls can batch or run bounded parallel with separate safe resources288289Prefer fixes: batch IDs once, join/include only needed fields, prefetch dictionaries, aggregate counts in one query, use bounded concurrency, preserve ordering/authorization semantics.290291### 4. Aggregation, Join, And Pipeline Shape292293**Think:** Does the pipeline reduce data before expensive join/unwind/group/sort/window stages?294295Find:296297- join/unwind/group before selective filter298- cartesian joins or duplicate expansion not collapsed299- grouping/sorting without pre-filter or supporting index300- aggregation loads all related rows/documents when only existence/count/min/max needed301- repeated post-processing that database can compute safely302303Prefer 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.304305### 5. Materialization And Memory306307**Think:** What enters memory? Is it bounded, streamed, and tracking-free when read-only?308309Find:310311- large collection materialized before paging/filtering312- read-only queries tracking entities/objects unnecessarily313- blob/file/large JSON fields loaded for lightweight responses314- buffering entire export/report when streaming/chunking fits315- accidental multiple enumeration re-running query316317Prefer fixes: page/chunk/stream, use no-tracking/read-only mode when local stack supports it, project lightweight DTOs, move filter before load, memoize intentionally.318319### 6. Write Path, Locks, And Transactions320321**Think:** Does write work batch safely and keep locks/transactions small?322323Find:324325- per-row save/update/delete inside loop326- long transaction wrapping remote calls or heavy reads327- unnecessary unique checks per row instead of bulk validation328- lock escalation/hot-row contention/counter updates without batching329- parallel writes sharing unsafe session/context/unit-of-work330- **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)331- **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`332- inconsistent lock acquisition ORDER across code paths (deadlock source), or no retry on the deadlock error333- 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)334- durability setting silently traded for throughput without the trade named (fsync/commit-sync relaxation)335336Prefer 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.337338### 7. Cache And Reuse339340**Think:** Is repeated expensive work stable, safe to reuse, and invalidated correctly?341342Find:343344- same lookup repeated within request/job345- hot reference data fetched every request346- cache key missing tenant/user/auth/filter/version dimensions347- cache hides unbounded query or stale security-sensitive data348- **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.349- **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 simultaneously350- **cold-start blindness** — post-deploy/failover empty cache indistinguishable from an origin outage; no warming and no LB slow-start351- unbounded cache (a memory leak with a friendly name): no size bound, no entry lifetime, no eviction policy matched to access skew — and **cache thrash** once the working set exceeds cache size (a cliff, not a slope)352- missing negative caching, so nonexistent keys generate repeated miss-storms353- schema/build version absent from the key, so a deploy can serve poisoned entries354355Prefer fixes: request-scope memoization first, then bounded shared cache with explicit key, TTL/invalidation, size limits, privacy constraints, and hit/miss metrics. Add single-flight + TTL jitter for hot keys, stale-while-revalidate where staleness is acceptable, negative caching (or a Bloom filter) for absent keys, a version segment in the key, and an eviction policy matched to the access skew (LRU default, LFU/W-TinyLFU for skewed). NEVER treat "we added a cache" as a completed fix without the measured hit ratio and the bound.356357### 8. API Payload, Frontend Delivery And Rendering358359**Think:** Does the user-perceived time come from payload size, render/interaction work on the main thread, or asset delivery? Judge against the Core360361…(truncated)