Find an N plus one query before optimizing the wrong layer

A project list endpoint becomes slower as the page size grows. The first suspicion is JSON serialization, but a request trace shows one query for projects and one owner lookup for every project. Twenty projects cause twenty-one storage operations even when several share the same owner. Caching the rendered page may hide the cost temporarily; the underlying data access still scales with the number of rows.

This example assumes MongoDB and a Node service, with optional ORM population. The term N plus one describes a query pattern, not a specific library. Some population APIs batch reads, while certain options or custom loops issue more queries. Measure the actual behavior of the installed version before naming the cause.

Measure the complete request

Choose a synthetic list request and count database operations from entry to response. Include middleware, permissions, related records, and serialization hooks. An endpoint may have a batched owner read while an authorization helper performs one membership query per row. The visible repository loop is not the whole journey.

Record page size, unique owner count, query count, and rough stage timing. Use request-scoped tracing or safe driver instrumentation where available. Avoid logging full filters or documents if they contain private values; operation name and relation identity can be enough.

Run the same request with a few different page sizes. Do not invent benchmark claims from these local measurements. The useful evidence is the relationship between rows and operations in this controlled case, plus the code path that creates it.

Find the per-row boundary

Look for awaited reads inside loops, Promise.all around one read per row, and serializer functions that perform storage access. Parallelizing N reads does not remove the N plus one pattern; it changes overlap and may increase connection pressure.

Inspect ORM population options and generated queries. A populated relationship is not automatically one query per parent, nor automatically one total query. Verify the library's documented behavior for the exact option used. A per-document limit may alter batching, and nested population can introduce additional stages.

Ask an AI assistant to trace data access from the route through the mapper. Require it to identify actual calls and distinguish inferred query counts from observed counts. A model can spot a loop, but instrumentation establishes whether a helper caches or batches internally.

Batch the needed relationship

For a small page, collect unique owner identifiers, read their safe public fields in one bounded query, and build a map. Then render each project from that map. Keep tenant scope and field projection in the owner read; batching must not broaden access.

const projects = await readProjectPage(actor, cursor)
const ids = [...new Set(projects.map(p => p.ownerId.toString()))]
const owners = await readPublicOwners(actor, ids)
const byId = new Map(owners.map(o => [o.id, o]))
const view = projects.map(p => ({
  id: p.id,
  title: p.title,
  owner: byId.get(p.ownerId.toString()) ?? null
}))

These are application functions and a public response mapper. The owner read should accept a bounded list and return only allowed fields. If the page can be huge, enforce pagination first rather than creating an unbounded $in request.

Define missing-owner behavior. A deleted account may produce a placeholder or null, depending on product policy. Do not drop the entire project silently because a related owner is absent, and do not expose a private owner merely because the project itself is readable.

Compare aggregation and denormalization

A MongoDB $lookup pipeline can express joining related data server-side. It may be appropriate when filtering or sorting depends on that relationship. Examine the actual plan and relationship indexes rather than assuming aggregation is always faster than two bounded reads.

Denormalizing a display name onto a project can reduce read work but creates an update-consistency problem. Decide whether the name is a historical snapshot or a current value that must be synchronized. A snapshot of an invoice recipient is different from a current profile name.

A request-scoped loader can coalesce repeated reads across resolvers. Its lifetime matters: a global cache keyed only by owner identifier can leak role-specific fields or become stale. Keep authorization and projection in the loader contract, and avoid using a broad cache as a substitute for query design.

Verify query count and response meaning

Repeat the measured request after the change and assert the expected bounded operation pattern. Test repeated owners, all unique owners, missing owners, and owners outside the actor's scope. Compare public response values with the earlier intended contract.

A query-count test should allow deliberate fixed work while detecting growth tied to rows. Avoid asserting an arbitrary universal count if middleware legitimately changes. Name the relationship whose reads must be batched and measure that portion of the trace.

Use an isolated real database or driver-level instrumentation for the query-count claim. A mocked repository called once proves only the application invoked that mock once; it cannot establish how the implementation queries storage. Unit tests can still verify map construction and missing-owner behavior.

Check load and connection effects

Removing per-row reads can reduce round trips, but it may increase the size of one query or response. Inspect projections, page limits, and index use. A batch fetching entire owner documents can trade query count for excessive data transfer and privacy risk.

Do not compensate for a query pattern by immediately increasing the connection pool. More connections can amplify storage load and hide queueing until traffic grows. First identify the operations the response actually needs, then tune resource limits from observed demand.

Choosing an index helps evaluate the new batch query. Private populated fields helps verify that the relationship mapper remains safe.

Use AI for bounded alternatives

Give the assistant the measured query pattern, page-size limit, required response fields, and access rule. Ask it to compare a batch read, aggregation, and denormalized snapshot for this exact endpoint. Require a failure case and consistency cost for each option.

Reject suggestions that claim a performance improvement without measurements or that add a new cache without an invalidation policy. The smallest safe fix may be a single batch read and map, not a repository-wide rewrite.

Keep the before-and-after evidence with the regression test. The endpoint is improved when it performs bounded data work while returning the same intended public relationships. That conclusion is narrower and more useful than saying the database has been optimized.

Preserve the requested row order

Batch relationship results do not necessarily arrive in the same order as the requested identifiers. Use an identifier map rather than matching arrays by position. Keep the original project order when constructing the response, and normalize identifier representations consistently so a string key is not compared with an object reference. Include a test where the owner query returns reversed order and another where an owner is absent. These cases catch a batch implementation that reduces query count while silently assigning the wrong owner to a project.