Django Database Optimization: select_related, prefetch_related, and Beyond

Django’s ORM is convenient but deceptively easy to misuse. A simple template loop that accesses related objects can fire hundreds of queries without a single line of code looking suspicious. The most common performance problem in Django applications is the N+1 query pattern, and the fix is almost always select_related or prefetch_related.

The N+1 Query Problem

Consider a blog with posts and authors:

# models.py
class Author(models.Model):
    name = models.CharField(max_length=100)

class Post(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    category = models.ForeignKey("Category", on_delete=models.SET_NULL, null=True)
    tags = models.ManyToManyField("Tag")

This view looks innocent:

def post_list(request):
    posts = Post.objects.all()
    return render(request, "blog/list.html", {"posts": posts})

{% for post in posts %}
  <h2>{{ post.title }}</h2>
  <p>By {{ post.author.name }}</p>  {# This triggers a query per post! #}
{% endfor %}

With 100 posts, this fires 101 queries: 1 for the posts + 100 for each author. That’s the N+1 problem. The initial query fetches N posts, then accessing each post’s author triggers 1 additional query.

select_related: JOIN in One Query

select_related performs a SQL JOIN and fetches related objects in the same query. Use it for ForeignKey and OneToOneField relationships.

# Before: 101 queries
posts = Post.objects.all()

# After: 1 query with JOIN
posts = Post.objects.select_related("author")

The generated SQL:

SELECT post.*, author.*
FROM blog_post
INNER JOIN blog_author ON post.author_id = author.id

Chain multiple relationships:

# Joins both author and category in one query
posts = Post.objects.select_related("author", "category")

Follow relationships through multiple levels:

# Author -> profile (OneToOne)
posts = Post.objects.select_related("author__profile")

When to use select_related

• ForeignKey and OneToOneField relationships
• When you always need the related object
• When the related table is small-to-medium sized

select_related uses SQL JOINs, so it works best for single-valued relationships. It doesn’t work with ManyToManyField or reverse ForeignKey relations.

prefetch_related: Separate Query, Python Join

prefetch_related executes a separate query for the related objects and joins them in Python. Use it for ManyToManyField and reverse ForeignKey relationships.

# Fetches tags in a separate query and attaches them in Python
posts = Post.objects.prefetch_related("tags")

This runs exactly 2 queries regardless of how many posts exist:

-- Query 1: all posts
SELECT * FROM blog_post;

-- Query 2: all tags for those posts
SELECT tag.*, post_tag.post_id
FROM blog_tag
INNER JOIN blog_post_tags ON tag.id = blog_post_tags.tag_id
WHERE blog_post_tags.post_id IN (1, 2, 3, ...);

Reverse ForeignKey Relations

If an author has many posts, accessing author.post_set.all() in a loop causes N+1. Prefetch it:

authors = Author.objects.prefetch_related("post_set")

for author in authors:
    # No additional query — already prefetched
    for post in author.post_set.all():
        print(post.title)

Combining Both

posts = (
    Post.objects
    .select_related("author", "category")       # ForeignKeys → JOIN
    .prefetch_related("tags", "comments")         # M2M and reverse FK → separate queries
)

Prefetch Objects: Filtered and Annotated Prefetches

The Prefetch object gives you control over the prefetched queryset:

from django.db.models import Prefetch

# Only prefetch published comments, ordered by newest first
posts = Post.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.filter(is_approved=True).order_by("-created_at"),
        to_attr="approved_comments",  # Access as post.approved_comments
    )
)

for post in posts:
    for comment in post.approved_comments:  # List, not QuerySet
        print(comment.text)

to_attr stores the result as a Python list on the instance, which is faster than a QuerySet and makes the intent clear.

Nested Prefetches

Prefetch through multiple levels:

authors = Author.objects.prefetch_related(
    Prefetch(
        "post_set",
        queryset=Post.objects.filter(status="published").prefetch_related(
            Prefetch(
                "comments",
                queryset=Comment.objects.filter(is_approved=True),
                to_attr="approved_comments",
            )
        ),
        to_attr="published_posts",
    )
)

Push Work to the Database with Annotations

Instead of computing values in Python, use annotations to let the database do the math:

from django.db.models import Count, Avg, F, Q, Sum

# Bad: computing in Python
posts = Post.objects.all()
for post in posts:
    comment_count = post.comments.count()  # N+1 queries

# Good: single query with annotation
posts = Post.objects.annotate(
    comment_count=Count("comments"),
    avg_rating=Avg("comments__rating"),
)

for post in posts:
    print(f"{post.title}: {post.comment_count} comments, avg rating {post.avg_rating}")

Conditional Annotations

Count only approved comments:

posts = Post.objects.annotate(
    approved_count=Count("comments", filter=Q(comments__is_approved=True)),
    pending_count=Count("comments", filter=Q(comments__is_approved=False)),
)

F Expressions: Database-Level Math

Use F() to reference model fields in queries without loading them into Python:

# Update without loading into Python
Product.objects.filter(stock__gt=0).update(price=F("price") * 1.10)

# Filter using field comparisons
Post.objects.filter(updated_at__gt=F("published_at"))

only() and defer(): Load Fewer Columns

If you only need a few fields from a large table:

# Only load title and slug — other fields fetched lazily if accessed
posts = Post.objects.only("title", "slug")

# Load everything except the large body field
posts = Post.objects.defer("body")

Use .values() or .values_list() when you don’t need model instances at all:

# Returns list of dicts — no model instantiation
post_titles = Post.objects.values("id", "title", "slug")

# Returns list of tuples
post_titles = Post.objects.values_list("id", "title", flat=False)

Profiling Queries

django-debug-toolbar

The best tool for spotting N+1 queries during development:

pip install django-debug-toolbar

# settings.py
INSTALLED_APPS += ["debug_toolbar"]
MIDDLEWARE = ["debug_toolbar.middleware.DebugToolbarMiddleware"] + MIDDLEWARE
INTERNAL_IPS = ["127.0.0.1"]

The SQL panel shows every query, its duration, and highlights duplicates.

assertNumQueries in Tests

Lock down query counts in your test suite so regressions are caught immediately:

from django.test import TestCase


class PostListTest(TestCase):
    def test_post_list_query_count(self):
        # Create test data
        author = Author.objects.create(name="Alice")
        for i in range(50):
            Post.objects.create(title=f"Post {i}", author=author)

        # Assert exactly 3 queries: posts, authors (select_related), tags (prefetch)
        with self.assertNumQueries(3):
            response = self.client.get("/posts/")
            self.assertEqual(response.status_code, 200)

Logging All Queries

Enable query logging in development:

# settings.py
LOGGING = {
    "version": 1,
    "handlers": {
        "console": {"class": "logging.StreamHandler"},
    },
    "loggers": {
        "django.db.backends": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

Common Optimization Patterns

Exists() Instead of Count()

When you just need a boolean:

# Slower: counts all rows
if Post.objects.filter(author=user).count() > 0:

# Faster: stops at the first match
if Post.objects.filter(author=user).exists():

Bulk Operations

# Bad: N INSERT queries
for item in items:
    Product.objects.create(**item)

# Good: 1 INSERT query
Product.objects.bulk_create([Product(**item) for item in items])

# Bulk update
Product.objects.filter(category="electronics").update(on_sale=True)

Subqueries

Avoid loading data into Python just to filter with it:

from django.db.models import Subquery, OuterRef

# Get the latest comment date for each post
latest_comment = Comment.objects.filter(
    post=OuterRef("pk")
).order_by("-created_at").values("created_at")[:1]

posts = Post.objects.annotate(
    latest_comment_at=Subquery(latest_comment)
)

Summary

The N+1 problem is Django’s most common performance issue. Use select_related for ForeignKey JOINs, prefetch_related for M2M and reverse relations, and Prefetch objects for filtered prefetches. Push computations to the database with annotations and F expressions. Profile with django-debug-toolbar and lock down query counts with assertNumQueries. Most Django performance problems disappear with these techniques.