Database Indexes In Models

1. Introduction

A database index is a data structure that speeds up lookups on a column. Without an index, the database scans every row in a table to find matches. With an index, it jumps directly to the relevant rows — much faster on large tables.

Django lets you define indexes in two ways: directly on a field using db_index=True, or more explicitly using models.Index in the model's Meta class. This guide covers both approaches and when to use each.

• You should already be comfortable with model fields and the Meta class.
• Your .venv must be active and Django 5.2 installed.

2. When to add an index

Not every column needs an index. Adding too many indexes slows down writes because the database must update every index on every insert or update. Index columns that you:

• Filter on frequently — .filter(status='published')
• Order by regularly — .order_by('-created')
• Join on — foreign keys are automatically indexed by Django
• Look up by uniqueness — fields with unique=True are automatically indexed

3. db_index on a field

The simplest way to index a single column is to set db_index=True on the field:

class Article(models.Model):
    title = models.CharField(max_length=200)
    slug = models.SlugField(unique=True)           # unique=True adds an index automatically
    status = models.CharField(max_length=20, db_index=True)  # explicit single-column index
    created = models.DateTimeField(auto_now_add=True, db_index=True)

Use db_index=True for simple single-column indexes. For multi-column indexes or more control, use models.Index in Meta.

4. models.Index in Meta

Define indexes in the Meta class using models.Index. This approach is more explicit, supports multi-column indexes, and lets you name the index:

from django.db import models


class Article(models.Model):
    title = models.CharField(max_length=200)
    slug = models.SlugField(unique=True)
    status = models.CharField(max_length=20)
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            models.Index(fields=['status', 'created'], name='article_status_created_idx'),
            models.Index(fields=['created'], name='article_created_idx'),
        ]

The name parameter is optional but recommended — it makes the index easier to identify in the database and in migrations. Without a name, Django generates one automatically.

The multi-column index on ['status', 'created'] is useful when you frequently run queries like:

Article.objects.filter(status='published').order_by('-created')

5. Partial indexes

A partial index only indexes rows that match a condition. This makes the index smaller and faster because it ignores rows you never query. For example, if you always filter on published articles, only index those:

from django.db import models
from django.db.models import Q


class Article(models.Model):
    title = models.CharField(max_length=200)
    status = models.CharField(max_length=20)
    created = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            models.Index(
                fields=['created'],
                condition=Q(status='published'),
                name='published_article_created_idx',
            )
        ]

This index only covers published articles. Draft and archived articles are not indexed here, so the index is smaller and the queries it supports are faster.

6. Column order in multi-column indexes

For a multi-column index, the order of fields matters. The index is useful for queries that filter or sort on the leftmost fields first.

models.Index(fields=['status', 'created'], name='status_created_idx')

This index helps these queries:

# Uses the index
Article.objects.filter(status='published').order_by('-created')
Article.objects.filter(status='published')

# Does NOT use this index efficiently
Article.objects.filter(created__gte=some_date)  # skips status, starts from the second column

Put the most selective column first — the one that narrows results the most. In this example, status has only a few possible values, so putting created first would give a less useful index.

7. Checking your indexes

After running migrations, you can confirm your indexes exist by inspecting the database. From the Django shell with SQLite:

python manage.py dbshell

-- SQLite: list indexes on the article table
.indexes pages_article

-- PostgreSQL
\d pages_article

You can also use python manage.py sqlmigrate pages 0001 to see the SQL Django generated for any migration, including the CREATE INDEX statements.

8. Next steps

Your models now have the right indexes for fast queries. The next step covers model inheritance — how to share fields and behavior across models using abstract base classes, multi-table inheritance, and proxy models.