Making Django queries measurable

Most of the performance work I have done has been on other people’s code, at work, where the numbers are not mine to publish. So I built a small version of the same problem in the open: a Django project where every page runs an unoptimized query and its optimized counterpart against identical data, and prints the query count and timing for both.

It is live at django-query-optimization.nyagah.me. You can click through it without cloning anything. The code is on GitHub.

The problem

An N+1 does not fail. It just adds one more query for every row, and the page that ran fine on ten rows gets slower as the table fills. The first time I hit it seriously was on a long-lived Django API: the view looked clean, the serializer looked clean, and the database was getting hammered. The code was not wrong in any way a reviewer would catch. It was wrong in a way only the query log shows.

That is the whole reason the demo exists. “This endpoint runs a query per row” is easy to say and hard to feel. A number on a page is easier to feel.

Making it visible

Every comparison page measures two things: how many SQL statements ran, and how long they took. Django only records queries when DEBUG is on, and I did not want the measurement to depend on a debug flag staying set in the environment. So the measurement forces query logging on for the duration of the run and turns it back off afterwards:

def _measure(queryset, serializer_fn):
    conn = connections["default"]
    old = conn.force_debug_cursor
    conn.force_debug_cursor = True
    reset_queries()
    start = time.perf_counter()
    try:
        data = serializer_fn(queryset)
    finally:
        elapsed = time.perf_counter() - start
        query_count = len(conn.queries)
        conn.force_debug_cursor = old
    return {"data": data, "queries": query_count, "time_ms": round(elapsed * 1000, 1)}

force_debug_cursor is the same switch the debug toolbar uses, and it works regardless of the DEBUG setting. The pages then render the two results side by side, so the difference is visible without any tooling or explanation.

There is also a REST API at /api/ with an optimized and unoptimized viewset for each resource, for comparing the JSON directly.

The data

The project models a small library. In books there are Book, Author, Genre, and Publisher, with through tables for the many-to-many relationships. In library there are Review, ReadingList, ReadingListEntry, and BorrowRecord. Users use a custom model with email login.

The shape matters more than the domain. It covers the three cases that produce most N+1s: a forward foreign key (Review.user), a reverse foreign key (Book.reviews), and a many-to-many (Book.genres).

The seeders generate a realistic amount of data with Mimesis — 5000 books, 300 authors, 100 publishers, 20 genres, and 1000 users — and insert it with bulk_create rather than one save() per row, because on that volume a naive seeder is itself a query-count problem.

uv run manage.py seed_books
uv run manage.py seed_users
uv run manage.py seed_library
# or: uv run manage.py seed_all

The fixes, by relationship shape

The rules are not complicated, but they only make sense once you can see which shape you are dealing with.

Forward foreign keys: select_related. The reviews page is the clearest case. The serializer reads review.user.email and review.book.title, both forward foreign keys, so with no optimization each review fires two extra queries. select_related('user', 'book') turns reviews, users, and books into a single joined query. That is 2N + 1 down to 1.

Reverse foreign keys and many-to-many: prefetch_related. The books page reads book.book_authors, book.genres, and book.authors.count(). All three are the “many” side, so a join would multiply rows. prefetch_related fetches each relation in its own batched query and stitches the result together in Python. Adding annotate(author_count=Count('authors')) moves the count into SQL as well. With a select_related('publisher') on top, the whole page goes from 4N + 1 to 3 queries.

The Prefetch object is worth knowing about separately. When you need the authors themselves and not just the links, you can pass a queryset that already has select_related('author') on it, so the join happens inside the prefetch instead of adding a query per author:

book_authors_prefetch = Prefetch(
    "book_authors",
    queryset=BookAuthor.objects.select_related("author"),
)

Counts and averages: annotate. The users page calls .count() on a reverse relation for each row, which is one query per user. annotate(review_count=Count('reviews')) computes it in the database and exposes it as a plain attribute. N + 1 down to 1.

Wide tables: only. When the serializer uses a handful of columns, only() stops the rest from crossing the wire. The catch is that touching a deferred field later fires a hidden query, so the list has to match the serializer exactly. I learned that one the hard way: combine only() with prefetch_related and you have to include the prefetched relation’s fields too.

Two newer tricks. For “latest review per book”, a SerializerMethodField calling .order_by('-created_at').first() per book is N + 1. A Subquery with OuterRef('pk') folds it into the main query and takes it to 1. The same idea with Exists(OuterRef('pk')) answers “does this book have an active borrow?” in the main query instead of one EXISTS per row.

Proving it stays fixed

A demo that shows 4N + 1 → 3 once is a screenshot. What makes it a guardrail is the test suite, which now has 24 tests across books, library, and users. Each one hits an unoptimized endpoint and its optimized twin and asserts the optimized one fires fewer queries.

The part I added most recently is the one I care about. “Fewer” is a weak claim; “constant” is the actual property. These tests seed 40 books, measure the optimized count, shrink the table to 5 rows, and measure again:

def _count(self, url):
    with CaptureQueriesContext(connection) as ctx:
        self.client.get(url)
    return len(ctx)

def test_optimized_count_is_independent_of_row_count(self):
    large = self._count(self.optimized_url)
    self._shrink_dataset()
    small = self._count(self.optimized_url)
    self.assertEqual(large, small)

The unoptimized endpoint gets the mirror test: its count must drop when the rows do. Between the two, the pair cannot pass unless the optimization is real and stays real as the data grows.

uv run manage.py test
# Ran 24 tests ... OK

Honest status

The demo is hosted and the API and frontend work. The tests pass and cover the main optimization paths, including the scale-invariance checks above. What is not done: I have an article draft for this project that is still in the repo and not published, and the seeders are the only place I have measured with a full production-sized dataset. Running the suite against the seeded 5000-book database, rather than a 40-row fixture, is the next thing I would add.

If you want to see it without setting anything up, open the site and click between the unoptimized and optimized versions of a page. The number at the top is the point.