Index Django JSONField Queries With GIN on PostgreSQL

Problem statement

You store flexible attributes in a JSONField (product attributes, event payloads, integration metadata), and queries filtering on it, such as attributes__color="red" or payload__contains={"type": "refund"}, have become slow as the table grew. PostgreSQL's jsonb supports several index types, but each serves different operators. An index that does not match how the ORM queries the field is ignored, and the planner falls back to scanning every row.

1. For containment queries (field__contains={...}), add GinIndex(fields=["attributes"], opclasses=["jsonb_path_ops"]).
2. For key existence (has_key, has_keys, has_any_keys), use a GIN index with the default jsonb_ops.
3. For equality or range on one key (attributes__color="red", payload__amount__gt=100), add an expression B-tree index on that key.
4. Check with explain() that queries use the index; rewrite key lookups as contains if you want the GIN index to serve them.
5. Create GIN indexes concurrently on large tables; they take longer to build than B-tree indexes.

Step-by-step solution

1. Identify the query patterns

List the JSON lookups in your code and their frequencies. Typical Django patterns and the SQL they produce:

Product.objects.filter(attributes__contains={"color": "red"})   # attributes @> '{"color": "red"}'
Product.objects.filter(attributes__has_key="discount")           # attributes ? 'discount'
Product.objects.filter(attributes__color="red")                  # (attributes -> 'color') = '"red"'
Event.objects.filter(payload__amount__gt=100)                    # (payload -> 'amount') > '100'

2. GIN for containment and key existence

from django.contrib.postgres.indexes import GinIndex

class Product(models.Model):
    attributes = models.JSONField(default=dict)

    class Meta:
        indexes = [
            GinIndex(fields=["attributes"], name="product_attrs_path_gin", opclasses=["jsonb_path_ops"]),
        ]

jsonb_path_ops indexes are smaller and faster for @> (containment) but do not support key-existence operators. If you need has_key too, use the default operator class (jsonb_ops) by omitting opclasses.

3. Expression indexes for single keys

For frequent filters on one key, a B-tree expression index is small and fast:

from django.db.models.fields.json import KeyTextTransform
from django.db.models.functions import Cast

class Meta:
    indexes = [
        models.Index(KeyTextTransform("color", "attributes"), name="product_attr_color"),
        models.Index(Cast(KeyTextTransform("amount", "payload"), models.DecimalField(max_digits=12, decimal_places=2)),
                     name="event_payload_amount"),
    ]

Query with the same expression so the planner can match it:

from django.db.models.fields.json import KeyTextTransform
Product.objects.annotate(color=KeyTextTransform("color", "attributes")).filter(color="red")

If a key is filtered constantly, consider promoting it to a real column; regular columns are simpler to index and validate.

4. Make queries use the index

Check the plan:

print(Product.objects.filter(attributes__contains={"color": "red"}).explain(analyze=True))

You want Bitmap Index Scan on product_attrs_path_gin. If you see Seq Scan, the operator does not match the index, the table is small enough that a scan is cheaper, or statistics are stale.

5. Build GIN indexes safely

GIN indexes on large tables take a while to build and slow down writes slightly. Use AddIndexConcurrently in a non-atomic migration, and consider raising maintenance_work_mem for the session that builds it. GIN's fastupdate buffers inserts; very write-heavy tables may need tuning of gin_pending_list_limit.

Explanation

GIN (Generalized Inverted Index) indexes map each key and value inside the JSON documents to the rows containing them, which suits containment and existence questions across arbitrary keys. The jsonb_path_ops class indexes hashed paths to values, giving smaller indexes for @> only. B-tree indexes, by contrast, index the result of one expression per row, ideal for equality and ranges on a single key. Django generates different SQL operators for different lookups, so the index has to match the lookup you actually use.

Edge cases / notes

• Nested keys. attributes__dimensions__width becomes #> path access; expression indexes need the same path.
• NULL vs JSON null. attributes__color=None matches JSON null, while attributes__color__isnull=True matches a missing key; indexes behave accordingly.
• MySQL. MySQL JSON columns need generated columns with indexes; GIN does not exist there.

Next steps

• Place JSON indexes in a broader plan with A Practical Database Indexing Strategy for Django.
• Confirm plans with Use QuerySet.explain() and EXPLAIN ANALYZE in Django.
• Build without blocking writes using Add Django Database Indexes Concurrently.

FAQ

Which index speeds up JSONField __contains?

A GIN index, ideally with the jsonb_path_ops operator class.

Why does my GIN index not help attributes__color="red"?

That lookup uses the -> operator with equality, which GIN does not support. Use an expression B-tree index or rewrite it as contains.

jsonb_ops or jsonb_path_ops?

jsonb_path_ops for containment only (smaller, faster); jsonb_ops when you also need key existence lookups.

Should frequently filtered JSON keys become columns?

Often, yes. Real columns are easier to index, validate, and query.