We inherited a Django project last year where a single page load was making 847 database queries. The page showed a list of 50 orders. Each order loaded its customer. Each customer loaded their address. Nobody had noticed because the page was still fast enough on the staging database, which had 200 rows. In production, with 50,000 rows, it was taking 14 seconds to render.
The N+1 query problem is not exotic. It is one of the most common performance issues in Django projects, and it is invisible until it isn't. This post covers how we find it, how we fix it, and how we catch it before it gets to production.
What the N+1 problem actually is
The name comes from the query pattern: one query to fetch a list of objects, then N more queries — one for each object — to fetch related data. If you have 50 orders and each one triggers a separate query to load its customer, that is 51 queries total. If loading the customer also triggers a query for the address, that is 101.
Here is the pattern that causes it:
# views.py
def order_list(request):
orders = Order.objects.all()[:50]
return render(request, 'orders/list.html', {'orders': orders})
<!-- orders/list.html -->
{% for order in orders %}
<tr>
<td>{{ order.reference }}</td>
<td>{{ order.customer.name }}</td> {# triggers a query per order #}
<td>{{ order.customer.address.city }}</td> {# triggers another query per order #}
</tr>
{% endfor %}
Django is doing exactly what you asked it to do. You asked for orders. When the template accesses order.customer, Django fetches the customer — as a separate database query, because you didn't tell it to fetch customers upfront.
The problem is silent. It does not raise an error. It does not log a warning. It just makes a lot of queries.
How we find N+1 problems
django-debug-toolbar in development
The first tool we install on every Django project is django-debug-toolbar. It adds a sidebar to your pages showing the number of SQL queries, the actual SQL, and how long each one took.
# settings.py (development only)
INSTALLED_APPS = [
...
'debug_toolbar',
]
MIDDLEWARE = [
'debug_toolbar.middleware.DebugToolbarMiddleware',
...
]
INTERNAL_IPS = ['127.0.0.1']
When you see "Similar queries: 47", that is your N+1 flag. Click into the queries and you will see the same SELECT * FROM customers WHERE id = ? repeated 47 times with different values.
nplusone for automated detection
django-debug-toolbar shows you problems manually. nplusone catches them in tests:
# settings.py (test/development)
INSTALLED_APPS = [
...
'nplusone.apps.NPlusOneConfig',
]
NPLUSONE_RAISE = True # raise an exception instead of just logging
# tests.py
from django.test import TestCase
class OrderListViewTests(TestCase):
def setUp(self):
customer = Customer.objects.create(name="Acme Corp")
Address.objects.create(customer=customer, city="London")
for i in range(10):
Order.objects.create(customer=customer, reference=f"ORD-{i:04d}")
def test_order_list_no_n_plus_one(self):
# nplusone will raise an exception if it detects N+1 queries
response = self.client.get('/orders/')
self.assertEqual(response.status_code, 200)
If your view has an N+1 problem, the test will fail with a clear error message pointing at the relationship being lazily loaded. This is how we catch N+1 issues before they reach code review.
Logging all SQL queries
When you suspect a problem but want raw numbers:
# settings.py
LOGGING = {
'version': 1,
'handlers': {
'console': {
'class': 'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'level': 'DEBUG',
},
},
}
This logs every SQL query to the console. Noisy, but useful when you need to see exactly what is happening.
How we fix N+1 problems
select_related — for ForeignKey and OneToOne
select_related performs a SQL JOIN and fetches related objects in the same query. Use it for ForeignKey and OneToOneField relationships:
# Before: 1 query for orders + N queries for customers + N queries for addresses
orders = Order.objects.all()[:50]
# After: 1 query with JOINs
orders = Order.objects.select_related(
'customer',
'customer__address',
).all()[:50]
The resulting SQL does a JOIN across all three tables and retrieves everything in one round trip. The template can access order.customer.name and order.customer.address.city without triggering any additional queries.
# You can chain selects arbitrarily deep
orders = Order.objects.select_related(
'customer__address__country',
'assigned_to', # a ForeignKey to a User
).all()
prefetch_related — for ManyToMany and reverse ForeignKey
select_related uses JOINs, which do not work for ManyToMany or reverse ForeignKey relationships (the "many" side). Use prefetch_related for those:
# Before: 1 query for orders + N queries for line items
orders = Order.objects.all()[:50]
# After: 2 queries total (one for orders, one for all line items)
orders = Order.objects.prefetch_related('line_items').all()[:50]
prefetch_related runs a second query — SELECT * FROM line_items WHERE order_id IN (1, 2, 3, ...) — and then assembles the results in Python. It is not a JOIN, but it is still vastly better than N separate queries.
You can combine both:
orders = Order.objects.select_related(
'customer',
'customer__address',
).prefetch_related(
'line_items',
'line_items__product',
'tags',
).all()[:50]
Prefetch with custom querysets
Sometimes you need to filter or order the prefetched data:
from django.db.models import Prefetch
active_line_items = LineItem.objects.filter(
is_cancelled=False
).select_related('product').order_by('position')
orders = Order.objects.prefetch_related(
Prefetch('line_items', queryset=active_line_items)
).all()[:50]
Prefetch gives you full control over the prefetched queryset. You can filter, order, annotate, and even give the prefetched result a different attribute name:
orders = Order.objects.prefetch_related(
Prefetch(
'line_items',
queryset=LineItem.objects.filter(is_cancelled=False),
to_attr='active_line_items', # access as order.active_line_items
)
).all()
Annotations instead of computed properties
A different class of N+1 occurs with computed properties that hit the database:
class Order(models.Model):
@property
def total(self):
# This runs a query for every order in a list
return self.line_items.aggregate(total=Sum('price'))['total'] or 0
If you loop through 50 orders and access order.total, that is 50 aggregation queries. The fix is to annotate:
from django.db.models import Sum
orders = Order.objects.annotate(
total=Sum('line_items__price')
).all()[:50]
Now order.total is a field computed in the database during the original query — no extra queries.
You can annotate counts, sums, averages, and more complex expressions:
from django.db.models import Count, Q, Case, When, IntegerField
orders = Order.objects.annotate(
item_count=Count('line_items'),
cancelled_item_count=Count(
'line_items',
filter=Q(line_items__is_cancelled=True)
),
).all()
What we do in practice
We have a few rules that have eliminated most N+1 issues on our projects:
Always annotate counts at query time. If a model has a related count that appears in a list view — number of orders for a customer, number of comments on a post — we annotate it instead of using a property.
Add nplusone to every project's test suite. It costs nothing and catches regressions immediately.
Review querysets in code review. When a PR touches a view that renders a list, we check that the queryset has appropriate select_related and prefetch_related calls. If the template accesses a related object, the queryset should be fetching it.
Profile staging with production-scale data. N+1 problems are often invisible with small datasets. We keep a script that populates a staging database with a realistic number of rows, and we run django-debug-toolbar against it before any major release.
The broader principle
The N+1 problem is not really a bug in your code — the code works. It is a mismatch between what you told Django to load and what you actually end up needing. The fix is to be explicit about your data requirements at the queryset level rather than letting Django figure it out lazily.
The tools exist: select_related, prefetch_related, Prefetch, annotate. The habit is what matters — asking yourself, when you write a queryset, what related data will this view actually need?
It takes a few minutes to think through when you write the code. It takes hours to diagnose and fix in production.
Lycore builds production Django applications for businesses — custom backends, REST APIs, AI integrations, and full-stack web and mobile products. Get in touch if you're working on a Django project.
Top comments (1)
Laravel has a switch for this. Model::preventLazyLoading() in a service provider, usually only outside production, and any relation loaded lazily across a collection throws instead of quietly firing another query.
That 847-query page would have blown up in development long before staging.