n-plus-one-detector

name

n-plus-one-detector

description

Detect and design out chatty data-access patterns — the N+1 (one query per row), repeated identical queries in one request, serial awaited calls in loops, and over-fetching. Detection is evidence-first: per-request query counts and logs, ORM instrumentation, static cues (lazy relation touched inside iteration); the fix is pattern-matched — eager/preload, batched IN-key or dataloader-style loads memoized per request scope, joins or denormalization with prices stated — plus the regression guard: query-count budgets asserted in tests so the pattern cannot return. Each individual query is usually FAST — that is why plan-reading finds nothing; the defect is the pattern. Use when a request issues suspiciously many queries, latency scales with result-set size, or lazy-load storms are suspected. Do NOT use for one slow query (query-plan-reader), unattributed slowness (profiling-methodology-designer), or caching design (caching-strategy-designer).

N-Plus-One Detector

Purpose

The N+1 is the most reliably re-invented performance defect in software: the list renders, each row lazily touches a relation, and a 50-row page issues 51 queries — every one of them fast, the sum of them slow, and the whole thing invisible to single-query tooling because no single query is guilty. This skill detects chatty data-access patterns with evidence (query counts per request, logs, ORM instrumentation), designs the pattern-matched fix (eager loading, batching, joins, denormalization — each with its price), and installs the regression guard: a query-count budget asserted in tests, because N+1s return the moment someone adds a field to a template. Latency that scales with result-set size is this skill's signature symptom.

Use When

• Use when: a request, page, or job issues suspiciously many queries — a query log, APM trace, or count shows tens-to-hundreds of similar statements per operation.
• Use when: latency scales with result-set size — 10 items fast, 100 items 10× slower — the arithmetic signature of per-row access.
• Use when: an ORM's lazy loading is suspected of firing inside loops, serializers, or templates.
• Use when: designing the regression guard — query-count budgets in tests for endpoints that have been burned before.
• Use when: serial awaited calls in a loop (per-item remote/DB round trips) need batching design, even where no ORM is involved.
• Do NOT use when: ONE query is slow — its plan is the evidence and query-plan-reader the tool; here every query is typically fast.
• Do NOT use when: nothing has attributed the slowness yet — "the app is slow" starts at profiling-methodology-designer; a query-count explosion in its findings is what routes here.
• Do NOT use when: the fix under design is a CACHE — caching a chatty pattern hides it and adds invalidation debt; the pattern gets fixed here first, and genuine cache candidates route to caching-strategy-designer.
• Do NOT use when: the chatter is service-to-service (one API call per item against another service) and the design question is the inter-service contract — batch endpoint design belongs to api-event-architect (external) or architecture-designer (internal boundaries); this skill still applies for the caller-side batching pattern.

Inputs to Inspect

1. Per-request query evidence: the query log / APM span list for one symptomatic operation — statement shapes, counts, and total time vs per-query time. This is the primary artifact; obtain it before theorizing.
2. The data-access layer's semantics: which ORM/query layer, its lazy vs eager defaults, its batching facilities (preload/include, IN-key loaders, dataloader equivalents), and its per-request scope (where memoization can safely live).
3. The code path: the loop/serializer/template touching relations per item; resolver structure in GraphQL-style APIs (the per-field resolver is the N+1's natural habitat).
4. Result-set characteristics: typical and worst-case item counts, pagination state (or its absence), and whether the endpoint's latency scales with them.
5. Existing test infrastructure: where query-count assertions can live (the test layer that owns integration-level checks), and any existing counter/instrumentation hooks.
6. Write/consistency context for fix pricing: how hot the touched tables are, and whether denormalized read fields are maintainable.

Workflow

1. Confirm the pattern with counts, not vibes. For the symptomatic operation, produce the evidence table: distinct statement shapes × repetitions × per-query time. The N+1 signature: one parent query + N near-identical child queries differing only in a key. Related signatures: repeated IDENTICAL queries (missing per-request memoization), serial awaits in a loop (wall-clock = N × round trip), and SELECT-everything over-fetch feeding two fields.
2. Locate the injection site. The loop, serializer, template, or per-field resolver touching a lazy relation per item. Name the file/ path; the fix lands where the touch happens, not where the query runs. Static cues for review: relation access inside iteration, awaited calls in loops, missing IN-batching on keyed lookups.
3. Choose the fix by pattern (catalog in references):
   • Known-ahead relations: eager/preload declarations on the parent query — the default fix; price: over-eager loading drags unneeded data on paths that don't render the relation (scope eager-ness per use, not globally).
   • Dynamic/conditional relations (resolvers, polymorphic): batched loader — collect keys per request tick, one IN-key query, memoized per request scope; price: loader infrastructure and per-request cache lifecycle.
   • Aggregates per row: one grouped aggregate query joined back, never per-row COUNT calls.
   • Serial awaits: batch the keys into one call where an interface exists; otherwise bounded-concurrency fan-out as the lesser fix, with the batch-endpoint need routed to the contract owner.
   • Truly relational shapes: a join producing the composed row set — price: row-width multiplication on one-to-many joins (deduplication cost); prefer two batched queries over exploding joins.
   • Read-path denormalization: a maintained count/summary column — price: consistency maintenance; only with an owner and an update rule. Over-fetch fixes ride along: project the fields the path uses. 3½. Reject the cache reflex explicitly. Caching the chatty result is not on the fix menu here: it hides the pattern at the price of invalidation debt, and the first cache miss replays the storm. After the pattern is fixed, a genuine staleness-tolerant candidate may route to caching-strategy-designer on its own merits.
4. Verify by re-measuring the count. Same operation, same data shape: statement count drops to the designed number (typically 2–3), wall-clock drops accordingly, and — the check that catches over- correction — the replacement queries' plans are sane (query-plan-reader if the new IN-key or join query is itself heavy at volume).
5. Install the regression guard. A query-count budget assertion in the integration layer for the fixed operation: "renders with ≤ K queries" (exact-count where stable, small budget where seeding varies). The budget documents intent — the next lazy touch fails a test instead of shipping. Wire the counter via the ORM's instrumentation hook; keep budgets on the endpoints that matter, not everywhere (assertion noise erodes the guard).

Pattern catalog with fix templates, counter-instrumentation options, and query-budget assertion patterns: references/chatty-access-patterns.md.

Output Format

N+1 / CHATTY ACCESS REPORT — <operation>
Evidence: <statement shapes × repetitions × per-query time; total vs per-query>
Signature: N+1 | repeated-identical | serial-await | over-fetch (combinations common)
Injection site(s): <file/path — loop, serializer, resolver, template>
Fix per site:
  <site>: <eager|batched-loader|grouped-aggregate|batch-call|join|denormalize>
  — price: <over-eagerness / loader lifecycle / row-width / consistency>
Cache reflex: rejected here — pattern fixed first (candidates → caching-strategy-designer after)
Verification: queries <before> → <after (designed number)>; wall-clock <before> → <after>;
              replacement-query plan sanity: <checked | routed to query-plan-reader>
Regression guard: <test file/layer — budget K, exact|bounded, counter hook>
Residual: <paths with the same pattern not yet fixed — listed, not implied done>

Validation Checklist

• The pattern is evidenced by a count table from a real operation — not inferred from code reading alone.
• The injection site is named at file/path level; the fix lands at the touch point.
• The chosen fix matches the pattern class and states its price; no global eager-loading, no reflexive cache.
• One-to-many joins were checked for row-width explosion; batched two-query shapes preferred where they explode.
• Before/after statement counts are measured, not estimated; the after-count equals the design.
• The replacement query's own plan was sanity-checked at realistic volume (large IN lists, big joins).
• A query-count budget assertion exists for the fixed operation, wired to a real counter hook.
• Unfixed sibling paths with the same pattern are listed as residual.

Gotchas

• Fast queries hide slow requests: per-query time of 2ms × 400 queries is 800ms of pure round-trips — dashboards ranking slow QUERIES never surface it. Count per request, not per statement.
• Global eager loading is the overcorrection: preloading every relation everywhere fixes one page and bloats twelve — eager-ness is scoped per use path.
• The batched IN-query can become the new problem: a 10,000-key IN list has its own plan pathology; chunk the keys and sanity-check the batched query's plan at worst-case volume.
• One-to-many joins multiply row width: joining a parent to 200 children repeats the parent 200 times on the wire; two batched queries beat the exploding join.
• Per-request memoization leaking across requests: a loader cache scoped wrong (process-global) serves user A's rows to user B — in multi-tenant systems this is a data leak, not just staleness. The request scope is a correctness boundary.
• GraphQL-style resolvers reinvent N+1 per field: each field resolver fetching independently is the architecture working as designed — batched loaders per entity type are the standing fix, not per-query patches.
• Exact-count assertions flake on seed drift: where fixture rows vary, assert a small budget (≤ K) instead of equality — the guard should catch a storm, not a seeding change.
• Pagination absence turns fixes moot: an unpaginated list of 50,000 items is slow after every fix; pagination discipline (bounded page sizes) rides along with the pattern fix.

Stop Conditions

• Asked to fix by caching ("just cache the page/result") → refuse the reflex: the pattern gets fixed first; caching a chatty pattern hides it and adds invalidation debt. Genuine cache candidates route to caching-strategy-designer after the count is sane.
• No per-request query evidence can be obtained (no logs, no instrumentation, no local reproduction) → stop and install the counter hook first; pattern fixes designed from code-reading alone misfire on framework internals.
• The evidence shows ONE heavy query, not many fast ones → route to query-plan-reader; this skill's fixes would restructure access that isn't the problem.
• The per-item calls are cross-service and the batch interface doesn't exist → design the caller-side batching AND route the batch-endpoint contract to its owner (api-event-architect / the owning team); do not invent another service's API unilaterally.
• Fixing the pattern requires schema change (denormalized counts, new keys) → the read-field design is this skill's output, but the migration itself follows schema-evolution-planner staging and the repo's change process — hand off, don't inline DDL.

Supporting Files

• references/chatty-access-patterns.md — the pattern catalog with fix templates per ORM idiom class, counter-instrumentation options, IN-list chunking guidance, and query-budget assertion patterns (exact vs bounded).
• evals/evals.json — behavior cases including the resolver-storm edge and the cache-it-instead refusal.
• evals/trigger-evals.json — discrimination against query-plan-reader (the sibling seam), profiling-methodology-designer, and caching-strategy-designer.