The N+1 Query Problem That's Silently Slowing Your Django App

PROFESSIONAL DJANGO ENGINEERING SERIES #5

100 orders. A loop. A template that accesses order.user.email. Result: 701 database queries. Here is how it happens — and how to fix it in one line.

The N+1 query problem is the most common performance issue in Django applications. It is also the most invisible one in development, because your local database has twenty records. It only becomes visible in production, where it has twenty thousand — and by then, it is an incident.

The ORM does not hide SQL from you. It writes SQL for you. Understanding what SQL it writes — and why — is what separates ORM users from ORM professionals.

How N+1 Happens

Here is a view that looks perfectly innocent:

# views.py — looks clean, causes disaster at scale
def order_list(request):
    orders = Order.objects.all()  # Query 1: SELECT * FROM orders
    return render(request, 'orders/list.html', {'orders': orders})

Then in the template:

{# orders/list.html #}
{% for order in orders %}
    {{ order.user.email }}      {# Query 2,3,...N: SELECT user WHERE id=? #}
    {% for item in order.items.all %}
        {{ item.product.name }} {# Query N+1, N+2... for each item #}
    {% endfor %}
{% endfor %}

For 100 orders with 5 items each, this template fires:

• 1 query to fetch all orders
• 100 queries to fetch each order's user
• 100 queries to fetch each order's items
• 500 queries to fetch each item's product — total: 701 queries

Every page load. Every visitor. The database CPU climbs. Response times degrade. And because it works fine locally with your 10 test records, no one notices until production slows to a crawl.

The Fix: select_related and prefetch_related

Django provides two tools for solving N+1, and using the right one matters.

select_related — for ForeignKey and OneToOne

Use select_related when traversing ForeignKey or OneToOneField relationships. It generates a SQL JOIN and fetches everything in one query:

# BAD: 1 + N queries
orders = Order.objects.all()

# GOOD: 1 query with a JOIN
orders = Order.objects.select_related('user', 'shipping_address')

# Multi-level: traverse FK chains
orders = Order.objects.select_related('user__profile')

prefetch_related — for reverse FK and ManyToMany

Use prefetch_related for reverse ForeignKey relationships and ManyToManyFields — relationships where the related object is a collection. It executes a separate query per relationship and joins the results in Python:

# BAD: 1 query for orders + N queries for items + N*M for products
orders = Order.objects.all()

# GOOD: 3 queries regardless of order count
# Query 1: SELECT * FROM orders
# Query 2: SELECT * FROM order_items WHERE order_id IN (...)
# Query 3: SELECT * FROM products WHERE id IN (...)
orders = Order.objects.prefetch_related('items__product')

# Combine both for the full solution:
orders = (
    Order.objects
    .select_related('user', 'shipping_address')
    .prefetch_related('items__product')
    .filter(status='paid')
    .order_by('-created_at')
)

⚠ A common mistake that defeats prefetch_related
Filtering a prefetched relation inside a loop re-fires a query per object and completely defeats the prefetch. This is the trap:

for order in orders:  # prefetched
    active_items = order.items.filter(is_active=True)  # NEW query each time!

Fix: use a Prefetch object with a custom queryset to apply the filter at prefetch time.

How to Detect N+1: django-debug-toolbar

Install django-debug-toolbar in your development environment and make the SQL panel part of your workflow. It shows every query executed during a request, the time each took, and whether any are duplicates.

# requirements/local.txt
django-debug-toolbar==4.3.0

# config/settings/local.py
INSTALLED_APPS += ['debug_toolbar']
MIDDLEWARE.insert(1, 'debug_toolbar.middleware.DebugToolbarMiddleware')
INTERNAL_IPS = ['127.0.0.1']

A healthy page has under 10 queries. A page with 50+ has an N+1 problem. Treat the SQL panel as a mandatory check before every feature is considered done.

Lock it in with Query Count Tests

The best way to prevent N+1 regressions is to assert query counts in your test suite:

from django.test import TestCase

class OrderListViewTest(TestCase):
    def test_query_count_is_bounded(self):
        user = UserFactory()
        OrderFactory.create_batch(20, user=user)

        self.client.force_login(user)
        with self.assertNumQueries(3):  # orders + users + items
            response = self.client.get('/orders/')

        self.assertEqual(response.status_code, 200)

This test will fail the moment someone adds a line to the view that introduces an N+1. That failure, caught in CI before it reaches production, is exactly what the test is for.

The N+1 problem is not unique to Django — it appears in every ORM-based framework. But Django's ORM makes it particularly invisible because querysets are lazy and the query only fires at iteration time, often deep in a template. select_related and prefetch_related are the tools designed to solve it. Learn to reach for them automatically whenever you access a related object.