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.
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-depl
…(truncated)
1---2name: performance-review-33description: '[Debugging] Use when a workflow step or the user asks for performance bottleneck analysis and validated fixes: queries, memory, network, rendering and concurrency.'4metadata:5 source: https://skillmd.com/skills/duc01226/performance-review-36---78> Codex compatibility note:9> - 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.10> - Track steps before execution and keep progress/evidence current. Explicit skill requests execute their protocol. Use the native question tool for required questions.11> - 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.12> - 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.13> - 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.14## Quick Summary1516> **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.1718**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.1920**Summary:**2122- **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.23- **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.24- **`--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).2526**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.2728**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.2930> **[IMPORTANT]** MANDATORY MUST ATTENTION stay project-generic: discover local stack, conventions, query APIs, index definitions, metrics, and report paths before judging.31>32> **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.33> **[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.34> **[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.35> **[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.36> **[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.37> **[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.3839> **[PERFORMANCE-FIRST PRINCIPLES — three non-negotiable checks on every hot path, OOM first]**40>41> 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.42> 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.43> 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.4445> **[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.4647> **Performance Knowledge (calibration constants & domain laws)** — the anchors severity depends on:48>49> - **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.50> - **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.51> - **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.52> - **Cache hit-ratio math** — 90%→99% cuts origin load **10×**; percentiles are NEVER averageable.53>54> **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.5556<!-- PROTOCOL-GUIDES:START -->5758> **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.5960- `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.md61- `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.md62- `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.md63- `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.md64- `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.md65- `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.md66- `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.md67- `scenario-stress-eval` — Judge the system under concrete failure and load scenarios; evaluating resilience or production readiness → .claude/skills/shared/protocols/scenario-stress-eval.md68- `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.md69- `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.md70- `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.md7172<!-- PROTOCOL-GUIDES:END -->7374<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:START -->7576> **[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.77> **[BLOCKING]** Before each step/sub-skill call, update todo tracking: set `in_progress` when step starts, `completed` when step ends.78> **[BLOCKING]** Every completed/skipped step MUST include brief evidence or explicit skip reason.79> **[BLOCKING]** If task tools unavailable, maintain equivalent step-by-step tracker with synchronized statuses.8081<!-- PROMPT-ENHANCE:STEP-TASK-ANCHOR:END -->8283## Contents8485- [Quick Summary](#quick-summary)86- [Detailed Summary](#detailed-summary)87- [Report-Only Mode (`--report-only`)](#report-only-mode---report-only)88- [Phase 0: Detect Scope](#phase-0-detect-scope)89- [Architecture-Altitude Performance Review](#architecture-altitude-performance-review)90- [Phase 1: Discover Local Context](#phase-1-discover-local-context)91- [Phase 2: Baseline Evidence](#phase-2-baseline-evidence)92- [Phase 3: Serial Dimension Passes](#phase-3-serial-dimension-passes)93- [Phase 4: Findings And Severity](#phase-4-findings-and-severity)94- [Phase 5: Optimize Plan](#phase-5-optimize-plan)95- [Sub-Agent Routing](#sub-agent-routing)96- [Phase 6: Why-Review Findings Validation Gate (MANDATORY when findings exist)](#phase-6-why-review-findings-validation-gate-mandatory-when-findings-exist)97- [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)98- [Output](#output)99- [Closing Reminders](#closing-reminders)100101## Detailed Summary102103Read [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.104105<target>$ARGUMENTS</target>106107## Report-Only Mode (`--report-only`)108109> **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.110>111> **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.112>113> 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.114> 2. **No nested fan-out.** Skip Sub-Agent Routing and size-capped batching; review sequentially in this context.115>116> For this mode the declared step order ends at Phase 6; stopping there is the mode's contract, not a skipped step.117118---119120## Phase 0: Detect Scope121122Classify before analysis. Detection drives dimensions, evidence, sub-agent choice.123124| Scope | Signals | Primary evidence |125| ------------------- | ----------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- |126| DB read | slow query, full scan, sort spill, high rows examined | query text/ORM expression, row count, plan/explain, indexes |127| DB write | slow save, lock waits, per-row updates, transaction bloat | write loop, batch size, lock/deadlock logs, transaction scope |128| N+1/fan-out | loop with query/API call, lazy loading, per-item lookup | caller trace, query count, loop source |129| API latency | high p95/p99, timeout, slow endpoint/job | trace/profile/logs, call chain |130| 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 |131| Memory/OOM | large materialization, blobs, no paging, buffering | allocation profile, result size, collection loads |132| Frontend | slow render, huge bundle, repeated fetch, DOM churn | browser profile, network waterfall, component/render trace |133| Distributed | message lag, cross-service waterfall, retry storm | trace spans, queue metrics, consumer/producer chain |134| Compute/CPU | hot loop, nested iteration, quadratic scaling, regex stall, heavy serialize/clone | input N, operation count vs N, profiler/flame-graph sample, microbench |135| 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 |136| 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 |137| 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 |138139Check 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.140141**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.142143---144145## Architecture-Altitude Performance Review146147> **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.148149Evaluate the **layer model** as a design concern, not a symptom site:150151```152Performance as architecture153├── Database — data access shape baked into the model (projection, paging, N+1 surface, index strategy, partition/shard key)154├── API — serialization/processing cost, batched vs per-item queries, response-DTO contracts155├── Network — payload size & call-count designed into the contract (batch endpoints vs chatty waterfalls), endpoint placement vs RTT budget156├── Frontend — bundle/lazy-load topology, change-detection/list-keying/virtual-scroll as default architecture157├── Runtime — allocation profile & collector choice, event-loop discipline, pool sizing, working-set target158└── Background jobs — bounded parallelism (local concurrency-limited primitive) + bulk write (local batch API) as the shape, not an afterthought159```160161Architecture-altitude rules (decide at design time — cheapest to fix here):162163- **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.164- **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.165- **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.166- **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.167- **Make the cost visible** — design slow-operation + query logging in from the start so regressions are observable in production.168- **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.169- **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.170- **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.171- **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.172173DB 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.174175---176177## Phase 1: Discover Local Context178179MANDATORY discovery before findings (MUST ATTENTION):180181- ALWAYS search local standards: `performance`, `index`, `query`, `pagination`, `projection`, `database`, `profiling`, `cache`, `timeout`, `retry`, `pool`, `contributing`, `style guide`.182- search 3+ similar local query/API patterns before proposing a fix.183- read target code and index/migration/schema files controlling the queried data.184- 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.185- 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.186- 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`.187- 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.188- 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.189190---191192## Phase 2: Baseline Evidence193194For 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.195196Prefer runtime proof. If unavailable, label finding `static risk` and include exact command/query needed to verify.197198MANDATORY baseline for DB findings:199200- ALWAYS capture query source: `file:line` and generated SQL/query/ORM expression when available201- ALWAYS capture volume: input size, rows matched, rows returned, rows examined/scanned, page size/limit202- ALWAYS capture access path: query plan/explain, used index, sort/group strategy, join method when available203- ALWAYS capture timing: p50/p95/p99, elapsed query time, query count, allocation or response size204- ALWAYS capture context: endpoint/job/consumer frequency and worst-case fan-out205206MANDATORY baseline for compute/CPU findings:207208- ALWAYS capture input size N and the growth assumption (expected and worst-case N)209- ALWAYS capture operation count vs N (constant / linear / quadratic+) and the nested-loop or repeated-scan source `file:line`210- ALWAYS capture timing: microbench / `console.time` / profiler or flame-graph sample at representative AND worst-case N211212MANDATORY baseline for saturation/pooling findings:213214- ALWAYS capture offered concurrency and arrival rate (RPS / worker count / threads.max)215- 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)216- ALWAYS capture pool state: size, active/idle/pending, and acquire-wait time / queue depth at the pool entrance217- ALWAYS capture aggregate demand on shared dependencies: replica count × per-instance pool → total connections/cores the shared backend must serve218219MANDATORY calibration + measurement rigor on EVERY baseline (`references/performance-knowledge.md` §1-2, §10):220221- 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.222- 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.223- 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.224- 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.225- 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.226- NEVER quote an anchor from the reference as a project requirement — local SLA/spec/config wins; the anchor calibrates, it does not govern.227228Confidence:229230| Confidence | Action |231| ---------- | ----------------------------------------------------- |232| 95%+ | Recommend fix freely. |233| 80-94% | Recommend with caveats and verification command. |234| 70-79% | List unknowns first; gather more evidence before fix. |235| <70% | STOP. Do not recommend. |236237---238239## Phase 3: Serial Dimension Passes240241MANDATORY 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.242243### 1. Query Shape And Data Minimization244245**Think:** Which rows/columns load? Are filters, projection, sorting, and limits executed by data source before materialization?246247MUST ATTENTION find:248249- unbounded list/read-all APIs without page, limit, cursor, or bounded business invariant250- filter after materialization (`ToList`/array/load-all before `Where`/filter)251- projection after materialization; full entity/document loaded for list/summary view252- unused includes/joins/lookup data; large text/blob/json fields in list queries253- client-side sort/group/distinct; offset pagination on very deep pages where cursor/keyset fits better254- missing tenant/auth/status/date filters in hot-path queries255256Prefer 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.257258### 2. Index, Access Path And Data Topology259260**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?261262> **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.263264Find:265266- no index for high-cardinality filters, joins, foreign keys, sort columns, or frequent group keys267- composite index field order mismatched with equality -> range -> sort access pattern268- **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 selectivity269- sort spill/filesort because index order does not match filter + order by270- covering/partial/filtered index opportunity for hot narrow query271- index bloat from adding every field without write-cost analysis272- **leftmost-prefix violation** — a query filtering only on the SECOND column of a composite index gets no seek from it273- **selectivity not established** — "add an index" proposed without the selectivity number; above ~5-20% selectivity a sequential scan legitimately beats random index lookups274- **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)275- **partition pruning lost** — partitioned table queried without the partition key, so every partition is scanned276- **shard/partition key skew** — monotonic (timestamp/auto-increment) or low-cardinality key creating a hot shard/partition; per-partition throughput ceilings hit by one key277- **replica read correctness-vs-lag** — read-your-writes broken by replication lag, or a lag-sensitive read pointed at a replica278- random-UUID primary key destroying index locality and inflating index size (time-ordered UUIDv7/ULID fits)279280Prefer 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).281282### 3. N+1 And Fan-Out283284**Think:** Does work scale with item count instead of request/job count?285286Find:287288- query/API/cache call inside loop, map, serializer, resolver, template/render loop, event handler loop289- per-item existence/count lookup; per-item lazy-loaded relation290- repeated same lookup with different IDs that could be one `IN`/batch/group query291- nested fan-out across services, queues, jobs, or retries292- sequential awaits where independent calls can batch or run bounded parallel with separate safe resources293294Prefer fixes: batch IDs once, join/include only needed fields, prefetch dictionaries, aggregate counts in one query, use bounded concurrency, preserve ordering/authorization semantics.295296### 4. Aggregation, Join, And Pipeline Shape297298**Think:** Does the pipeline reduce data before expensive join/unwind/group/sort/window stages?299300Find:301302- join/unwind/group before selective filter303- cartesian joins or duplicate expansion not collapsed304- grouping/sorting without pre-filter or supporting index305- aggregation loads all related rows/documents when only existence/count/min/max needed306- repeated post-processing that database can compute safely307308Prefer 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.309310### 5. Materialization And Memory311312**Think:** What enters memory? Is it bounded, streamed, and tracking-free when read-only?313314Find:315316- large collection materialized before paging/filtering317- read-only queries tracking entities/objects unnecessarily318- blob/file/large JSON fields loaded for lightweight responses319- buffering entire export/report when streaming/chunking fits320- accidental multiple enumeration re-running query321322Prefer fixes: page/chunk/stream, use no-tracking/read-only mode when local stack supports it, project lightweight DTOs, move filter before load, memoize intentionally.323324### 6. Write Path, Locks, And Transactions325326**Think:** Does write work batch safely and keep locks/transactions small?327328Find:329330- per-row save/update/delete inside loop331- long transaction wrapping remote calls or heavy reads332- unnecessary unique checks per row instead of bulk validation333- lock escalation/hot-row contention/counter updates without batching334- parallel writes sharing unsafe session/context/unit-of-work335- **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)336- **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`337- inconsistent lock acquisition ORDER across code paths (deadlock source), or no retry on the deadlock error338- 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)339- durability setting silently traded for throughput without the trade named (fsync/commit-sync relaxation)340341Prefer 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.342343### 7. Cache And Reuse344345**Think:** Is repeated expensive work stable, safe to reuse, and invalidated correctly?346347Find:348349- same lookup repeated within request/job350- hot reference data fetched every request351- cache key missing tenant/user/auth/filter/version dimensions352- cache hides unbounded query or stale security-sensitive data353- **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.354- **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 simultaneously355- **cold-start blindness** — post-depl356357…(truncated)