As a Django team lead, what policy would you set so that N+1 query regressions are caught before a release rather than by users?
answer
- count must not grow with data
- hot endpoints get a budget
- capture in CI, not in production
- strict fetching where it pays
basics
~20 sMake query volume a tested property: CI captures the queries of each important page at two data sizes and fails if the count grows with the data or exceeds its budget, backed by visible SQL in development and fetch restrictions on hot paths.
solid answer
~50 sI treat query count as a contract rather than something found in production. For the handful of pages and endpoints that matter, CI runs the request under `CaptureQueriesContext` twice, with N and 2N rows; if the count grows with the data, it is an N+1 and the build fails, whatever the absolute number. On top of that each endpoint gets a budget recorded next to its test, so a new lazy relation shows up as a diff in review. Development settings keep SQL visible, and on Django 6.1 performance-critical read paths can opt into `FETCH_RAISE`, so an unplanned lazy load of a foreign key or deferred field raises `FieldFetchBlocked` in tests; related managers are not covered, so it complements the count checks. The tradeoff is maintenance: exact counts are brittle, so I prefer scaling checks plus coarse budgets, and I limit them to endpoints where latency matters.
go deeper
Know that query counts can be tested automatically and that tiny test data hides per-row queries.
Explain how a capture around a test request produces a count and why comparing two data sizes exposes a per-row query.
Design the checks for a real codebase: which endpoints get budgets, how fixtures scale, how to keep counts from becoming blind bumps.
Own the tradeoff between brittleness and coverage, set the default for the organisation, and decide where strict fetching is mandatory versus optional.
## Why this needs a policy at all An N+1 is almost never written on purpose. It appears when someone adds `{{ lesson.room.name }}` to a template, adds a relation to a model's `__str__`, or reuses a queryset in a new view. Code review cannot see it, because the Python and template code look fine; the damage is only visible in the SQL the ORM emits. A lead's job is to make that SQL visible at the right moment, cheaply, without drowning the team in brittle tests. There is no single right policy. What follows is one defensible shape and the tradeoffs behind it. ## Layer 1: a scaling check, not just a number The most robust signal is **how the count scales**, not what it is: 1. Build a fixture generator for the endpoint (for the timetable: classes, periods, lessons, teachers). 2. Request the page with N rows under `CaptureQueriesContext(connection)` and record `len(ctx)`. 3. Double the data and request again. 4. Fail if the second count is larger. A page whose count grows with its rows has a per-row query, whatever the absolute number. This survives unrelated changes (a new middleware lookup adds one query to both runs) and catches exactly the regression that matters. ## Layer 2: budgets for the endpoints that matter For the hot pages and API endpoints, also record an **absolute budget** in the test. Django's `assertNumQueries` is the standard way to pin an exact count; many teams prefer a helper that asserts `len(ctx) <= budget` so small legitimate changes do not break the build. The value of the budget is less the failure than the **diff**: raising a budget from 6 to 7 in a pull request forces someone to say why. - Keep budgets to the endpoints where latency or database load matters; budgeting every view creates churn and people start bumping numbers without reading them. - Put the budget next to the endpoint's own tests so ownership is obvious. ## Layer 3: visibility during development - Development settings run with `DEBUG = True`, so `connection.queries` and the team's usual SQL panel show counts while people work. - A shared fingerprinting helper (normalise literals, count shapes) turns "this page is slow" into "this statement repeats 380 times" in minutes. - Reviewers are encouraged to ask for the captured count when a change touches a list template or a serializer. ## Layer 4: strict fetching where it pays (Django 6.1) Django 6.1 added `QuerySet.fetch_mode()`. Using `models.FETCH_RAISE` on a performance-critical read makes an unplanned lazy load of a foreign key, a one-to-one, a generic relation or a deferred field raise `FieldFetchBlocked` instead of silently querying, so tests fail at the exact line. It is opt-in per QuerySet, which is the point: apply it where a lazy load is always a bug, not everywhere. It does **not** cover everything: fetch modes do not affect related managers' queries, so a `period.lesson_set.all()` inside a loop still runs one query per row. That is why strict fetching complements the scaling check rather than replacing it. ## Tradeoffs to own | Choice | Buys | Costs | |---|---|---| | Exact counts everywhere | Every change is visible | Brittle; people bump numbers blindly | | Scaling check only | Robust, catches true N+1 | Misses a constant but wasteful query | | Coarse budgets on hot paths | Cheap, reviewable | Needs an owner per endpoint | | Strict fetching on hot reads | Fails at the line | Needs deliberate loading everywhere it is used | | Production-only monitoring | Real traffic | Users find the problem first | A reasonable default: scaling checks on every list endpoint, budgets on the top ten, strict fetching on a few hot read paths, and production query metrics as the backstop rather than the detector. ## What to avoid - **Turning `DEBUG` on anywhere near production** to look at `connection.queries`; it is a development tool and leaks far more than SQL. - **Tests with tiny fixtures.** Three rows hide a per-row query that 300 rows expose. - **Treating the policy as a substitute for design.** The checks tell you a query regressed; choosing how to load related data is still a per-view decision.
- Why is comparing the query count at two data sizes more robust than pinning one exact number?An exact number fails on every legitimate change (an extra permission check, a new middleware lookup) and teaches people to bump it without reading. Comparing N rows with 2N rows cancels those constant costs out and fails only when the count depends on the data, which is precisely the per-row pattern you care about.
- Where would you not use FETCH_RAISE, even on Django 6.1?On code paths where an occasional lazy load is acceptable and cheap, such as admin pages or detail views touching one related object, and on shared querysets used by many callers with different needs. There the exception becomes noise that pushes people to disable it. Keep it for hot list reads where every lazy load is a bug.
saying these in an interview costs you the question
- Code review alone reliably catches N+1 regressions
- Pin an exact query count on every view in the project
- Turn DEBUG on in production briefly to check query counts
- Small fixtures are fine because query shape does not depend on rows