N+1 queries: detecting them by counting and fixing them by batching

What it is

Code loads a list of N parent rows with one query and then reads a lazily loaded relationship on each row; the ORM issues one more query per row. The SQLAlchemy documentation names it: for any N objects loaded, accessing their lazy-loaded attributes means there will be N+1 SELECT statements emitted. The same shape appears without an ORM: a loop calling an HTTP API per item, a GraphQL resolver fetching per node, a cache lookup per key.

Why it matters

Each query costs a round trip regardless of how little it returns. A page of 200 rows becomes 201 round trips; the cost scales with the data, not with the code, so the problem is invisible on a three-row test fixture and appears only in production. Slow-query logs do not show it either, because every one of the N queries is fast.

How to apply

• Detect by counting. Assert the number of queries per request in tests (Django's assertNumQueries, which asserts that a call executes a given number of queries; an engine event listener in SQLAlchemy; a per-request counter in the query logger) and fail when the count depends on the result size: run the same request with 1 row and with 50 rows and compare.
• Make unwanted lazy loads fail loudly. SQLAlchemy's raiseload() replaces lazy loading with an exception, so a new attribute access in a template or serializer surfaces in tests rather than as N+1 in production.
• Fix to-one relations with a join: select_related() in Django, joinedload() in SQLAlchemy. One query, wider rows.
• Fix collections with a second query keyed by IN (ids): prefetch_related() in Django, selectinload() in SQLAlchemy, which its documentation prefers over the older subquery loading. Two queries instead of N+1, no row multiplication.
• Outside ORMs, apply the same idea: collect the keys first, fetch them in one call, then map results back to the items (the DataLoader pattern); for remote APIs, use batch endpoints or bounded concurrency.
• The Django optimisation guide recommends understanding when querysets are evaluated and which attributes are cached, and applying select_related() and prefetch_related() where needed, possibly in managers, with the caveat that related-object access uses the base manager rather than the default one.

Pitfalls

Joining a collection repeats the parent row per child and can be slower than two queries. Prefetching everything wastes memory on fields nobody reads. An IN list with tens of thousands of ids needs chunking. Query count is a property of the code path, not of the model: one extra attribute in a serializer reintroduces N+1, which is why the count assertion belongs in the test suite permanently.