python

How to Avoid N+1 Queries in Django Python

Federico Trotta

Federico Trotta on

How to Avoid N+1 Queries in Django Python

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:

plaintext
django_queries/ └── venv/

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

Shell
python3 -m venv venv

To activate it on Windows, run:

Shell
venv\Scripts\activate

On macOS and Linux, execute:

Shell
source venv/bin/activate

In the activated virtual environment, install the dependencies:

Shell
pip install django

Step 1: Create a New Django Project

Start a new project like so:

Shell
django-admin startproject nplus1_project

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

Shell
cd nplus1_project

Start a new Django application called blog:

Shell
python manage.py startapp blog

The structure of your repository should now look like this:

plaintext
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:

Python
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:

Shell
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:

Shell
python manage.py shell

Add the following data to the database:

Python
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:

Python
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:

Shell
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:

Python
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:

Python
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:

Python
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:

html
<!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:

plaintext
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:

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

Now, run the server:

Shell
python manage.py runserver

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

The listed data from the n+1 query with Django

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

The n+1 query with Django

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.

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:

Python
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:

Using select related for the n+1 query with Django

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!

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

Python
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:

Using prefetch related for the n+1 query with Django

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

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:

Featureselect_related()prefetch_related()
Use CaseSingle-valued relationships (ForeignKey, OneToOneField)Multi-valued relationships (ManyToManyField, reverse ForeignKey)
Number of Queries1 query with JOIN2 queries (main query + prefetch query)
PerformanceFaster for single-valued relationshipsBetter for large datasets or multi-valued relationships
Database LoadUses JOIN, which can be expensive for large datasetsSeparate queries, which can be more efficient for large datasets

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:

The performance tab in AppSignal for the n+1 query with Django

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!

Wondering what you can do next?

Finished this article? Here are a few more things you can do:

  • Share this article on social media
Federico Trotta

Federico Trotta

Guest author Federico is a freelance Technical Writer who specializes in writing technical articles and documenting digital products. His mission is to democratize software through technical content.

All articles by Federico Trotta

Become our next author!

Find out more

AppSignal monitors your apps

AppSignal provides insights for Ruby, Rails, Elixir, Phoenix, Node.js, Express and many other frameworks and libraries. We are located in beautiful Amsterdam. We love stroopwafels. If you do too, let us know. We might send you some!

Discover AppSignal
AppSignal monitors your apps