Query Optimization & Fetch Modes

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Django 6.1 (6.1.1, released 2026-09-02; 6.1.2 is scheduled for 2026-10-06) on Python 3.12 to 3.14, with PostgreSQL 15 or later as the production database and the 5.2 LTS differences noted where they matter, and was checked on 2026-10-03. The examples ran on Django 6.1.1 and Python 3.13 with SQLite in a throw-away project containing the Bookshelf catalogue models of this section (Author, Genre, Book, Review); 29 tests pass, the pages were rendered through runserver and curl, and the Debug Toolbar settings passed manage.py check and served its panels. There was no PostgreSQL server in that project, so the connection-pool and PostgreSQL-specific parts are only configuration that passed manage.py check; each of them says so where it appears.

An ORM makes it easy to write code that is correct and slow: every attribute access that crosses a relationship can silently become a query. The cure is a habit rather than a trick: measure the queries a page issues, fetch related data in bulk instead of one row at a time, load only the columns you need, and keep the number of statements per request constant instead of proportional to the data. This page covers the N+1 problem and the two classic tools against it (select_related and prefetch_related), the fetch modes added in Django 6.1 that fix most N+1 patterns automatically or forbid them outright, deferring columns, streaming large result sets, reading query plans with explain(), hooking every statement with database instrumentation, connection persistence and pooling, and the Django Debug Toolbar. For the vendor-neutral workflow that takes you from "this feels slow" to a confirmed root cause, see Diagnosing and Fixing Slow Queries.

The N+1 problem

A query is lazy and so are related objects: book.author does not exist on the instance until you touch it, and then Django runs a query to fetch it. Looping over N books and printing each author therefore costs one query for the list plus one query per book, which is where "N+1" comes from. With 12 books that is 13 queries; with 12,000 it is 12,001, each with its own round trip to the database.

The examples that follow use these models.

# catalog/models.py
from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=200)

    def __str__(self) -> str:
        return self.name


class Genre(models.Model):
    name = models.CharField(max_length=100, unique=True)

    def __str__(self) -> str:
        return self.name


class Book(models.Model):
    title = models.CharField(max_length=300)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    genres = models.ManyToManyField(Genre, related_name="books", blank=True)
    summary = models.TextField(blank=True)
    published = models.DateField(null=True, blank=True)

    class Meta:
        ordering = ["title"]

    def __str__(self) -> str:
        return self.title


class Review(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="reviews")
    rating = models.PositiveSmallIntegerField()
    body = models.TextField(blank=True)

    def __str__(self) -> str:
        return f"{self.book_id}: {self.rating}"

Source: Models and Many-to-one relationships. See Models & Fields and Relationships for how these fields work.

The function below has the classic N+1 shape, and the test pins the count so the problem is visible in the test output instead of in production.

# catalog/services.py
from .models import Book


def books_with_authors():
    # N+1: every book.author access below costs one query.
    return [(book.title, book.author.name) for book in Book.objects.all()]

# catalog/tests.py (excerpt)
from django.test import TestCase

from .models import Author, Book
from .services import books_with_authors


class OptimizationTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        for a in range(4):
            author = Author.objects.create(name=f"Author {a}")
            for b in range(3):
                Book.objects.create(title=f"Book {a}-{b}", author=author)

    def test_n_plus_one(self):
        with self.assertNumQueries(13):  # 1 + 12 books
            books_with_authors()

Source: assertNumQueries.

The figure compares what the database sees with and without bulk fetching. The prefetching side has the same shape however many books there are.

flowchart LR subgraph nopre["Without prefetching: 1 + N + N queries"] direction TB w0["SELECT books"] --> w1["SELECT author for book 1"] w0 --> w2["SELECT author for book 2"] w0 --> w3["... one per book"] w0 --> r1["SELECT reviews for book 1"] w0 --> r2["SELECT reviews for book 2"] w0 --> r3["... one per book"] end subgraph pre["With select_related + prefetch_related: 2 queries"] direction TB p0["SELECT books JOIN authors"] --> p1["SELECT reviews WHERE book_id IN (all ids)"] p1 --> p2["Django groups the rows in Python"] end

Figure 1. Queries issued for a list of books with their authors and reviews, without and with prefetching

The rest of this page is about moving from the left side of that figure to the right side, and about keeping it there.

Where this sits in the request cycle

Queries are issued wherever a QuerySet is evaluated or a lazy attribute is accessed, and that is often not the view. A view that returns a Book queryset to a template can trigger N+1 inside the template when it writes {{ book.author.name }}, because the template engine calls attributes for you and its syntax hides the cost. So the budget has to be enforced where the data is consumed: in the view that builds the context, in serializers when you use Django REST Framework, and in tests. The usual place to fix a query problem is the view (or a manager method it calls) that builds the QuerySet, not the template that consumes it. A QuerySet is only evaluated when you iterate it, slice it with a step, call len(), list(), bool() or repr() on it, or call a method such as get() or count(); see Making Queries.

Django also caches in two places and understanding both avoids unnecessary work. A QuerySet caches its results once evaluated, so iterating the same variable twice costs one query. And attributes on model instances are cached: after entry.blog is fetched once, the next entry.blog is free, while callable attributes such as entry.authors.all() run a query every time. The template system calls callables automatically, so be careful reading template code, and for your own expensive properties use functools.cached_property.

Source: Database access optimization.

Measure first

The official optimisation guide begins with "profile first", and that applies to everything below: each technique can make a particular case slower, so change one thing, measure and keep it only if it helps. Django gives you several complementary tools.

Table 1. Tools for seeing and counting queries
Tool	What it gives you	Use it for
`assertNumQueries` / `CaptureQueriesContext`	The count and the SQL of every statement inside a block (the test runner forces `DEBUG=True` while testing)	Regression tests that pin a query budget
`connection.queries`	A list of the SQL and time of each statement issued on a connection, available only when `DEBUG` is `True`; use `reset_queries()` to clear it	Quick checks in `manage.py shell` during development
`connection.execute_wrapper()`	A hook around every statement execution, which can log, time, count or block queries (see Database instrumentation)	Logging slow queries, blocking queries in templates, per-request counters
`QuerySet.explain()`	The database’s execution plan for a QuerySet (see Reading query plans with explain())	Finding missing indexes and bad joins
Django Debug Toolbar	A browser panel listing every query of a page with duplicates highlighted (see Django Debug Toolbar)	Interactive investigation in development

CaptureQueriesContext works outside tests too, and its captured_queries list holds dictionaries with sql and time keys.

from django.db import connection
from django.test.utils import CaptureQueriesContext

from catalog.models import Book

with CaptureQueriesContext(connection) as ctx:
    list(Book.objects.defer("summary"))

print(len(ctx.captured_queries))
print(ctx.captured_queries[0]["sql"])

Source: CaptureQueriesContext and How can I see the raw SQL queries Django is running.

The captured SQL of the example above omits the deferred column, which is a first taste of what only() and defer() do later on this page.

SELECT "catalog_book"."id", "catalog_book"."title", "catalog_book"."author_id", "catalog_book"."published" FROM "catalog_book" ORDER BY "catalog_book"."title" ASC

select_related: one JOIN for single-valued relations

select_related() follows foreign-key and one-to-one relationships and fetches the related row in the same SQL statement, using a JOIN. It works for forward relationships and for the reverse side of a OneToOneField, but not for many-valued relations (reverse foreign keys and many-to-many), which cannot be flattened into one row per object. It is the right default for "the author of each book".

# catalog/services.py
def books_with_authors_joined():
    books = Book.objects.select_related("author")
    return [(book.title, book.author.name) for book in books]

# catalog/tests.py (excerpt)
def test_select_related(self):
    with self.assertNumQueries(1):
        books_with_authors_joined()

Source: QuerySet.select_related().

The behaviour you need to know:

Chain relationships with the double-underscore syntax, for example select_related("book__author") for a Review queryset, to join several levels in one statement.
Calling select_related() with no arguments follows every non-null foreign key; avoid it, because the join list then grows silently as the model grows, and it is deprecated since Django 6.1. Name the fields.
Several calls add to the list of relations, and select_related(None) clears it. A foreign key with null=True is followed with a LEFT OUTER JOIN, so rows without a related object are still returned.
It combines with only() and defer(), for example select_related("author").only("title", "author__name"), to also trim the joined columns.
The result is one wide row per book, so it is a poor fit when a parent repeats thousands of times with large columns; measure.

Since Django 6.1, select_related() also matters for fetch modes: a relation that was joined is already loaded, so no fetch happens for it whatever the mode (see Fetch modes (Django 6.1)).

prefetch_related and Prefetch: one extra query per relation

prefetch_related() handles the relations select_related() cannot: many-to-many, reverse foreign keys, and generic relations. It runs one additional query per lookup, using WHERE id IN (…) with the primary keys of the first query’s results, and then joins the rows to their parents in Python. Two queries instead of N+1, regardless of N.

# catalog/services.py
def books_with_reviews_prefetched():
    books = Book.objects.select_related("author").prefetch_related("genres", "reviews")
    return [
        (book.title, [g.name for g in book.genres.all()], [r.rating for r in book.reviews.all()])
        for book in books
    ]

# catalog/tests.py (excerpt)
def test_prefetch(self):
    with self.assertNumQueries(3):  # books JOIN authors, genres, reviews
        books_with_reviews_prefetched()

Source: QuerySet.prefetch_related().

Three queries, whatever the number of books: one for books joined to authors, one for all the genres of those books, one for all their reviews. Notice that the loop uses book.genres.all() and book.reviews.all(): with a prefetch, .all() reads the cache that Django filled and issues no query.

Customising a prefetch with Prefetch objects

A Prefetch(lookup, queryset=None, to_attr=None) object lets you control the query behind a lookup, for example to filter or order the related rows, to select_related further objects, or to store the result in a separate attribute so that the plain relation manager stays untouched.

# catalog/services.py
from django.db import models

from .models import Book, Review


def best_reviews_per_book():
    best = Review.objects.filter(rating__gte=4).order_by("-rating")
    return Book.objects.prefetch_related(
        models.Prefetch("reviews", queryset=best, to_attr="best_reviews")
    )

# catalog/tests.py (excerpt)
def test_prefetch_with_to_attr(self):
    with self.assertNumQueries(2):
        books = list(best_reviews_per_book())
        assert all(len(b.best_reviews) == 1 for b in books)

Source: Prefetch objects.

to_attr stores a plain Python list in book.best_reviews, so book.reviews.all() still returns every review (with its own query) and nothing is silently filtered. Without to_attr, a custom queryset replaces what book.reviews.all() returns for those instances, which surprises the next reader.

Since Django 4.2 the queryset of a Prefetch may be sliced, for example "the two highest-rated reviews of each book", which the ORM implements with a window function; it ran on SQLite in the scratch project.

latest = Review.objects.order_by("-rating")[:2]
books = Book.objects.prefetch_related(
    models.Prefetch("reviews", queryset=latest, to_attr="top")
)

Source: Django 4.2 release notes (sliced Prefetch querysets).

Lookups can traverse relationships (prefetch_related("bookgenres") on a Review queryset), select_related and prefetch_related can be combined, and prefetch_related(None) clears the prefetch list. In the scratch project, Review.objects.select_related("bookauthor").prefetch_related("book__genres") issued two queries: one joining reviews, books and authors, one for the genres.

prefetch_related_objects for instances you already have

When the instances already exist (a page of results from a paginator, or objects returned by get()), prefetch_related_objects(instances, *lookups) runs the same bulk fetch afterwards. It is exported from django.db.models.

from django.db import models

books = list(Book.objects.all())
models.prefetch_related_objects(books, "reviews")  # one extra query

Source: prefetch_related_objects().

Pitfalls of prefetching

Filtering a prefetched relation discards the cache. book.reviews.filter(rating__gte=4) builds a new query and ignores what was prefetched, so a loop over prefetched books that calls .filter() or .order_by() on the relation is N+1 again (.all() and a plain .count() reuse the prefetched rows). Do the filtering in the Prefetch queryset, or in Python over .all(). In the scratch project three prefetched books plus a .filter(…).count() per book cost three extra queries, five in total.
Prefetching does not help single-object lookups. For a single instance the join in select_related is cheaper; prefetch pays off when several parents share each query.
Large IN lists. The second query lists every parent id. On SQLite and Oracle the number of terms in an IN clause is limited; with iterator() pass a chunk_size small enough for that limit (see Streaming large results with iterator()).
Related-object access uses the base manager. When Django follows a relation it uses the model’s base manager rather than the default manager, so custom filtering in a default manager does not apply to prefetched objects. Keep this in mind when you put select_related or prefetch_related in a manager (see Managers & Custom QuerySets).
Choose by shape. Use select_related for forward foreign keys and one-to-one (a join), prefetch_related for the many side. Two many-valued joins in one statement would multiply the rows, which is why Django fetches those relations with separate queries instead.

Fetch modes (Django 6.1)

Django 6.1 added fetch modes: a per-QuerySet setting that decides what happens when you touch a field that was not loaded by the original query. Before 6.1 the only behaviour was "fetch it for this one instance", which is what produces N+1; now you can choose to fetch it for the whole batch, or to forbid the fetch. The mode applies to ForeignKey fields, OneToOneField fields and their reverse accessors, fields deferred with defer() or only(), and generic relations. It does not change the queries of related managers such as author.books.all(); the mode is merely copied onto the objects those managers fetch. A fetch mode is set with QuerySet.fetch_mode(); the three modes live in django.db.models.fetch_modes and are re-exported from django.db.models, so the convention is from django.db import models and models.FETCH_PEERS.

Table 2. The three fetch modes
Mode	Behaviour on first access of an unfetched field	Queries for the author loop
`FETCH_ONE` (default)	Fetches the missing field for the current instance only; the historical behaviour	1 + N
`FETCH_PEERS`	Fetches the missing field for the current instance and its peers, the instances that came from the same initial QuerySet; similar to an on-demand `prefetch_related()`	2
`FETCH_RAISE`	Raises `django.core.exceptions.FieldFetchBlocked`, a subclass of `FieldError`, so no query is made	1, then an exception at `book.author`

The loop used for the table is the N+1 loop from the start of the page.

from django.db import models

from catalog.models import Book

# FETCH_ONE (default): 1 query for the books + 1 per book for the author.
for book in Book.objects.all():
    print(book.author.name)

# FETCH_PEERS: 1 query for the books + 1 query for all missing authors.
for book in Book.objects.fetch_mode(models.FETCH_PEERS):
    print(book.author.name)

# catalog/tests.py (excerpt)
def test_fetch_one_default(self):
    with self.assertNumQueries(13):
        for book in Book.objects.all():
            book.author.name

def test_fetch_peers(self):
    with self.assertNumQueries(2):
        for book in Book.objects.fetch_mode(models.FETCH_PEERS):
            book.author.name

Source: Fetch modes and QuerySet.fetch_mode().

With FETCH_PEERS, the first book.author access fetches the authors of all 12 books in one query; the other 11 accesses find the value already loaded. The peers are tracked in a list of weak references, so instances that are discarded do not leak memory, and the mode is a one-line change for code where listing every select_related and prefetch_related is impractical because nobody can predict which fields a template will touch. It also works for deferred columns:

# 1 query for the books without the summary + 1 for all summaries of the batch.
for book in Book.objects.fetch_mode(models.FETCH_PEERS).defer("summary"):
    print(len(book.summary))

Source: Fetch modes.

Forbidding lazy loading with FETCH_RAISE

FETCH_RAISE turns an accidental query into an error, which is what you want in a performance-critical view, a serializer or a test: if a relation is touched that you did not load explicitly, the code fails immediately instead of degrading quietly. Relations you did load, with select_related or prefetch_related, still work.

from django.core.exceptions import FieldFetchBlocked
from django.db import models

book = Book.objects.fetch_mode(models.FETCH_RAISE).first()
try:
    book.author
except FieldFetchBlocked as exc:
    print(exc)  # Fetching of Book.author blocked.

# Loaded explicitly, so allowed:
book = Book.objects.fetch_mode(models.FETCH_RAISE).select_related("author").first()
print(book.author.name)

Source: Fetch modes.

Fetch modes spread along the object tree

Django copies the fetch mode of an instance to the related objects it fetches, so the mode applies to a whole tree of relationships, not just the top-level model. If a book is loaded with FETCH_PEERS, the author object fetched through book.author carries FETCH_PEERS too, and so on down the chain. The copy is also made by related managers, although the mode does not change the queries that those managers themselves run.

book = Book.objects.fetch_mode(models.FETCH_PEERS).first()
assert book.author._state.fetch_mode is models.FETCH_PEERS

author = Author.objects.fetch_mode(models.FETCH_PEERS).first()
list(author.books.all())  # a normal single query; the manager ignores the mode

Source: Fetch modes.

Making a fetch mode the default for a model

The mode is set per QuerySet, so to apply it to every query of a model, override get_queryset() in a custom manager. The official guidance says to prefer FETCH_PEERS over select_related/prefetch_related when it is hard to predict which fields will be accessed, and to use select_related/prefetch_related when FETCH_PEERS is not appropriate or not efficient enough.

from django.db import models


class BookManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().fetch_mode(models.FETCH_PEERS)


class Book(models.Model):
    title = models.CharField(max_length=300)
    author = models.ForeignKey("Author", on_delete=models.CASCADE)

    objects = BookManager()

Source: Make a fetch mode the default for a model class.

This was run in the scratch project by attaching a manager with add_to_class and counting queries (2 for five books); in your own code define it on the model as above. See Managers & Custom QuerySets for managers and from_queryset().

Choosing between the tools

Table 3. Choosing a way to avoid N+1
Situation	Prefer	Why
You know exactly which single-valued relations a view needs	`select_related`	One statement, nothing to batch afterwards
You need many-valued relations, or filtered/ordered/sliced related rows	`prefetch_related` with `Prefetch`	One extra, tunable query per relation
You cannot predict which relations code (a template, a serializer) will touch	`FETCH_PEERS`, ideally as the model’s default manager mode	On-demand batching without maintaining a list of fields
You want accidental queries to fail fast in a hot path or in tests	`FETCH_RAISE`	Turns a hidden query into an exception
Instances already exist	`prefetch_related_objects`	The same bulk fetch afterwards

Fetch modes make the default behaviour safer, but they are not a licence to stop measuring: FETCH_PEERS costs one query per relation touched, a hand-written select_related can cost one query in total. A typical mature setup is FETCH_PEERS as the safety net, explicit select_related/prefetch_related on hot paths, and FETCH_RAISE in the tests of those paths.

Sync and async behaviour of lazy fetches

A lazy foreign-key access performs a synchronous query, so doing it from async code raises SynchronousOnlyOperation; in the scratch project book.author inside an async def after await Book.objects.afirst() was blocked. In async code, load what you need up front with select_related or prefetch_related (both work with async for and aiterator()), or use FETCH_RAISE to make any slip visible, and fetch a single relation explicitly with a query such as await Author.objects.aget(pk=book.author_id). The 5.2 LTS release has none of the fetch-mode API: fetch_mode(), FETCH_PEERS and FieldFetchBlocked are new in 6.1, so code that must run on 5.2 uses select_related and prefetch_related only. See Async Support.

Loading fewer columns: only, defer and values

Columns have a cost too: large TextField values, JSON documents and computed columns are transferred and converted into Python objects whether you use them or not.

only("title", "author") loads just the named fields (plus the primary key) and defers everything else.
defer("summary") loads everything except the named fields.
values() and values_list() skip model instances altogether and return dictionaries or tuples, useful for read-only listings and for feeding templates or JSON output.

books = list(Book.objects.only("title"))      # 1 query, no summary/published/author_id
for book in books:
    book.summary                               # deferred: 1 extra query per book

rows = Book.objects.values_list("title", "author__name")   # one JOIN, no model instances
print(Book.objects.defer("summary").first().get_deferred_fields())   # {'summary'}

Source: QuerySet.only(), QuerySet.defer() and QuerySet.values().

The official guide warns that accessing a deferred field makes a separate query, so deferring a column you end up needing is a pessimisation: only defer large text data or fields expensive to convert, and profile first. The database must read most non-text data from disk for a row whatever columns you ask for, so deferring many small columns rarely pays. With FETCH_PEERS a deferred field is loaded for the whole batch on first access, which softens (but does not remove) the penalty of a mistake.

The rest of the optimisation guide

The official guide gathers a long list of smaller rules. They are cheap to apply and each has a one-line justification.

Table 4. Rules from the official optimisation guide
Instead of	Do
Filtering in Python	`filter`, `exclude`, `F()` and `annotate` so the database does the work (see Aggregation, Expressions & Functions)
`if queryset:` just to test emptiness	`queryset.exists()`
`len(queryset)` just to count	`queryset.count()`
`obj in queryset` for one object	`queryset.contains(obj)` (Django 4.0)
Overusing the three above when you will need the rows anyway	Evaluate once and reuse the result cache (`if members:`, `len(members)`, `x in members` then run no extra query)
`entry.blog.id`	`entry.blog_id`, which is already on the instance
Loading objects, changing them and calling `save()` one by one	`QuerySet.update()` and `delete()`; they do not call `save()`/`delete()` or send model signals
`create()` in a loop	`bulk_create()`; `bulk_update()` for changes
Adding many-to-many links one by one	`add()` with several objects
Default `Meta.ordering` that you do not need	`order_by()` with no arguments to remove it
`get(headline="…")` on an unindexed, non-unique column	Look up by a unique, indexed column such as the primary key
Unindexed filter and order columns	`Meta.indexes` or `db_index` after profiling (see Constraints, Indexes & Composite Keys)

The following test measures several of these rules in the scratch project.

# catalog/tests.py (excerpt)
def test_bulk_create(self):
    with self.assertNumQueries(1):
        Genre.objects.bulk_create([Genre(name=f"g{i}") for i in range(5)])

def test_fk_value(self):
    book = Book.objects.first()
    with self.assertNumQueries(0):
        book.author_id

def test_cached_attributes(self):
    book = Book.objects.first()
    with self.assertNumQueries(1):
        book.author
        book.author          # cached on the instance
    with self.assertNumQueries(2):
        book.reviews.all().count()
        book.reviews.all().count()   # callable: queries again

Source: Database access optimization.

For writes and bulk operations see Making Queries; wrapping several writes in a transaction is covered in Transactions, Raw SQL & Multiple Databases. When a single QuerySet cannot express what you need, RawSQL and raw SQL exist, but are less portable; try annotations and expressions first.

Streaming large results with iterator()

A QuerySet caches every row it has loaded, which is what makes a second iteration free but also means that a million-row export holds a million objects. iterator(chunk_size=None) evaluates the query and returns a generator without any QuerySet-level caching, so memory stays flat when each object is processed once. The asynchronous twin is aiterator().

total = 0
for review in Review.objects.filter(rating__gte=4).iterator(chunk_size=2000):
    total += review.rating

# catalog/tests.py (excerpt)
def test_iterator(self):
    # 12 books / chunk_size 5 = 3 chunks -> 1 books query + 3 prefetch queries
    with self.assertNumQueries(1 + 3):
        list(Book.objects.prefetch_related("reviews").iterator(chunk_size=5))

def test_async_aiterator(self):
    async def run():
        return [b.author.name async for b in Book.objects.select_related("author").aiterator()]
    from asgiref.sync import async_to_sync
    self.assertEqual(len(async_to_sync(run)()), 12)

Source: QuerySet.iterator().

Points to remember:

Since Django 4.1, iterator() supports prefetch_related() as long as chunk_size is given; before 4.1 no prefetching happened at all. The prefetch queries then run once per chunk, as the test above shows. Without chunk_size, current Django raises ValueError ("chunk_size must be provided when using QuerySet.iterator() after prefetch_related()", observed on 6.1.1).
When no prefetching is involved, omitting chunk_size uses an implicit default of 2000.
On PostgreSQL and Oracle, iterator() uses server-side cursors that stream from the database; chunk_size is then the number of rows cached by the driver. MySQL cannot stream, so the driver loads the whole result set anyway. On SQLite results come in batches via fetchmany(), but be careful writing to the table being iterated.
Behind a transaction-pooling proxy such as PgBouncer, server-side cursors fail because a later transaction may be served by a different connection; set DISABLE_SERVER_SIDE_CURSORS to True for that database.
Calling iterator() on an already evaluated QuerySet runs the query again.

Reading query plans with explain()

QuerySet.explain(format=None, **options) returns the database’s execution plan for the query as a string, showing indexes and joins in use; its async version is aexplain(). It is supported by all built-in backends except Oracle. The output differs between databases and the format argument selects, for example, 'JSON' on PostgreSQL, MySQL and MariaDB. Extra keyword arguments are passed to the database as flags: PostgreSQL accepts analyze=True, verbose=True and others, and Django 5.1 added the generic_plan option for PostgreSQL 16 and later.

>>> from catalog.models import Book
>>> print(Book.objects.select_related("author").filter(author__name="Author 1").explain())
5 0 216 SCAN catalog_author
9 0 60 SEARCH catalog_book USING INDEX catalog_book_author_id_b0849980 (author_id=?)
25 0 0 USE TEMP B-TREE FOR ORDER BY

Source: QuerySet.explain().

That output is from SQLite in the scratch project; the columns are SQLite’s EXPLAIN QUERY PLAN fields. Read it from the inside out: here SQLite scans the (tiny) author table, uses the foreign-key index on catalog_book.author_id for the join, and sorts with a temporary B-tree because of Meta.ordering. On PostgreSQL the same call prints a plan tree with Seq Scan or Index Scan nodes and cost estimates. Look for sequential scans on big tables, sorts that an index could provide, and row estimates that differ wildly from reality.

explain(analyze=True) on PostgreSQL, MariaDB and MySQL executes the statement, so a trigger or function call can change data even in a SELECT; do not run it against production data you cannot afford to touch. This page’s PostgreSQL examples were not run, because there was no PostgreSQL server in the scratch project; the plan formats are described in the QuerySet reference. For how to interpret plans and what to change, see Diagnosing and Fixing Slow Queries.

Database instrumentation

connection.execute_wrapper(wrapper) returns a context manager that installs a wrapper around every query execution on the current thread’s connection, then removes it. Wrappers resemble middleware: each receives execute (the next callable), sql, params, many (whether it is executemany()) and context (a dictionary with the connection and cursor), and must call execute(sql, params, many, context) and return its result. They can count, time, log or block queries, and they only live inside the with block, so they are specific to one flow.

# catalog/querylog.py
import time
from contextlib import contextmanager

from django.db import connection


class QueryLogger:
    """Database wrapper that records every statement and its duration."""

    def __init__(self) -> None:
        self.queries: list[dict] = []

    def __call__(self, execute, sql, params, many, context):
        entry = {"sql": sql, "params": params, "many": many}
        start = time.monotonic()
        try:
            result = execute(sql, params, many, context)
        except Exception as exc:
            entry["status"] = "error"
            entry["exception"] = exc
            raise
        else:
            entry["status"] = "ok"
            return result
        finally:
            entry["duration"] = time.monotonic() - start
            self.queries.append(entry)


def blocker(execute, sql, params, many, context):
    alias = context["connection"].alias
    raise RuntimeError(f"Access to database '{alias}' blocked here")


@contextmanager
def log_queries():
    logger = QueryLogger()
    with connection.execute_wrapper(logger):
        yield logger

# catalog/tests.py (excerpt)
def test_logger_and_blocker(self):
    with log_queries() as ql:
        books_with_authors_joined()
    self.assertEqual(len(ql.queries), 1)

    with connection.execute_wrapper(blocker):
        with self.assertRaises(RuntimeError):
            list(Book.objects.all())

Source: Database instrumentation.

The blocker is the more surprising use: render a template inside with connection.execute_wrapper(blocker): to guarantee that it issues no queries, so any N+1 hidden in the template fails loudly. A wrapper installed in middleware gives you per-request counters and slow-query logs that you can send to your logging system (see Middleware and Logging & Error Reporting); the django.db.backends logger can also log statements when DEBUG is True. A wrapper is installed on the thread-local connection, so it only sees queries run on that thread’s connection.

A query budget for a view

Putting the pieces together, this view combines a join, a prefetch, an aggregate and a deferred column, and the test pins its cost to two queries regardless of how many books exist.

# catalog/views.py
from django.db import models
from django.shortcuts import render

from .models import Book


def book_list(request):
    books = (
        Book.objects.select_related("author")
        .prefetch_related("genres")
        .annotate(review_count=models.Count("reviews"))
        .defer("summary")
    )
    return render(request, "catalog/book_list.html", {"books": books})

{# catalog/templates/catalog/book_list.html #}
<ul>
{% for book in books %}
  <li>{{ book.title }} by {{ book.author.name }} ({{ book.review_count }} reviews;
      {% for genre in book.genres.all %}{{ genre.name }}{% if not forloop.last %}, {% endif %}{% endfor %})</li>
{% endfor %}
</ul>

# catalog/tests.py (excerpt)
class BookListViewTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        for a in range(5):
            author = Author.objects.create(name=f"Author {a}")
            for b in range(4):
                book = Book.objects.create(title=f"B{a}{b}", author=author)
                book.genres.add(Genre.objects.get_or_create(name=f"G{b}")[0])
                Review.objects.create(book=book, rating=4)

    def test_query_budget(self):
        # books + authors + review count (1), genres prefetch (1), nothing per row
        with self.assertNumQueries(2):
            response = self.client.get("/books/")
        self.assertContains(response, "B00 by Author 0 (1 reviews")

Source: Aggregation, assertNumQueries and Templates. See Templates for the template language and Testing for the test tools.

Connection management

Opening a database connection is slow, much more so over TLS or to a remote server, so how connections live matters as much as how many statements run.

Persistent connections: CONN_MAX_AGE and CONN_HEALTH_CHECKS

By default Django closes the connection at the end of every request (CONN_MAX_AGE = 0). A positive number of seconds keeps it open for reuse by later requests handled by the same thread, and None keeps it forever. At the start and end of each request Django closes connections that have reached their maximum age or that are unusable. Setting CONN_HEALTH_CHECKS to True (Django 4.1) makes Django verify a persistent connection before reusing it, once per request and only when the request accesses the database, which avoids errors after the server closed an idle connection or restarted.

# settings.py (excerpt)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "bookshelf",
        "CONN_MAX_AGE": 60,          # keep a connection for up to 60 seconds
        "CONN_HEALTH_CHECKS": True,  # Django 4.1+: validate before reuse
    }
}

Source: Persistent database connections and CONN_MAX_AGE.

The caveats from the official page matter. Each thread keeps its own connection, so the database must allow at least as many connections as you have worker threads. The development server creates a new thread for every request, which negates persistence, so do not enable it in development. When you change connection parameters such as the isolation level or time zone, persistent connections keep the change across requests unless you reset it. And in a long-running process outside the request cycle (a script, a worker, a thread of your own) a connection stays open until you close it, and django.db.close_old_connections() closes the old or unusable ones. Most importantly, the documentation states that persistent connections should be disabled under ASGI: use the database backend’s own pooling instead.

PostgreSQL connection pools (Django 5.1)

Since Django 5.1 the PostgreSQL backend can use a connection pool provided by psycopg_pool. Pools reuse connections across threads, and Django keeps a separate pool per database alias in each process; closing a Django connection returns it to the pool. Pooling requires psycopg 3 with the pool extra (pip install "psycopg[pool]"). Enable it with the "pool" key in OPTIONS, either True for the defaults or a dictionary of ConnectionPool arguments. CONN_MAX_AGE must be 0 when a pool is enabled; control lifetime with the pool’s max_lifetime option instead. Unless you pass a check callback in the pool options, CONN_HEALTH_CHECKS decides whether connections are checked as they are taken from the pool.

# config/settings_pg.py
import os

from .settings import *  # noqa: F401,F403

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ.get("DB_NAME", "bookshelf"),
        "USER": os.environ.get("DB_USER", "bookshelf"),
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": os.environ.get("DB_HOST", "localhost"),
        "PORT": os.environ.get("DB_PORT", "5432"),
        "CONN_MAX_AGE": 0,           # must stay 0 with a pool
        "CONN_HEALTH_CHECKS": True,  # validate connections taken from the pool
        "OPTIONS": {
            "pool": {"min_size": 2, "max_size": 10, "timeout": 10},
        },
    }
}

Source: Connection pool and Django 5.1 release notes.

This settings module passed python manage.py check --settings=config.settings_pg in the scratch project, which does not open a connection; the pool itself was not exercised, because there was no PostgreSQL server and psycopg_pool was not installed there. Treat the pool sizes as starting values to tune against max_connections on the server and the number of worker processes: with p processes each holding a pool of up to max_size connections, the database sees up to p * max_size connections. The password is read from the environment and never written in the settings file.

Table 5. Which connection strategy to use
Strategy	When	Notes
`CONN_MAX_AGE = 0` (default)	Low traffic, development, ASGI without a pool	A new connection per request; simplest, slowest to connect
`CONN_MAX_AGE` > 0 with `CONN_HEALTH_CHECKS`	WSGI with threaded workers	One connection per thread; needs enough server connections; disable under ASGI
`OPTIONS["pool"]` (Django 5.1, psycopg 3)	PostgreSQL under WSGI or ASGI	`CONN_MAX_AGE` must be 0; per-process pool
External pooler (PgBouncer, a cloud proxy)	Many processes or serverless workloads	In transaction-pooling mode set `DISABLE_SERVER_SIDE_CURSORS = True`

See PostgreSQL & Database Backends for backend specifics and Deployment for sizing workers against connections.

Django Debug Toolbar

The Django Debug Toolbar is a third-party package (8.0.0 on PyPI on 2026-10-03, classified for Django 5.2, 6.0 and 6.1) that adds a side panel to HTML pages in development. Its SQL panel lists every query of the request with its time, flags duplicate and similar queries (the signature of N+1), shows the stack trace that issued each query, and lets you run EXPLAIN on a statement. Its other panels cover timing, templates, cache calls, signals and settings. Install it as a development dependency only.

pip install django-debug-toolbar

# config/settings_dev.py
from .settings import *  # noqa: F401,F403

INSTALLED_APPS += ["debug_toolbar"]
MIDDLEWARE.insert(0, "debug_toolbar.middleware.DebugToolbarMiddleware")
INTERNAL_IPS = ["127.0.0.1"]

# config/urls.py
from django.conf import settings
from django.contrib import admin
from django.urls import include, path

from catalog.views import book_list

urlpatterns = [
    path("admin/", admin.site.urls),
    path("books/", book_list, name="book-list"),
]

if settings.DEBUG and "debug_toolbar" in settings.INSTALLED_APPS:
    urlpatterns += [path("__debug__/", include("debug_toolbar.urls"))]

Source: Debug Toolbar installation and Debug Toolbar panels.

With this configuration check passes, and a request to /books/ returned a page containing the toolbar’s djDebug markup and the /debug/ panel endpoints; the toolbar only appears when DEBUG is True, the request comes from an address in INTERNAL_IPS, and the response is HTML. Pitfalls: put the middleware as early as possible but after any middleware that encodes the response (such as GZipMiddleware), never enable the toolbar in production, and remember that it measures a single request, so use assertNumQueries to keep what it taught you from regressing. The older toolbar recipes that registered the URL with url(r'^debug/', include(debug_toolbar.urls)) and listed the middleware in MIDDLEWARE_CLASSES no longer work: url() was removed in Django 4.0 and MIDDLEWARE_CLASSES in Django 2.0.

Sync versus async at a glance

Table 6. Query behaviour in synchronous and asynchronous code
Feature	Sync / async notes
Lazy foreign-key access, deferred fields, `FETCH_PEERS`	Synchronous queries; from `async def` they raise `SynchronousOnlyOperation`, so load relations first with `select_related` or `prefetch_related`
`select_related`, `prefetch_related`, `Prefetch`	Work unchanged with `async for` and the `a`-prefixed methods (a prefetched `async for` over 3 books and 15 reviews ran in the scratch project)
`iterator()`	`aiterator()` in async code (the asynchronous ORM interface arrived in Django 4.1)
`explain()`	`aexplain()` in async code
`connection.execute_wrapper()`	Installed on the thread-local connection, so install it inside the thread that runs the queries
`CONN_MAX_AGE` persistent connections	Disable under ASGI; use `OPTIONS["pool"]` or an external pooler

See Async Support.

What changed recently

Table 7. Query-related changes by version
Version	Change
2.1	`QuerySet.explain()` introduced
4.0	`QuerySet.contains()` added; `url()` and the old-style recipes built on it are gone
4.1	Asynchronous ORM interface (`aiterator()`, `aexplain()`, `acount()` and friends); `CONN_HEALTH_CHECKS`; `iterator()` can prefetch when `chunk_size` is given
4.2	`Prefetch` accepts sliced querysets
5.1	PostgreSQL connection pools (`OPTIONS["pool"]`); `explain(generic_plan=True)` on PostgreSQL 16+
6.1	Fetch modes: `QuerySet.fetch_mode()`, `FETCH_ONE`, `FETCH_PEERS`, `FETCH_RAISE`, `FieldFetchBlocked`; `select_related()` with no arguments deprecated; PostgreSQL 15 is the minimum

Source: Django 6.1, 5.1, 4.2, 4.1 and 4.0 release notes, plus the Django 2.1 release notes for explain().

On Django 5.2 LTS everything above except the fetch modes applies; the supported way to fight N+1 there is select_related, prefetch_related and tests with assertNumQueries.

Where the books are outdated

The four books this section consults were written for Django 1.11 to 5.x and predate several of the tools on this page, so check what they tell you against the current documentation.

Fetch modes do not exist in them: they describe select_related and prefetch_related as the only remedies for N+1, and treat lazy loading as unavoidable and unobservable. In Django 6.1 you can batch it (FETCH_PEERS) or forbid it (FETCH_RAISE).
Older books rely on connection.queries and the Debug Toolbar to count queries. Both still work, but assertNumQueries, CaptureQueriesContext and connection.execute_wrapper() let you enforce a budget in tests and in production code.
Debug Toolbar setup in older books uses MIDDLEWARE_CLASSES (removed in Django 2.0) and the url() helper (removed in Django 4.0); current setup is the MIDDLEWARE list and path() with include("debug_toolbar.urls") as shown above.
Connection handling: CONN_MAX_AGE is described without CONN_HEALTH_CHECKS (4.1), without pools (5.1) and without the ASGI warning. Driver advice also moved on: psycopg 3 is supported since Django 4.2 and is the recommended driver.
Large exports that use iterator() in older books cannot combine it with prefetch_related(); since Django 4.1 they can, with chunk_size.
Composite indexes: index_together, used for composite indexes in older books, was removed in Django 5.1; use Meta.indexes (see Constraints, Indexes & Composite Keys).

Pitfalls checklist

Count queries in tests with assertNumQueries for every list page; a loop over related objects that "works" locally with five rows is the usual N+1.
The template engine hides queries: {{ book.author.name }} inside a loop is a query per row unless the view loaded the author.
Calling .filter() or .order_by() on a prefetched relation throws the prefetch away; .all() and .count() reuse it.
only()/defer() followed by access to a deferred field is slower than loading everything; defer big columns, not small ones.
select_related() without arguments follows every non-null foreign key and is deprecated since Django 6.1; name the fields.
FETCH_PEERS is a safety net, not a plan: it costs one query per relation touched; select_related costs none extra.
iterator() after prefetch_related() raises ValueError unless you pass chunk_size; it also bypasses the result cache, so repeated use repeats the query; behind PgBouncer in transaction mode disable server-side cursors.
Under ASGI keep CONN_MAX_AGE at 0, because persistent connections are not reused safely there; combining CONN_MAX_AGE above 0 with OPTIONS["pool"] raises ImproperlyConfigured.
explain(analyze=True) runs the statement; never point it at data you cannot afford to modify.
Never leave the Debug Toolbar, DEBUG = True or query logging of params on in production.

Query Optimization & Fetch Modes

The N+1 problem

Where this sits in the request cycle

Measure first

select_related: one JOIN for single-valued relations

prefetch_related and Prefetch: one extra query per relation

Customising a prefetch with Prefetch objects

prefetch_related_objects for instances you already have

Pitfalls of prefetching

Fetch modes (Django 6.1)

Forbidding lazy loading with FETCH_RAISE

Fetch modes spread along the object tree

Making a fetch mode the default for a model

Choosing between the tools

Sync and async behaviour of lazy fetches

Loading fewer columns: only, defer and values

The rest of the optimisation guide

Streaming large results with iterator()

Reading query plans with explain()

Database instrumentation

A query budget for a view

Connection management

Persistent connections: CONN_MAX_AGE and CONN_HEALTH_CHECKS

PostgreSQL connection pools (Django 5.1)

Django Debug Toolbar

Sync versus async at a glance

What changed recently

Where the books are outdated

Pitfalls checklist

References