Optimizing Your Queries in Django

The Why

I have been working on a long-lived Django project for a few years now. The bigger the codebase got, the slower our queries became. This started my journey into better understanding how to optimized our database queries.

Let’s be real, you can have the greatest looking frontend, but if your queries are slow, your users will suffer. So, let’s dive into some tips on how to optimize your Django queries.

The How

Since I’m a hands-on learner, we’ll make a simple project to demonstrate the concepts.
A simple library project will suffice.

The Setup

To make installing things relatively easy, we will use UV.
Our dependencies will be:

• Django (duh)
• Django REST Framework (for API endpoints)
• Locust (for load testing)
• Mimesis (for generating fake data)

The initial project can be found on GitHub: django-query-library

Since we’re using UV, we setup the project by running uv init <project_name>, switch into the project folder and install the dependencies with uv add django djangorestframework locust mimesis.

Inside the project folder we start building the Django project by running:

uv run django-admin startproject core .

App creation

We’re going to use sqlite as our database backend since this is a demo project. No configuration is needed.

Modify core/urls.py to include the API router, we will add app routes as we build each app:

from django.contrib import admin
from django.urls import include, path
from rest_framework.routers import DefaultRouter

router = DefaultRouter()

urlpatterns = [
    path("admin/", admin.site.urls),
    path("api/", include(router.urls)),
    # app routes — we'll add these as we build each app
    # path("api/books/", include("books.urls")),
    # path("api/library/", include("library.urls")),
    # path("api/users/", include("users.urls")),
]

Users App

We’ll start with the users app. First, create the app with:

uv run django-admin startapp users

Then, define a User model in users/models.py. We’ll use email as the username field:

from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
from django.contrib.auth.models import PermissionsMixin
from django.db import models


class CustomUserManager(BaseUserManager):
    def _create_user(self, email, password, **extra_fields):
        email = self.normalize_email(email)
        user = self.model(email=email, **extra_fields)
        user.set_password(password)
        user.save(using=self._db)
        return user

    def create_user(self, email, password=None, **extra_fields):
        extra_fields.setdefault("is_staff", False)
        extra_fields.setdefault("is_superuser", False)
        return self._create_user(email, password, **extra_fields)

    def create_superuser(self, email, password, **extra_fields):
        extra_fields.setdefault("is_staff", True)
        extra_fields.setdefault("is_superuser", True)
        return self._create_user(email, password, **extra_fields)

    def get_by_natural_key(self, username):
        """
        Normalize email before lookup so that varying case in the domain
        doesn’t block authentication.
        """
        email = self.normalize_email(username)
        return self.get(**{self.model.USERNAME_FIELD: email})


class CustomUser(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(unique=True)
    first_name = models.CharField(max_length=30, blank=True)
    last_name = models.CharField(max_length=30, blank=True)
    is_staff = models.BooleanField(default=False)  # type: ignore[assignment]
    is_active = models.BooleanField(default=True)  # type: ignore[assignment]
    date_joined = models.DateTimeField(auto_now_add=True)

    USERNAME_FIELD = "email"
    REQUIRED_FIELDS: list[str] = []  # EMAIL_ONLY signup–no other "required" fields

    objects = CustomUserManager()

    class Meta(PermissionsMixin.Meta):  # type: ignore[override]
        verbose_name = "user"
        verbose_name_plural = "users"
        ordering = ["email"]

    def __str__(self):
        return f"{self.first_name} {self.last_name} ({self.email})"

To use the CustomUser model in your Django project, add AUTH_USER_MODEL = "users.CustomUser" to your settings file in core/settings.py.

Add users to your INSTALLED_APPS in core/settings.py:

INSTALLED_APPS = [
    ...
    "users",
]

Run migrations to create the database tables:

uv run python manage.py makemigrations
uv run python manage.py migrate

There, our users app is ready to go. Run the server to test it out, you’ll see the default Django admin interface at http://localhost:8000:

uv run python manage.py runserver

Books app

Next, let’s create a books app to test our queries. Run the following commands:

uv run python manage.py startapp books

Add books to your INSTALLED_APPS in core/settings.py:

INSTALLED_APPS = [
    ...
    "books",
]

Create a Book model in books/models.py:

from django.db import models


class Publisher(models.Model):
    name = models.CharField(max_length=255)
    country = models.CharField(max_length=100, blank=True)
    website = models.URLField(blank=True)

    class Meta:
        ordering = ["name"]

    def __str__(self):
        return self.name


class Author(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    birth_date = models.DateField(null=True, blank=True)
    bio = models.TextField(blank=True)
    nationality = models.CharField(max_length=100, blank=True)

    class Meta:
        ordering = ["last_name", "first_name"]

    def __str__(self):
        return f"{self.first_name} {self.last_name}"


class Genre(models.Model):
    name = models.CharField(max_length=100)
    slug = models.SlugField(unique=True)

    class Meta:
        ordering = ["name"]

    def __str__(self):
        return self.name


class Book(models.Model):
    title = models.CharField(max_length=255)
    isbn = models.CharField(max_length=13, unique=True)
    publisher = models.ForeignKey(
        Publisher,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="books",
    )
    authors = models.ManyToManyField(
        Author,
        through="BookAuthor",
        related_name="books",
    )
    genres = models.ManyToManyField(
        Genre,
        through="BookGenre",
        related_name="books",
    )
    published_date = models.DateField(null=True, blank=True)
    page_count = models.PositiveIntegerField(null=True, blank=True)
    cover_image_url = models.URLField(blank=True)
    language = models.CharField(max_length=50, default="English")
    average_rating = models.DecimalField(
        max_digits=3, decimal_places=2, null=True, blank=True
    )

    class Meta:
        ordering = ["title"]

    def __str__(self):
        return self.title


class BookAuthor(models.Model):
    class Role(models.TextChoices):
        PRIMARY = "primary", "Primary Author"
        CO_AUTHOR = "co_author", "Co-Author"
        ILLUSTRATOR = "illustrator", "Illustrator"
        EDITOR = "editor", "Editor"

    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="book_authors")
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="book_authors")
    role = models.CharField(max_length=20, choices=Role.choices, default=Role.PRIMARY)

    class Meta:
        unique_together = ("book", "author")

    def __str__(self):
        return f"{self.author} — {self.book} ({self.get_role_display()})"


class BookGenre(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="book_genres")
    genre = models.ForeignKey(Genre, on_delete=models.CASCADE, related_name="book_genres")

    class Meta:
        unique_together = ("book", "genre")

    def __str__(self):
        return f"{self.book} — {self.genre}"

Run migrations to create the database tables:

uv run python manage.py makemigrations
uv run python manage.py migrate

Now you have a books app with a Book model. You can add books to the database and test your queries.

Library App

Next, let’s create a library app. Run the following commands:

uv run python manage.py startapp library

Add library to INSTALLED_APPS in core/settings.py.

Define the models in library/models.py:

from django.conf import settings
from django.db import models


class Review(models.Model):
    book = models.ForeignKey(
        "books.Book",
        on_delete=models.CASCADE,
        related_name="reviews",
    )
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="reviews",
    )
    rating = models.PositiveSmallIntegerField()  # 1–5
    body = models.TextField(blank=True)
    is_spoiler = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ["-created_at"]
        # Enforces one review per user per book + speeds up lookups
        unique_together = ("book", "user")

    def __str__(self):
        return f"{self.user} → {self.book} ({self.rating}★)"


class ReadingList(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="reading_lists",
    )
    name = models.CharField(max_length=255)
    is_public = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ["-created_at"]
        unique_together = ("user", "name")

    def __str__(self):
        return f"{self.user} — {self.name}"


class ReadingListEntry(models.Model):
    class Status(models.TextChoices):
        WANT_TO_READ = "want_to_read", "Want to Read"
        READING = "reading", "Reading"
        FINISHED = "finished", "Finished"
        ABANDONED = "abandoned", "Abandoned"

    reading_list = models.ForeignKey(
        ReadingList,
        on_delete=models.CASCADE,
        related_name="entries",
    )
    book = models.ForeignKey(
        "books.Book",
        on_delete=models.CASCADE,
        related_name="reading_list_entries",
    )
    status = models.CharField(
        max_length=20, choices=Status.choices, default=Status.WANT_TO_READ
    )
    date_added = models.DateTimeField(auto_now_add=True)
    order = models.PositiveIntegerField(default=0)

    class Meta:
        ordering = ["order", "date_added"]
        unique_together = ("reading_list", "book")

    def __str__(self):
        return f"{self.book} in '{self.reading_list.name}' ({self.get_status_display()})"


class BorrowRecord(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="borrow_records",
    )
    book = models.ForeignKey(
        "books.Book",
        on_delete=models.CASCADE,
        related_name="borrow_records",
    )
    borrowed_at = models.DateTimeField(auto_now_add=True)
    due_date = models.DateTimeField()
    returned_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        ordering = ["-borrowed_at"]

    def __str__(self):
        return f"{self.user} borrowed '{self.book}' on {self.borrowed_at:%Y-%m-%d}"

    @property
    def is_overdue(self) -> bool:
        from django.utils import timezone
        return self.returned_at is None and self.due_date < timezone.now()

Run migrations to create the database tables:

uv run python manage.py makemigrations
uv run python manage.py migrate

Now you have a library app with a Book model that references the books app’s Book model. You can add books to the database and test your queries.