Kuldeep Pisda
From 30 Seconds to 200ms: The Django Queries Nobody Asked For

From 30 Seconds to 200ms: The Django Queries Nobody Asked For

Some endpoints took 30 seconds. The fix was N+1 elimination, real Postgres indexes, and Directus-style sparse fields so clients fetch only what they need.

17 min read

From 30 Seconds to 200ms: The Django Queries Nobody Asked For
Contents

With the ERP’s permissions now defined from a screen instead of a deploy (chapter 9), the platform had crossed a line I could feel: the content team, the department heads, and the booking staff were all self-serving from the same Django backend without filing a ticket. It was doing real work for real people. And then, quietly, it started doing that work slowly.

The complaints didn’t arrive as a crash. They arrived as a shrug. “The courses page takes a while to load.” “The admin list just spins.” On a good connection the course listing came back in three or four seconds — annoying but survivable. On a mid-range Android phone on 4G in a small town, which is most of our users, the same endpoint could take twenty to thirty seconds before the first byte. That is well past the point where a user assumes the app is broken and closes it. We were losing people not to a bug, but to a spinner.

This chapter is about why those endpoints were slow, and why the fix turned out to be less about faster queries and more about not running queries nobody asked for.

First, find where the seconds actually go

Before touching a line of code, I turned on query logging and loaded the offending page once. In Django that’s a two-line change to settings.py that dumps every SQL statement, with its timing, to the console:

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

The number that scrolled past was the whole story: a single request to the course-list endpoint was firing just over 900 database queries.

Not one slow query. Nine hundred fast ones. Each took a couple of milliseconds, which sounds fine until you multiply, and then remember that every one of them is a round trip across the network to Postgres and back. That is the shape of almost every mysteriously slow ORM endpoint I have ever debugged, and it has a name.

The N+1 query problem, in plain terms

Say you ask the API for 50 courses. The ORM runs one query to fetch the list of courses. Fine. But your serializer — the code that turns each course into JSON — reaches for related data on every course: the author’s name, the category’s title, how many lessons it has. Each of those reaches is a separate trip to the database, executed once per course.

So it’s one query for the list, then N more queries, one for each of the N rows. 1 + N. With 50 courses and three related lookups each, that is 1 + 150 queries for a single page. Nest one more relation — the author’s profile, say — inside the loop, and the count explodes. That is the N+1 problem: the database chatter grows with the number of rows, and you never see it in the code because each individual line looks innocent. course.author.name doesn’t look like a database query. It is one.

Here is the traffic that request was generating, drawn out:

The two tools Django already gives you

Django’s ORM has known how to fix this for over a decade. The trick is knowing which of two tools to reach for, and that depends entirely on the shape of the relationship.

select_related — for to-one relationships. A course has one author and one category (foreign keys pointing outward). For these, Django can do a single SQL JOIN: it asks the database to glue the author and category rows onto each course row in one query. A JOIN is the database matching rows across tables on a shared key and returning them stitched together. One trip, all the to-one data comes back attached.

prefetch_related — for to-many relationships. A course has many lessons and many tags. You cannot JOIN those without the result set exploding — 50 courses times 30 lessons is 1,500 duplicated course rows, which is slower, not faster. So Django does something smarter: it runs the courses query, collects their IDs, then fires one second query that fetches every lesson for all those courses at once with WHERE course_id IN (...), and stitches them together in Python. Two queries total instead of 51.

The distinction is the whole game: JOIN the to-one, batch the to-many. Get that right and 900 queries collapse to a handful:

qs = (
    Course.objects
    .select_related("author", "category")   # 2 to-one FKs -> one JOIN
    .prefetch_related("lessons", "tags")     # 2 to-many -> two IN-queries
)
# 1 (courses) + 1 (author/category joined in) + 1 (lessons) + 1 (tags) = 4 queries.

But a bare prefetch_related("lessons") fetches every lesson column for every lesson, including the ones the home screen never shows, and in whatever order the table hands them back. On the sparse path that’s wasted bytes over the wire and wasted work in Postgres. The fix is a Prefetch() object with a tuned inner queryset — you get to filter, order, and trim the batched query the same way you would any other:

from django.db.models import Prefetch

published_lessons = (
    Lesson.objects
    .filter(is_published=True)                       # skip drafts entirely
    .only("id", "course_id", "title", "duration_seconds", "order")  # 5 columns, not 20
    .order_by("order")                               # ordered in SQL, not in Python
)

qs = (
    Course.objects
    .select_related("author", "category")
    .prefetch_related(Prefetch("lessons", queryset=published_lessons))
)

The course_id in that .only(...) is not optional — it is the key Django uses to stitch each lesson back onto its course in Python. Drop it and the prefetch silently breaks.

The SerializerMethodField trap

There is one N+1 that survives all of this, and it hides inside the serializer. The moment you write a SerializerMethodField that touches the database, you have re-introduced the per-row storm that prefetch_related just killed:

class CourseSerializer(serializers.ModelSerializer):
    lesson_count = serializers.SerializerMethodField()

    def get_lesson_count(self, obj):
        return obj.lessons.count()   # a COUNT(*) query PER course. 50 rows -> 50 queries.

obj.lessons.count() looks like a cheap attribute read. It is a fresh SELECT COUNT(*) FROM lesson WHERE course_id = ? for every course in the page, and no prefetch will save you, because .count() deliberately ignores the prefetched cache and asks the database again. The fix is to compute the count once, in the list query, with an annotation:

from django.db.models import Count, Q

qs = qs.annotate(
    lesson_count=Count("lessons", filter=Q(lessons__is_published=True))
)

class CourseSerializer(serializers.ModelSerializer):
    lesson_count = serializers.IntegerField(read_only=True)  # reads the annotation, 0 extra queries

One caveat worth internalising before you stack annotations: Count over a to-many is a LEFT JOIN under the hood, so annotating two different to-many counts on the same queryset makes the joins multiply and each count comes back inflated. When that happens, either add distinct=True to each Count, or push the counts into Subquery/OuterRef expressions so each is its own scalar subquery and the rows never fan out.

The index: turning a scan into a lookup

There was a second, quieter cost hiding underneath. Even the batched queries were filtering on columns Postgres had no fast path to. When you ask for WHERE language = 'hi' AND is_published = true and there’s no index, the database does a sequential scan — it reads every row in the table to find the matching ones. At a few hundred rows that’s invisible. At the size our catalogue was heading toward, it’s a tax on every request.

A database index is the index at the back of a textbook. Instead of reading all 400 pages to find every mention of “transcoding,” you flip to the back, find the word, and jump straight to the pages. Postgres builds a sorted B-tree of the column’s values so it can jump straight to matching rows — a lookup instead of a full read. You pay a small cost on writes to keep the index current, and in exchange reads on that column go from linear to logarithmic. For a filter column on a read-heavy endpoint, that trade is almost always worth it.

This matters more than usual for us because of what the catalogue is. Tejgyan has been recording discourses and satsangs for years; by this point the course table held roughly 46,000 published items, and the home screen’s default query is always the same shape: “the newest published Hindi content,” i.e. WHERE language = 'hi' AND is_published = true ORDER BY published_at DESC LIMIT 50.

Composite indexes: column order is not cosmetic

The naive move is to index each filter column on its own. The right move is one composite index whose column order matches how the query narrows. The rule: equality columns first, then the range/sort column last.

class Course(models.Model):
    # ...
    class Meta:
        indexes = [
            # equality (language, is_published) THEN the ordering column (published_at)
            models.Index(
                fields=["language", "is_published", "-published_at"],
                name="course_lang_pub_date_idx",
            ),
        ]

Here’s why the order is load-bearing. A B-tree is sorted left-to-right, like a phone book sorted by last name, then first name. Put language and is_published first and Postgres walks straight to the contiguous block of rows where language = 'hi' AND is_published = true. Crucially, within that block the rows are already sorted by published_at DESC — so the ORDER BY is free and the LIMIT 50 just reads the first 50 entries and stops. No sort step at all.

Flip it — put published_at first — and the index is useless for the filter: Hindi published rows are scattered throughout the date ordering, so Postgres can’t jump to them, and you’re back to scanning. Equality-then-range isn’t a style preference; it’s the difference between the index working and the index being dead weight the writes still have to maintain.

You verify it worked by reading the actual query plan, never by guessing. EXPLAIN (ANALYZE, BUFFERS) runs the query for real and reports where the time and the I/O went — Buffers: shared read=N is pages fetched from disk, shared hit=N is pages already in cache.

Before — no usable index, sequential scan over all 46k rows:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, thumbnail, duration_seconds
FROM course
WHERE language = 'hi' AND is_published = true
ORDER BY published_at DESC
LIMIT 50;

 Limit  (cost=4181.05..4181.17 rows=50 width=72)
        (actual time=228.451..228.469 rows=50 loops=1)
   Buffers: shared hit=96 read=1740
   ->  Sort  (cost=4181.05..4199.42 rows=7348 width=72)
             (actual time=228.449..228.457 rows=50 loops=1)
         Sort Key: published_at DESC
         Sort Method: top-N heapsort  Memory: 41kB
         ->  Seq Scan on course  (cost=0.00..3936.00 rows=7348 width=72)
                                 (actual time=0.031..214.802 rows=7412 loops=1)
               Filter: (is_published AND (language = 'hi'::text))
               Rows Removed by Filter: 38588
               Buffers: shared hit=96 read=1740
 Planning Time: 0.214 ms
 Execution Time: 228.605 ms

Read the two lines that matter: Rows Removed by Filter: 38588 — Postgres touched all 46,000 rows and threw away 38,588 to find the 7,412 Hindi-published ones — and shared read=1740, meaning it pulled 1,740 pages off disk to do it. Then it sorted the survivors. 228 ms for one filter, on the hottest query in the app.

After — the composite index, same query:

 Limit  (cost=0.41..12.63 rows=50 width=72)
        (actual time=0.028..0.311 rows=50 loops=1)
   Buffers: shared hit=8
   ->  Index Scan using course_lang_pub_date_idx on course
             (cost=0.41..1795.02 rows=7348 width=72)
             (actual time=0.026..0.297 rows=50 loops=1)
         Index Cond: ((language = 'hi'::text) AND (is_published = true))
 Planning Time: 0.183 ms
 Execution Time: 0.352 ms

The Sort node is gone — the index already delivers rows in published_at DESC order. Rows Removed by Filter is gone — the Index Cond jumped straight to the matches. Buffers: shared hit=8, all from cache, nothing from disk. 228 ms to 0.35 ms, roughly 650x, on that one query.

When Index Scan becomes Index Only Scan

There’s a further gear. A plain Index Scan still visits the table (“the heap”) to fetch the columns that aren’t in the index — here title, thumbnail, duration_seconds. If a query only ever needs columns the index already carries, Postgres can skip the heap entirely: an Index Only Scan. Our navigation counter — “how many published Hindi items exist” — is exactly that shape, and a covering index with INCLUDE lets it never touch the table:

from django.contrib.postgres.indexes import BTreeIndex

# key columns answer the WHERE; INCLUDE carries published_at so a count
# with a date cutoff is still answered from the index alone.
BTreeIndex(fields=["language", "is_published"], include=["published_at"],
           name="course_lang_pub_covering_idx")

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM course WHERE language = 'hi' AND is_published = true;

 Aggregate  (actual time=1.204..1.205 rows=1 loops=1)
   Buffers: shared hit=41
   ->  Index Only Scan using course_lang_pub_covering_idx on course
             (actual time=0.022..0.887 rows=7412 loops=1)
         Index Cond: ((language = 'hi'::text) AND (is_published = true))
         Heap Fetches: 0
 Planning Time: 0.161 ms
 Execution Time: 1.238 ms

Heap Fetches: 0 is the prize — the table was never opened. But it comes with a requirement people miss: Index Only Scans rely on Postgres’s visibility map to know a page has no in-flight row versions, and that map is only kept current by VACUUM. On a write-heavy table that hasn’t been vacuumed recently, Heap Fetches climbs and the “only” quietly degrades back toward a normal Index Scan. On read-heavy content tables like ours it stays at zero, but it’s a guarantee you check, not one you assume.

The part I’m actually proud of: let the client ask for less

Fixing the N+1 and adding indexes took the course list from ~30 seconds to about 1.5 seconds. A 20x win, and I could have stopped there. But 1.5 seconds still bothered me, because I knew why it was slow: even done efficiently, we were assembling the entire object graph of every course — author, category, all lessons, all tags, progress stats — and serializing it into JSON, on every request. The mobile home screen, which is what most of that traffic was, displayed exactly three things per course: a title, a thumbnail, and a duration. We were fetching a novel to show a headline.

The conventional next move is to build separate serializers — a CourseListSerializer with three fields and a CourseDetailSerializer with everything. It works, but it hard-codes the answer. Every time a screen needs a slightly different slice, someone edits Python and ships a deploy. I had lived a better pattern already, at the very start of this journey.

Back in chapter 1, the throwaway MVP ran on Directus, and the single feature I missed most after we left it was ?fields=. Directus let the client name exactly which fields it wanted in the URL, and returned only those. So I brought it back. We taught our DRF serializers to honour a sparse fieldset — the client sends ?fields=id,title,thumbnail,duration, and the server returns only those keys.

The performance win isn’t really in the smaller JSON payload, though that helps on a slow phone. It’s this: if nobody asked for lessons, the server doesn’t prefetch lessons. The requested field list drives the queryset. Ask for four scalar fields and the endpoint does one indexed query with zero joins and zero prefetches. The most expensive work an endpoint can do is fetching a relation graph that the caller is going to throw away — so the cleanest optimization is to never fetch it.

The mechanism has two halves that have to agree with each other: a serializer that trims its output to the requested fields, and a queryset that trims its work to match. Get them out of sync and you either serialize a field you never fetched (crash) or fetch a relation you never serialize (waste). Here’s the serializer half — the standard DRF sparse-fieldset base class, which pops unrequested fields off self.fields in __init__:

from rest_framework import serializers


class DynamicFieldsModelSerializer(serializers.ModelSerializer):
    """
    A ModelSerializer that honours a ?fields=a,b,c sparse fieldset.
    Any field not named is popped BEFORE serialization runs, so we never
    render — nor trigger the DB work behind — a field nobody asked for.
    """

    def __init__(self, *args, **kwargs):
        # Allow an explicit fields= kwarg (for nested/programmatic use)…
        explicit = kwargs.pop("fields", None)
        super().__init__(*args, **kwargs)

        requested = explicit
        if requested is None:
            # …otherwise read it off the request query string.
            request = self.context.get("request")
            if request is not None:
                raw = request.query_params.get("fields")
                requested = raw.split(",") if raw else None

        if requested is not None:
            allowed = set(requested)
            existing = set(self.fields)          # self.fields is an OrderedDict
            for field_name in existing - allowed:
                self.fields.pop(field_name)      # drop everything not asked for


class CourseSerializer(DynamicFieldsModelSerializer):
    lesson_count = serializers.IntegerField(read_only=True)  # served by annotation

    class Meta:
        model = Course
        fields = [
            "id", "title", "slug", "thumbnail", "duration_seconds",
            "language", "published_at", "author", "category",
            "lessons", "tags", "lesson_count",
        ]

And here’s the queryset half. It reads the same ?fields=, and for a request that asks for nothing but scalar columns it takes a fast path: only() trims the SELECT to those columns (so the composite index can serve the whole thing) and it adds no joins and no prefetches at all. Only when a relation is actually named does the corresponding select_related / Prefetch / annotation get attached:

from django.db.models import Count, Prefetch, Q


class CourseViewSet(ModelViewSet):
    serializer_class = CourseSerializer

    # columns that live on the course row itself
    SCALAR_FIELDS = {
        "id", "title", "slug", "thumbnail",
        "duration_seconds", "language", "published_at",
    }
    # to-one FKs -> select_related (JOIN)
    SELECT_RELATED = {"author", "category"}
    # to-many -> Prefetch with a tuned, trimmed inner queryset
    PREFETCH = {
        "lessons": Prefetch(
            "lessons",
            queryset=Lesson.objects.filter(is_published=True)
            .only("id", "course_id", "title", "duration_seconds", "order")
            .order_by("order"),
        ),
        "tags": Prefetch("tags", queryset=Tag.objects.only("id", "name", "slug")),
    }

    def get_queryset(self):
        raw = self.request.query_params.get("fields")
        wanted = set(raw.split(",")) if raw else None

        qs = Course.objects.all()

        # Fast path: only row-local columns requested -> no joins, no prefetch.
        # only(*wanted) keeps the SELECT narrow enough for an index-served read.
        if wanted is not None and wanted <= self.SCALAR_FIELDS:
            return qs.only(*(wanted | {"id"}))

        # Otherwise attach exactly the machinery the requested fields require.
        for rel in self.SELECT_RELATED:
            if wanted is None or rel in wanted:
                qs = qs.select_related(rel)

        for rel, prefetch in self.PREFETCH.items():
            if wanted is None or rel in wanted:
                qs = qs.prefetch_related(prefetch)

        if wanted is None or "lesson_count" in wanted:
            qs = qs.annotate(
                lesson_count=Count("lessons", filter=Q(lessons__is_published=True))
            )
        return qs

Now the two halves are in lockstep. ?fields=id,title,thumbnail,duration_seconds — the home screen’s real request — falls entirely into the fast path: one index-served query, four columns, no author JOIN, no lessons prefetch, no lesson_count annotation, and the serializer pops the other eight fields before they’re ever touched. The full detail view, with no ?fields=, gets everything. Same endpoint, same serializer, and the caller decides how much work the backend does.

Here is the whole before-and-after in one picture:

The outcome

The home-screen course list, the endpoint users hit most, went from ~30 seconds to roughly 200 milliseconds at the same catalogue size and the same phone. The N+1 fix removed the round-trip storm; the indexes removed the sequential scans; sparse fields removed the work we were doing for no one. Layered, they didn’t just add up — they compounded, because the biggest saving was the one where the fast path does almost nothing at all.

The other thing it bought us was headroom. The same three techniques applied cleanly to the Ambarsthan booking lists and the ERP’s staff directories, which had been quietly slow for the same reasons. One pattern, three products, because it’s the same backend.

The lesson

Most slow endpoints are not slow because the database is slow. They are slow because the ORM is faithfully fetching a graph of data that nobody on the other end asked for, and doing it one lazy attribute access at a time. The fix has three moves, in order of leverage:

  1. Stop the chatter. JOIN the to-one relations with select_related, batch the to-many with prefetch_related. This alone usually wins 10x or more.
  2. Index the columns you filter and join on, and confirm it with EXPLAIN rather than faith.
  3. Let the client ask for exactly what it needs, and fetch exactly that. Sparse fieldsets turn “fast” into “does almost nothing,” because the cheapest query is the one you skip.

The deepest version of the lesson is a mindset, not a technique: the client knows what it’s rendering; the server shouldn’t have to guess. Push that knowledge to the edge — into the request — and the backend gets to be lazy in the good way.

With the API finally fast and the whole platform feeling solid under real load, I ran out of excuses for the one problem I had been deliberately dodging since chapter 3 — the 1.2 GB course video that a user in a small town was still expected to swallow whole before a single frame played. It was time to build streaming ourselves.