The Case of the Missing Database Index in Django

Introduction

Your Django app runs smoothly in development. Queries return instantly. But in production, things slow to a crawl. Pages take seconds to load, users complain, and CPU usage spikes.

The culprit? A missing database index - an invisible performance killer that hides until traffic grows. At Glinteco, we’ve seen this countless times: apps that scale in users but not in database design.

The Scene: The Slow Query

A simple query like this runs in milliseconds in dev:

BlogPost.objects.filter(author_id=42).order_by('-created_at')

But in production, with millions of rows, the same query takes multiple seconds. The database scans the entire table instead of using a shortcut.

The missing witness in this crime scene? An index.

How the Problem Happens

Small Dev Datasets
- Local databases have only a few hundred rows. Performance looks fine
Rapid Growth in Production
- As records climb into the millions, queries slow exponentially
No Index Strategy
- Developers rely on defaults, forgetting to optimize columns often used in filters or joins
Over-Indexing Mistakes
- On the flip side, too many indexes can slow writes and waste storage

Debugging the Bottleneck

To catch the missing index, developers use database tools:

EXPLAIN / EXPLAIN ANALYZE in Postgres or MySQL to see query plans
Django Debug Toolbar for spotting slow queries
Monitoring tools like New Relic or Datadog for query performance metrics

PostgreSQL’s documentation provides detailed guidance on reading query plans.

The Fix

Add Indexes on Common Filters

Example

CREATE INDEX idx_blogpost_author ON blogpost (author_id);

Use Django’s db_index=True

author = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)

Leverage Composite Indexes

For queries filtering on multiple fields.

BlogPost.objects.filter(author_id=42, created_at__gte="2025-01-01")

Without an index, the database scans the entire blogpost table. With a composite index on (author_id, created_at), the database can jump straight to the right subset.

Solution

class BlogPost(models.Model): author = models.ForeignKey(User, on_delete=models.CASCADE) created_at = models.DateTimeField(auto_now_add=True)

class Meta: indexes = [ models.Index(fields=['author', 'created_at']), ]

Regularly Audit Queries

Run query analysis in staging with real data volumes.

Avoid Over-Indexing

Every index speeds up reads but slows down writes. Strike a balance.

More info can be found here: https://testdriven.io/blog/django-db-indexing/

Lesson Learned

Database indexes are invisible when they’re missing until they bring your system to a halt. The Case of the Missing Index is a reminder that performance isn’t just about code, but about data design at scale.

At Glinteco, we help clients across industries design scalable Django and database systems. Whether it’s adding the right index, tuning queries, or building a long-term growth plan, we ensure your application stays fast as it scales.

If your app feels sluggish and you can’t find the cause, it might just be a missing index and we know how to find it.