Deep dive into Django ORM performance. Learn how to identify and fix N+1 queries, use select_related, prefetch_related, only(), defer(), and Django Debug Toolbar to measure the real impact.
The Django ORM is a productivity marvel — until it quietly fires four hundred queries to render a page that should run two. The convenience that lets you traverse relationships with a dot is exactly what hides the cost, and the result is the single most common Django performance problem: the N+1 query. This tutorial goes deep on ORM optimization — eliminating N+1 queries, the tools that fix them, and the wider set of techniques that keep your database access fast as your app grows.
The ORM's great strength is that it makes database access feel like ordinary Python: you access order.customer.name and the data is just there. But that simplicity hides the fact that each such access may be a database query, executed lazily the moment you touch the attribute. In a loop over many objects, this lazy loading turns one query into hundreds, and because the code looks innocent — just attribute access — the cost is invisible in review. Understanding that the ORM trades explicit query control for convenience, and that the convenience can silently multiply queries, is the foundation of optimizing it. The goal is to keep the productivity while taking back control of when and how queries run.
The N+1 problem is the dominant Django performance bug. You fetch a list of N objects with one query, then in a loop access a related object on each, firing one query per object — N more queries, for N+1 total. A page listing fifty orders, each showing its customer's name, runs one query for the orders and fifty for the customers. Each individual query is fast, so nothing looks wrong, but the cumulative round trips make the page slow, and the problem grows with your data. Recognizing the N+1 pattern — a query inside a loop over query results — is the most valuable diagnostic skill in Django performance, because this one bug accounts for a huge share of real-world slowness.
The fix for N+1 on forward relationships is select_related, which tells the ORM to fetch the related objects in the same query using a SQL JOIN. Instead of a query per related object, you get one query that brings everything back together:
# N+1: one query for orders, then one per order for the customer
for order in Order.objects.all():
print(order.customer.name)
# Fixed: a single JOIN fetches orders and customers together
for order in Order.objects.select_related("customer"):
print(order.customer.name)
Use select_related for single-valued relationships — foreign keys and one-to-ones — that you will traverse. It collapses the N+1 into one query, and you can follow multiple relationships and chains in a single call.
A JOIN works for single-valued relations, but for many-valued ones — reverse foreign keys and many-to-many — it would multiply rows, so the ORM uses a different strategy. prefetch_related runs a second query to fetch all the related objects and joins them to the originals in Python:
for order in Order.objects.prefetch_related("items"):
for item in order.items.all(): # no extra query — already fetched
print(item.name)
Two queries total — one for orders, one for all their items — instead of one per order. Use prefetch_related for collections, and combine it with select_related freely: each addresses a different relationship shape, and together they cover every traversal a page needs.
The prefetched data lives in a per-instance cache that is only consulted when you call .all() on the related manager. Any method that builds a new queryset — filter(), exclude(), order_by(), annotate() — bypasses that cache and goes back to the database, one query per parent object. This is the most common reason a developer adds prefetch_related, sees no improvement, and concludes it "does not work":
orders = Order.objects.prefetch_related("items")
for order in orders:
# Uses the cache: no query
total = sum(i.price for i in order.items.all())
# New queryset: one query per order, prefetch ignored
shipped = order.items.filter(shipped=True)
# Filter in Python instead, over the cached rows
shipped = [i for i in order.items.all() if i.shipped]
Filtering in Python over a small prefetched set is fine; if the set is large or the filter is selective, push the filter into a Prefetch queryset instead, as the next section shows. Model methods and template tags are frequent hiding places for this bug, because the .filter() call lives far from the view that set up the prefetch.
Sometimes a simple prefetch is not enough — you want to filter the related objects, order them, or apply a select_related within the prefetch. The Prefetch object gives that control:
from django.db.models import Prefetch
Order.objects.prefetch_related(
Prefetch("items",
queryset=OrderItem.objects.filter(active=True)
.select_related("product"))
)
This prefetches only the active items, and fetches each item's product efficiently in the same operation. Prefetch objects are how you keep prefetching efficient when the related set needs filtering or its own optimization, avoiding the trap of prefetching everything and then filtering in Python, which wastes both queries and memory.
By default a filtered Prefetch replaces the contents of order.items.all(), which can confuse other code that expects the full set. Passing to_attr stores the result as a plain list on a separate attribute instead, which makes the intent explicit and lets you prefetch the same relation more than once with different filters:
from django.db.models import Prefetch
orders = Order.objects.select_related("customer").prefetch_related(
Prefetch(
"items",
queryset=OrderItem.objects.filter(active=True).select_related("product"),
to_attr="active_items",
),
Prefetch(
"notes",
queryset=Note.objects.order_by("-created_at")[:3],
to_attr="recent_notes",
),
"items__product__tags",
)
for order in orders:
order.active_items # list, no query
order.recent_notes # at most three per order, no query
Two details are worth knowing. First, since Django 4.2 a Prefetch queryset may be sliced; Django implements the per-parent limit with a window function, so "the three most recent notes for each order" is still one extra query rather than one per order. Second, lookups nest: "items__product__tags" prefetches items, then their products, then the products' tags, adding one query per level regardless of how many orders are on the page. A page that previously ran hundreds of queries typically lands at four or five with this pattern, and that number stays constant as the page size grows.
You cannot fix what you cannot see, and N+1 queries are invisible in code. The tools that surface them are essential: django-debug-toolbar shows the queries a page runs in development, with duplicates highlighted, and django-silk records per-request queries including in production-like settings. When you see "73 queries, 68 duplicates" on a page that should run a handful, you have found an N+1, and the duplicates point you at the relationship to prefetch. Make a habit of watching the query count on your important pages; a number that grows with the data on the page is the signature of an N+1 waiting to be collapsed with select_related or prefetch_related.
Toolbars catch regressions only when someone looks. A test that pins the query count catches them in CI, the moment a template change or a new serializer field reintroduces a lazy load. The key is to create enough rows that an N+1 would be obvious, then assert a number that does not depend on the row count:
from django.test import TestCase
from django.test.utils import CaptureQueriesContext
from django.db import connection
from django.urls import reverse
class OrderListQueryTests(TestCase):
@classmethod
def setUpTestData(cls):
for i in range(30):
customer = Customer.objects.create(name=f"c{i}")
order = Order.objects.create(customer=customer)
OrderItem.objects.bulk_create(
[OrderItem(order=order, name=f"item{j}") for j in range(3)]
)
def test_order_list_query_count_is_constant(self):
self.client.force_login(make_staff_user()) # your own test helper
# session + user + orders (with customer JOIN) + items;
# adjust to what your middleware stack actually queries
with self.assertNumQueries(4):
self.client.get(reverse("orders:list"))
def test_queryset_does_not_scale_with_rows(self):
with CaptureQueriesContext(connection) as ctx:
orders = Order.objects.select_related("customer").prefetch_related("items")
for order in orders:
order.customer.name, [i.name for i in order.items.all()]
# orders JOIN customers, then one query for all items
self.assertEqual(len(ctx.captured_queries), 2,
[q["sql"] for q in ctx.captured_queries])
When assertNumQueries fails, its message lists every captured SQL statement, which usually points straight at the missing prefetch. Keep the exact number rather than an upper bound: a test that asserts "fewer than 20" lets a new N+1 slip in on pages with small fixtures.
By default a queryset fetches every column of every row, which is wasteful when a table is wide and you need only a few fields. only() fetches just the named columns (and the primary key), while defer() fetches everything except the named ones. On a table with large text or binary columns you do not need for a given view, this cuts the data transferred and the memory used. The caveat is that accessing a deferred field later triggers a query, so use these when you know which fields you need. For list views over wide models, fetching only the displayed columns is a simple, effective optimization that reduces both database and network load.
When you do not need full model instances — just some data to display or aggregate — building model objects is overhead you can skip. values() returns dictionaries and values_list() returns tuples, fetching only the requested fields and skipping model instantiation entirely:
emails = User.objects.filter(active=True).values_list("email", flat=True)
For read-only data, reports, or feeding another system, these are significantly lighter than fetching and instantiating full objects. They are a good fit whenever you want raw data rather than the behavior of model instances, trading the convenience of model methods for a leaner, faster query that does less work per row.
Counting, summing, and averaging should happen in the database, not in Python. Fetching rows to count them in a loop pulls all the data over the wire and wastes memory; the database can compute the aggregate and return just the number. Django's aggregate and annotate push this work down:
from django.db.models import Count, Sum
Customer.objects.annotate(order_count=Count("orders"),
total_spent=Sum("orders__total"))
annotate adds a per-row computed value, aggregate computes over the whole queryset. Letting the database do arithmetic over rows — where it is fast and the data already lives — instead of pulling rows into Python is one of the clearer ORM wins, especially as row counts grow.
Annotating across two different many-valued relations in one queryset produces wrong numbers, not just slow ones. Each relation adds a JOIN, the rows multiply, and every aggregate is computed over the cross product. A customer with 3 orders and 4 support tickets appears as 12 joined rows, so both counts come back as 12:
from django.db.models import Count, Sum, Q, OuterRef, Subquery, IntegerField
from django.db.models.functions import Coalesce
# Wrong: orders x tickets cross product inflates both values
Customer.objects.annotate(n_orders=Count("orders"), n_tickets=Count("tickets"))
# Counts can be repaired with distinct=True ...
Customer.objects.annotate(
n_orders=Count("orders", distinct=True),
n_tickets=Count("tickets", distinct=True),
)
# ... but sums cannot: aggregate each relation in its own subquery
orders_total = (
Order.objects.filter(customer=OuterRef("pk"))
.values("customer")
.annotate(total=Sum("total"))
.values("total")
)
Customer.objects.annotate(
total_spent=Coalesce(Subquery(orders_total), 0, output_field=IntegerField()),
n_open_tickets=Count("tickets", filter=Q(tickets__status="open")),
)
The subquery pattern — filter on OuterRef("pk"), group with values(), aggregate, then select the single aggregated column — evaluates the sum per customer without joining orders into the outer query, so it cannot be inflated by other relations. Coalesce turns the NULL a subquery returns for customers with no orders into zero; if total is a DecimalField, use a matching output_field and Value(Decimal("0")) instead. The filter= argument on aggregates compiles to a SQL FILTER (WHERE ...) clause on PostgreSQL, letting one pass over the data compute several conditional counts, which is far cheaper than running a separate count() query per status.
Creating or updating many objects one at a time means one query each — slow and chatty. bulk_create inserts many rows in a single query, and bulk_update updates many in one statement, collapsing hundreds of round trips into one:
OrderItem.objects.bulk_create([OrderItem(order=o, ...) for ... ])
For large imports, batch creation, or mass updates, bulk operations are dramatically faster than looping over saves. There are caveats — they may skip signals and some model logic — so use them where the performance matters and you understand what they bypass. But for the common case of writing many similar rows, reaching for bulk operations instead of a save loop turns a slow operation into a fast one.
A single INSERT with a hundred thousand rows is itself a problem: it holds locks for the whole statement, builds a huge parameter list, and can exceed driver limits. Pass batch_size so Django splits the work into several statements, and wrap the whole import in transaction.atomic() so a failure halfway through does not leave partial data. On PostgreSQL, bulk_create sets primary keys on the returned objects, so you can use them immediately to build child rows:
from django.db import transaction
with transaction.atomic():
products = Product.objects.bulk_create(
[Product(sku=row["sku"], name=row["name"], price=row["price"]) for row in rows],
batch_size=1000,
update_conflicts=True,
unique_fields=["sku"],
update_fields=["name", "price"],
)
# Uniform change: one UPDATE, no instances loaded
Product.objects.filter(discontinued=True).update(stock=0)
# Per-row values: load, modify in Python, write back in batches
changed = list(Product.objects.filter(sku__in=price_map).only("id", "sku", "price"))
for p in changed:
p.price = price_map[p.sku]
Product.objects.bulk_update(changed, ["price"], batch_size=500)
update_conflicts with unique_fields and update_fields turns the insert into an upsert (INSERT ... ON CONFLICT DO UPDATE on PostgreSQL), which makes repeated imports idempotent; ignore_conflicts=True skips duplicates instead, but then primary keys are not set on the returned objects. Know precisely what all bulk paths skip: save() is not called, pre_save/post_save signals do not fire, and auto_now fields are not refreshed by update() or bulk_update() — set updated_at=timezone.now() explicitly if downstream code relies on it. If a search index or cache is kept fresh by signals, a bulk import needs an explicit reindex step afterwards.
Looping over a queryset loads all its rows into memory at once, which is fine for a page of results but catastrophic for a query that returns millions of rows — it can exhaust memory and crash the process. iterator() streams rows from the database in chunks rather than loading them all, keeping memory flat regardless of the result size. For management commands, exports, and any processing over a very large table, iterating is what lets you handle the data without materializing it all. The default eager loading is a convenience for normal-sized results that becomes a liability at scale, and iterator() is the tool that scales it down to a constant memory footprint.
The fastest queryset still depends on the database having the right indexes. A filter or ordering on an unindexed column forces a full table scan no matter how clean your ORM code is, so every column you regularly filter, join, or order on should be indexed, declared in the model's Meta.indexes. When a query is slow despite good ORM usage, look at the actual SQL (the ORM exposes it via .query) and run it through EXPLAIN to see whether the database is using an index or scanning. ORM optimization and database indexing are two halves of the same goal: the ORM controls how many queries run and what they fetch, and indexes control how fast each one executes.
A foundational concept that prevents many performance mistakes is that querysets are lazy: building a queryset issues no database query, and the query runs only when you actually evaluate it — by iterating, slicing in certain ways, calling list(), or accessing its results. This laziness is powerful because it lets you compose and refine a query in steps without hitting the database each time, but it also surprises people who unknowingly evaluate the same queryset multiple times, each triggering a fresh query. Understanding exactly when a queryset hits the database — and caching the result when you need it more than once — is essential to controlling how many queries your code actually runs.
A common inefficiency is fetching rows just to check something about them. To test whether any matching rows exist, exists() asks the database that question directly and returns a boolean, far cheaper than loading the rows and checking if the list is non-empty. To count, count() lets the database count without transferring rows. Using these instead of pulling full result sets into Python and inspecting them avoids moving data you do not need. These small choices — asking the database the precise question rather than fetching everything and computing the answer in Python — add up to meaningful savings on pages that check existence or counts frequently.
Updating a value based on its current value — incrementing a counter, adjusting a balance — is often done by reading the object, changing it in Python, and saving, which both costs an extra query and risks a race condition between concurrent updates. F expressions let the database do the arithmetic in a single atomic statement: F("views") + 1 increments the column directly in the database without reading it first. This is both faster and safe against concurrent updates, since the database performs the operation atomically. Reaching for F expressions for any update that depends on a current value is a small change that eliminates both an extra query and a class of concurrency bug.
Beyond the queries themselves, the cost of talking to the database includes establishing connections, and opening a new one per request adds latency. Django's persistent connections keep a connection open across requests for reuse, cutting that overhead, while at larger scale a connection pooler handles it more robustly. Understanding that every query carries round-trip overhead reinforces why reducing query count matters so much — each query is not just its execution time but a round trip to the database. The optimization techniques throughout this tutorial ultimately aim at the same goal: fewer, more efficient round trips between your application and the database that serves it.
The Django ORM's convenience hides the cost of database access, and the price is most often paid as the N+1 query — one query per object in a loop, the dominant Django performance bug. Collapse N+1 on single-valued relationships with select_related (a JOIN) and on collections with prefetch_related (a second query joined in Python), reaching for Prefetch objects when the related set needs filtering or its own optimization. Find these problems with django-debug-toolbar and django-silk by watching query counts. Beyond N+1, fetch only what you need with only, defer, values, and values_list; push counting and arithmetic into the database with annotate and aggregate; write many rows with bulk_create and bulk_update; stream huge result sets with iterator; and make sure the columns you filter and order on are indexed. Master these and you keep the ORM's productivity while taking back control of its cost — the difference between a page that runs two queries and one that quietly runs four hundred.