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.

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.

2. PostgreSQL Indexing Strategies in Django

Indexes allow PostgreSQL to find records in $O(\log n)$ 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')
            )
        ]

Speed up your database and scale your software effortlessly with KEHEM IT.