>Advanced Indexing in Django and PostgreSQL

Haki Benita’s talk at DjangoCon Europe 2026 on picking the right PostgreSQL index for the job, all through Django’s ORM.

Your Django app works fine until tables grow. Default B-Tree indexes aren’t always the best choice, db_index=True on every field wastes disk space, and more indexes slow down writes. The running example is a URL shortener.

Covering index

Store extra columns inside the index so the database never has to read the table row:

class Meta:
    constraints = [
        models.UniqueConstraint(
            fields=['key'],
            name='%(app_label)s_%(class)s_key_uk',
            include=['url'],  # stored in index, not indexed
        ),
    ]

Two disk reads instead of three. Doubles storage for the included field.

Function-based index

Index the result of an expression. The regex runs once on insert, not on every query:

class Meta:
    indexes = [
        models.Index(
            Func(F('url'), function='SUBSTRING',
                template="%(function)s(%(expressions)s from '.*://([^/]*)')"),
            name='%(app_label)s_%(class)s_domain_fix',
        ),
    ]

Result: 3 seconds down to 51ms. The expression in your query must match exactly.

Partial index

Index only the rows you care about:

class Meta:
    indexes = [
        models.Index(
            fields=['id'],
            condition=Q(hits=0),
            name='shorturl_unused_part_ix',
        ),
    ]

Result: 7 MB down to 88 KB. Best candidates: nullable foreign keys, status fields, boolean flags. One team freed 20GB by replacing full indexes on nullable FKs with partial indexes.

Hash index

Hash indexes store hash values, not actual values. Size doesn’t depend on value length:

from django.contrib.postgres.indexes import HashIndex

class Meta:
    indexes = [
        HashIndex(fields=['url'], name='shorturl_url_hix'),
    ]

47 MB down to 32 MB. Only works for equality lookups — no range queries, no ordering.

BRIN index

Block Range Index stores min/max values per group of pages. Tiny indexes for naturally ordered data:

from django.contrib.postgres.indexes import BrinIndex

class Meta:
    indexes = [
        BrinIndex(fields=('created_at',), pages_per_range=4,
                  name='shorturl_created_at_bix'),
    ]

B-Tree on created_at: 2208 KB, 2.2ms. BRIN: 64 KB, 5.2ms. 35x smaller, slightly slower. Works when data is naturally sorted on disk (timestamps, auto-incrementing IDs).

The workflow

Always: establish baseline with explain(analyze=True), add index, measure, compare. Every index slows down writes and costs disk space.

Slides | Experiment code