Solving the N+1 Problem in Django: A Guide by Prachanda Oli

The N+1 query problem is the silent killer of Django performance. It happens when you access related objects in a loop, causing a separate database query for every iteration. To fix this, we use `select_related()` for single-valued relationships (ForeignKey, OneToOne), which performs a SQL JOIN in the initial query. For multi-valued relationships (ManyToMany, Reverse ForeignKey), we use `prefetch_related()`, which runs a separate lookup and efficiently stitches the data together in Python.

Scenario 1: Forward ForeignKey (select_related)

Use this when the model has the ForeignKey definition. It does a SQL JOIN.

python

# Bad: Fires 1 query for books + N queries for authors
for book in Book.objects.all():
    print(book.author.name)

# Good: Fires exactly 1 query with a JOIN
books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name)

Scenario 2: Many-to-Many or Reverse FK (prefetch_related)

Use this for accessing lists of related items (e.g., a Book has many Tags).

python

# Good: Fires 2 queries total (1 for Books, 1 for Tags)
# Django merges them in Python memory.
books = Book.objects.prefetch_related('tags').all()

for book in books:
    # No DB hit here
    print([tag.name for tag in book.tags.all()])

Scenario 3: Custom Prefetch with Filtering

Prefetch only specific related objects (e.g., only 'active' comments).

python

from django.db.models import Prefetch

# Only prefetch comments that are approved
active_comments = Comment.objects.filter(is_approved=True)

posts = Post.objects.prefetch_related(
    Prefetch('comments', queryset=active_comments, to_attr='approved_comments')
)

for post in posts:
    print(post.approved_comments) # Uses the filtered list