Performance Review

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

[PERFORMANCE-FIRST PRINCIPLES — three non-negotiable checks on every hot path, OOM first]

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

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

Performance Knowledge (calibration constants & domain laws) — the anchors severity depends on:

• Latency ladder 1 ns → 100 ns → 100 µs → 10 ms → 100 ms (L1 → RAM → SSD → disk seek → intercontinental), each rung ~100-1000×; ~1 ms RTT per 100 km of fiber is a hard floor no code fix beats.
• Measure the local SLO knee — M/M/1 illustrates mean queue wait service_time × ρ/(1−ρ): 80%→4×, 90%→9×, 95%→19× under its assumptions, not a universal CPU limit. Little's Law L = λ × W estimates average in-flight work, not a pool-size floor. Tail amplification — fan-out to 100 backends hits a p99 ~63% of the time, so a backend p99 becomes the user's median.
• Core Web Vitals LCP ≤2.5 s · INP ≤200 ms · CLS ≤0.1 · TTFB ≤800 ms, measured at p75 of real users (field), never a lab score alone.
• Cache hit-ratio math — 90%→99% cuts origin load 10×; percentiles are NEVER averageable.

MANDATORY MUST ATTENTION [BLOCKING at the severity/anchor moment] READ references/performance-knowledge.md — full ladder, universal laws, symptom→cause triage matrix, and deep tables for network/protocol, DB engine + isolation + sharding, caching, web/CWV, memory/GC, distributed resilience, measurement rigor. The read is REQUIRED — never optional — before you assign a severity or quote/compare any anchor constant; NEVER assign a severity or cite an anchor from memory or from the 4-bullet digest above. A scope-narrowed review that assigns no severity and quotes no constant may proceed on the digest alone. — why: the digest orders hypotheses but only the body carries the thresholds severity depends on, and quoting a constant without measuring THIS system is the guess-as-fact failure this skill exists to prevent.

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

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

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

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

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

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

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

Contents

• Quick Summary
• Detailed Summary
• Report-Only Mode (--report-only)
• Phase 0: Detect Scope
• Architecture-Altitude Performance Review
• Phase 1: Discover Local Context
• Phase 2: Baseline Evidence
• Phase 3: Serial Dimension Passes
• Phase 4: Findings And Severity
• Phase 5: Optimize Plan
• Sub-Agent Routing
• Phase 6: Why-Review Findings Validation Gate (MANDATORY when findings exist)
• Phase 7: Validated Fix + Full Performance Re-Review Loop (MANDATORY when validated findings remain)
• Output
• Closing Reminders

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.

<target>$ARGUMENTS</target>

Report-Only Mode (--report-only)

Use when a caller runs this skill as a read-only leaf — e.g. a workflow parallel review barrier over a plan or design, or a review batch — and another step owns every fix. --report-only in $ARGUMENTS selects it; review-only is the default; standalone --fix-loop alone runs repair/restart phases.

MANDATORY — when --report-only is passed, read .claude/skills/workflow-review-changes/references/caller-mode.md § --report-only in full FIRST. It holds the rules every read-only leaf shares (no fix or restart, scope from the caller's brief, no nested fan-out, no user questions, write only the report, return contract); the rules below are this skill's own.

1. Run Phases 0–6 only. A plan or design target uses the Architecture-Altitude lens with static risk labels. Phase 5 becomes the report's recommended optimization plan — no code, plan, or artifact edit. Phase 6 $why-review --validate-findings still validates every finding. Phase 7 does not run: return the validated report; the caller owns fixes and any re-review. — why: two writers of one artifact inside a barrier race each other.
2. No nested fan-out. Skip Sub-Agent Routing and size-capped batching; review sequentially in this context.

For this mode the declared step order ends at Phase 6; stopping there is the mode's contract, not a skipped step.

Phase 0: Detect Scope

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

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:

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 thras