Query discipline: performance you can see

The single most common performance problem in Django applications is not slow algorithms or heavy computation — it is too many database queries, issued without the developer noticing, because the ORM hides them. A page that looks instant on your laptop with ten rows quietly fires hundreds of queries on production data. Query discipline is the habit of seeing your queries and keeping them few — and it is probably the highest-value engineering skill this whole course teaches. This lesson brings the ORM's performance lessons together into a working practice.

The core discipline: know your query count

The habit that separates a competent Django developer from a beginner is simple: you know how many queries your page makes, and why. Not a guess — a number you have looked at. The ORM's convenience is also its trap: appointment.doctor.name looks free, but it is a database query, and one inside a loop is N queries. The only defence is to measure, and Django gives you the tools:

• Django Debug Toolbar — the essential development tool; it shows, on every page, the query count, each query, its time, and — crucially — duplicate queries (the signature of N+1). Install it first on any real project.
• connection.queries — with DEBUG=True, len(connection.queries) is the exact count in a block of code (how this course measured N+1 at 6 → 1 → 2).
• assertNumQueries(n) — in a test, asserts a block runs exactly n queries, so a regression that reintroduces N+1 fails the build.

If you cannot answer "how many queries does this page make?", you are not yet in control of its performance. Make the count visible, and the problems become obvious.

The recurring offenders, and their fixes

Nearly all query problems are a handful of patterns you have already met — collected here as the checklist to run against any view touching the database:

• N+1 across a relationship in a loop → select_related (to-one) / prefetch_related (to-many). The most common bug by far; verified earlier at 6 queries dropping to 1. If a template or view loops over objects and touches a related object, this is almost certainly present.
• Summarising in Python instead of the database → aggregate/annotate. Looping to sum or count loads every row; the database can do it in one query (verified: Sum("fee") returns one number, not five rows).
• Read-modify-write in a loop → F() expressions or a single bulk .update(). Saving each object individually is N queries and risks the lost-update race.
• Fetching columns or rows you do not use → .only()/.values() to fetch fewer columns, and filter in the database, not by loading everything and filtering in Python.
• A missing index on a filtered/sorted column → add the index (verified with EXPLAIN showing USING INDEX), but only after confirming the query is actually slow.

You do not need to memorise these as rules — they all come from one instinct: do the work in the database, in as few queries as possible, and never cross a relationship inside a loop without eager-loading it.

Measure first, optimise second, cache last

The order of operations matters, and getting it wrong wastes effort:

1. Measure. Find the actually-slow page (Debug Toolbar, slow-query log) and its query count. Do not optimise on a hunch — the slow page is often not the one you expected.
2. Fix the queries. Apply the checklist above — usually select_related/prefetch_related or moving a summary into annotate. This resolves the large majority of real performance problems, because the problem is almost always query count, not query speed.
3. Add indexes where EXPLAIN shows a scan on a filtered column.
4. Cache only what is still expensive after all of the above, and only where staleness is acceptable (the caching lesson). Caching a page you never optimised hides a fixable problem behind an invalidation problem.

The discipline is to work top-down: most slow pages are fixed at step 2, for free, by removing needless queries — long before caching or clever tricks are warranted. Reaching for a cache first is treating the symptom.

Make it a habit, not a rescue

The mistake is treating query performance as something you fix after a page is slow in production. By then, real users have felt it. Instead, build the habit into how you work:

• Keep the Debug Toolbar open while developing a data-heavy page, and glance at the query count as you build it. A count that jumps when you add a loop is N+1 appearing in real time.
• Write an assertNumQueries test for pages where the count matters, so a future change that reintroduces N+1 fails CI, not production.
• Review your own queries before you ship: for any view with a loop over objects, ask "does this cross a relationship? then is it eager-loaded?"

This is the same "measure, do not guess" instinct as the rest of engineering, applied to the one place Django most tempts you to guess. A developer who watches their query count ships fast pages by default; one who does not ships pages that work in development and crawl in production. That difference — and the habit behind it — is exactly what makes a Django developer worth hiring.

Check your work

The core discipline. Know your page's query count and why — measured, not guessed; the ORM hides queries behind attribute access, so measuring is the only defence.

The tools. Django Debug Toolbar (query count + duplicates per page), connection.queries with DEBUG=True (exact count), assertNumQueries(n) (fails CI on a regression).

The recurring offenders. N+1 (→ select_related/prefetch_related), Python summation (→ aggregate/annotate), read-modify-write in a loop (→ F()/bulk update), over-fetching (→ only/values, filter in the DB), missing index (→ add after EXPLAIN).

The order. Measure → fix queries → index → cache last; most slow pages are fixed for free by removing needless queries, so caching first is treating the symptom.

Make it a habit. Debug Toolbar open while building, assertNumQueries tests where count matters, and self-review any loop that crosses a relationship — performance by default, not as a rescue.

Practice

1. Install the Debug Toolbar; open a data-heavy page and read its query count and any duplicate queries.
2. Introduce an N+1 (loop touching a related object), watch the count jump in the toolbar, then fix it with select_related/prefetch_related and watch it drop.
3. Write an assertNumQueries(1) test around an optimised view; remove the optimisation and confirm the test fails.
4. Replace a Python summation with aggregate; confirm (via query logging) it drops from N rows to one query.
5. Take a genuinely slow page and walk the four steps in order — measure, fix queries, index, and only then consider caching. Note how far step 2 alone gets you.
6. Review a view you wrote: for every loop, confirm whether it crosses a relationship and whether that is eager-loaded.

Official documentation

• Django — Database access optimization — The definitive checklist.
• Django Debug Toolbar — Seeing queries per page.
• Django — assertNumQueries — Guarding query counts in tests.

Next: errors, logging and failing loudly.