skip to content

In a Django model, when do you declare Meta.indexes with Index(condition=...), include= or expressions instead of setting db_index=True on a field?

level: seniorimportance: should knowfreq 32%

answer

  1. one column versus a shaped index
  2. a name becomes mandatory
  3. some options are silently ignored
  4. PostgreSQL-only covering columns

basics

~10 s

db_index=True gives one plain single-column index. Meta.indexes with models.Index adds multi-column, descending, partial (condition), covering (include) and functional (expressions) indexes. Those need a name, and some backends ignore condition or include.

solid answer

~40 s

`db_index=True` creates one plain index on one column, and a `ForeignKey` already has one by default. When the query needs more shape, I declare `models.Index` in `Meta.indexes`: several `fields` in order with `-` for descending, a `condition=Q(...)` for a partial index, `include=[...]` for covering columns, or positional expressions such as `Lower("email")` for a functional index. `condition`, `include` and `expressions` all require an explicit `name`, at most 30 characters. Backend support differs: MySQL and MariaDB ignore `condition`, `include` works only on PostgreSQL, and PostgreSQL rejects non-immutable functions in expressions. For the renewal job that scans active subscriptions by `renews_at`, a partial index `Index(fields=["renews_at"], condition=Q(status="active"), name="active_renewal_idx")` stays small and matches the query.

code

python · 19 lines
python
from django.db import models
from django.db.models import Q
from django.db.models.functions import Lower


class Subscription(models.Model):
    # user, plan, status, started_at, renews_at as before
    coupon_code = models.CharField(max_length=40, blank=True)

    class Meta:
        indexes = [
            models.Index(fields=["plan", "-started_at"], name="sub_plan_started_idx"),
            models.Index(
                fields=["renews_at"],
                condition=Q(status="active"),
                name="sub_active_renewal_idx",
            ),
            models.Index(Lower("coupon_code"), name="sub_coupon_lower_idx"),
        ]

go deeper

for a junior

Recall that db_index=True adds a single-column index and that foreign keys are indexed by default.

for a middle

Explain Meta.indexes: composite and descending fields, and why condition, include and expressions need a name.

for a senior

Design indexes from real queries, check which options the production backend honours, and avoid duplicating indexes that constraints already create.

for a principal

Balance read speed against write cost and storage across the schema, and decide how index changes on large tables are reviewed and rolled out.

## Two ways to ask Django for an index 1. **`db_index=True` on a field** creates a plain, single-column, ascending index. `ForeignKey` sets it by default, so `user` and `plan` on a `Subscription` are already indexed. 2. **`Meta.indexes`** takes a list of `models.Index` objects. This is where every non-unique index with more shape goes, including multi-column ones: the old `Meta.index_together` option was deprecated in 4.2 and removed in 5.1. (Unique multi-column indexes come from `UniqueConstraint`.) Both produce migration operations; `Meta.indexes` entries appear as `AddIndex` and `RemoveIndex`. ## What models.Index can express | Option | Example | Use | Backend notes | |---|---|---|---| | `fields` (several, ordered) | `Index(fields=["plan", "-started_at"], name=...)` | composite lookups and sorts | everywhere | | `condition` | `Index(fields=["renews_at"], condition=Q(status="active"), name=...)` | partial index over a subset of rows | ignored on MySQL, MariaDB and Oracle, with a check warning | | `include` | `Index(fields=["user"], include=["status"], name=...)` | covering index for index-only scans | PostgreSQL only; ignored elsewhere | | `*expressions` | `Index(Lower("coupon_code"), name=...)` | functional index on a computed value | PostgreSQL requires immutable functions, Oracle deterministic ones | | `opclasses` | `Index(fields=["email"], opclasses=["varchar_pattern_ops"], name=...)` | operator class for pattern matching | PostgreSQL only | ## Rules Django enforces - **A name is required** whenever you use `condition`, `include`, `opclasses` or expressions; constructing the `Index` without one raises `ValueError`. Pick a stable, descriptive name: the system checks reject names longer than **30 characters** (`models.E034`) and names starting with a number or underscore (`models.E033`). - `fields` and `expressions` are alternatives: an expression index lists its expressions positionally. - Django does not validate that a function is immutable. A functional or partial index that uses, say, a date function or `Concat` passes `makemigrations` and then fails when PostgreSQL runs the migration. ## Choosing, with the subscription scenario A billing system runs a nightly renewal job: `Subscription.objects.filter(status="active", renews_at__lte=cutoff)`. Most rows are cancelled history. - A plain `db_index=True` on `renews_at` indexes every row, including history the job never reads. - A **partial index** on `renews_at` with `condition=Q(status="active")` holds only active rows, so it is smaller and matches the job's filter exactly. The query's filter must include `status="active"` for the database to use it. - If support staff look up subscriptions by a coupon code typed in any case, a **functional index** on `Lower("coupon_code")` serves queries that filter on that same expression, such as `alias(code=Lower("coupon_code")).filter(code=typed.lower())`. The database matches the expression, so a lookup that compiles to a different one (on PostgreSQL `__iexact` uses `UPPER`) will not use it. - A **covering index** helps only when a hot query reads a couple of columns and filters on others; on PostgreSQL it can avoid touching the table. Also remember that a `UniqueConstraint` already creates a unique index: the one-active-subscription constraint on `user` for active rows can serve lookups of a user's active subscription, so a second index on the same columns and condition is waste. ## Naming and abstract models Index names live in the database, so they must be unique across the whole schema, not just the model. Conventions such as `<model>_<columns>_idx` or `<model>_<purpose>_idx` keep them readable and under the 30-character limit. On an abstract base class a fixed name would repeat in every subclass, so Django lets you write `name="%(app_label)s_%(class)s_renewal_idx"`; the placeholders become the concrete model's app label and lowercased class name. Plain field indexes may omit the name and let Django generate one, but a named index is easier to find in a query plan and in later migrations. ## Costs and cautions - Every index slows writes and takes disk space; add them for measured queries, not speculatively. - On a backend that ignores `condition` or `include`, the index Django creates is not the one you designed. Check which database production runs before relying on either. - Adding an index to a large, busy table is its own operational problem; the plain `AddIndex` operation builds it in the normal, locking way. The interview signal is knowing that `Meta.indexes` is Django's full index vocabulary, which options need a name, and which ones a given backend quietly drops.

  • Your Django project runs on MySQL and declares Index(fields=["renews_at"], condition=Q(status="active"), name=...). What does the database get?
    MySQL and MariaDB do not support conditional indexes, so a system check warns that conditions will be ignored and Django creates an ordinary index on `renews_at` covering every row. The index still serves the query but is larger than designed. Unlike a conditional unique constraint, which is not created at all there, no rule is lost, only the size saving.
  • Why might a partial index declared through Django's Index(condition=...) fail when the migration runs on PostgreSQL?
    PostgreSQL requires functions in an index condition to be immutable. Django does not validate that, so a condition using, for example, a date function or a comparison that casts with the session time zone passes `makemigrations` and then errors when PostgreSQL executes `CREATE INDEX`.

saying these in an interview costs you the question

  • A ForeignKey needs db_index=True added to get an index.
  • Index names are optional even when using condition or expressions.
  • include= creates covering indexes on every database backend.
  • Meta.index_together is the current way to declare multi-column indexes.
  • More indexes always make a Django model faster.