Problem and Applicable Context
An order-list endpoint first fetches a page of 50 orders. During response serialization, the ORM loads order.customer separately for every order. A production trace contains one order query and 50 customer queries. No individual statement looks slow, yet a request performs 51 database roundtrips. When the page size doubles, the statement count and request latency grow with it.
Assume each order belongs to one customer, the response needs only the customer's display name, and the endpoint must preserve its current order, authorization filters, and pagination semantics. The numbers are interview assumptions that make the diagnosis testable. The core question is how to detect an application access-pattern problem, not how to tune one slow SQL plan.
The target role is a backend engineer working with a relational database and an ORM. A complete answer should compare a single joined query, a two-query select-in batch, and changes to loading defaults. It must also cover one-to-many relationships, transaction consistency, observability, and a regression check whose query count does not depend on timing.
What the Interviewer Is Evaluating
The first signal is whether the candidate measures work at the request boundary. An N+1 problem can consist of many individually efficient indexed queries. Looking only at the slow-query log or running EXPLAIN on one customer lookup can miss the multiplier. The useful evidence is a trace or query log that groups statements by request and shows the same normalized lookup repeated from the same call site.
The second signal is a correct growth model. For N parent rows, the naive path executes one parent query plus one related-row query per parent:
Q(N) = 1 + N
Q(50) = 51A select-in batch normally changes that to one parent query plus one related-row query, so the count remains two for the tested page sizes. If the ID list must be split into B batches, the count becomes 1 + B; it still does not grow once per parent.
The third signal is choosing a loading shape from relationship cardinality and response needs. A many-to-one customer lookup with a few narrow columns is often suitable for a JOIN. A large one-to-many collection can multiply result rows and duplicate parent columns, making a parent-page query followed by a batched child query safer. “Enable eager loading everywhere” is not a design; some ORMs can still issue secondary selects for eager associations, and global eager loading can fetch data that this endpoint never returns.
Finally, the interviewer expects proof that behavior is preserved. Query-count improvement does not excuse missing tenant filters, changed page boundaries, unstable ordering, inconsistent reads, or extra rows caused by a join. A strong answer validates database work and response equivalence.
Clarifying Questions Before Answering
- Where does the relationship access occur? If serialization, a template, logging, or a mapper
touches the property after the repository returns, the query source is outside the apparent loop. The fix must cover the actual access path.
- What is the relationship cardinality? Many-to-one data can join without multiplying one
order into several rows. A one-to-many child collection changes pagination and payload-size risk.
- Which related fields are required? A display name supports a narrow projection. Loading an
entire customer entity and every association creates overfetching even if the query count falls.
- Are related IDs repeated? A request-scoped identity map may reduce duplicate lookups, but it
does not bound the count when most IDs are unique. Measure instead of assuming the cache fixes it.
- How is pagination applied? Parent rows must be selected with a deterministic order before a
one-to-many join or child load; otherwise row multiplication can change which parents appear.
- Must the two reads share one snapshot? A JOIN is one statement. A parent query followed by a
child query can observe a concurrent change under the default isolation behavior. If point-in-time consistency matters, use an appropriate transaction snapshot or the single-statement shape.
- What does the ORM actually generate? Names such as eager, include, prefetch, or split query
do not guarantee a particular statement count. Inspect emitted SQL for the deployed version.
30-Second Answer Framework
“I would group database spans by request and verify one page query followed by the same normalized customer lookup 50 times. Then I would vary the page size; counts of 11, 21, and 41 for pages of 10, 20, and 40 prove linear query amplification even if every lookup is fast. For this many-to-one display-name field, I would compare a narrow JOIN with a two-query select-in batch. I would avoid a global eager default because it can overfetch and does not guarantee one statement. For a large one-to-many relation, I would page parents first and batch children to avoid row multiplication. Finally, I would assert a constant query budget, compare response IDs and ordering, and monitor request-level query count and latency after rollout.”
Step-by-Step Deep Dive
Step 1: Prove amplification at the request boundary
Attach a request or trace ID to database spans, normalize SQL by replacing parameter values, and group by call site. The suspicious trace should look structurally like this:
1 × SELECT id, customer_id, created_at, total_cents FROM orders ... LIMIT ?
50 × SELECT id, display_name FROM customers WHERE id = ?The repeated fingerprint and linear scaling distinguish N+1 from a single expensive statement, lock wait, connection-pool queue, or slow serializer. Record total database duration and roundtrip count as well as statement duration. Fifty one-millisecond queries are not equivalent to one fifty-one-millisecond query because each roundtrip also consumes a connection, protocol work, and scheduler time.
Repeat the request with controlled page sizes of 10, 20, and 40. A count of 11, 21, and 41 is a strong causal signature. Temporarily removing the relationship field should collapse the extra queries; that confirms which property access triggers loading. This experiment is more useful than adding an index to an already indexed primary-key lookup.
Step 2: Define the required result before changing loading
Write down the response contract: ordered order IDs, cursor or page boundary, allowed tenant, and the exact customer fields. Also decide how missing or deleted customers appear. This prevents a query optimization from silently becoming a data-contract change.
Keep authorization and soft-delete predicates in the batched path. If the original relationship loader enforced tenant scope, a hand-written WHERE id = ANY(...) query that omits tenant scope can become a data leak. Query count is only one acceptance criterion.
Step 3: Choose between a narrow JOIN and a select-in batch
For a mandatory many-to-one relation and a narrow response, a single joined statement is simple:
SELECT
o.id,
o.created_at,
o.total_cents,
c.id AS customer_id,
c.display_name
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
AND c.tenant_id = o.tenant_id
WHERE o.tenant_id = $1
ORDER BY o.created_at DESC, o.id DESC
LIMIT $2;The tie-breaker on o.id makes ordering deterministic. Use a left join instead if an order may legitimately outlive its customer record and the existing contract returns that order.
A two-query batch keeps parent pagination separate and works well when join width or collection cardinality would inflate the result. The following illustrative TypeScript deduplicates IDs, loads only required columns, and maps them in memory:
interface OrderRow {
id: string
customerId: string
createdAt: Date
totalCents: number
}
interface CustomerRow {
id: string
displayName: string
}
const orders = await loadOrderPage(tenantId, limit)
const customerIds = [...new Set(orders.map((order) => order.customerId))]
const customers = await loadCustomersByIds(tenantId, customerIds)
const customerById = new Map(customers.map((customer) => [customer.id, customer]))
return orders.map((order) => ({
...order,
customer: customerById.get(order.customerId) ?? null,
}))The repository's loadCustomersByIds should use one set-based predicate for a normal page and split unusually large ID lists into bounded batches. Deduplication reduces transferred parameters; it is not the main fix. The main fix is moving the related load outside the per-row access path.
Step 4: Handle one-to-many relations without breaking pagination
Suppose each order also returns many line items. Joining orders, customers, and items can emit one row per item and repeat order columns. Applying LIMIT 50 after that join may limit joined rows, not 50 distinct orders. Loading several collections in one join can multiply them against each other.
Select the 50 parent orders first with a stable order, then fetch all items whose order_id is in that parent ID set. Group items by order_id and attach them in the original parent order. This is the practical reason official ORM documentation offers joined, subquery, select-in, and split-query strategies instead of one universal eager-loading switch.
Step 5: Reject global loading changes as a shortcut
Changing every relation from lazy to eager can move the problem rather than solve it. Endpoints that do not need customers now overfetch them. A query that does not join-fetch an eager association may still provoke secondary selects in some ORM behavior. Wide object graphs can also create large joins or cycles that are hard to predict.
Prefer an endpoint-specific projection or explicit loading plan. In development and tests, use an ORM option that raises on unexpected lazy SQL when available. That turns a hidden database access into a visible failure at the boundary where the response is assembled.
Step 6: Verify query shape, semantics, and production effect
Build a regression matrix with empty results, one row, repeated customer IDs, all unique customer IDs, a missing optional customer, and the maximum allowed page size. Assert response order, IDs, null behavior, tenant isolation, and a constant query budget. For the two-query plan, pages of 10 and 40 should both use two statements under the chosen batch bound.
Then compare representative production-like data for total request latency, database spans per request, rows and bytes returned, connection-pool occupancy, and database load. A JOIN that reduces 51 statements to one but returns a huge repeated payload may be a regression under a different metric. Roll out by endpoint, watch the query-count distribution, and retain a trace sample that can identify the call site if lazy loading reappears.
High-Quality Sample Answer
“The evidence points to query amplification rather than one slow plan. I would start with one request trace and group normalized SQL by call site. If a page of 50 shows one order query and 50 customer primary-key lookups, then pages of 10, 20, and 40 produce 11, 21, and 41 statements, I can show that database work grows once per parent row.
Before fixing it, I would preserve the contract: tenant predicates, order IDs, deterministic ordering, page boundary, required customer fields, and missing-customer behavior. Because this is a narrow many-to-one lookup, a JOIN is a good first candidate. A two-query select-in load is also valid: fetch the order page, deduplicate customer IDs, load those customers in one set query, and map by ID. I would choose between them from generated SQL, payload width, and consistency needs.
If the relation were a large one-to-many collection, I would page orders first and batch items in a second query. That avoids joined-row multiplication changing pagination. I would not make every association globally eager; it can overfetch and some ORM query shapes still issue secondary selects.
The regression test would run empty, repeated-ID, unique-ID, missing-relation, and maximum-page cases. It would assert identical IDs, order, authorization, and null behavior, plus a constant statement budget. After rollout I would monitor database spans per request and total latency, not only slow individual statements. That proves both the performance fix and the unchanged result.”
Common Mistakes
- Adding an index to the repeated customer lookup → Each lookup may already use a primary-key
index, while the request still performs one roundtrip per order → **Measure and change the access pattern.**
- Enabling global eager loading → Unrelated endpoints overfetch, and ORM-specific eager behavior
may still issue secondary statements → Use an endpoint-specific projection or loading plan.
- Joining every relation → One-to-many collections multiply rows, repeat parent data, and can
corrupt page boundaries → Page parents first and batch large collections.
- Using a process-wide cache as the fix → Cold or unique IDs still produce linear queries, and
stale or cross-tenant data becomes a new risk → Bound query count independently of cache hits.
- Counting only slow statements → Dozens of fast queries evade a threshold-based slow-query log
while consuming roundtrips and connections → Aggregate spans by request and fingerprint.
- Dropping security predicates in a batch query → The optimization may load a related row from
another tenant → Preserve authorization and soft-delete filters explicitly.
- Asserting only lower latency → Timing tests are noisy and can pass with a warm cache → **Assert
a constant query budget and response equivalence, then measure latency separately.**
- Assuming two queries equal one snapshot → Concurrent updates can appear between statements
under common isolation behavior → **Choose a transaction snapshot or one statement when the contract requires point-in-time consistency.**
Follow-Up Questions and Responses
Follow-up 1: When is a JOIN better than a two-query batch?
A JOIN is attractive for a narrow many-to-one or one-to-one relation, when one-statement snapshot semantics matter and row multiplication is bounded. A batch is attractive when parent pagination must remain isolated, related data is a collection, or a join would repeat wide parent columns. Inspect the emitted SQL and returned bytes; statement count alone does not decide.
Follow-up 2: What if 50 orders reference only three customers?
A request-scoped identity map might reduce the naive path to four statements, but that remains data-dependent. Deduplicate the three IDs and issue one set query so the planned count is two. Do not rely on a cross-request cache for correctness or tenant isolation.
Follow-up 3: How would you catch N+1 in a GraphQL-style nested resolver?
Collect relationship keys during one request execution and dispatch a request-scoped batch before resolving the fields. Preserve result order by mapping rows back to the original key sequence and represent missing keys explicitly. The regression test should request the nested field for several parents and assert a bounded statement count; omitting the field should avoid the related query.
Follow-up 4: What if the batch contains more IDs than one query should carry?
Split the deduplicated IDs into bounded chunks chosen from database and driver constraints. The model becomes 1 + B statements for B chunks, so tests should assert the expected bound rather than an unconditional two. If ordinary endpoint pages require many chunks, reduce the page or reconsider the data shape.
Follow-up 5: The query count is fixed, but latency barely improves. What next?
Compare database time, network time, serialization, rows and bytes returned, lock waits, and pool queueing before proposing another fix. The new joined or batched statement may itself need an index, may return too much data, or may not dominate end-to-end latency. Keep the N+1 correction if it removes linear amplification, but diagnose the remaining bottleneck with fresh evidence.