Model Indexes

Database indexes improve query performance by allowing the database to locate data without scanning every row in a table. In Django, you define indexes within the Meta class of your model using the indexes list.

Basic Usage

To create an index, add an instance of models.Index to the indexes list in your model's Meta class. You must specify which fields to index.

from django.db import models

class Customer(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

    class Meta:
        indexes = [
            models.Index(fields=['last_name', 'first_name']),
            models.Index(fields=['last_name'], name='lastname_idx'),
        ]

Common Index Options

The models.Index class accepts several arguments to customize how the index is built. The table below outlines the most frequently used options.

Option	Description
fields	A list of field names to include in the index. Use a hyphen (Ex: '-pub_date') to specify descending order.
name	The name of the index in the database. If you do not provide one, Django automatically generates it.
condition	A Q object used to create a "partial index." This restricts the index to a subset of rows (Ex: only active users).
include	A list of extra fields to store in the index as non-key columns. This allows queries to retrieve data directly from the index (Covering Index).
db_tablespace	The name of the database tablespace to use for this index.

For a complete list of Index options, refer to the Django Model Index Reference.

Functional Indexes

You can create indexes on the result of expressions or database functions rather than simple field values. This is useful for optimizing queries that filter by transformed data, such as case-insensitive searches.

To use this, pass the expression as a positional argument before any keyword arguments.

from django.db.models.functions import Lower

class Meta:
    indexes = [
        # Index the lowercased value of the 'city' field
        models.Index(Lower('city'), name='city_lower_idx'),
    ]

Partial Indexes

If you have a large table but your queries mostly target a specific subset of rows, you can use a partial index. This saves space and improves performance by only indexing rows that match a specific condition.

from django.db.models import Q

class Meta:
    indexes = [
        # Only index customers who reside in 'New York'
        models.Index(
            fields=['first_name'], 
            condition=Q(city='New York'), 
            name='ny_customers_idx'
        ),
    ]