A Practical Database Indexing Strategy for Django

Problem statement

As tables grow, list views, admin filters, and API endpoints slow down. Adding db_index=True to every field feels safe but makes writes slower, wastes memory, and still misses the indexes queries actually need, such as composites for filter-plus-order patterns. A good indexing strategy starts from real queries, matches index shape to query shape, and removes indexes nobody uses.

1. Find the queries that cost the most total time with pg_stat_statements.
2. Read their plans with EXPLAIN (ANALYZE, BUFFERS) or QuerySet.explain().
3. Add indexes that match filters and ordering: composite indexes with equality columns first, then range or sort columns.
4. Use partial indexes for common subsets (is_active = true), expression indexes for Lower() lookups, and include for covering indexes.
5. Create indexes concurrently on large tables, and drop indexes with zero scans after checking all replicas.

Step-by-step solution

1. Find what to index

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Total time matters more than mean time: a 5 ms query run a million times outweighs a 2 s report run twice. See Find Slow Django Queries With pg_stat_statements.

2. Read the plan

print(Order.objects.filter(customer_id=42, status="paid").order_by("-created_at")[:20].explain(analyze=True, buffers=True))

A Seq Scan on a large table with a selective filter, or a Sort step on many rows for a small LIMIT, signals a missing index.

3. Shape indexes to queries

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT)
    status = models.CharField(max_length=20)
    created_at = models.DateTimeField(auto_now_add=True)
    email = models.EmailField()
    total = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta:
        indexes = [
            models.Index(fields=["customer", "status", "-created_at"], name="order_cust_status_created"),
            models.Index(fields=["created_at"], name="order_open_recent", condition=models.Q(status="open")),
            models.Index(models.functions.Lower("email"), name="order_email_lower"),
            models.Index(fields=["status"], include=["total"], name="order_status_cov"),
        ]

Rules of thumb for composites: equality filters first, then the range filter or sort column. One composite index often serves several queries that share its leading columns.

4. Add indexes safely

On PostgreSQL, a plain CREATE INDEX blocks writes to the table while it builds. For large tables, use Django's concurrent operations in a non-atomic migration:

from django.contrib.postgres.operations import AddIndexConcurrently

class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0031_previous")]
    operations = [
        AddIndexConcurrently("order", models.Index(fields=["customer", "status", "-created_at"], name="order_cust_status_created")),
    ]

See Add Django Database Indexes Concurrently.

5. Remove unused indexes

Every index slows inserts and updates and occupies memory. Find candidates:

SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Check statistics on replicas too (queries routed there do not count on the primary), keep unique indexes that enforce constraints, and wait through a full business cycle before dropping.

Explanation

PostgreSQL's planner uses an index when it estimates fewer pages to read than a sequential scan. B-tree indexes are ordered by their columns, so a composite index on (customer_id, status, created_at DESC) can find one customer's paid orders and return them already sorted, avoiding a sort. Partial indexes store only matching rows, keeping them small and hot in memory. Each index also costs work on every write, which is why pruning matters as much as adding.

Edge cases / notes

• Low-selectivity columns. An index on a boolean or a status with two common values rarely helps alone; use it as part of a composite or as a partial index condition.
• LIKE '%term%'. B-tree indexes cannot help leading wildcards; use trigram GIN indexes or full-text search.
• MySQL. InnoDB lacks partial indexes and INCLUDE; composite ordering rules are similar.

Next steps

• Build indexes without locks using Add Django Database Indexes Concurrently.
• Read plans confidently with Use QuerySet.explain() and EXPLAIN ANALYZE in Django.
• Index JSON data with Index Django JSONField With GIN on PostgreSQL.

FAQ

Should I add db_index=True to every filtered field?

No. Index based on real queries; composite and partial indexes usually serve them better.

Does Django index foreign keys automatically?

Yes, for ForeignKey fields on PostgreSQL and most backends. Composite indexes starting with the foreign key still need declaring.

How do I add an index without downtime?

Use AddIndexConcurrently in a migration with atomic = False on PostgreSQL.

How do I know an index is unused?

Check idx_scan in pg_stat_user_indexes on the primary and replicas over a representative period.