Django Module 4 – The ORM in depth: performance, integrity and PostgreSQL
N+1 queries, prefetching and 6.1 fetch modes
Count and fix Django N+1 queries with select_related, prefetch_related, Prefetch and only(), and use the 6.1 fetch modes FETCH_PEERS and FETCH_RAISE.
What you will learn
- Detect N+1 queries by counting them with CaptureQueriesContext and assertNumQueries
- Fix them with select_related() for foreign keys and prefetch_related() or Prefetch for reverse and many-to-many relations
- Load fewer columns with only() and defer(), and predict what reading a deferred field costs
- Choose the Django 6.1 fetch modes FETCH_PEERS and FETCH_RAISE for a QuerySet or as a manager's default
- Write a test that pins a page's query budget and fails when an N+1 comes back
Before you start
What you’ll learn · Before you start
What you will learn
- Detect N+1 queries by counting them with CaptureQueriesContext and assertNumQueries
- Fix them with select_related() for foreign keys and prefetch_related() or Prefetch for reverse and many-to-many relations
- Load fewer columns with only() and defer(), and predict what reading a deferred field costs
- Choose the Django 6.1 fetch modes FETCH_PEERS and FETCH_RAISE for a QuerySet or as a manager's default
- Write a test that pins a page's query budget and fails when an N+1 comes back
Before you start
On this page
A notice board that lists 30 notices with the name of each poster sent 31 queries to the database: one for the notices, then one for every poster. Nothing in the code looks wrong, the page is right, and the cost grows with every notice added; with 200 notices it would be 201 queries.
You will count the queries of a loop and see where an N+1 comes from, fix it with select_related(),
prefetch_related() and Prefetch, leave columns out with only() and defer() without creating new queries, use
the fetch modes new in Django 6.1, FETCH_PEERS and FETCH_RAISE, and pin a page’s query budget in a test. Every
count on this page comes from a run on Django 6.1.2, with SQLite 3.51.0 as the database.
A loop over 30 notices sent 31 queries
The board reads 30 notices and, for each, notice.posted_by.name. The rows come from one file that every example
imports first: 6 committee members, 30 notices and 43 comments.
"""The notice board the examples of this lesson read: 6 residents of the committee, 30 notices and 43 comments, the
same on every run.
Importing it starts Django, creates every table with migrate and adds the rows to an empty database; running it as a
script does the same. compact() prints a query's SQL shorter, so that it fits the page: without the double quotes and
without the app prefix of the table names (society_, notices_). The SQL that was sent is the same, with them.
"""
import os
import re
from datetime import UTC, datetime, timedelta
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
import django # noqa: E402
django.setup()
from django.core.management import call_command # noqa: E402
from notices.models import Comment, Notice # noqa: E402
from society.models import Resident # noqa: E402
call_command('migrate', verbosity=0)
def compact(sql):
"""SELECT "notices_notice"."title" FROM "notices_notice" -> SELECT notice.title FROM notice"""
return re.sub(r'"(?:society|notices)_(\w+)"', r'\1', sql).replace('"', '')
COMMITTEE = [('Asha K.', 'A-101'), ('Imran Q.', 'B-101'), ('Meera P.', 'A-1101'),
('Rohan M.', 'B-1205'), ('Neha D.', 'B-1203'), ('Vikram B.', 'A-102')]
TOPICS = ['Water tank cleaning', 'Lift maintenance', 'Power cut', 'Parking rules', 'Festival plans', 'Pest control']
START = datetime(2026, 9, 1, 9, 0, tzinfo=UTC)
if not Resident.objects.exists():
people = [Resident.objects.create(name=name, flat=flat) for name, flat in COMMITTEE]
for i in range(30):
notice = Notice.objects.create(
title=f'{TOPICS[i % 6]} ({i + 1})',
body=f'Notice {i + 1} of the society. ' + 'Details for the residents of every tower. ' * 40,
posted_by=people[i % 6],
posted_at=START + timedelta(days=i),
)
for j in range(i % 4): # 0, 1, 2 or 3 comments
Comment.objects.create(notice=notice, author=people[(i + j + 1) % 6], text=f'Reply {j + 1}',
posted_at=notice.posted_at + timedelta(hours=j + 1))This program runs the same loop five ways and counts the queries of each with CaptureQueriesContext, the context
manager behind Django’s query-count assertions. Look at the first way, the default, for now:
"""One loop, five ways: the board reads each notice's poster, notice.posted_by.name, and the program counts the
queries each way sends, with the first two of them.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import warnings
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.core.exceptions import FieldFetchBlocked
from django.db import connection, models
from django.test.utils import CaptureQueriesContext
from notices.models import Notice
from seed import compact
ways = {
'default (FETCH_ONE)': Notice.objects.order_by('posted_at'),
"select_related('posted_by')": Notice.objects.select_related('posted_by').order_by('posted_at'),
"prefetch_related('posted_by')": Notice.objects.prefetch_related('posted_by').order_by('posted_at'),
'fetch_mode(FETCH_PEERS)': Notice.objects.fetch_mode(models.FETCH_PEERS).order_by('posted_at'),
'fetch_mode(FETCH_RAISE)': Notice.objects.fetch_mode(models.FETCH_RAISE).order_by('posted_at'),
}
for name, notices in ways.items():
with CaptureQueriesContext(connection) as queries:
try:
posters = [notice.posted_by.name for notice in notices]
result = f'{len(posters)} names'
except FieldFetchBlocked as error:
result = f'FieldFetchBlocked: {error}'
count = f'{len(queries)} {"query" if len(queries) == 1 else "queries"}'
print(f'{name:<30} {count:>10} {result}')
for query in queries[:2]:
print(' ', compact(query['sql']))
with warnings.catch_warnings(record=True) as caught:
warnings.simplefilter('always')
Notice.objects.select_related()
print('select_related() with no arguments:', caught[0].category.__name__, '|', caught[0].message)Output
default (FETCH_ONE) 31 queries 30 names
SELECT notice.id, notice.title, notice.body, notice.posted_by_id, notice.posted_at FROM notice ORDER BY notice.posted_at ASC
SELECT resident.id, resident.name, resident.flat FROM resident WHERE resident.id = 1 LIMIT 21
select_related('posted_by') 1 query 30 names
SELECT notice.id, notice.title, notice.body, notice.posted_by_id, notice.posted_at, resident.id, resident.name, resident.flat FROM notice INNER JOIN resident ON (notice.posted_by_id = resident.id) ORDER BY notice.posted_at ASC
prefetch_related('posted_by') 2 queries 30 names
SELECT notice.id, notice.title, notice.body, notice.posted_by_id, notice.posted_at FROM notice ORDER BY notice.posted_at ASC
SELECT resident.id, resident.name, resident.flat FROM resident WHERE (resident.id) IN ((1), (2), (3), (4), (5), (6))
fetch_mode(FETCH_PEERS) 2 queries 30 names
SELECT notice.id, notice.title, notice.body, notice.posted_by_id, notice.posted_at FROM notice ORDER BY notice.posted_at ASC
SELECT resident.id, resident.name, resident.flat FROM resident WHERE (resident.id) IN ((1), (2), (3), (4), (5), (6))
fetch_mode(FETCH_RAISE) 1 query FieldFetchBlocked: Fetching of Notice.posted_by blocked.
SELECT notice.id, notice.title, notice.body, notice.posted_by_id, notice.posted_at FROM notice ORDER BY notice.posted_at ASC
select_related() with no arguments: RemovedInDjango2028Warning | Calling select_related() with no arguments is deprecated. Specify the fields to fetch instead.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 query_counts.py
The first query read the notices, and each row held posted_by_id, the id of its poster, but not the poster. Reading
notice.posted_by then needed the resident’s own row, so Django sent SELECT … FROM resident WHERE resident.id = 1,
and the same again for the next notice. Step through the loop:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- The notices. Reading the QuerySet sends one SELECT for the 30 notices. Each row holds posted_by_id, the id of its poster, but not the poster.
- The first poster. notice.posted_by on the first notice needs the resident's own row, so Django sends a SELECT for resident 1. FETCH_ONE fetches it for this one notice.
- The second poster. The second notice is a different object with its own empty cache, so its poster costs another query, for resident 2.
- The same poster again. The seventh notice was posted by resident 1 again, and still sends a query: each notice remembers only its own poster, not the posters other notices loaded.
- 31 queries. After the 30th notice the loop has sent 1 query for the notices and 30 for their posters: the N+1 of its name, although only 6 residents posted.
- The same loop, joined. With select_related('posted_by') the first query joins the resident table, every notice arrives with its poster, and the loop sends nothing more: 1 query.
That is the N+1 queries: One query that reads N rows, then one more query for each of them to read something the first one left out, such as each notice's poster. The number of queries grows with the rows a page reads.: 1 query for N rows, then 1 more for each row. Only 6 residents
posted, and it did not matter. Each notice is its own object with its own cache, and the default fetch mode,
FETCH_ONE, fetches a missing field for the current instance only. In a template the loop is hidden:
{{ notice.posted_by.name }} reads the relation, and Django’s documentation notes that templates call methods for
you, so an attribute and a query look alike. Tools such as django-debug-toolbar, which Django’s documentation
suggests, list the SQL queries of a page with their times; this lesson counts them in code.
Key idea
An N+1 is one query for the rows and one more per row for something the first query left out, so its cost grows with the rows a page reads, not with how many different related objects there are.
Checkpoint
Check: The N+1 queries problem
Joins for foreign keys, one more query for reverse relations
The second and third ways of the same run fixed the poster loop. select_related('posted_by') joined the resident table
into the first query, so every notice arrived with its poster: 1 query. prefetch_related('posted_by') read the
notices, then the posters of all of them at once, WHERE resident.id IN (1, 2, 3, 4, 5, 6), and matched them to the
notices in Python: 2 queries.
The comments are the other way round. notice.comments is a reverse relation, with many rows per notice, and a join
cannot bring a list into one row, so select_related() cannot follow it:
"""The comments of every notice: a reverse relation, which select_related() cannot follow and fetch modes do not
batch. prefetch_related() and Prefetch objects read them in one more query; filter() on the prefetched comments throws
that away again.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.db import connection, models
from django.db.models import Prefetch
from django.test.utils import CaptureQueriesContext
from notices.models import Comment, Notice
from seed import compact
def count(label, read):
with CaptureQueriesContext(connection) as queries:
result = read()
print(f'{label:<52} {len(queries):>2} {"query" if len(queries) == 1 else "queries"} {result}')
return queries
notices = Notice.objects.order_by('posted_at')
count('notice.comments.all(), default', lambda: sum(len(n.comments.all()) for n in notices.all()))
count('notice.comments.all(), FETCH_PEERS',
lambda: sum(len(n.comments.all()) for n in notices.fetch_mode(models.FETCH_PEERS)))
count('notice.comments.all(), FETCH_RAISE',
lambda: sum(len(n.comments.all()) for n in notices.fetch_mode(models.FETCH_RAISE)))
queries = count("prefetch_related('comments')",
lambda: sum(len(n.comments.all()) for n in notices.prefetch_related('comments')))
print(' ', compact(queries[1]['sql']))
count("prefetch_related('comments'), then .count()",
lambda: sum(n.comments.count() for n in notices.prefetch_related('comments')))
count("prefetch_related('comments'), then .filter()",
lambda: sum(len(n.comments.filter(text='Reply 1')) for n in notices.prefetch_related('comments')))
thread = Prefetch('comments', queryset=Comment.objects.select_related('author').order_by('posted_at'),
to_attr='thread')
queries = count("Prefetch(..., select_related('author'), to_attr)",
lambda: sorted({c.author.name for n in notices.prefetch_related(thread) for c in n.thread}))
print(' ', compact(queries[1]['sql']))
count("prefetch_related('comments__author')",
lambda: len({c.author.name for n in notices.prefetch_related('comments__author') for c in n.comments.all()}))Output
notice.comments.all(), default 31 queries 43
notice.comments.all(), FETCH_PEERS 31 queries 43
notice.comments.all(), FETCH_RAISE 31 queries 43
prefetch_related('comments') 2 queries 43
SELECT comment.id, comment.notice_id, comment.author_id, comment.text, comment.posted_at FROM comment WHERE comment.notice_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)
prefetch_related('comments'), then .count() 2 queries 43
prefetch_related('comments'), then .filter() 32 queries 22
Prefetch(..., select_related('author'), to_attr) 2 queries ['Asha K.', 'Imran Q.', 'Meera P.', 'Neha D.', 'Rohan M.', 'Vikram B.']
SELECT comment.id, comment.notice_id, comment.author_id, comment.text, comment.posted_at, resident.id, resident.name, resident.flat FROM comment INNER JOIN resident ON (comment.author_id = resident.id) WHERE comment.notice_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) ORDER BY comment.posted_at ASC
prefetch_related('comments__author') 3 queries 6
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 comments.py
prefetch_related('comments') read the 43 comments of the 30 notices in one more query, WHERE comment.notice_id IN (1, …, 30), and .count() on the prefetched comments used them without a query. .filter() did not: it builds a new
QuerySet, which asks the database again, so the prefetch was wasted and the loop sent 32 queries. To load only some
related rows, or in an order, give a Prefetch its own queryset, as the next line did, with the authors joined in by
select_related('author') and the list kept apart under to_attr='thread': 2 queries. 'comments__author' without a
Prefetch took 3, one per level.
The first three lines show what fetch modes do not change: a related manager such as notice.comments sent 31 queries
under every mode. Answer three questions about your own loop, and the table marks the fixes that fit:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Fix | Foreign key forward | Reverse or many-to-many | Only some related rows | On Django 5.2 LTS |
|---|---|---|---|---|
| select_related() Queries: 1, a JOIN | Good one query with a JOIN: the 30 posters cost nothing more | Poor cannot follow a reverse relation, which has many rows per notice | Poor joins the related row as it is; there is nothing to filter | Good there since long before 5.2 |
| prefetch_related() Queries: 1 more per relation | Good works too: a second query with WHERE id IN (…) | Good the 43 comments in one more query, joined in Python | Poor filter() on the prefetched manager queries again: 32 queries | Good there since long before 5.2 |
| Prefetch(queryset=…) Queries: 1 more per relation | Mixed works, though select_related() is simpler for a foreign key | Good one query, ordered and joined to the authors as you choose | Good its queryset filters and orders; to_attr keeps the list apart | Good there since long before 5.2 |
| FETCH_PEERS Queries: 1 more when first read | Good the first read fetches the posters of all 30 notices: 2 queries | Poor related managers are not batched: comments still sent 31 queries | Poor fetches the whole related row, the same for every notice | Poor new in Django 6.1 |
| annotate(Count(…)) Queries: 1, a GROUP BY | Mixed gives numbers about the relation, not its objects | Good a comment count per notice in the same query | Good Count('comments', filter=Q(…)) counts some rows only | Good there since long before 5.2 |
| FETCH_RAISE Queries: none; reading raises | Mixed fixes nothing: reading posted_by raised FieldFetchBlocked | Mixed related managers' queries are not blocked by it | Mixed a guard for tests and hot paths, not a way to load rows | Poor new in Django 6.1 |
How the answers decide
- “Which way does the loop follow the relation?” Backward or many-to-many, notice.comments: rules out select_related() and FETCH_PEERS. A JOIN cannot bring a list of comments into one row, and fetch modes leave related managers such as notice.comments alone; that loop sent 31 queries under every mode.
- “Which way does the loop follow the relation?” Backward or many-to-many, notice.comments: rules out FETCH_RAISE. FETCH_RAISE loads nothing, and it does not block the queries of related managers either, so the comments loop still sent its 31 queries.
- “Which way does the loop follow the relation?” Forward, notice.posted_by: adds a caveat to FETCH_RAISE. FETCH_RAISE loads nothing; reading posted_by raised FieldFetchBlocked. Keep it beside a real fix, in tests and measured code, so a forgotten join fails loudly.
- “Does the page need every related row as it is, or only some of them, or only a count?” Every related row: rules out annotate(Count(…)). A Count annotation says how many related rows there are, not what they hold; a page that shows the rows needs a join or a prefetch.
- “Does the page need every related row as it is, or only some of them, or only a count?” Only some, or in an order: rules out prefetch_related(), FETCH_PEERS and select_related(). Filtering a prefetched manager sends new queries; a Prefetch with its own queryset loads exactly the rows the page shows.
- “Does the page need every related row as it is, or only some of them, or only a count?” Only some, or in an order: rules out annotate(Count(…)). A Count annotation gives a number per row, never the related rows themselves; showing some of them needs a Prefetch with its own queryset.
- “Does the page need every related row as it is, or only some of them, or only a count?” Only how many: adds a caveat to prefetch_related() and Prefetch(queryset=…). Loading every comment only to count it reads rows the page never shows; a Count annotation answers in the same query.
- “Must the code also run on Django 5.2 LTS?” Yes, on 5.2 as well: rules out FETCH_PEERS and FETCH_RAISE. Fetch modes are new in Django 6.1; on 5.2 LTS the loop needs select_related(), prefetch_related() and a query-count test.
- “Which way does the loop follow the relation?” Forward, notice.posted_by and “Does the page need every related row as it is, or only some of them, or only a count?” Every related row: adds a caveat to prefetch_related() and Prefetch(queryset=…). Both work for a foreign key, with a second query; select_related() does it in the first query with a JOIN.
In short: select_related() for a foreign key you follow forwards, prefetch_related() for reverse and many-to-many
relations, a Prefetch when only some related rows or an order matter, and a Count annotation when the page needs
only how many.
Checkpoint
Check: select_related() and prefetch_related()
only() and defer() leave columns behind, and reading one is a query
A list of notices shows titles, and each notice also has a body of about 1,700 characters that the list never shows.
only('title') loads the fields it names, and always the primary key; defer('body') loads every field except the
ones it names. A field left out is a deferred field: A model field that a query left out with only() or defer(). Its value stays in the database until code reads it, and that read is a query of its own unless a fetch mode batches it., and reading it later is a query,
exactly like a foreign key:
"""only() and defer() leave columns out of the SELECT. Reading a column that was left out is a query of its own, the
same N+1 as a foreign key, and the fetch modes of Django 6.1 decide what it costs.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.core.exceptions import FieldFetchBlocked
from django.db import connection, models
from django.db.models import Sum
from django.db.models.functions import Length
from django.test.utils import CaptureQueriesContext
from notices.models import Notice
from seed import compact
with CaptureQueriesContext(connection) as queries:
titles = [notice.title for notice in Notice.objects.only('title')]
print(f"only('title'), titles read: {len(queries)} query, {len(titles)} titles")
print(' ', compact(queries[0]['sql']))
total = Notice.objects.aggregate(characters=Sum(Length('body')))['characters']
print(f"the 30 bodies hold {total:,} characters, which only('title') left in the database")
for label, notices in [('default (FETCH_ONE)', Notice.objects.only('title')),
('FETCH_PEERS', Notice.objects.only('title').fetch_mode(models.FETCH_PEERS)),
('FETCH_RAISE', Notice.objects.only('title').fetch_mode(models.FETCH_RAISE))]:
with CaptureQueriesContext(connection) as queries:
try:
sizes = [len(notice.body) for notice in notices]
result = f'{len(sizes)} bodies'
except FieldFetchBlocked as error:
result = f'FieldFetchBlocked: {error}'
print(f"only('title'), bodies read, {label:<20} {len(queries):>2} {'query' if len(queries) == 1 else 'queries'}"
f' {result}')
if len(queries) == 2:
print(' ', compact(queries[1]['sql'])[:120], '…')Output
only('title'), titles read: 1 query, 30 titles
SELECT notice.id, notice.title FROM notice
the 30 bodies hold 51,171 characters, which only('title') left in the database
only('title'), bodies read, default (FETCH_ONE) 31 queries 30 bodies
only('title'), bodies read, FETCH_PEERS 2 queries 30 bodies
SELECT notice.id AS pk, notice.body AS body FROM notice WHERE notice.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1 …
only('title'), bodies read, FETCH_RAISE 1 query FieldFetchBlocked: Fetching of Notice.body blocked.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 deferred.py
| Code | SELECT | Reading notice.body for all 30 |
|---|---|---|
Notice.objects.all() |
every column, bodies included | nothing more: 1 query in all |
only('title') |
notice.id, notice.title |
1 query per body: 31 in all |
only('title') with FETCH_PEERS |
notice.id, notice.title |
one query for all 30 bodies: 2 in all |
only('title') with FETCH_RAISE |
notice.id, notice.title |
FieldFetchBlocked at the first body |
Leaving the bodies out kept 51,171 characters in the database, which is the point of only(). Reading them in a loop
turned 1 query into 31, which Django’s documentation calls a pessimization: defer only what the page will not read,
such as large text, and measure before and after.
Deferring a field the template reads
only('title') on the board’s QuerySet, with a template that also prints {{ notice.body|truncatewords:20 }},
sends one query per notice for the bodies. Defer only the fields no code on the page reads, and check with a query
count, not by reading the view.
Checkpoint
Check: only() and defer()
Fetch modes decide what reading a missing field costs
Django 6.1 made the on-demand fetch configurable. A fetch mode: Django 6.1's setting of a QuerySet, chosen with fetch_mode(), for reading a field the query did not load: FETCH_ONE fetches it for one instance, FETCH_PEERS for every instance of the same QuerySet, FETCH_RAISE refuses., set with
QuerySet.fetch_mode(), decides what happens when code reads a field the query did not load: a foreign key, a
one-to-one field and its reverse accessor, a deferred field or a generic relation. The fourth and fifth
ways of query_counts.py used the two new ones, and the first read of a poster goes three ways:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- A field that was not loaded. The 30 notices came from one query, which held posted_by_id but not the resident. Reading notice.posted_by is a field Django must fetch, and the fetch mode says how.
- FETCH_ONE, the default. FETCH_ONE fetches the poster of this one notice: SELECT … WHERE id = 1. Every other notice will do the same when the loop reaches it, 30 queries in all.
- FETCH_PEERS. FETCH_PEERS fetches the poster of every notice that came from the same QuerySet, its peers, in one query: WHERE id IN (1, 2, 3, 4, 5, 6). The other 29 reads cost nothing.
- FETCH_RAISE. FETCH_RAISE fetches nothing and raises FieldFetchBlocked: "Fetching of Notice.posted_by blocked." The loop stops at the first notice, after the 1 query that read the notices.
- The mode travels. Objects fetched from an instance get its mode: comments read with FETCH_PEERS fetched their notices and those notices' posters the same way, 3 queries instead of 87.
FETCH_PEERS fetched the poster of every notice from the same QuerySet, its peers, when the first one was read: the
same IN query that prefetch_related() sent, without naming the relation in advance. FETCH_RAISE refused with
FieldFetchBlocked: Fetching of Notice.posted_by blocked., and the deferred bodies too, “Fetching of Notice.body
blocked.” Use it where an extra query must never happen unnoticed: in tests, and in code you have measured.
A mode can be a model’s default through its manager, and it travels: objects fetched from an instance get its mode.
nivas/fetch_mode_default.py
"""A fetch mode as the default of a manager, Comment.with_peers in notices/models.py, and how it travels: the comments
are read with FETCH_PEERS, and so are the notices and the residents reached from them. FETCH_RAISE travels the same
way.
Run it next to manage.py. seed.py makes db.sqlite3 there with every table and the lesson's rows.
"""
import seed # noqa: F401 (starts Django, creates the tables, adds the rows)
from django.core.exceptions import FieldFetchBlocked
from django.db import connection, models
from django.test.utils import CaptureQueriesContext
from notices.models import Comment
for label, queryset in [('Comment.objects, FETCH_ONE', Comment.objects.order_by('pk')),
('Comment.with_peers', Comment.with_peers.order_by('pk'))]:
with CaptureQueriesContext(connection) as queries:
posters = {comment.notice.posted_by.name for comment in queryset}
print(f'{label:<28} {len(queries):>2} queries for comment -> notice -> poster ({len(posters)} posters)')
with CaptureQueriesContext(connection) as queries:
try:
for comment in Comment.objects.select_related('notice').fetch_mode(models.FETCH_RAISE):
comment.notice.title # loaded by select_related: no query
comment.notice.posted_by # one relation further, not loaded
except FieldFetchBlocked as error:
print(f'FETCH_RAISE, select_related(notice), then notice.posted_by: {len(queries)} query, {error}')Output
Comment.objects, FETCH_ONE 87 queries for comment -> notice -> poster (6 posters) Comment.with_peers 3 queries for comment -> notice -> poster (6 posters) FETCH_RAISE, select_related(notice), then notice.posted_by: 1 query, Fetching of Notice.posted_by blocked.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 fetch_mode_default.py
notices/models.py · nivas/notices/models.py
from django.db import models
from society.models import Resident
class Notice(models.Model):
title = models.CharField(max_length=120)
body = models.TextField()
posted_by = models.ForeignKey(Resident, on_delete=models.PROTECT, related_name='notices')
posted_at = models.DateTimeField()
def __str__(self):
return self.title
class PeersManager(models.Manager):
"""Every QuerySet of this manager starts with the FETCH_PEERS fetch mode of Django 6.1."""
def get_queryset(self):
return super().get_queryset().fetch_mode(models.FETCH_PEERS)
class Comment(models.Model):
notice = models.ForeignKey(Notice, on_delete=models.CASCADE, related_name='comments')
author = models.ForeignKey(Resident, on_delete=models.CASCADE, related_name='comments')
text = models.CharField(max_length=300)
posted_at = models.DateTimeField()
objects = models.Manager() # the default manager, as before
with_peers = PeersManager() # a model whose every query should batch would declare objects = PeersManager()
def __str__(self):
return f'comment {self.pk}'Comment.with_peers is such a manager. Reading each comment’s notice and that notice’s poster took 87 queries with
FETCH_ONE, 1 + 43 + 43, and 3 with FETCH_PEERS: the comments, the notices of all of them, the posters of those
notices. FETCH_RAISE travelled the same way: select_related('notice') loaded the notices, and reading
notice.posted_by, one relation further, raised.
Fetch modes are new in Django 6.1
Django 5.2 LTS has no fetch_mode(); there, select_related(), prefetch_related() and query-count tests do the
same work. Django 6.1 also deprecates select_related() with no arguments, which followed every non-null foreign
key: the last line of query_counts.py caught its RemovedInDjango2028Warning. Name the relations, or use
FETCH_PEERS. Setting ModelAdmin.list_select_related = True is deprecated as well.
Checkpoint
Check: The 6.1 fetch modes
A query budget in a test keeps the N+1 from coming back
An N+1 fixed today comes back the day someone adds a field to a template. A test that counts the queries of a page
catches that day. assertNumQueries(2) checks that exactly 2 queries run inside its with block; here the block
wraps a request to the board, which shows the five newest notices with their posters and comment counts:
The test, run twice · nivas/budget_session.py
"""The query budget of the notice board as a test, run twice in a terminal: against the board as it is, which reads
each notice's poster and comments one notice at a time, and again after the fix in fixed/views.py is copied over it.
unittest prints how long the tests took ("Ran 1 test in 0.021s"); that time changes on every run.
"""
from session import run
run(
"""
python manage.py test notices
cp fixed/views.py notices/views.py
python manage.py test notices.tests
""",
fails=('python manage.py test notices',),
)Output
$ python manage.py test notices
Found 1 test(s).
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
F
======================================================================
FAIL: test_board_sends_two_queries (notices.tests.BoardQueryBudget.test_board_sends_two_queries)
The board reads its notices, posters and comments in 2 queries, however many notices it shows.
----------------------------------------------------------------------
Traceback (most recent call last):
File "notices/tests.py", line 24, in test_board_sends_two_queries
with self.assertNumQueries(2):
~~~~~~~~~~~~~~~~~~~~~^^^
AssertionError: 11 != 2 : 11 queries executed, 2 expected
Captured queries were:
1. SELECT "notices_notice"."id", "notices_notice"."title", "notices_notice"."body", "notices_notice"."posted_by_id", "notices_notice"."posted_at" FROM "notices_notice" ORDER BY "notices_notice"."posted_at" DESC LIMIT 5
2. SELECT "society_resident"."id", "society_resident"."name", "society_resident"."flat" FROM "society_resident" WHERE "society_resident"."id" = 1 LIMIT 21
3. SELECT "notices_comment"."id", "notices_comment"."notice_id", "notices_comment"."author_id", "notices_comment"."text", "notices_comment"."posted_at" FROM "notices_comment" WHERE "notices_comment"."notice_id" = 5
4. SELECT "society_resident"."id", "society_resident"."name", "society_resident"."flat" FROM "society_resident" WHERE "society_resident"."id" = 2 LIMIT 21
5. SELECT "notices_comment"."id", "notices_comment"."notice_id", "notices_comment"."author_id", "notices_comment"."text", "notices_comment"."posted_at" FROM "notices_comment" WHERE "notices_comment"."notice_id" = 4
6. SELECT "society_resident"."id", "society_resident"."name", "society_resident"."flat" FROM "society_resident" WHERE "society_resident"."id" = 1 LIMIT 21
7. SELECT "notices_comment"."id", "notices_comment"."notice_id", "notices_comment"."author_id", "notices_comment"."text", "notices_comment"."posted_at" FROM "notices_comment" WHERE "notices_comment"."notice_id" = 3
8. SELECT "society_resident"."id", "society_resident"."name", "society_resident"."flat" FROM "society_resident" WHERE "society_resident"."id" = 2 LIMIT 21
9. SELECT "notices_comment"."id", "notices_comment"."notice_id", "notices_comment"."author_id", "notices_comment"."text", "notices_comment"."posted_at" FROM "notices_comment" WHERE "notices_comment"."notice_id" = 2
10. SELECT "society_resident"."id", "society_resident"."name", "society_resident"."flat" FROM "society_resident" WHERE "society_resident"."id" = 1 LIMIT 21
11. SELECT "notices_comment"."id", "notices_comment"."notice_id", "notices_comment"."author_id", "notices_comment"."text", "notices_comment"."posted_at" FROM "notices_comment" WHERE "notices_comment"."notice_id" = 1
----------------------------------------------------------------------
Ran 1 test in 0.079s
FAILED (failures=1)
Destroying test database for alias 'default'...
[exit status 1]
$ cp fixed/views.py notices/views.py
$ python manage.py test notices.tests
Found 1 test(s).
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
.
----------------------------------------------------------------------
Ran 1 test in 0.079s
OK
Destroying test database for alias 'default'...
This output changes from run to run: unittest prints how long the tests took, which changes from run to run; everything else is the same on every run
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 budget_session.py
notices/tests.py · nivas/notices/tests.py
from datetime import UTC, datetime, timedelta
from django.test import TestCase
from society.models import Resident
from .models import Comment, Notice
class BoardQueryBudget(TestCase):
@classmethod
def setUpTestData(cls):
asha = Resident.objects.create(name='Asha K.', flat='A-101')
imran = Resident.objects.create(name='Imran Q.', flat='B-101')
start = datetime(2026, 9, 1, 9, 0, tzinfo=UTC)
for i in range(5):
notice = Notice.objects.create(title=f'Notice {i + 1}', body='…', posted_by=[asha, imran][i % 2],
posted_at=start + timedelta(days=i))
for j in range(2):
Comment.objects.create(notice=notice, author=asha, text='Noted', posted_at=notice.posted_at)
def test_board_sends_two_queries(self):
"""The board reads its notices, posters and comments in 2 queries, however many notices it shows."""
with self.assertNumQueries(2):
response = self.client.get('/notices/')
self.assertContains(response, 'posted by', count=5)notices/views.py · nivas/notices/views.py
from django.shortcuts import render
from .models import Notice
def board(request):
"""The five newest notices, each with who posted it and how many comments it has."""
notices = Notice.objects.order_by('-posted_at')[:5]
return render(request, 'notices/board.html', {'notices': notices})fixed/views.py · nivas/fixed/views.py
from django.shortcuts import render
from .models import Notice
def board(request):
"""The five newest notices, each with who posted it and how many comments it has."""
notices = Notice.objects.select_related('posted_by').prefetch_related('comments').order_by('-posted_at')[:5]
return render(request, 'notices/board.html', {'notices': notices})nivas/notices/templates/notices/board.html
<ul>
{% for notice in notices %}
<li>{{ notice.title }}, posted by {{ notice.posted_by.name }}, {{ notice.comments.all|length }} comments</li>
{% endfor %}
</ul>nivas/session.py
"""Replays a terminal session, so that the page can show exactly what each command printed.
Every line of a script is one command of a single bash session that starts in the folder of the script that calls
run(), so `cd` and `source .venv/bin/activate` work as they do in a terminal, and later lines see their effect. The
session prints "$ " and the command, then what the command printed (standard output and standard error together, as a
terminal shows them), and "[exit status N]" when it fails. A line that starts with "#" is printed as a comment.
Commands whose failure is the point are listed in `fails`; any other failure, or a listed command that succeeds,
makes the session exit with status 1, so a session that went wrong is never recorded as if it had gone right.
pip: when a folder named "wheelhouse" sits in the environment of the Python running this file, as it does where these
sessions are recorded, pip installs from that folder and never from the network (pip's own PIP_NO_INDEX and
PIP_FIND_LINKS settings). On your computer there is no such folder, and pip downloads the same files from PyPI. The
lines in which pip names where it found each file ("Collecting …", "Downloading …", "Processing …", "Looking in
links: …") depend on that, so they are left out; every other line is printed as pip wrote it.
serve() starts a development server the way you would in a second terminal, waits until it answers on its port, runs
the commands that talk to it, stops it as Ctrl+C would (SIGINT to its process group), then prints what the server's
own terminal showed. The date and time in that output are printed as "…", because they change on every run.
"""
import os
import re
import shlex
import signal
import socket
import subprocess
import sys
import time
from pathlib import Path
WHEELHOUSE = Path(sys.prefix) / "wheelhouse"
# pip lines that only say where pip found a file: PyPI on your computer, the wheelhouse where this is recorded.
_PIP_SOURCE = re.compile(r"^\s*(?:Collecting |Downloading |Processing |Looking in links: |Using cached |Obtaining )")
# runserver prints the date and time when it starts, and before each request it logs.
_SERVER_TIME = [
(re.compile(r"^[A-Z][a-z]+ \d{1,2}, \d{4} - \d{2}:\d{2}:\d{2}$"), "…"),
(re.compile(r"^\[\d{2}/[A-Z][a-z]{2}/\d{4} \d{2}:\d{2}:\d{2}\]"), "[…]"),
]
def _environment():
env = {k: v for k, v in os.environ.items() if not k.startswith(("PIP_", "PYTHON", "VIRTUAL_ENV", "DJANGO_"))}
env["PYTHONDONTWRITEBYTECODE"] = "1" # no __pycache__ folders in the listings
env["PYTHONUNBUFFERED"] = "1" # a server's lines reach its terminal at once
env["PIP_DISABLE_PIP_VERSION_CHECK"] = "1"
if WHEELHOUSE.is_dir():
env["PIP_NO_INDEX"] = "1"
env["PIP_FIND_LINKS"] = str(WHEELHOUSE)
return env
def _shell(commands, cwd, env):
"""Runs the commands in one bash; returns what the terminal showed and each command's exit status."""
read_end, write_end = os.pipe()
lines = ["exec 2>&1"]
for i, command in enumerate(commands):
if command.startswith("#"):
lines.append(f"printf '%s\\n' {shlex.quote(command)}")
continue
lines.append(f"printf '$ %s\\n' {shlex.quote(command)}")
lines.append(command)
lines.append(f'__s=$?; [ "$__s" -ne 0 ] && printf "[exit status %d]\\n" "$__s"; echo "{i} $__s" >&{write_end}')
with os.fdopen(read_end) as statuses:
done = subprocess.run(
["/bin/bash", "--noprofile", "--norc", "-c", "\n".join(lines)],
cwd=cwd, env=env, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, pass_fds=(write_end,),
)
os.close(write_end)
status = {int(i): int(s) for i, s in (line.split() for line in statuses.read().splitlines())}
shown = [line for line in done.stdout.splitlines() if not _PIP_SOURCE.match(line)]
return "\n".join(shown) + ("\n" if shown else ""), status
def _check(commands, status, fails):
wrong = 0
for i, command in enumerate(commands):
if command.startswith("#"):
continue
failed = status.get(i, 1) != 0
if failed != (command in fails):
wrong += 1
return wrong
def _lines(script):
return [line.strip() for line in script.strip().splitlines() if line.strip()]
def run(script, fails=()):
"""Runs each line of `script` as one command of a single bash session and prints the session."""
commands = _lines(script)
out, status = _shell(commands, Path.cwd(), _environment())
print(out, end="")
if _check(commands, status, fails):
sys.exit(1)
def _answers(port):
with socket.socket() as s:
s.settimeout(0.5)
return s.connect_ex(("127.0.0.1", port)) == 0
def _stop(job):
"""Stops the server and everything it started, as Ctrl+C would; kills it if it has not ended after 15 seconds."""
for sig in (signal.SIGINT, signal.SIGKILL):
try:
os.killpg(job.pid, sig)
except ProcessLookupError:
break # nothing of the group is left
try:
job.wait(timeout=15)
break
except subprocess.TimeoutExpired:
continue
job.wait()
def serve(setup, server, client, port, folder):
"""Runs `setup`, starts `server` in `folder` as a second terminal would, runs `client` against it, stops it."""
env = _environment()
before = _lines(setup)
out, status = _shell(before, Path.cwd(), env)
print(out, end="")
if _check(before, status, ()):
sys.exit(1)
if _answers(port):
sys.exit(f"port {port} is already in use: stop what listens there and run the session again")
where = Path.cwd() / folder
print(f"$ {server}")
print("# The server keeps that terminal. In a second terminal:")
log = where.parent / f".{folder}-server-terminal"
with open(log, "w") as terminal:
job = subprocess.Popen(["/bin/bash", "--noprofile", "--norc", "-c", f"exec {server}"], cwd=where, env=env,
stdout=terminal, stderr=subprocess.STDOUT, start_new_session=True)
try:
deadline = time.monotonic() + 30
while not _answers(port):
if job.poll() is not None or time.monotonic() > deadline:
print(log.read_text(), end="")
sys.exit("the server did not start")
time.sleep(0.1)
after = _lines(client)
out, status = _shell(after, where, env)
print(out, end="")
wrong = _check(after, status, ())
finally:
_stop(job)
print("# Ctrl+C in the first terminal stops the server. That terminal showed:")
for line in log.read_text().splitlines():
for pattern, replacement in _SERVER_TIME:
line = pattern.sub(replacement, line)
print(line)
log.unlink()
if wrong:
sys.exit(1)The first run failed with 11 != 2 : 11 queries executed, 2 expected and listed them: the notices, then a resident
and the comments of one notice, five times over, sent by the template’s loop. After fixed/views.py was copied over the
view, with select_related('posted_by') and prefetch_related('comments'), the same test passed: 2 queries, and 2 for
50 or 500 notices as well, because nothing on the page sends a query per row any more.
Key idea
Fix an N+1 where the rows are fetched, then pin the page’s number of queries in a test, which fails the moment a query per row comes back.
Checkpoint
Check: A query budget in a test
Practise: prefetch by hand and spot the repeats
What prefetch_related() does after its second query, and what a query log looks like when a page has an N+1, in
plain Python. First match notices to their posters with one call to a stand-in database; then group a page’s queries
by shape and find the one that repeats. Each step has tests you can run here and a hint:
Code lab · 2 steps
Prefetch by hand and spot the repeats
Join notices to their posters with a single bulk read, then find the query shape that a page sends again and again.
- Read every poster in one query, as prefetch_related does To do
- Find the query that repeats, as an N+1 detector does To do
Exercise · Medium · Python
Read every poster in one query, as prefetch_related does
prefetch_related('posted_by') sent two queries in this lesson: the notices, then SELECT … FROM resident WHERE id IN (1, 2, 3, 4, 5, 6), and it joined the two in Python. Do the joining part.
with_posters(notices, load_residents) gets notices as dictionaries, {'title': 'Power cut (3)', 'posted_by_id': 3}, and a function that stands for the database: load_residents(ids) returns the residents with those ids, as {'id': 3, 'name': 'Meera P.'}, in any order. Return (title, name) pairs in the order of the notices.
- Call
load_residentsonce, with each id once, in ascending order, as theIN (…)of the query lists them. - Do not call it at all when there are no notices.
- A notice whose poster is not among the residents returned gets
Nonefor the name.
Starter code · posters.py
def with_posters(notices, load_residents):
"""(title, poster's name) for each notice, in order, reading the residents with as few calls as possible."""
pairs = []
for notice in notices:
found = load_residents([notice['posted_by_id']])
pairs.append((notice['title'], found[0]['name'] if found else None))
return pairsThe sample tests · test_posters.py
from posters import with_posters
RESIDENTS = {1: 'Asha K.', 2: 'Imran Q.', 3: 'Meera P.', 4: 'Rohan M.', 5: 'Neha D.', 6: 'Vikram B.'}
NOTICES = [{'title': f'Notice {i + 1}', 'posted_by_id': i % 6 + 1} for i in range(30)]
class Database:
"""Stands for the residents table and remembers every query it was asked."""
def __init__(self):
self.calls = []
def __call__(self, ids):
self.calls.append(list(ids))
return [{'id': i, 'name': RESIDENTS[i]} for i in reversed(list(ids)) if i in RESIDENTS]
def test_names_in_order():
"""every notice gets its poster, in the order of the notices"""
pairs = with_posters(NOTICES, Database())
assert pairs[:3] == [('Notice 1', 'Asha K.'), ('Notice 2', 'Imran Q.'), ('Notice 3', 'Meera P.')]
assert len(pairs) == 30
def test_one_query_for_thirty_notices():
"""30 notices, 6 posters: one call with each id once, ascending, like WHERE id IN (1, 2, 3, 4, 5, 6)"""
db = Database()
with_posters(NOTICES, db)
assert db.calls == [[1, 2, 3, 4, 5, 6]]
def test_no_notices_no_query():
"""an empty board sends no query at all"""
db = Database()
assert with_posters([], db) == []
assert db.calls == []
def test_unknown_poster():
"""a poster the residents table no longer has gives None"""
db = Database()
assert with_posters([{'title': 'Old notice', 'posted_by_id': 99}], db) == [('Old notice', None)]
assert db.calls == [[99]]A hint
Collect the ids first, with a set, and sort them; that one list is the only call to load_residents. Then turn what it returns into a dictionary from id to name, and look each notice's id up in it with .get(), which gives None for an id it does not have.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Exercise · Medium · Python
Find the query that repeats, as an N+1 detector does
An N+1 shows in a log of queries as the same statement again and again with a different value each time: … WHERE resident.id = 1 LIMIT 21, then = 2, then = 1 again. Grouping the log by the shape of each statement makes the repeat stand out. Write that grouping.
repeated(queries, at_least=3) gets the SQL of every query of a page, in order, and returns (shape, count) pairs for each shape that appears at least at_least times, the most frequent first; shapes with the same count keep the order in which they first appeared.
- A shape is the query with every number and every quoted string replaced by
?:'water'and42both become?, while names such asresident.idornotice_idstay as they are. - Spaces are left as they are; only values change.
Starter code · repeats.py
def repeated(queries, at_least=3):
"""(shape, count) for every query shape that appears at least at_least times, the most frequent first."""
counts = {}
for sql in queries:
counts[sql] = counts.get(sql, 0) + 1
return [(sql, n) for sql, n in counts.items() if n >= at_least]The sample tests · test_repeats.py
from repeats import repeated
BOARD = ['SELECT notice.id, notice.title FROM notice ORDER BY notice.posted_at DESC LIMIT 5']
for notice_id, resident_id in [(5, 1), (4, 2), (3, 1), (2, 2), (1, 1)]:
BOARD.append(f'SELECT resident.id, resident.name FROM resident WHERE resident.id = {resident_id} LIMIT 21')
BOARD.append(f'SELECT comment.id, comment.text FROM comment WHERE comment.notice_id = {notice_id}')
def test_the_board_of_the_lesson():
"""the 11 queries of the slow board: two shapes, 5 times each"""
assert repeated(BOARD) == [
('SELECT resident.id, resident.name FROM resident WHERE resident.id = ? LIMIT ?', 5),
('SELECT comment.id, comment.text FROM comment WHERE comment.notice_id = ?', 5),
]
def test_strings_are_values():
"""a quoted string is a value, so these three are one shape"""
log = [f"SELECT id FROM notice WHERE category = '{c}'" for c in ('water', 'power', 'water')]
assert repeated(log) == [("SELECT id FROM notice WHERE category = ?", 3)]
def test_names_keep_their_digits():
"""digits inside names are not values: notice_2 stays notice_2"""
log = ['SELECT notice_2.id FROM notice_2 WHERE notice_2.id = 7'] * 3
assert repeated(log) == [('SELECT notice_2.id FROM notice_2 WHERE notice_2.id = ?', 3)]
def test_below_the_threshold():
"""a shape seen twice is not reported when at_least is 3, and is when it is 2"""
log = ['SELECT 1 FROM notice WHERE id = 1', 'SELECT 1 FROM notice WHERE id = 2']
assert repeated(log) == []
assert repeated(log, at_least=2) == [('SELECT ? FROM notice WHERE id = ?', 2)]
def test_most_frequent_first():
"""the shape seen 4 times comes before the one seen 3 times, though it appeared later"""
log = ['SELECT a FROM t WHERE id = 1'] * 3 + ['SELECT b FROM t WHERE id = 2'] * 4
assert repeated(log) == [('SELECT b FROM t WHERE id = ?', 4), ('SELECT a FROM t WHERE id = ?', 3)]A hint
A regular expression can find the values: quoted strings first, '[^']*', then numbers that are not part of a name, \b\d+\b. re.sub with ? gives the shape. A dictionary keeps the order in which keys were first added, so counting into one and then sorting by count alone, with sorted(…, key=…, reverse=True), keeps ties in that order.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Then the quiz on the five ideas of this lesson, from the N+1 itself to the query budget; it starts with the idea where your answers have been weakest:
Practice that adapts to you
Practise this lesson
10 questions about 5 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up (fresher): When do you use select_related() and when prefetch_related()?
select_related() follows a foreign key or a one-to-one field, a relation with one related row, in the same query,
with a JOIN: each notice arrives with its poster, 1 query in all. prefetch_related() is for reverse foreign keys
and many-to-many relations, where each row has many related rows: it sends one more query per relation, with
WHERE … IN (…), and joins the results in Python, so 30 notices and their comments take 2 queries. It also works for
foreign keys. A Prefetch object gives the second query its own filter, order or to_attr.
Two design questions of this module start from this lesson: a page that sends far more queries than it shows rows, and a proposal to make one fetch mode the default for every model.
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
The notice board shows 30 notices, each with its poster's name and its number of comments, and the page sends 61 queries. Explain how you find where they come from, fix them, and make sure they do not come back after the next change to the template.
-
Senior to staff
A teammate proposes making FETCH_PEERS the default fetch mode of every model, through a common manager, so that nobody has to write select_related() again. What does it fix, what can it make worse, and where would you use FETCH_RAISE instead?
Key takeaways
- An N+1 is one query for the rows and one more per row for a field the first query did not load; the default fetch
mode,
FETCH_ONE, fetches it for one instance at a time. select_related()joins forward foreign keys into the first query;prefetch_related()reads reverse and many-to-many relations in one more query per relation, and aPrefetchgives that query its filter and order.only()anddefer()leave columns out, and reading a column left out is a query per row unless a fetch mode batches it.FETCH_PEERSfetches a missing field for every peer of the same QuerySet at once, andFETCH_RAISEturns the fetch intoFieldFetchBlocked; a manager can make either a model’s default, and related objects inherit it.assertNumQueriespins a page’s query count in a test, so an N+1 that comes back fails the build instead of slowing the page.
Terms in this lesson
- deferred field
- A model field that a query left out with only() or defer(). Its value stays in the database until code reads it, and that read is a query of its own unless a fetch mode batches it.
- fetch mode
- Django 6.1's setting of a QuerySet, chosen with fetch_mode(), for reading a field the query did not load: FETCH_ONE fetches it for one instance, FETCH_PEERS for every instance of the same QuerySet, FETCH_RAISE refuses.
- N+1 queries
- One query that reads N rows, then one more query for each of them to read something the first one left out, such as each notice's poster. The number of queries grows with the rows a page reads.
References
- Django documentation: Database access optimization (Django Software Foundation)
- Django documentation: Fetch modes (Django Software Foundation)
- Django documentation: QuerySet API reference, select_related() (Django Software Foundation)
- Django documentation: QuerySet API reference, prefetch_related() (Django Software Foundation)
- Django documentation: Testing tools, assertNumQueries() (Django Software Foundation)
- Django 6.1 release notes (Django Software Foundation)
- Django Debug Toolbar documentation: Panels (Django Debug Toolbar project)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress