Django is a powerful web framework that simplifies how developers interact with databases through its Object-Relational Mapping (ORM) system. However, even with its benefits, it’s easy to fall into performance pitfalls such as the N+1 query problem.

In this article, we’ll explore what N+1 queries are, why they can be an issue for your application, and how to mitigate them using Django’s best practices.

Let's dive in!

Understanding the N+1 Query Problem in Django

Before diving into solutions, it’s essential to understand the fundamentals of this performance bottleneck.

Imagine you’re creating a Django application that needs to display a list of authors and their corresponding books. You might write a simple query to retrieve all the authors, and then iterate over each one to pull their books. At first glance, this appears straightforward. However, if your application makes a separate database query for each author’s books, you might end up issuing many more queries than you intended: a classic symptom of the N+1 query problem.

In Django, N+1 queries occur when one initial query (the “1”) is followed by additional queries (the “N”) for each object retrieved. This scenario usually surfaces when code is structured in a way that iterates over many objects and lazily retrieves related data. The result can be significant performance degradation when interacting with a database. For example, if you have 100 authors and you access each author’s books in a loop, Django could run 101 queries in total, rather than a single, optimized query. This pattern not only increases latency, but also puts unnecessary load on your database server.

The impact of N+1 queries on database performance can be substantial. As the number of objects grows, so does the number of queries, leading to a linear increase in database calls. This kind of inefficiency can slow down page load times and strain your application, especially when your database scales or when operating in a high-traffic environment. This issue brings to light the importance of querying data as efficiently as possible and leveraging Django’s abilities to ensure optimized performance.

How N+1 Queries Arise in Django

The N+1 problem typically appears in scenarios where developers do not explicitly optimize the way related objects are queried. Two common patterns that lead to this pitfall are:

1. Accessing related fields in templates: Django’s ORM performs lazy loading, which means that when you reference a related field in a template, Django may execute a separate query for each object accessed. For example, if you loop through a list of articles in a template and display each article’s author, Django might issue an extra query for each article to fetch the corresponding author details. This situation leads to N+1 queries because one query retrieves the articles, and each article then triggers another query for its associated author.

2. Iterating over related objects in views: A second common pattern occurs in view logic, particularly when iterating over a queryset and accessing related objects without preloading. Consider a scenario where you retrieve a list of authors and then, within a loop in your view, access each author’s set of books. If you don't optimize your query, Django will execute a separate query for each author to fetch their books. This repetition causes query numbers to proliferate as the number of authors grows, resulting in an N+1 query scenario.

How to Avoid N+1 Queries

Now that we've gone through the theory, it is time to provide a step-by-step tutorial. This section first shows how N+1 queries look, then two ways of solving the issue using Django.

We will use Django to retrieve a list of authors and related books from a blog.

Requirements

To replicate the tutorial, you must have Python 3.10.1 or higher installed on your machine.

Prerequisites and Dependencies

Suppose you call the main folder of your project django_queries/. In the beginning, the folder should look like this:

django_queries/
    └── venv/

Where venv/ contains the virtual environment. You can create the venv/ virtual environment like so:

python3 -m venv venv

To activate it on Windows, run:

venv\Scripts\activate

On macOS and Linux, execute:

source venv/bin/activate

In the activated virtual environment, install the dependencies:

pip install django

Step 1: Create a New Django Project

Start a new project like so:

django-admin startproject nplus1_project

This will automatically create subfolders with Python files. Go to the nplus1_project/ subfolder:

cd nplus1_project

Start a new Django application called blog:

python manage.py startapp blog

The structure of your repository should now look like this:

nplus1_project/
    ├── blog/
    │   ├── migrations/
    │       └── __init__.py
    │   ├── __init__.py
    │   ├── admin.py
    │   ├── apps.py
    │   ├── models.py
    │   ├── tests.py
    │   └── views.py
    ├── nplus1_project/
    │   ├── __init__.py
    │   ├── asgi.py
    │   ├── settings.py
    │   ├── urls.py
    │   └── wsgi.py
    └── manage.py

Step 2: Define Models

The N+1 query problem typically arises when querying related models. So, let’s create two models: Author and Post, where each Post is written by an Author.

Define the models in the blog/models.py file, like so:

from django.db import models
 
class Author(models.Model):
    name = models.CharField(max_length=100)
 
    def __str__(self):
        return self.name
 
class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='posts')
 
    def __str__(self):
        return self.title

Apply the migrations to Django by running:

python manage.py makemigrations
python manage.py migrate

Now you've created the basis of this tutorial.

Step 3: Populate the Database

The next step is to populate the database with some data. Open the Django shell:

python manage.py shell

Add the following data to the database:

from blog.models import Author, Post
 
# Create authors
author1 = Author.objects.create(name="Author 1")
author2 = Author.objects.create(name="Author 2")
 
# Create posts
Post.objects.create(title="Post 1", content="Content 1", author=author1)
Post.objects.create(title="Post 2", content="Content 2", author=author1)
Post.objects.create(title="Post 3", content="Content 3", author=author2)
Post.objects.create(title="Post 4", content="Content 4", author=author2)

If you encounter an error at this stage that says something like "NameError: name 'Post' is not defined", it means that Django doesn't recognize your blog application or its models. To fix this, go into the settings.py file inside the nplus1_project directory, search for the INSTALLED_APPS list and add your blog app name, like so:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'blog',  # Add your app here
]

Save the settings.py file and apply the migrations again:

python manage.py makemigrations
python manage.py migrate

Step 4: Create a View with the N+1 Query Problem

In this step, we'll actually create the N+1 query issue on purpose to help you understand how to visualize it. To do so, open blog/views.py and create a view:

from django.shortcuts import render
from blog.models import Post
 
def post_list(request):
    # This will cause the N+1 query problem
    posts = Post.objects.all()
    for post in posts:
        print(post.author.name)  # Accessing the related author for each post
 
    return render(request, 'blog/post_list.html', {'posts': posts})

In this code, the post_list() view queries all the Post objects. Then, for each Post, it accesses the related Author object, causing a separate query for each Post, thus creating the N+1 query issue.

Step 5: Create a URL for the View

Create a file called urls.py in the folder blog/ and define the URL for the post_list view:

from django.urls import path
from . import views
 
urlpatterns = [
    path('', views.post_list, name='post_list'),
]

Also, set the nplus1_project/urls.py file like this:

from django.contrib import admin
from django.urls import path, include
 
urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('blog.urls')),
]

Step 6: Create a Template

To visualize how data is retrieved and displayed by the UI, you need to create a template. Create a directory for the templates in blog/templates/blog/. Inside it, create a file named post_list.html with the following code:

<!DOCTYPE html>
<html>
  <head>
    <title>Post List</title>
  </head>
  <body>
    <h1>Posts</h1>
    <ul>
      {% for post in posts %}
      <li>{{ post.title }} by {{ post.author.name }}</li>
      {% endfor %}
    </ul>
  </body>
</html>

The final repository structure looks like this:

django_queries/
│    ├── blog/
│    │   ├── migrations/
│    │   ├── templates/
│    │   │   └── blog/
│    │   │       └── post_list.html
│    │   ├── __init__.py
│    │   ├── admin.py
│    │   ├── apps.py
│    │   ├── models.py
│    │   ├── tests.py
│    │   ├── urls.py
│    │   └── views.py
│    ├── nplus1_project/
│    │   ├── __init__.py
│    │   ├── asgi.py
│    │   ├── settings.py
│    │   ├── urls.py
│    │   └── wsgi.py
│    ├── db.sqlite3
│    └── manage.py
└── venv/

Step 7: Run The Application

In nplus1_project/settings.py, enable the possibility to log SQL queries via the console by adding the following:

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    },
}

Now, run the server:

python manage.py runserver

Go to http://127.0.0.1:8000/ and see the server running. The listed data is displayed like this:

However, what matters to us is what is happening under the hood. Here is the expected output you will see on the CLI:

This is an N+1 query issue, as you can observe:

• One query to fetch all Post objects.
• Additional queries for each Post to fetch its related Author object.

Now, let's see how to solve the issue.

Step 9: Fix the N+1 Query Problem With select_related()

One way to solve the N+1 query is to use the select_related() method. To do so, modify the blog/views.py file like so:

def post_list(request):
    # Use select_related to avoid the N+1 query problem
    posts = Post.objects.select_related('author')
    for post in posts:
        print(post.author.name)  # No additional queries are executed here
 
    return render(request, 'blog/post_list.html', {'posts': posts})

Run the server again. This is the expected result:

In this case, the query fetches all Post objects, joins the Author table using the author_id foreign key, and retrieves all the necessary fields in a single query.

Hooray! You fixed the problem!

Step 10: Fix the N+1 Query Problem With prefetch_related()

Another way to solve this issue is to use the prefetch_related() method. Modify the blog/views.py file like so:

def post_list(request):
    # Use prefetch_related to avoid the N+1 query problem
    posts = Post.objects.prefetch_related('author')
    for post in posts:
        print(post.author.name)  # Accessing the related author for each post
 
    return render(request, 'blog/post_list.html', {'posts': posts})

After running the server again, this is the expected result:

And again, the data is retrieved in a single query!

When to Use prefetch_related() or select_related()

You may be wondering when to use select_related() over prefetch_related(). This depends on the type of relationship between your models and your specific use case:

• select_related() is used for single-valued relationships, such as ForeignKey or OneToOneField. It performs a single SQL query with a JOIN to fetch the related data.
• prefetch_related() is used for multi-valued relationships, such as ManyToManyField or reverse ForeignKey relationships. It performs two separate queries and uses Python to match the related objects.

Here is a summary table:

Level Up Django Monitoring with AppSignal

If you are a Django user and want to level up your monitoring capabilities, consider trying AppSignal's integration for Django. To spot N+1 queries, AppSignal has an instrumentation events feature that detects if the same event is repeated more than once in succession. Such events are marked as potential N+1 events and shown in the Performance tab of your dashboard, under Issue list:

And that's it!

Wrapping Up

In this article, you learned about N+1 queries in Django. First, we explored how N+1 queries come to exist in your application, before diving into how to solve them using prefetch_related() and select_related().

Happy coding!