PostgreSQL Indexes Every Django Developer Should Know

Slow pages are very often slow queries. Before adding caching, check your indexes.

Find the slow query first

Use django-debug-toolbar locally, or run EXPLAIN ANALYZE on the SQL from str(queryset.query). If you see Seq Scan on a large table, an index will probably help.

Composite indexes

If you often filter by two columns together, index them together — in the right order (most selective / equality columns first):

class Payment(models.Model):
    child = models.ForeignKey(Child, on_delete=models.CASCADE)
    period = models.DateField()
    amount = models.PositiveIntegerField()

    class Meta:
        indexes = [
            models.Index(fields=["child", "period"]),
        ]

Partial indexes

Index only the rows you actually query. Perfect for "active" or "unpaid" flags:

from django.db.models import Q

class Meta:
    indexes = [
        models.Index(
            fields=["due_date"],
            name="unpaid_due_idx",
            condition=Q(is_paid=False),
        ),
    ]

The index stays tiny even when the table grows to millions of rows.

Don't over-index

Every index slows down INSERT and UPDATE and takes disk space. Add indexes for real query patterns, measure, and remove the ones that are never used (pg_stat_user_indexes shows this).