A practical way to spot hidden database calls, fix the read path, and check what the fix costs.

You open an orders page with 20 rows. The endpoint returns the right customers, the response looks perfectly normal, and nothing throws an error. Then you check the query log and see 21 database calls.

One query loaded the orders while the other 20 fetched a customer for each order.

That pattern is called an N+1 query problem, and it is easy to miss because the code that triggers it often sits inside a serializer, template, or property rather than next to the query that loaded the list. Let’s trace a small example, fix the relationship load, and make sure the fix stays useful as the response grows.

A list of orders triggers one repeated customer lookup for every row

First, follow the data all the way to the response

Imagine an Order has a foreign key to Customer. The view loads a page of orders, then the serializer includes the customer’s name:

orders = Order.objects.filter(status="open")[:20] payload = [ {"id": order.id, "customer": order.customer.name} for order in orders ]

The first line looks like one read. But unless the related customer was loaded already, each access to order.customer can trigger another query. That makes the query count grow with the number of orders: one list query plus one relationship lookup per order.

In an interview, I’d make the hidden work visible before suggesting a fix. I’d trace the complete response path, including serializer fields, template properties, and helper methods. Then I’d inspect the actual SQL or query log for repeated statements that differ only by customer ID. Counting only the view’s first queryset can miss the expensive part entirely.

Match the loading strategy to the relationship

For a single-valued foreign-key relationship such as Order.customer, Django’s select_related loads the related row through a SQL join. The example becomes:

orders = ( Order.objects .filter(status="open") .select_related("customer") [:20] )

Now the serializer can read order.customer.name without making a separate customer query for every order. The database still returns the order and customer data, but the application asks for them together.

The important detail is that the relationship is single-valued. If an order has many line items, joining every collection into one large result can repeat the order columns for each line item. For a collection, prefetch_related("items") is often a better starting point. Django performs a separate batched lookup and associates the items with their orders in memory. SQLAlchemy offers similar choices with joined and select-in loading; the right option depends on the relationship and the shape of the result.

Single-valued relations can join while collection relations are often batch-loaded

Don’t optimize the query counter in isolation

It is tempting to hear 21 queries and decide that one query must always be the goal. That can replace one problem with another. Joining several collection relationships may multiply result rows, duplicate parent data, consume more memory, and make pagination harder to reason about. A very broad eager load can fetch columns or relationships the response never uses.

I’d choose the loading plan by asking what the response actually needs. For each relationship, check whether it is one-to-one or many valued, whether the data is used for every returned row, and how many child records might exist. Then compare the query count with the rows and payload returned. A batched query that avoids hundreds of round trips can be a good trade, even if the request still uses more than one query.

A lower query count can still have higher row and memory cost

Also look at how the relation is accessed. A prefetch cache only helps when the code uses the prefetched relation in a compatible way. Calling a new filtered query for each order can bypass the prefetched collection and bring the repeated work back. Query inspection should include the actual serializer or template behavior, not just the model declaration.

A small test can catch the regression

I’d write a focused test around the endpoint or serialization path and vary the number of orders while keeping the request shape the same. Try one, 20, and 100 rows, then check whether relationship queries stay bounded instead of increasing once per order. The exact total can vary because authentication, pagination counts, middleware, and other request work may add queries, so the useful signal is the slope of the repeated relationship loads.

For a Django unit test, assertNumQueries can make a narrow query expectation explicit. Keep that assertion close to the code path it protects, and avoid pinning a brittle endpoint-wide total if unrelated request behavior changes frequently. Pair the count check with response assertions so the optimization does not silently omit customer data or change ordering.

I’d also cover the cases that make a technically fast response wrong. Check orders with no optional related record, tenant or permission filtering, empty pages, and the exact fields the response promises. If the database enforces foreign keys, a required relation may behave differently from an optional one, but authorization still belongs in the query or policy layer; eager loading is not a substitute for access control.

The interview answer I’d keep in my back pocket

The concise version is that I would trace query execution through the full response path, identify the repeated lazy relationship lookup, and choose eager loading based on cardinality. I’d use a join for a single-valued relation or a batched prefetch for a collection, then verify both response correctness and query growth at different result sizes. I’d also check row multiplication, payload size, and memory before calling the fix complete.

That answer shows more than familiarity with select_related. It shows that you can find work hidden outside the view, choose a strategy based on data shape, and explain how you would know whether the trade-off helped.

Checklist for tracing, choosing, and validating a relationship load

The habit transfers nicely to other backend performance questions too, because the useful move is usually to trace the work the request actually performs rather than optimize the line that looks suspicious. If you want another practice prompt about scaling a service and making schema, indexing, and search trade-offs, try this related backend exercise.

For reference, Django’s select_related and prefetch_related documentation explains the single-valued join and batched-prefetch distinction, while SQLAlchemy’s relationship loading guide describes lazy loads and alternatives such as joined and select-in loading.

A saveable debugging checklist

  • Trace from the list query through serializers, templates, and properties.
  • Look for repeated SQL that changes only the related row’s ID.
  • Choose a join for single-valued relations and a batch load for collections.
  • Check that access patterns actually use the loaded relationship data.
  • Compare query growth at 1, 20, and 100 results, holding the request path fixed.
  • Validate response fields, empty relations, permissions, row counts, and memory cost.

If the endpoint still returns the same useful data while relationship queries stop growing one-for-one with the result list, you have fixed the hidden repetition and kept the trade-off visible.

~ Emily