Database index strategies: db_index, Index, unique_together, GIN/GiST. Use when: optimizing query performance, adding composite indexes, full-text search indexes.
EXPLAIN ANALYZE)db_index=True on the field definitionmodels.Index(fields=[...]) in Meta.indexesmodels.UniqueConstraint(fields=[...]) in Meta.constraintsopclasses parameter on models.Indexclass 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
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"),
]
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",
),
]
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"),
]
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"),
]
class DownloadToken(TimestampedModel):
class Meta:
constraints = [
models.UniqueConstraint(
fields=["user", "firmware"],
condition=models.Q(status="active"),
name="one_active_token_per_user_firmware",
),
]
| Query Pattern | Index Type | Example |
|---|---|---|
WHERE field = X | Single db_index=True | status = "active" |
WHERE a = X AND b = Y | Composite Index(fields=[a, b]) | Brand + type filter |
ORDER BY -created_at | Descending Index(fields=["-created_at"]) | Recent items list |
WHERE status = "active" only | Partial index with condition= | Active tokens only |
| Full-text search | GinIndex on SearchVectorField | Search firmware names |
| JSON key lookups | GinIndex on JSONField | JSON config queries |
UNIQUE(a, b) | UniqueConstraint | One active token per user |
-- 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"
unique_together — use UniqueConstraint insteadEXPLAIN ANALYZE — index may not be used& .\.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