Performance Review

Codex compatibility note:

• Invoke repository skills with $skill-name in Codex; this mirrored copy rewrites legacy Claude /skill-name references.
• Task tracker mandate: BEFORE executing any workflow or skill step, create/update task tracking for all steps and keep it synchronized as progress changes.
• User-question prompts mean to ask the user directly in Codex.
• Ignore Claude-specific mode-switch instructions when they appear.
• Strict execution contract: when a user explicitly invokes a skill, execute that skill protocol as written.
• 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.
• Do not skip, reorder, or merge protocol steps unless the user explicitly approves the deviation first.
• For workflow skills, execute each listed child-skill step explicitly and report step-by-step evidence.
• If a required step/tool cannot run in this environment, stop and ask the user before adapting.

Codex Project-Reference Loading (No Hooks)

Codex uses static project-reference loading instead of runtime-injected project docs. When coding, planning, debugging, testing, or reviewing, open project docs explicitly using this routing.

Always read:

• docs/project-config.json (project-specific paths, commands, modules, and workflow/test settings)
• docs/project-reference/docs-index-reference.md (routes to the full docs/project-reference/* catalog)
• docs/project-reference/lessons.md (always-on guardrails and anti-patterns)

Missing/stale context route: If docs/project-config.json, the docs index, lessons.md, CLAUDE.md, AGENTS.md, or any task-required reference doc is missing or stale, auto-run $project-init or the narrow setup route ($project-config, $docs-init, $scan-all, $scan --target=<key>, $claude-md-init) before ordinary project-specific work. If Codex mirrors or AGENTS.md are missing/stale, ask the user to run $sync-codex; do not auto-run it.

Situation-based docs:

• Project structure/architecture/tech-stack/deployment/setup (any layer — backend, frontend, or infra): project-structure-reference.md
• Backend/CQRS/API/domain/entity changes: backend-patterns-reference.md, domain-entities-reference.md
• Frontend/UI/styling/design-system: frontend-patterns-reference.md, scss-styling-guide.md, design-system/README.md
• Spec authoring, docs/specs/ pathing, or TC format: feature-spec-reference.md, spec-system-reference.md, spec-principles.md
• Behavior/public-contract changes or spec-test-code sync: workflow-spec-test-code-cycle-reference.md plus the spec docs above
• Derived spec indexes/ERDs/reimplementation guides: spec-system-reference.md and source Feature Specs under docs/specs/
• Integration test implementation/review: integration-test-reference.md
• E2E test implementation/review: e2e-test-reference.md
• Code review/audit work: code-review-rules.md plus domain docs above based on changed files

Do not read all docs blindly. Start from docs-index-reference.md, then open only relevant files for the task.

[IMPORTANT] MANDATORY MUST ATTENTION stay project-generic: discover local stack, conventions, query APIs, index definitions, metrics, and report paths before judging. [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, suspect saturation not the query: measure pool/thread acquire-wait and queue depth, and size pools by Little's Law (in-use = arrival-rate × hold-time) × replica count. [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.

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.
• Utilization knee ~70-80% — queue wait ≈ service_time × ρ/(1−ρ): 80%→4×, 90%→9×, 95%→19×. Little's Law L = λ × W sizes every pool. 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.

Critical Thinking Mindset — Apply critical thinking, sequential thinking. Every claim needs traced proof, confidence >80% to act. Anti-hallucination: Never present guess as fact — cite sources for every claim, admit uncertainty freely, self-check output for errors, cross-reference independently, stay skeptical of own confidence — certainty without evidence root of all hallucination.

AI Mistake Prevention — Failure modes to avoid on every task:

Re-read files after context changes. Context compaction, resume, or long-running work can make memory stale; verify current files before acting. Verify generated content against source evidence. AI hallucinates APIs, names, claims, and document facts. Check the relevant source before documenting or referencing. Check downstream references before deleting or renaming. Removing an artifact can stale docs, generated mirrors, configs, and callers; map references first. Trace the full impact chain after edits. Changing a definition can miss derived outputs and consumers. Follow the affected chain before declaring done. Verify ALL affected outputs, not just the first. One green check is not all green checks; validate every output surface the change can affect. Assume existing values are intentional — ask WHY before changing OR flagging one as a defect. Before changing or reporting a constant, limit, flag, cutoff, wording, or pattern, read nearby context and history, the CALLER's ordering, and 2+ sibling call sites of the same convention. A doc stating WHAT without WHY is missing rationale, not proof of a missing guard. Surface ambiguity before acting — don't pick silently. Multiple valid interpretations require an explicit question or stated assumption with risk. Assert the outcome your system owns, not the intermediate state your infrastructure owns. When verifying async work, assert the final business state — never the delivery/retry bookkeeping held in shared infrastructure that any co-running process can write. Such a check passes when run alone and flakes the moment anything else shares that infrastructure. Keep shared guidance role-relevant. Universal guidance must help every receiving skill or agent; code-specific obligations belong only in code-specific protocols.

[BLOCKING] Execute skill steps in declared order. NEVER skip, reorder, or merge steps without explicit user approval. [BLOCKING] Before each step/sub-skill call, update task 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.

Quick Summary

Goal: Ensure every shipped performance fix removes a measured (or static-risk-labeled) real bottleneck — across database waste (rows/columns, missing/unused indexes, query-in-loop fan-out, unbounded materialization, slow joins/aggregations, write amplification, partition/shard skew), in-process compute (O(n²) scans, wrong data structures, ReDoS, serialize/clone churn), runtime cost (GC pauses, allocation pressure, blocked event loop), network round trips (handshake/keep-alive, chatty contracts, RTT floors), client delivery (Core Web Vitals, long tasks, payload/asset weight), and concurrency/resilience saturation (pool acquire-wait sized by Little's Law, timeouts, retries, unbounded queues) — every number calibrated against a known anchor, while preserving behavior, authorization, and semantics, proven by before/after evidence, validated via $why-review before any fix, and confirmed by a clean full Phase-0 re-review — never a guess-driven change that hides waste or breaks correctness.

Summary:

• Purpose & 8-phase pipeline (the main tasks): drive a target through Phase 0 Detect scope (+ symptom→cause triage) → Phase 1 Discover local context (grep 3+ patterns, read index/schema, map callers) → Phase 2 Baseline evidence + anchor calibration (or static risk + verify cmd) → Phase 3 twelve serial dimension passes → Phase 4 Findings + Severity → Phase 5 Optimize plan (behavior-preserving) → Phase 6 $why-review --validate-findings gate → Phase 7 validated-fix + full Phase-0 re-review — so every recommendation removes a real bottleneck, preserves behavior, is evidence-proven; an Architecture-Altitude lens applies the same gate at design time.
• Evidence is the gate, not intuition: capture a runtime baseline (query plan/explain, row counts, p95/p99 distributions, pool acquire-wait, GC pauses, call count × RTT, field CWV, microbench at worst-case N) or label the finding static risk with the exact verify command — never recommend below 60% confidence, never average percentiles, always name the load model.
• Calibrate against the anchors in references/performance-knowledge.md — latency ladder (1 ns → 100 ns → 100 µs → 10 ms → 100 ms), utilization knee ~70-80% (ρ/(1−ρ)), Little's Law, tail amplification, CWV thresholds, cache hit-ratio math — a breached anchor is a hypothesis to prove locally, NEVER a finding by itself.
• Walk dimensions ONE pass at a time — (1) query shape/data-minimization → (2) index/access-path/data-topology → (3) N+1/fan-out → (4) aggregation/join/pipeline → (5) materialization/memory → (6) write/locks/transactions → (7) cache/reuse → (8) API payload/frontend/CWV → (9) compute/algorithmic → (10) network/protocol → (11) runtime/memory/GC → (12) distributed resilience/load — never all at once; reduce rows at the source before trimming columns or caching, and size pools by Little's Law (replica count × per-instance pool) when a fast op shows high p99.
• No finding is fixable until $why-review --validate-findings confirms it (Phase 6); each validated fix then restarts the FULL review from Phase 0 over the whole target (Phase 7) — a targeted before/after check alone never earns a PASS.

Renamed: formerly /performance — that name no longer resolves as a slash command; use $performance-review.

Workflow:

1. Detect - Classify scope and bottleneck type; order hypotheses via the symptom→cause matrix.
2. Discover - Read local code, metrics, docs, query/index definitions, similar patterns.
3. Measure - Capture baseline against a known anchor, or mark static-only risk.
4. Analyze - Run 12 serial dimension passes with evidence.
5. Plan - Propose smallest fix preserving behavior.
6. Verify - Re-measure, run tests, and record evidence.
7. Validate Findings - Run $why-review --validate-findings <report-path> before any fix.
8. Fix + Full Re-Review - Fix only validated findings, then restart from Detect over the full target.

Key Rules:

• MANDATORY ALWAYS measure before/after; static review findings need explicit verification command.
• MANDATORY ALWAYS calibrate a number against a known anchor before assigning severity; an anchor breach alone is a hypothesis, never a finding.
• MANDATORY ALWAYS push row filters to data source before projection/caching; row-count reduction beats column trimming.
• MANDATORY ALWAYS verify index usability with query shape/order, not index existence alone.
• MANDATORY ALWAYS count call count × RTT on a remote path, and check the timeout/retry/queue-bound before optimizing inside a call.
• NEVER recommend caching until query shape, indexes, pagination, batching, and data volume are understood; NEVER call a cache done without its measured hit ratio and bound.
• NEVER average percentiles, and NEVER trust a throughput number whose load model (open vs closed) is unstated.
• Findings are not eligible for fix until $why-review --validate-findings confirms them; every validated fix restarts the full performance review from Phase 0.

$ARGUMENTS

Phase 0: Detect Scope

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

Skip reason allowed only when target explicitly narrows scope and evidence proves dimension irrelevant.

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 — derive connection/thread/permit pool size from Little's Law (in-use = arrival-rate × hold-time), state the assumptions; shrink hold-time (release the resource across non-DB / external-wait spans) before growing the pool; size a shared backend against fleet-aggregate demand (replica count × per-instance pool), not one instance — local per-instance tuning becomes a thundering herd on the 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. Plan capacity below the ~70-80% utilization knee (wait ≈ service × ρ/(1−ρ)) and autoscale on a leading indicator (queue depth/concurrency), never lagging 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. When .code-graph/graph.db exists, run a graph blast-radius pass (trace --direction downstream on the hot path) to size the fan-out before proposing a fix — see the Graph-Assisted Investigation gate 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.
• ALWAYS read the local resilience + delivery configuration the new dimensions rest on: HTTP client/keep-alive and pool settings, timeout/retry/breaker policy, queue and consumer bounds, rate limits, GC/runtime and container memory limits, CDN/asset caching headers, and whatever RUM/field-metrics source exists.
• 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

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.
• ALWAYS report distributions, never means: p50/p90/p99/p99.9 + max, segmented by endpoint/tenant/region. NEVER average percentiles across instances or windows — aggregate histograms instead.
• ALWAYS name the load model behind any throughput/latency number: open-model (arrival-rate) exposes queueing collapse, closed-model (fixed VUs) HIDES it; flag suspected coordinated omission when a tool reports an implausibly clean tail.
• ALWAYS state data volume and cache state of the measurement — a benchmark on toy data or a warm-only cache is fiction; soak/endurance is the only shape that surfaces leaks, fragmentation, and bloat.
• 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). Wrapping a column in a function/cast/transformation inside a query predicate translates to func(column) = $param — the DB CANNOT use an index on that column and full-scans. Scan every query expression for a transformation on the COLUMN side, not the parameter side: .ToLower()/.ToUpper()/.Trim()/.Substring() on a column, col1 + " " + col2 == x (concatenation), .Date/date-part extraction, Convert/cast/collation change, leading-wildcard LIKE '%x', or a computed expression compared to a value. Fix — keep the column bare and move the transformation to the in-memory PARAMETER (e.g. case-insensitive via a candidate list col == x || col == xLower), OR persist a normalized indexed column, OR add a functional/expression index. ALWAYS prove with EXPLAIN/query plan: Index Scan/Seek expected, Seq Scan = the smell confirmed.

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 hit-ratio evidence — a cache added without measuring the ratio; the ratio IS the value (90%→99% cuts origin load 10×, so a 60% hit ratio is barely a cache)
• stampede/thundering-herd exposure — hot key expiring sends every request to origin at once; no single-flight/request-coalescing, no per-key lease, no TTL jitter, or a whole key class expiring simultaneously
• cold-start blindness — post-deploy/failover empty cache indistinguishable from an origin outage; no warming and no LB slow-start
• unbounded cache (a memory leak with a friendly name): no size bound, no entry lifetime, no eviction policy matched to access skew — and cache thrash once the working set exceeds cache size (a cliff, not a slope)
• missing negative caching, so nonexistent keys generate repeated miss-storms
• schema/build version absent from the key, so a deploy can serve poisoned entries

Prefer fixes: request-scope memoization first, then bounded shared cache with explicit key, TTL/invalidation, size limits, privacy constraints, and hit/miss metrics. Add single-flight + TTL jitter for hot keys, stale-while-revalidate where staleness is acceptable, negative caching (or a Bloom filter) for absent keys, a version segment in the key, and an eviction policy matched to the access skew (LRU default, LFU/W-TinyLFU for skewed). NEVER treat "we added a cache" as a completed fix without the measured hit ratio and the bound.

8. API Payload, Frontend Deli

…(truncated)