Optimizing database access in Django comes down to one principle: do the fewest queries possible, fetch only the data you need, and let the database do the heavy lifting. Most slow Django pages are not caused by slow SQL — they are caused by the Django ORM silently firing hundreds of small queries (the N+1 problem) or pulling back columns and rows the view never uses. This guide is a current, hands-on reference for the tools that fix that: select_related/prefetch_related, only/defer, values/values_list, annotate/aggregate, F/Q expressions, bulk operations, iterator(), indexing, and QuerySet.explain() — with code that runs on Python 3.12+ and Django 5.x (notes for the 4.2 LTS where it matters).
At MicroPyramid we have spent 12+ years and 50+ projects tuning Django data layers, and the same handful of patterns below account for the vast majority of the wins.
Why QuerySets are lazy (and why it matters)
A Django QuerySet does not touch the database when you build it. SQL is only sent when the QuerySet is evaluated — when you iterate it, slice with a step, call len(), list(), bool(), or render it in a template. Understanding this lets you build and combine filters cheaply and control exactly when the query runs.
qs = User.objects.all() # No SQL yet — lazy
qs = qs.filter(is_active=True) # Still no SQL — filters compose lazily
for user in qs: # SQL runs HERE, results are cached on qs
print(user.email)
for user in qs: # No new SQL — reuses the cached result set
print(user.first_name)The result cache: reuse one QuerySet instead of re-querying
Once a QuerySet is evaluated, its rows are cached on that QuerySet object. Reuse the same variable and you pay for the database once; create a fresh QuerySet and you pay again.
# Two database hits — a new QuerySet each time
first_names = [u.first_name for u in User.objects.all()]
emails = [u.email for u in User.objects.all()]
# One database hit — the second loop reads from the cache
users = User.objects.all()
first_names = [u.first_name for u in users] # hits the DB, caches rows
emails = [u.email for u in users] # served from the cacheTwo important caveats. First, an unevaluated slice does not populate the full cache — users[:5] runs its own LIMIT query every time. If you need a few different slices of the same data, force evaluation first with bool(users) (or list(users)) and then slice the cached list. Second, the result cache lives only as long as the QuerySet object; it is per-request, in-memory and is not shared between requests — for that you need Django's cache framework (covered below).
Killing the N+1 problem: select_related vs prefetch_related
The single most common Django performance bug is the N+1 query: you fetch N objects in one query, then trigger one extra query per object to load a related row. select_related and prefetch_related both fix it, but they work differently and apply to different relationship types.
select_related |
prefetch_related |
|
|---|---|---|
| Technique | SQL JOIN, related columns added to the SELECT |
Separate query per relation, joined in Python |
| Queries added | 0 (folded into the main query) | 1 per relation prefetched |
| Works for | ForeignKey, OneToOneField (forward), single-valued |
ManyToManyField, reverse FK, GenericRelation, multi-valued |
| Risk | Wide rows / row multiplication on deep joins | Extra round trips; large IN (...) lists |
| Use when | Following a to-one relation | Following a to-many (or to-one in bulk) relation |
select_related — for to-one relations
Always pass the relation names explicitly. Calling select_related() with no arguments is deprecated (it is slated for removal in Django 7.0), so name the fields you actually traverse — that also keeps the JOIN narrow. You can follow chains with the __ lookup.
# N+1: one query for the books, then one per book for the author
for book in Book.objects.all():
print(book.title, book.author.name) # book.author hits the DB each loop
# Fixed: a single JOINed query loads books AND their authors
books = Book.objects.select_related("author")
for book in books:
print(book.title, book.author.name) # no extra queries
# Follow a chain of to-one relations in the same JOIN
books = Book.objects.select_related("author__profile")prefetch_related — for to-many relations
select_related cannot follow a many-valued relation without multiplying rows, so for ManyToManyField and reverse foreign keys use prefetch_related. It runs a second query and stitches the results together in Python.
# N+1: one query for authors, then one per author for their books
for author in Author.objects.all():
print(author.name, list(author.books.all())) # hits DB per author
# Fixed: 2 queries total regardless of how many authors there are
authors = Author.objects.prefetch_related("books")
for author in authors:
print(author.name, list(author.books.all())) # served from prefetch cacheCombine and filter prefetches with the Prefetch object
Prefetch lets you customize the inner query — filter it, order it, call select_related on it, or stash it under a custom attribute with to_attr. Combine select_related and prefetch_related freely.
from django.db.models import Prefetch
# Prefetch only the published books, newest first, and store them
# on author.recent_books so the template needs no extra filtering.
recent = Book.objects.filter(status="published").order_by("-published_on")
authors = Author.objects.prefetch_related(
Prefetch("books", queryset=recent, to_attr="recent_books")
)
for author in authors:
for book in author.recent_books: # a plain list, no extra query
print(book.title)
# Mix both: JOIN the to-one publisher, prefetch the to-many books,
# and select_related the books' category inside the prefetch query.
qs = Author.objects.select_related("agency").prefetch_related(
Prefetch("books", queryset=Book.objects.select_related("category"))
)Fetch only the columns you need: only, defer, values, values_list
select_related/prefetch_related reduce the number of queries; the next lever is reducing how much each query returns. By default Django selects every column on a model. When a table has wide columns (large text, JSON, binary) and your view needs three fields, that is wasted I/O.
| Method | Returns | Use when |
|---|---|---|
only("a", "b") |
Model instances, only listed fields loaded | You need model methods/relations but few columns |
defer("big_field") |
Model instances, everything except deferred | One or two heavy columns you rarely read |
values("a", "b") |
List of dicts |
You want plain data for an API/serializer, no model behavior |
values_list("a", flat=True) |
List of tuples (or scalars with flat=True) |
You need one or two raw columns, e.g. an ID list |
# only(): real Employee instances, but only name + salary are fetched.
# Touching a non-loaded field later triggers a fresh per-object query,
# so only list fields the view actually uses.
employees = Employee.objects.only("name", "salary")
# defer(): load everything EXCEPT a heavy column.
articles = Article.objects.defer("body_html")
# values(): dicts, ideal for feeding a DRF serializer or JsonResponse.
rows = Employee.objects.values("name", "salary")
# -> [{"name": "Ravi", "salary": Decimal("30000")}, ...]
# values_list(..., flat=True): a flat list of one column.
active_ids = User.objects.filter(is_active=True).values_list("id", flat=True)
# -> [1, 2, 5, 8]Push work into the database: F, Q, annotate, aggregate
A huge class of "slow" code is really Python doing in a loop what SQL can do in one statement. Expressions move that work into the database engine.
F() — reference and update columns without loading them
F() refers to a column's value on the database side. It lets you update rows in a single atomic UPDATE (no read-modify-write race) and filter one column against another.
from django.db.models import F
# Give every developer a raise in ONE query, with no race condition.
Employee.objects.filter(designation="Developer").update(salary=F("salary") + 5000)
# Compare two columns server-side instead of in Python.
Product.objects.filter(stock__lt=F("reorder_level"))Q() — OR, NOT and grouped conditions
Chained .filter() calls are AND-ed together. For OR, NOT, and parenthesized logic, use Q objects: | is OR, & is AND, ~ is NOT. For a deeper dive see Querying with Django Q objects.
from django.db.models import Q
# WHERE email LIKE 'an%' OR email LIKE 'sa%'
User.objects.filter(Q(email__startswith="an") | Q(email__startswith="sa"))
# (premium AND active) OR staff
User.objects.filter((Q(plan="premium") & Q(is_active=True)) | Q(is_staff=True))annotate() and aggregate() — counts and rollups in SQL
aggregate() collapses a whole QuerySet to a single dict of summary values; annotate() attaches a computed value to each row. Doing this in SQL is dramatically faster than pulling rows into Python and summing them. Use Count(..., filter=Q(...)) for conditional counts in one pass. See also Aggregation in Django and conditional expressions in queries.
from django.db.models import Count, Avg, Q
# aggregate(): one summary row for the whole table
Employee.objects.aggregate(headcount=Count("id"), avg_salary=Avg("salary"))
# -> {"headcount": 42, "avg_salary": Decimal("48250.00")}
# annotate(): per-author book count, computed in SQL
authors = Author.objects.annotate(num_books=Count("books"))
for a in authors:
print(a.name, a.num_books) # no N+1 — the count came back with the row
# Conditional aggregation: two counts in a single query pass
Author.objects.annotate(
total=Count("books"),
highly_rated=Count("books", filter=Q(books__rating__gte=7)),
)Bulk writes: bulk_create, bulk_update, update_or_create
Looping over .save() or .create() issues one INSERT/UPDATE per row. For more than a handful of rows, the bulk methods collapse that into a few statements.
# Slow: N INSERTs
for name, email in people:
Person.objects.create(name=name, email=email)
# Fast: batched INSERTs (tune batch_size for very large lists)
Person.objects.bulk_create(
[Person(name=n, email=e) for n, e in people],
batch_size=1000,
)
# Bulk-update existing instances in a few UPDATEs (Django 2.2+)
for p in people_qs:
p.is_verified = True
Person.objects.bulk_update(people_qs, ["is_verified"], batch_size=1000)
# Atomic get-or-update-or-insert in a single helper
obj, created = Person.objects.update_or_create(
email="ravi@test.com",
defaults={"name": "Ravi", "is_verified": True},
)Note that bulk_create and bulk_update do not call Model.save() or send the pre_save/post_save signals, and on most databases bulk_create will populate primary keys (PostgreSQL returns them; some backends do not). If you depend on signals or custom save() logic, weigh that trade-off.
Stream large result sets with iterator()
By default, evaluating a QuerySet loads every row into memory and caches it. For a one-pass job over hundreds of thousands of rows (a report, a data migration), that cache is pure overhead. iterator() streams rows in chunks and skips the result cache.
# Loads ALL rows into memory at once — risky on big tables
for order in Order.objects.all():
process(order)
# Streams server-side in chunks; constant memory, no result cache
for order in Order.objects.all().iterator(chunk_size=2000):
process(order)Because iterator() bypasses the cache, do not iterate it twice — re-iterating re-runs the query. It also pairs poorly with prefetch_related unless you pass a large enough chunk_size (Django supports prefetch with iterator() since 4.1, but it still buffers a chunk at a time).
Count and existence checks: count() and exists()
To answer "how many?" or "are there any?", never load the objects. count() issues a SELECT COUNT(*); exists() issues an efficient SELECT 1 ... LIMIT 1. Both avoid materializing rows.
# Just the number — no rows transferred
num = Book.objects.filter(author_id=5).count()
# Just a boolean — stops at the first matching row
if Book.objects.filter(author_id=5).exists():
...
# Anti-pattern: do NOT do this just to test for existence
if len(Book.objects.filter(author_id=5)): # loads every matching row!
...Indexing: the biggest single win for read-heavy tables
No amount of ORM tuning helps if the database is scanning a million rows to satisfy a WHERE or ORDER BY. Index the columns you filter, join, and sort on. In modern Django you declare indexes in Meta.indexes — the old index_together is deprecated and removed in current versions, so do not use it.
from django.db import models
from django.db.models import Index, Q
from django.db.models.functions import Lower
class Book(models.Model):
title = models.CharField(max_length=200, db_index=True) # single-column index
author = models.ForeignKey(
"Author", on_delete=models.CASCADE, related_name="books"
)
status = models.CharField(max_length=20)
published_on = models.DateField()
class Meta:
ordering = ["-published_on"]
indexes = [
# Composite index for a common filter+sort combination
Index(fields=["author", "-published_on"]),
# Functional index for case-insensitive title lookups
Index(Lower("title"), name="book_title_lower_idx"),
# Partial index: only index rows you actually query (PostgreSQL)
Index(
fields=["published_on"],
name="book_published_idx",
condition=Q(status="published"),
),
# Covering index: serve a query from the index alone (PostgreSQL)
Index(fields=["author"], include=["title"], name="book_author_cover_idx"),
]A few rules of thumb. ForeignKey columns are indexed automatically, so you rarely need db_index=True on them. Composite index column order matters — put the column you filter on first, the one you sort on second. Partial indexes (a condition) keep the index small when you only ever query a subset, and covering indexes (include) let PostgreSQL answer a query from the index alone (an index-only scan), avoiding the table read entirely. Both condition and include are PostgreSQL-only and require a named index.
Find the slow queries: explain(), connection.queries, Debug Toolbar
You cannot optimize what you cannot see. Three tools, from quick to thorough:
QuerySet.explain()— prints the database's execution plan so you can confirm an index is actually used. Passanalyze=Trueto run the query and show real timings (PostgreSQL 17+ also acceptsmemoryandserializein Django 5.2).connection.queries— whenDEBUG=True, every SQL statement and its duration is recorded; counting them is the fastest way to catch an N+1 in a test or shell.- django-debug-toolbar — the SQL panel shows every query per request, flags duplicates, and links each query to the line of code that fired it. This is the standard way to hunt N+1 in development.
# 1) Inspect the execution plan (and real timings with analyze=True)
print(Book.objects.filter(status="published").explain(analyze=True))
# 2) Count queries in a shell / test to detect N+1
from django.db import connection, reset_queries
reset_queries()
list(Author.objects.all()) # no prefetch
print(len(connection.queries)) # e.g. 1 + N queries
reset_queries()
list(Author.objects.prefetch_related("books")) # with prefetch
print(len(connection.queries)) # e.g. 2 queries
# 3) In tests, assert the query count directly
from django.test import TestCase
class BookViewTests(TestCase):
def test_no_n_plus_one(self):
with self.assertNumQueries(2):
list(Author.objects.prefetch_related("books"))When to drop to raw SQL
The ORM covers the overwhelming majority of cases, but a few jobs are clearer or faster in hand-written SQL: complex window functions, recursive CTEs, vendor-specific features, or a heavily tuned reporting query. Reach for Manager.raw() (which still returns model instances) or connection.cursor() for full control — and always parameterize to avoid SQL injection. See Django raw SQL queries for the patterns.
# raw(): returns real model instances, parameters passed safely as a list
people = Person.objects.raw(
"SELECT * FROM app_person WHERE last_name = %s", ["Smith"]
)
# Direct cursor for results that do not map to a single model
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(
"SELECT designation, COUNT(*) FROM app_employee GROUP BY designation"
)
rows = cursor.fetchall()Does caching reduce database load?
Yes — for repeated, read-heavy queries, Django's cache framework is the layer above query tuning. Once you have minimized and indexed your queries, cache the expensive ones (or whole rendered fragments) so popular requests skip the database entirely. Per-view, low-level (cache.get/cache.set), and template-fragment caching all back onto Redis or Memcached. Caching is a complement to ORM optimization, not a substitute: cache correct, lean queries, never papered-over N+1s.
Optimization checklist
- Profile first with the Debug Toolbar or
assertNumQueries— fix the real N+1, not a guessed one. - Use
select_relatedfor to-one relations,prefetch_related(withPrefetchwhen you need to filter) for to-many. - Select only the columns you use:
only/deferfor instances,values/values_listfor plain data. - Push computation into SQL with
F,Q,annotate, andaggregate(includingCount(filter=...)). - Batch writes with
bulk_create/bulk_update; useupdate_or_createfor upserts. - Stream big jobs with
iterator(); answer existence withexists(), counts withcount(). - Index what you filter, join, and sort on via
Meta.indexes; use partial and covering indexes where they pay off. - Cache the expensive, repeated reads — after, not instead of, the steps above.
Frequently Asked Questions
How do I find N+1 queries in Django?
The fastest way is to count the queries a piece of code runs. Install django-debug-toolbar and watch the SQL panel — it highlights duplicated queries and links each one to the code that triggered it. In a shell or test you can use from django.db import connection, reset_queries, run your code, then check len(connection.queries) (requires DEBUG=True). In automated tests, wrap the code in self.assertNumQueries(n) to fail the build if the query count regresses. A list view whose query count grows with the number of rows is the classic N+1 signature.
select_related vs prefetch_related — when do I use which?
Use select_related for to-one relationships (ForeignKey, OneToOneField) — it adds a SQL JOIN and pulls the related row back in the same query, so it adds zero extra queries. Use prefetch_related for to-many relationships (ManyToManyField, reverse foreign keys, generic relations) — a JOIN there would multiply rows, so Django runs one extra query per relation and joins the results in Python. You can and often should combine them in one chain. Always name the relations explicitly; calling select_related() with no arguments is deprecated.
What is the difference between only/defer and values/values_list?
only and defer still return real model instances — only loads just the named fields and defer loads everything except the named ones (accessing a field that was not loaded triggers an extra query). values returns a list of dictionaries and values_list returns tuples (or scalars with flat=True); both skip model instantiation entirely, which is faster and lighter when you only need raw data — for example feeding a serializer or building an ID list. Choose only/defer when you still need model methods or relations, and values/values_list when you just need the data.
Does Django caching reduce database load?
Yes. Django's cache framework stores the result of expensive operations in Redis or Memcached so repeated requests skip the query altogether. You can cache at several levels: per-view, template fragments, or low-level cache.get/cache.set around a specific query. Caching is the layer above query optimization — first minimize and index your queries, then cache the ones that are still hot. Cache only correct, efficient queries; caching never fixes an underlying N+1, it just hides it from some requests.
How do I do a bulk insert or update efficiently in Django?
Use bulk_create() to insert many rows in a few batched INSERT statements instead of one INSERT per object, and bulk_update() to push changes to many existing instances at once; both accept a batch_size for very large lists. For "insert if missing, otherwise update" use update_or_create(). Be aware that the bulk methods bypass Model.save() and the pre_save/post_save signals, so if you rely on custom save logic or signals you either keep the per-object loop or replicate that logic explicitly.
Which Django and Python versions do these techniques target?
All of the code here runs on Python 3.12+ with Django 5.x, and the core APIs (select_related, prefetch_related, F, Q, annotate, bulk_create, Meta.indexes) work on the 4.2 LTS too. A couple of things to watch: index_together is removed (use Meta.indexes), bare select_related()/prefetch_related() calls are deprecated (name the relations), and explain(analyze=True) plus condition/include indexes shine brightest on PostgreSQL. Django 4.2's mainstream security support ends in April 2026, so new projects should standardize on 5.x.