Django ORM Optimization — Deep Dive

How Django querysets execute internally

A Django QuerySet is a lazy descriptor that builds a SQL query incrementally. Every call to .filter(), .exclude(), or .order_by() clones the queryset and appends to its internal Query object — no database call happens yet.

Evaluation triggers include iteration, slicing with a step, len(), list(), bool(), and serialization. When evaluation happens, the Query object compiles to SQL via the database backend’s compiler, executes through the connection cursor, and the results are cached in queryset._result_cache.

This caching means iterating the same queryset twice only hits the database once. But creating a new queryset (even with the same filters) always produces a fresh query. Understanding this distinction prevents accidental duplicate queries in template rendering.

# One query — result cache reused
posts = Post.objects.filter(published=True)
for p in posts:         # query executes here
    print(p.title)
for p in posts:         # uses cached results
    print(p.slug)

# Two queries — different queryset objects
for p in Post.objects.filter(published=True):  # query 1
    print(p.title)
for p in Post.objects.filter(published=True):  # query 2
    print(p.slug)

select_related vs prefetch_related: under the hood

select_related modifies the SQL to include INNER JOIN (or LEFT OUTER JOIN for nullable ForeignKeys). The joined columns are mapped onto related model instances during result hydration. This adds columns to each row but eliminates separate queries.

# Generates: SELECT post.*, author.* FROM post
#            INNER JOIN author ON post.author_id = author.id
posts = Post.objects.select_related('author')

prefetch_related executes a completely separate query and matches results in Python using _prefetched_objects_cache. It works with any relationship type and supports custom querysets through Prefetch objects.

from django.db.models import Prefetch

# Custom prefetch: only active comments, ordered by date
posts = Post.objects.prefetch_related(
    Prefetch(
        'comments',
        queryset=Comment.objects.filter(active=True).order_by('-created'),
        to_attr='active_comments'  # stores as list attribute, not manager
    )
)
# Access without triggering additional queries
for post in posts:
    for comment in post.active_comments:
        print(comment.text)

The to_attr parameter is particularly valuable — it stores prefetched results as a plain list instead of overriding the manager, which avoids conflicts when you need both filtered and unfiltered access.

Prefetch chains and depth control

For deeply nested relationships, chain prefetches with double-underscore notation:

# Fetch publishers → books → authors → profiles in 4 queries total
publishers = Publisher.objects.prefetch_related(
    'books__authors__profile'
)

Without this, accessing publisher.books.all()[0].authors.all()[0].profile would trigger a query at each level for each object — easily thousands of queries on a moderately sized dataset.

Subquery and annotation patterns

Django 3.0+ provides Subquery and OuterRef for correlated subqueries that push complex logic into SQL:

from django.db.models import Subquery, OuterRef, Count

# Annotate each author with their most recent post date
latest_post = Post.objects.filter(
    author=OuterRef('pk')
).order_by('-published_at')

authors = Author.objects.annotate(
    latest_post_date=Subquery(latest_post.values('published_at')[:1]),
    post_count=Count('posts')
)

This generates a single SQL query with a correlated subquery. The alternative — fetching all authors then looping to find each one’s latest post — would be dramatically slower.

Exists() over Count() for boolean checks

When you only need to know whether related objects exist (not how many), Exists is faster than Count:

from django.db.models import Exists, OuterRef

active_comments = Comment.objects.filter(
    post=OuterRef('pk'), active=True
)
posts = Post.objects.annotate(
    has_active_comments=Exists(active_comments)
)

The database can short-circuit after finding one matching row instead of counting all matches.

Bulk operations and batch processing

For write-heavy workloads, individual save() calls are the bottleneck:

# Bad: 10,000 individual INSERT statements
for data in large_dataset:
    MyModel.objects.create(**data)

# Good: ~10 INSERT statements with 1000 rows each
MyModel.objects.bulk_create(
    [MyModel(**data) for data in large_dataset],
    batch_size=1000
)

# Bulk update with specific fields
MyModel.objects.filter(status='pending').update(status='processed')

# For complex per-row updates
objs = list(MyModel.objects.filter(needs_update=True))
for obj in objs:
    obj.computed_field = expensive_calculation(obj)
MyModel.objects.bulk_update(objs, ['computed_field'], batch_size=500)

Note that bulk_create skips save(), so signals and custom save() logic won’t fire. This is a tradeoff: speed for correctness hooks.

Iterator and chunked processing

For querysets that return millions of rows, Django loads everything into memory by default. Use iterator() to process rows one at a time without caching:

# Memory-efficient processing of large datasets
for post in Post.objects.all().iterator(chunk_size=2000):
    process(post)

The chunk_size parameter controls how many rows Django fetches from the database cursor at once. Too small wastes round trips; too large defeats the memory savings.

Database connection and cursor management

Each Django thread maintains its own database connection. Connection setup has overhead, so persistent connections (CONN_MAX_AGE in settings) keep connections open between requests.

For raw performance-critical operations:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        UPDATE posts SET view_count = view_count + 1
        WHERE id = %s
    """, [post_id])

Raw SQL bypasses ORM overhead completely. Use it for complex aggregations or database-specific features the ORM doesn’t support, but keep it isolated in repository functions for testability.

Indexing strategy

Composite indexes dramatically improve queries that filter on multiple columns:

class Post(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    published = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            models.Index(fields=['published', '-created_at']),
            models.Index(
                fields=['author', 'published'],
                name='author_published_idx'
            ),
        ]

Index order matters. An index on (published, created_at) helps queries filtering by published alone or by both fields, but not queries filtering only by created_at.

For PostgreSQL, partial indexes are powerful:

class Meta:
    indexes = [
        models.Index(
            fields=['created_at'],
            condition=models.Q(published=True),
            name='published_posts_date_idx'
        ),
    ]

This index is smaller and faster because it only includes published posts.

Profiling in production

Django Debug Toolbar works in development. For production, integrate query logging at the middleware level:

import logging
import time
from django.db import connection

logger = logging.getLogger('query_profiler')

class QueryCountMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        start_queries = len(connection.queries)
        start_time = time.monotonic()
        response = self.get_response(request)
        total_queries = len(connection.queries) - start_queries
        elapsed = time.monotonic() - start_time
        if total_queries > 20 or elapsed > 1.0:
            logger.warning(
                'Slow view: %s queries in %.2fs for %s',
                total_queries, elapsed, request.path
            )
        return response

Set DEBUG = False in production but enable connection.queries selectively for profiling by using database logging backends.

Tradeoffs to keep in mind

Every optimization has a cost. select_related increases row size and memory per query. prefetch_related runs extra queries but keeps row sizes small. only() risks deferred-field queries if you access omitted fields. Bulk operations skip model validation and signals.

The right approach depends on your data shape, access patterns, and scale. Profile real workloads, not hypothetical ones.

The one thing to remember: Django ORM optimization is about controlling when and how data moves between your database and Python — fewer round trips, smaller payloads, and pushing computation to SQL whenever possible.