Models Indexes

Single-Column Index

class Firmware(TimestampedModel):
    name = models.CharField(max_length=255, db_index=True)
    slug = models.SlugField(max_length=255, unique=True)  # unique implies index
    brand = models.ForeignKey("Brand", on_delete=models.CASCADE, related_name="firmwares_brand")
    # FK fields are auto-indexed by Django

Composite Index

class Firmware(TimestampedModel):
    class Meta:
        indexes = [
            # Most selective column first
            models.Index(fields=["brand", "firmware_type", "is_active"], name="idx_fw_brand_type_active"),
            models.Index(fields=["-created_at", "is_active"], name="idx_fw_recent_active"),
            models.Index(fields=["brand", "-download_count"], name="idx_fw_brand_popular"),
        ]

Partial Index (Conditional)

class DownloadToken(TimestampedModel):
    class Meta:
        indexes = [
            # Only index active tokens — skip expired/used ones
            models.Index(
                fields=["user", "firmware"],
                condition=models.Q(status="active"),
                name="idx_active_tokens_user_fw",
            ),
        ]

GIN Index for Full-Text Search

from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVectorField

class Firmware(TimestampedModel):
    search_vector = SearchVectorField(null=True)

    class Meta:
        indexes = [
            GinIndex(fields=["search_vector"], name="idx_fw_search_gin"),
        ]

GIN Index for JSONField

from django.contrib.postgres.indexes import GinIndex

class DeviceConfig(TimestampedModel):
    settings_data = models.JSONField(default=dict)

    class Meta:
        indexes = [
            GinIndex(fields=["settings_data"], name="idx_device_config_json_gin"),
        ]

Unique Constraint with Condition

class DownloadToken(TimestampedModel):
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["user", "firmware"],
                condition=models.Q(status="active"),
                name="one_active_token_per_user_firmware",
            ),
        ]

Index Strategy Decision Table

Checking Index Usage

-- Run in Django shell or psql:
EXPLAIN ANALYZE SELECT * FROM firmwares_firmware WHERE brand_id = 1 AND is_active = true;
-- Look for "Index Scan" or "Bitmap Index Scan" — NOT "Seq Scan"

Anti-Patterns

• Indexing every column — too many indexes slow writes
• Composite index in wrong order — put most selective column first
• Missing index on FK fields — Django adds these, but verify
• Using unique_together — use UniqueConstraint instead
• Index names that collide across apps — prefix with app/model name
• Not checking EXPLAIN ANALYZE — index may not be used

Quality Gate

& .\.venv\Scripts\python.exe -m ruff check . --fix
& .\.venv\Scripts\python.exe -m ruff format .
& .\.venv\Scripts\python.exe manage.py check --settings=app.settings_dev

References

• Django Index Reference
• PostgreSQL Index Types