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.
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.
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.
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.
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.
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.