Django ORM N+1 Queries: Detection and Prevention

What an N+1 Query Looks Like in Django

I regret to inform you that your Django view may be running rather more queries than the situation requires. Allow me to demonstrate with a simplified bookstore:

class Author(models.Model):
    name = models.CharField(max_length=200)
    country = models.ForeignKey("Country", on_delete=models.CASCADE)

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

class Tag(models.Model):
    name = models.CharField(max_length=100)
    books = models.ManyToManyField(Book, related_name="tags")

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

Now, the code that looks entirely innocent:

books = Book.objects.all()
for book in books:
    print(f"{book.title} by {book.author.name}")

This generates the following SQL:

-- Query 1: fetch all books
SELECT * FROM book;

-- Query 2: fetch author for book 1
SELECT * FROM author WHERE id = 1;

-- Query 3: fetch author for book 2
SELECT * FROM author WHERE id = 2;

-- Query 4: fetch author for book 3
SELECT * FROM author WHERE id = 3;

-- ... one query per book

100 books = 101 queries. 1,000 books = 1,001 queries. The cost is linear in the number of rows and compounds when you access multiple relationships.

Django does this by design, and it is worth understanding why before we judge the approach. The ORM uses lazy loading as its default strategy: related objects are not fetched until they are accessed. This is a deliberate choice — it avoids loading data you might not need. But when you always need the related data (as in the loop above), lazy loading becomes the N+1 query problem. The ORM's caution, intended to help, is working against you.

For the N+1 pattern across other ORMs and languages, see the general N+1 guide.

Detection — Finding N+1 Queries Before Your Users Do

The good news: these patterns are eminently detectable. The tools are excellent, and I would recommend making them part of your workflow from the start.

django-debug-toolbar

The most direct detection tool for development. The SQL panel shows every query executed during a request, with timing, duplicate detection, and EXPLAIN for each query.

pip install django-debug-toolbar

# settings.py
INSTALLED_APPS = [
    # ...
    "debug_toolbar",
]

MIDDLEWARE = [
    "debug_toolbar.middleware.DebugToolbarMiddleware",
    # ...
]

INTERNAL_IPS = ["127.0.0.1"]

What to look for in the SQL panel:

• Repeated queries with different parameter values. If you see 50 queries that all look like SELECT * FROM author WHERE id = %s with varying parameter values, you have found an N+1.
• The duplicate query count. The toolbar flags duplicates — queries with the same SQL template but different parameters. A high duplicate count is the unmistakable N+1 signature.
• Total query count. If a list view shows 150+ queries, something is lazy-loading in a loop. That warrants investigation.

Limitation: django-debug-toolbar only works in development with a browser. It does not catch N+1 queries in management commands, background tasks, or API endpoints tested without a browser.

nplusone

Automatic N+1 detection that catches lazy loading at the exact point it occurs:

pip install nplusone

# settings.py
INSTALLED_APPS = [
    # ...
    "nplusone",
]

MIDDLEWARE = [
    "nplusone.ext.django.NPlusOneMiddleware",
    # ...
]

# Raise exceptions in development, log warnings in staging
NPLUSONE_RAISE = True  # raises NPlusOneError on lazy load
# or
NPLUSONE_LOG_LEVEL = logging.WARNING  # logs a warning instead

nplusone is more precise than counting queries: it tells you exactly which model and which relationship triggered the lazy load, along with the line of code responsible. This makes locating the source of an N+1 considerably faster.

I should note: nplusone adds overhead and is best suited for development and staging. It is not recommended for high-traffic production servers.

Query Logging

Django's built-in query logging prints every SQL query to the console:

# settings.py
LOGGING = {
    "version": 1,
    "handlers": {
        "console": {"class": "logging.StreamHandler"},
    },
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

For programmatic inspection, use connection.queries:

from django.db import connection, reset_queries

reset_queries()

# ... execute your code ...

print(f"Query count: {len(connection.queries)}")
for q in connection.queries:
    print(f"  {q['time']}s: {q['sql'][:100]}")

The most powerful detection method, and the one I would recommend above all others, is assertNumQueries in tests:

from django.test import TestCase

class BookListViewTest(TestCase):
    def test_query_count(self):
        # Create test data
        author = Author.objects.create(name="Author 1", country=self.country)
        for i in range(100):
            Book.objects.create(title=f"Book {i}", author=author, published_date="2026-01-01")

        with self.assertNumQueries(2):  # 1 for books, 1 for authors
            response = self.client.get("/books/")

        self.assertEqual(response.status_code, 200)

assertNumQueries is the single most effective prevention mechanism. Write one for every view and API endpoint. When someone adds a new related field access in a template or serializer, the test catches it immediately — before your users do.

pg_stat_statements — Production Detection

For finding N+1 patterns in production without modifying application code — and this is well worth your attention — pg_stat_statements tracks query patterns at the PostgreSQL level:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT%FROM author WHERE id%'
ORDER BY calls DESC
LIMIT 10;

An N+1 pattern shows up as a query with extremely high calls count relative to other queries. If SELECT * FROM author WHERE id = $1 has 50,000 calls per hour while other queries have 500, something is fetching authors one at a time in a loop.

This approach requires no application changes and adds minimal overhead — pg_stat_statements is a PostgreSQL extension that tracks query statistics continuously. See the auto_explain extension for another production-safe detection method.

select_related — JOIN-Based Eager Loading

Now to the remedy. select_related tells Django to fetch related objects in the same SQL query using a JOIN.

The fix for the book/author N+1:

books = Book.objects.select_related("author").all()
for book in books:
    print(f"{book.title} by {book.author.name}")  # no additional queries

The generated SQL:

SELECT book.*, author.*
FROM book
INNER JOIN author ON book.author_id = author.id;

101 queries become 1. One query. Every book row arrives with its author data already attached.

select_related works with ForeignKey and OneToOneField relationships — relationships where each row on the “many” side points to exactly one row on the “one” side. The result is a single SQL query with a JOIN.

Chaining select_related

Multiple relationships:

Book.objects.select_related("author", "publisher")

Generates a query that JOINs both author and publisher in a single SQL statement.

Nested relationships:

Book.objects.select_related("author__country")

Follows the chain: book → author → country. Generates a query with two JOINs. Accessing book.author.country.name requires zero additional queries.

All forward relationships:

With no arguments, select_related() follows all non-null ForeignKey and OneToOneField relationships. I would advise care here on models with many relationships — each additional JOIN adds columns to the result set. For models with 5+ ForeignKey relationships, explicitly name the ones you need rather than selecting all.

select_related Limitations

• Does not work with ManyToManyField or reverse ForeignKey relationships. These are one-to-many: a JOIN would produce row duplication (one author row repeated for each of their books). Use prefetch_related for these.
• Does not work with GenericForeignKey (content types framework). The target table is not known at query time.
• Optional relationships (null=True) work correctly — Django generates a LEFT OUTER JOIN instead of an INNER JOIN.

The rule: if the relationship returns a single object, use select_related. If it returns a queryset (potentially many objects), use prefetch_related.

prefetch_related — Separate Query Eager Loading

prefetch_related takes a different approach: it fetches related objects in a separate query and joins them in Python.

The fix for a many-to-many N+1:

books = Book.objects.prefetch_related("tags").all()
for book in books:
    for tag in book.tags.all():  # no additional queries
        print(f"  {tag.name}")

The generated SQL:

-- Query 1: fetch all books
SELECT * FROM book;

-- Query 2: fetch all tags for those books
SELECT tag.*, book_tag.book_id
FROM tag
INNER JOIN book_tag ON tag.id = book_tag.tag_id
WHERE book_tag.book_id IN (1, 2, 3, 4, 5, ...);

101 queries become 2. Django executes the main query, collects all book primary keys, then executes a second query with an IN clause to fetch all related tags at once. It stitches the results together in Python so that book.tags.all() returns the correct tags for each book without touching the database again. Quite tidy.

prefetch_related works with ManyToManyField, reverse ForeignKey relationships, GenericRelation, and also forward ForeignKey/OneToOneField (though select_related is more efficient for the latter since it uses a single query).

Prefetch Objects — Filtering and Annotating Prefetched Querysets

The Prefetch object is the advanced form of prefetch_related. It lets you customize the queryset used for the prefetch — filtering, annotating, ordering, and storing the result in a custom attribute.

Filtering prefetched objects:

from django.db.models import Prefetch

books = Book.objects.prefetch_related(
    Prefetch("tags", queryset=Tag.objects.filter(name__startswith="python"))
)

Only tags starting with “python” are prefetched. Accessing book.tags.all() returns only the matching tags, without an additional query.

Annotating prefetched objects:

from django.db.models import Count

authors = Author.objects.prefetch_related(
    Prefetch(
        "books",
        queryset=Book.objects.annotate(review_count=Count("reviews"))
    )
)

# Each prefetched book now has a review_count attribute
for author in authors:
    for book in author.books.all():
        print(f"  {book.title}: {book.review_count} reviews")

For more on Django annotation patterns, see the annotate, subquery, and CTE guide.

Ordering prefetched objects:

authors = Author.objects.prefetch_related(
    Prefetch("books", queryset=Book.objects.order_by("-published_date"))
)

Books arrive sorted by publication date, most recent first, without a separate sort query.

Custom attribute name with to_attr:

authors = Author.objects.prefetch_related(
    Prefetch(
        "books",
        queryset=Book.objects.filter(published_date__year=2026),
        to_attr="books_2026"
    )
)

for author in authors:
    print(f"{author.name}: {len(author.books_2026)} books in 2026")

With to_attr, the result is stored as a Python list on a custom attribute instead of populating the relationship manager's cache. Use to_attr when you need a simple list and want to avoid confusion with the default relationship accessor.

Nested prefetches:

books = Book.objects.prefetch_related(
    Prefetch(
        "tags",
        queryset=Tag.objects.prefetch_related("category")
    )
)

This prefetches tags for each book, and for each tag, prefetches its category. The total query count: 3 (books, tags, categories) instead of 1 + N_books + N_tags.

The prefetch_related Cache Invalidation Trap

I'm afraid this one requires your attention. Once prefetch_related has been evaluated, filtering the prefetched relationship does not use the prefetch cache — it triggers a new database query:

books = Book.objects.prefetch_related("tags")

for book in books:
    # This uses the prefetch cache - no extra query
    all_tags = book.tags.all()

    # This IGNORES the prefetch cache and hits the database
    python_tags = book.tags.filter(name="python")

The .filter() call on the prefetched manager creates a new queryset that bypasses the cache entirely. Silently. You are back to N+1 with no indication that anything has gone wrong.

Fix: Use a Prefetch object with to_attr and filter the Python list:

books = Book.objects.prefetch_related(
    Prefetch("tags", to_attr="all_tags")
)

for book in books:
    python_tags = [t for t in book.all_tags if t.name == "python"]

Or use a Prefetch with a filtered queryset if you only ever need the filtered subset:

books = Book.objects.prefetch_related(
    Prefetch("tags", queryset=Tag.objects.filter(name="python"), to_attr="python_tags")
)

This is the most common prefetch_related pitfall. It silently turns an optimized query back into an N+1 with no error or warning — the sort of thing one discovers in production at the least opportune moment.

The Decision Tree — select_related vs. prefetch_related

In practice, the combined pattern for the bookstore models:

books = (
    Book.objects
    .select_related("author", "author__country")
    .prefetch_related("tags", "reviews")
)

This produces:

• 1 query for books with author and country JOINed (select_related)
• 1 query for tags with the IN clause (prefetch_related)
• 1 query for reviews with the IN clause (prefetch_related)

3 queries total. Regardless of how many books are in the result set. That is the kind of number one can present with confidence.

Preventing N+1 Regressions

assertNumQueries in Tests

If you take one thing from this guide, let it be this. assertNumQueries is the single most effective prevention mechanism. Assert the expected query count for every view and API endpoint:

class BookListViewTest(TestCase):
    def setUp(self):
        self.country = Country.objects.create(name="UK")
        self.author = Author.objects.create(name="Author 1", country=self.country)
        for i in range(50):
            book = Book.objects.create(
                title=f"Book {i}",
                author=self.author,
                published_date="2026-01-01"
            )
            book.tags.add(Tag.objects.create(name=f"tag-{i}"))
            Review.objects.create(book=book, rating=5, body="Great")

    def test_book_list_query_count(self):
        """Book list view uses exactly 3 queries: books+author, tags, reviews."""
        with self.assertNumQueries(3):
            response = self.client.get("/books/")
        self.assertEqual(response.status_code, 200)
        self.assertEqual(len(response.context["books"]), 50)

When someone adds a new related field access in the template or serializer — {{ book.publisher.name }}, for instance — the assertNumQueries(3) assertion fails immediately. The developer sees the failure, adds the appropriate select_related or prefetch_related, and the N+1 never reaches production.

Write assertNumQueries tests for every view, every API endpoint, and every management command that queries the database. This is the pattern that scales, and your future self will thank you for it.

Django's Strict Loading (Django 4.2+)

A welcome addition. strict_loading() on a queryset raises an AttributeError if any lazy loading occurs on the returned instances:

books = Book.objects.strict_loading().all()

for book in books:
    print(book.title)          # works fine — title is on the Book model
    print(book.author.name)    # raises AttributeError — author was not eager-loaded

This catches N+1 at development time: if you forget to add select_related or prefetch_related, the strict queryset tells you immediately instead of silently executing hundreds of queries.

The correct pattern with strict loading:

books = (
    Book.objects
    .select_related("author")
    .prefetch_related("tags")
    .strict_loading()
)

for book in books:
    print(book.author.name)     # works — author was select_related
    for tag in book.tags.all(): # works — tags were prefetch_related
        print(tag.name)

Use strict loading in development and testing to enforce eager loading. Most teams leave it on in production as well — the AttributeError is a better failure mode than silently degraded performance.

Common Patterns That Silently Introduce N+1

These deserve particular care, because they produce N+1 queries without any visible sign in the code that ordinarily receives your attention.

Template access: {{ book.author.name }} in a Django template triggers lazy loading if select_related was not used in the view's queryset. The template engine silently evaluates the relationship, and there is no visible query in the view code.

DRF serializer fields: Django REST Framework serializers that include related fields (author_name = serializers.CharField(source="author.name")) lazy-load the relationship unless the view's queryset is optimized. See the DRF nested serializer N+1 guide for specific fixes.

Admin list_display: list_display = ['title', 'author'] in a Django admin class lazy-loads each author for every row in the list view. Fix by overriding get_queryset:

class BookAdmin(admin.ModelAdmin):
    list_display = ["title", "author"]

    def get_queryset(self, request):
        return super().get_queryset(request).select_related("author")

Signal handlers: A post_save signal handler that accesses related objects (instance.author.name) triggers an additional query for every signal invocation. If the signal fires during a bulk create, this produces an N+1.

Property methods: A @property on a model that accesses a related object is an invisible N+1 trigger when the property is called in a loop:

class Book(models.Model):
    # ...
    @property
    def author_display(self):
        return f"{self.author.name} ({self.author.country.name})"  # 2 potential lazy loads

String representations: __str__ methods that include related object data are triggered by the admin, logging, debugging, and anywhere Django converts an object to a string:

class Book(models.Model):
    def __str__(self):
        return f"{self.title} by {self.author.name}"  # lazy load on every str() call

Real-World Example — Before and After

Before: The Implementation That Needs Attending To

A book list page that shows each book's title, author name, author country, review count, and tags:

# views.py
def book_list(request):
    books = Book.objects.all()
    return render(request, "books/list.html", {"books": books})

<!-- books/list.html -->
{% for book in books %}
<div>
    <h3>{{ book.title }}</h3>
    <p>{{ book.author.name }} ({{ book.author.country.name }})</p>
    <p>{{ book.reviews.count }} reviews</p>
    <p>Tags: {% for tag in book.tags.all %}{{ tag.name }}{% if not forloop.last %}, {% endif %}{% endfor %}</p>
</div>
{% endfor %}

For 100 books, this generates:

401 queries for 100 books. I'm afraid that is rather more than the situation requires.

After: The Optimized Implementation

# views.py
from django.db.models import Count, Prefetch

def book_list(request):
    books = (
        Book.objects
        .select_related("author", "author__country")
        .prefetch_related("tags")
        .annotate(review_count=Count("reviews"))
    )
    return render(request, "books/list.html", {"books": books})

<!-- books/list.html (updated to use annotated review_count) -->
{% for book in books %}
<div>
    <h3>{{ book.title }}</h3>
    <p>{{ book.author.name }} ({{ book.author.country.name }})</p>
    <p>{{ book.review_count }} reviews</p>
    <p>Tags: {% for tag in book.tags.all %}{{ tag.name }}{% if not forloop.last %}, {% endif %}{% endfor %}</p>
</div>
{% endfor %}

401 queries become 2. The timing improvement is proportional: the original implementation might take 800ms on a remote database; the optimized version completes in under 15ms.

The test:

def test_optimized_book_list(self):
    with self.assertNumQueries(2):
        response = self.client.get("/books/")
    self.assertEqual(response.status_code, 200)

Two queries. One test. The matter is resolved.

For the full Django PostgreSQL optimization playbook — connection pooling, projections, bulk operations, and indexing — see the comprehensive Django guide. For understanding the query plans behind your N+1 queries, see the EXPLAIN ANALYZE guide. For comprehensive PostgreSQL tuning, see the performance tuning guide.

Where Gold Lapel Fits

Gold Lapel's proxy sees the N+1 pattern at the database level — repeated queries with identical structure but varying parameter values arriving in rapid succession. The proxy identifies this pattern even when application code obscures it: nested serializers, template includes, signal chains, and property methods all generate the same database-level signature.

This is not a replacement for fixing the N+1 with select_related and prefetch_related — the techniques above are the right solution, and you should leave this guide equipped to apply them. Gold Lapel is a complement that catches the patterns you miss: the ones introduced by a new template tag, an admin customization, or a signal handler three layers deep.