Database Indexing & Query Optimization in Django ORM

Slow database queries are the #1 cause of sluggish web application performance.

As your database grows from 1,000 rows to 10 million rows, un-indexed queries and the dreaded N+1 Query Problem will bring your production server to its knees.

Here is how to diagnose, optimize, and index your database using Django and PostgreSQL.

The order matters: measure, then fix the query pattern, then add indexes. Teams that start by adding indexes to everything end up with a slower write path, more bloat, and the same slow page — because the real problem was often an N+1 loop or a missing WHERE clause rather than a missing index.

1. Eliminating the N+1 Query Problem

The N+1 problem occurs when fetching a list of records triggers a separate database query for every single item in the list:

# ❌ SLOW: 101 queries for 100 posts (1 initial + 100 author lookups)
posts = Post.objects.all()
for p in posts:
    print(p.author.name)

# ✅ FAST: 1 single SQL JOIN query
posts = Post.objects.select_related('author').all()
for p in posts:
    print(p.author.name)

• Use select_related() for ForeignKey and OneToOne relations (SQL JOIN).
• Use prefetch_related() for ManyToManyField and reverse foreign keys.

Where N+1 Hides in Real Codebases

• DRF serializers. A SerializerMethodField or a nested serializer that touches a related object per row produces N+1 even when the view looks correct. Fix the get_queryset() with select_related/prefetch_related.
• The Django admin. list_display calling a method that reads a related field is a classic. Override get_queryset() on the ModelAdmin.
• Templates. A {% for %} loop accessing item.customer.plan in a Django template queries per iteration.
• Totals and counts. for obj in qs: obj.items.count() is N+1 in disguise; annotate instead: python from django.db.models import Count Project.objects.annotate(item_count=Count("items"))

Prefetching With Control

prefetch_related runs a second query and joins in Python, which is usually fine — but it is worth knowing how to shape it:

from django.db.models import Prefetch

# Only recent, unarchived items; alias the result for template access
projects = Project.objects.prefetch_related(
    Prefetch(
        "items",
        queryset=Item.objects.filter(archived=False).order_by("-created_at")[:5],
        to_attr="recent_items",
    )
)

That pattern — a filtered, limited prefetch — replaces a surprisingly large share of "we need a second endpoint for this" cases. Note that slicing inside a Prefetch is not supported by Django's queryset slicing rules for all cases; use a subquery-based approach if you need per-parent limits, or accept one query per page of parents.

Proving It's Fixed

• django-debug-toolbar in development shows every query and flags duplicates.
• assertNumQueries in tests turns query counts into a regression test: python with self.assertNumQueries(2): response = self.client.get("/api/projects/")
• qs.explain(analyze=True) prints the PostgreSQL plan, including actual row counts and timings.
• pg_stat_statements in production ranks the queries consuming the most total time — the single most useful optimisation input you can have. Sort by total time, not by mean, and fix the top five.

2. PostgreSQL Indexing Strategies in Django

Indexes allow PostgreSQL to find records in logarithmic time instead of scanning every row in the table:

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    status = models.CharField(max_length=20)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            # Composite index for frequent dashboard filters
            models.Index(fields=['customer', 'status']),
            # Partial index: Only index active pending orders
            models.Index(
                fields=['created_at'],
                name='pending_orders_idx',
                condition=models.Q(status='pending')
            )
        ]

Column Order in Composite Indexes

A composite index is only usable from the left. Index(fields=['customer', 'status']) helps queries filtering on customer, or customer + status — but not status alone. The practical ordering rule:

1. Equality conditions first (customer_id = ?, status = ?)
2. Then range conditions (created_at > ?)
3. Then sort columns whose order matches a common ORDER BY

Getting this wrong is why two teams can add "the same index" and see very different results.

Index Types Beyond B-tree

Partial Indexes Are the Cheapest Win Available

Most B2B applications query a small slice of a table most of the time — rows with status = 'pending', deleted_at IS NULL, is_active = true. A partial index on that slice can be a small fraction of the size of a full index, which means less to read, less to maintain, and a better chance of staying in cache. Our rule: if a filter appears in the majority of your queries, it belongs in a partial index condition.

Index Discipline

• Every index costs writes. Each insert and update maintains every index on the table. Ten indexes on an orders table make bulk inserts measurably slower.
• Find unused indexes with pg_stat_user_indexes where idx_scan = 0, and drop them. Indexes created for a feature that was removed are pure overhead.
• Add indexes concurrently in production. Django 5 supports this: ```python from django.contrib.postgres.operations import AddIndexConcurrently from django.db import migrations

class Migration(migrations.Migration): atomic = False # required for CONCURRENTLY

  operations = [
      AddIndexConcurrently(
          model_name="order",
          index=models.Index(fields=["customer", "status"], name="order_cust_status_idx"),
      ),
  ]

`` WithoutCONCURRENTLY, a large table takes an exclusive lock and your application stops serving requests until the index finishes. - **RunANALYZEafter bulk loads** so the planner has accurate statistics. A bulk insert of 500,000 rows with stale statistics can produce catastrophically bad plans. - **Remember unique constraints create indexes** — aunique=True` field is already indexed; do not add a duplicate.

Query Patterns That Beat Indexes

Sometimes the answer is to ask for less data:

• exists() instead of count() when you only need to know whether something exists.
• .values() / .values_list() for reports and dashboards, which skips model instantiation and can cut response time dramatically on wide tables.
• iterator() for processing large querysets in a loop, so Django does not cache every row in memory.
• Keyset pagination instead of large OFFSET values: WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 50 stays fast at any depth, where OFFSET 100000 degrades linearly.
• select_for_update() for read-modify-write flows, and F() expressions to increment counters atomically: python Project.objects.filter(pk=pk).update(view_count=F("view_count") + 1) This avoids the lost-update bug that appears under concurrency and is much cheaper than locking the row in Python.

Locking, Isolation and Concurrency

Slow queries are only half of database performance; the other half is what happens when several users act at once.

• Read-modify-write in Python loses updates. obj.count += 1; obj.save() can lose increments under concurrency. Use F() expressions, or lock the row with select_for_update() inside a transaction.
• Keep transactions short. A transaction that sends an email, calls an API and generates a PDF holds locks for seconds. Do the external work outside the transaction, ideally in a queued task after transaction.on_commit().
• Lock consistently. Two transactions taking the same two rows in opposite orders will deadlock. Establish a rule (for example, always lock accounts in ascending ID order) and follow it everywhere.
• Know your isolation level. PostgreSQL defaults to read committed, which is usually right; select_for_update is what you need for "read, decide, write" flows.
• Watch for lock waits in production. pg_locks and pg_stat_activity reveal blocking sessions immediately; a long-running migration or an accidental table lock shows up as a queue of waiting queries.

A Practical Optimisation Routine

When a page is slow, work in this order:

1. Count the queries. Toolbar locally, APM in production. If it is N+1, fix that first.
2. Read the plan with explain(analyze=True). Look for sequential scans on large tables, and for a planner estimate that is wildly off from the actual rows — that usually means stale statistics.
3. Check the filters. A query that filters on a computed field or applies a function to an indexed column cannot use the index.
4. Check the existing indexes against the query's WHERE, ORDER BY and JOIN clauses, remembering the leftmost-prefix rule.
5. Add one index, measure, and keep it only if it earns its write cost.
6. Reduce the data returned — .values(), fewer columns, keyset pagination — before reaching for a cache.
7. Cache last, and only for genuinely expensive aggregates. A cache on top of a broken query hides the problem until the cache expires under load.

What a Fix Looks Like

Indicative numbers from a reporting endpoint on a 10-million-row table:

Stacking the fixes is where the order-of-magnitude gains live — no single change delivers all of it.

Related reading: PostgreSQL vs MySQL covers why the engine matters before the index does; database design covers the schema decisions; and performance optimisation covers the end-to-end view. Speed up your database and scale your software effortlessly with KEHEM IT.

Queries getting slower every quarter? Send us the slowest one and we will tell you what the planner is doing.