Optimizing Django SQL queries

Database queries are among the most common bottlenecks of any backend, and unoptimised SQL queries, even though not the primary reason, deteriorate your bottleneck significantly.

The problem of unoptimized SQL queries is especially common in Django backends, as Django's ORM, despite its simplicity, makes it really easy to overlook the structure or frequency of the queries.

Here, we'll take a look at the ways to optimise the queries we make in our Django application. Many of the things that we talk about are standard SQL practices, but using Django ORM, many of these concepts are abstracted.

Before optimizing the queries

Before we start, I have to mention that even though optimising your SQL queries is really important, it should not be the only way to improve the performance of your application. Here, we make the assumption that these SQL queries are necessary and are being optimised after implementing caching, indexing, etc.

How to monitor the SQL queries

Django natively provides some tools to get the list of queries made. However, here we will use a library named pervpy that provides a class named DjangoORMWatch which provides anything we need. You can install pervpy using

You can use DjangoORMWatch to monitor the queries made in any part of your code. I, however, always prefer to use it in my tests as it lists all the queries made during the lifecycle of a request. Have in mind that the DEBUG should be set to true. If you're using the unit tests, you can use the @override_settings(DEBUG=True)

To get and display the SQL queries, all you need to do is to reset the queries before the code, and evaluate the queries after the code.

You can print the queries with 4 different levels of verbosity, 0 to 3. Level 1 just gives you a small summary of each query, while Level 3 gives you the complete query. The result of your queries is printed as shown in the code below.

During the rest of this article, I will talk about how much faster one approach is compared to another. These figures are not absolute and are not a benchmark. These figures are relative and just to demonstrate the fact that one approach is more performant than the other.

Starting with optimisations

Now that we can monitor our SQL queries, we go through the common mistakes every developer makes while using Django ORM.

All the codes shown here are run in the unit tests, and before each code snippet, 10 authors are created along with 100,000 books for each author to better demonstrate the performance differences.

Mistake 1: Too many queries

Making too many queries is, in my opinion, the most common mistake we make. As a rule of thumb, the fewer queries you make, the better.

In the simplest cases, we just get entangled in a complex code base and make duplicate queries. Of course, these mistakes are the easiest to spot in our evaluation and the easiest to fix. However, such simple errors are not what we look for.

The main reason for making too many requests is the N+1 problem, where we query a model and then access its related model.

Take a look at the following code. Here, we fetch 1,000 books using the filter function and then access their author. What happens here is that Django will perform a query to get the books, and once we loop through the books and access the author (even though all books belong to the same author), Django sends another query to get the author on every iteration.

Here we can clearly see the N+1 problem. We have one initial query plus N extra queries, where N is the number of items in our initial query.

To solve the N+1 problem, Django ORM gives us two functions, each for a different use case.

If your models have a many-to-one relationship and you are getting the foreign key, similar to the previous code, you need to use select_related in your query. This function will join the tables, select the columns of both tables, and then include the related model in the main instance(s). So, there won't be any additional query to get the parent as the data is already loaded.

In the next code, you can see the difference between using and not using select_related. After using select_related, we made only 1 query, saving us 1,000 queries while taking ~94% less time to run.

Have in mind that if you only need to access the primary ID of the related model (the author, in this example), the relared model's ID is accessible without any additional query or using select_related. You can access the related ID by using the property with this name format: [related]_id

Select_related is used when we are fetching the related model accessed via the foreign key. However, if we are performing a reverse foreign key lookup or dealing with many-to-many relations, select_related won't be useful.

In these circumstances, we have to use prefetch_related, which runs an additional SQL query to get all the children of the parent instance. Django then uses these two queries to stitch the objects together outside the database and in the Python code.

This approach would not make any sense if you are, for example, fetching one author and want to get the list of her books. However, if you are getting a list of authors and want to get the list of the books by every author, you'd end up making a separate query for each author. What prefetch_related does is that it reduces all those extra queries into a single one.

In the next code sample, we first get 5 authors, and for each author, we get all their books. As you can see in the results, after using prefetch_related, we only made 2 queries to the database. One thing we need to point out is the fact that both results took almost the same amount of time. However, the big difference is that after using prefetch_related, most of the time is spent inside our Python code rather than inside the database, leaving the connection open for other requests.

Have in mind that sometimes it makes more sense to make a few small and optimized queries rather than one large and slow query. But for most cases, a single query is more performant.

Mistake 2: Too many columns

Django, by default, will include all columns of a table during its queries, whether for read or a write query. As a rule of thumb, the fewer columns you touch during a query, the more performant your queries are.

For a read query, Django provides two functions, only and defer, to include and exclude a column, respectively. The selection or omission of the columns is especially important if a model contains long texts or JSON fields.

Be aware that if you make a query on a model, deferring a certain column and then access that column in your code, Django will make an extra query to get that column, which not only defeats the purpose of minimizing column selection but also makes things much worse than if you had selected all columns in the first place.

In the next code sample, we query all books, which will return 1 million rows. By selecting one column instead of 5, our code takes ~33% less time to run.

Django, following the same pattern, will update all columns of a table while updating a row, even if only one column is actually updated. The good news is that the save function of Django ORM accepts an update_fields (which is a list of strings), and if the list is provided, only the given columns are updated.

You might accurately notice that update queries are far less common than read queries. Even though update queries are much less common than read queries (by a large margin), you still have a limited number of workers available for your Django application and a limited number of concurrent connections with the database. So, even though less important than read queries, optimising the update or insert queries is still important.

In the following code, we run two loops, each calls the save function 8,500 times, one loop updating all 5 columns and the other loop updating only one column. You can see that the second loop using update_fields takes ~13% less time to run.

Mistake 3: Using count to check if a row exists

In many cases, we cannot mark a column as unique and instead, need to perform a manual check if a certain row exists in the database or not.

Django's get function, if cannot find the existing row, will throw a DoesNotExist exception, and to avoid wrapping the code in a try-catch statement, many developers use count() > 0 to check if the row exists. The problem with using the count function is that the database scans the whole table.

The best approach is to use the exists function, which uses the limit clause in the query and returns the query as soon as the first row is found, avoiding the unnecessary scan of the rest of the table.

In the next sample, I run two loops, each running 500 queries, one using the count() > 0 approach and one using the exists function. As you can see, the exists function takes ~98% less time to run, demonstrating the performance gap between the two.

Mistake 4: Fetch and modify

Often, we need to modify or delete a series of rows. Looking at the required actions purely from the Python perspective, we naturally fetch the rows, loop through them, and update or delete them individually.

The problem with this approach is that we are making the N+1 mistake again, but the extra queries are update or delete queries that are slower than read queries. There are multiple ways to improve these queries which follow the standard SQL query practices.

If you are updating certain fields of a list of objects, you can use the bulk_update function. Using bulk_update, you would be making two queries: one to fetch the initial data and one to update all the rows. Have in mind that if you're updating a large number of objects, Django may not be able to put them in a single update query and may breaks them into batches.

In the next code sample, we fetch 100 books, change their price to a random number, and then save them. In the first approach, we save each object individually, resulting in 101 queries. In the second approach, we use bulk_update, resulting in only 2 queries, and the code takes ~29% less time to run.

If you are updating certain columns of many objects to a single value, you can call the update function of the queryset. Have in mind that this approach is useful when all of the rows get the same value.

In the next code sample, we fetch 100 books and change their available field. Of course, looping through the objects and saving them individually, we make 101 queries (1 select and 100 updates). However, using the update function, the rows are updated within 1 update query, and the rows are never loaded into memory. The difference between the two approaches is drastic, and by using update, our code takes ~98% less time to run.

If you are deleting a series of objects, you can call the delete function of the query set, using the same logic as the update function.

Here in the code sample, we are deleting 100 books. In the first approach, we fetch the 100 books and delete each one individually, resulting in 101 queries (1 select and 100 deletes). Using the delete function, we just run a single delete query, deleting all objects without loading them into memory, which takes ~97% less time to run.

Note that bulk update functions do not trigger the custom pre_save and post_save signals and custom save function, if you have defined one for your model. So, if you have a custom save workflow, you either have to resort to an individual update or handle the logic manually.

Wrapping up

Django is a great framework, and I personally like its ORM a lot. However, its simplicity often leads us to make some mistakes that, down the line, can hinder our application's performance.

These are not the only ways to improve the performance of a Django application or optimise the queries we make using its ORM, and as time goes by, I'll extend the list to cover more common mistakes.