Inverse Relationships in Django and the N+1 Problem

▶

Let's now look at reverse or inverted relationships. Understand that we have the Categories model and we want to obtain its related Elements; this is perfectly feasible in Django.

This is a topic that is closely linked to the N+1 problem we presented earlier, since it is due to the relationships.

For this test, we will not use the full project. I am going to perform the query directly in the view to avoid wasting time creating an additional template. We will obtain all the categories and practice how to access their linked data:

categorias = Category.objects.prefetch_related('element_set').all()
for cat in categorias:
    print(f"Categoría: {cat.title}")
    # # Here Django makes a DB query for EACH category to find its elements
    for el in cat.element_set.all(): 
        print(f" - Elemento: {el.title}")

Accessing the Default _set

If we have a relationship where an Element belongs to a Category, and we are standing on the Category object, how do we get its elements? That is the reverse relationship:

class Category(models.Model):
   ***
   
class Element(models.Model):
   ***
   category = models.ForeignKey(Category, on_delete=models.CASCADE)

By default, Django names this relationship as lowercase_model_name_set (in this case, element_set). We don't have to configure anything extra. It is the reverse process of what we did before: if before we obtained the category from the element, now we obtain the elements from the category.

Customization with related_name

If you don't like the name element_set, you can customize it in the model using the related_name attribute. For example:

category = models.ForeignKey(Category, on_delete=models.CASCADE, related_name='elements')

If you do this, Django will no longer recognize element_set; instead, you must simply use elements. It is optional, but very useful for making the code more readable:

for el in cat.elements.all(): 
   print(f" - Elemento: {el.title}")

The N+1 Query Problem

To see what happens behind the scenes, I activated the SQL log in the console. If we don't optimize the query, when accessing the reverse relationship inside a loop, Django performs a database query for each category to find its elements. This is known as the N+1 problem.

If you have 50 categories, Django will make 51 queries! This can kill your server's performance. When checking the console, you will see a lot of repetitive SELECT statements, which is a clear red flag.

DEBUG (0.000) SELECT COUNT(*) AS "__count" FROM "elements_element"; args=(); alias=default
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" LIMIT 8; args=(); alias=default
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default     
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default     
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default     
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default     
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default     
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHER" FROM "elements_category" WHERE "elements_category"."id" = 1 LIMIT 21; args=(1,); alias=default
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 2 LIMIT 21; args=(2,); alias=default
DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category" WHERE "elements_category"."id" = 2 LIMIT 21; args=(2,); alias=default

Optimization with prefetch_related

How do we solve this without relying on global configurations? We use prefetch_related. Unlike select_related (which uses a JOIN), prefetch_related performs a separate second query to bring all related elements at once and then joins them in Python.

Example of use:

categories = Category.objects.prefetch_related('element_set').all()

By executing this and checking the logs, you will see that now only two queries are performed:

Categoría: Cate 1.1
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 1; args=(1,); alias=default
 - Elemento: Test 1
 - Elemento: ELement 2
 - Elemento: asasas
 - Elemento: Elem 3asas
 - Elemento: asasasa
 - Elemento: ELem 1sasas
Categoría: Cate 2
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 2; args=(2,); alias=default
 - Elemento: asasas
 - Elemento: Element
Categoría: asaas
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 4; args=(4,); alias=default
Categoría: Laravel
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 7; args=(7,); alias=default
Categoría: FastAPI
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 8; args=(8,); alias=default
Categoría: Cate 5
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 9; args=(9,); alias=default
Categoría: cate-6
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" = 10; args=(10,); alias=default
Categoría: Django 6

One to fetch all categories.

A nested SELECT (or using an IN) that fetches all elements related to those categories.

In this way, we go from having hundreds of queries to just two, drastically optimizing database access:

DEBUG (0.000) SELECT "elements_category"."id", "elements_category"."title", "elements_category"."slug" FROM "elements_category"; args=(); alias=default
DEBUG (0.000) SELECT "elements_element"."id", "elements_element"."title", "elements_element"."slug", "elements_element"."description", "elements_element"."price", "elements_element"."category_id", "elements_element"."created", "elements_element"."updated", "elements_element"."type_id" FROM "elements_element" WHERE "elements_element"."category_id" IN (1, 2, 4, 7, 8, 9, 10, 11, 12); args=(1, 2, 4, 7, 8, 9, 10, 11, 12); alias=default