What the Django ORM Really Sends to PostgreSQL: N+1 Queries and JSONB

Most database problems I hit on a Django team had the same cause: the Python looked fine, and the SQL it produced didn't. This post covers two cases, N+1 queries and JSON lookups. Both are solved by the same habit: look at the SQL first.

Part 1: The N+1 problem

An N+1 is one query for a list, then one more per item for something related. Ten rows cost 11 queries, and 10,000 rows cost 10,001. It passes in development and slows down production.

class Author(models.Model):
    name = models.CharField(max_length=255)
 
class Book(models.Model):
    title = models.CharField(max_length=255)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    tags = models.ManyToManyField("Tag", related_name="books")

Relations are lazy. Loading a Book doesn't load its Author until you touch it:

for book in Book.objects.all():          # 1 query
    print(book.author.name)              # +1 query per book

The fixes

select_related follows a forward FK or one-to-one with a SQL JOIN, so everything arrives in one query:

Book.objects.select_related("author")
# SELECT book.*, author.* FROM book INNER JOIN author ON ...

prefetch_related handles many-to-many and reverse FKs. A JOIN there would duplicate rows. Instead, it runs one extra query per relation with WHERE ... IN (...) and stitches the results together in Python:

Book.objects.select_related("author").prefetch_related("tags")  # 2 queries total

Prefetch filters or orders the related rows:

Author.objects.prefetch_related(
    Prefetch("books", queryset=Book.objects.filter(title__icontains="django"),
             to_attr="django_books")
)

The trap: prefetching caches .all() only. Calling book.tags.filter(...) inside a loop runs a new query per book. Prefetch the filtered set instead.

Annotations and subqueries replace per-row counts and existence checks:

Author.objects.annotate(book_count=Count("books"))   # one GROUP BY query
Book.objects.annotate(is_python=Exists(
    Book.tags.through.objects.filter(book_id=OuterRef("pk"), tag__name="python")
))

Reading the through table gets you linked IDs without loading full objects. I used this in a promotion engine, where each discount rule applied to several store channels:

pairs = Book.tags.through.objects.filter(book_id__in=ids).values_list("book_id", "tag_id")

That's one query in place of rule.channels.all() per rule.

Keep it fixed

Lock the query count in a test, so a later change can't bring the N+1 back:

with self.assertNumQueries(2):
    list(Book.objects.select_related("author").prefetch_related("tags"))

N+1s also hide in templates ({{ book.author.name }} in a loop), GraphQL resolvers (usually fixed with DataLoaders), and deferred fields from only() and defer().

Part 2: Querying JSON columns

On the same team, an external system's ID lived in a JSON metadata column, like {"source_id": "CAMP-001"}. Finding records by that ID taught me what Django's JSON lookups really do.

On PostgreSQL, JSONField uses jsonb. It's parsed once on write and is fast to query and index.

The operators

What lookups compile to

filter(metadata__contains={"source_id": "CAMP-001"})  # metadata @> '{...}'
filter(metadata__source_id="CAMP-001")                # (metadata -> 'source_id') = '"CAMP-001"'
filter(metadata__source_id__startswith="CAMP-")       # (metadata ->> 'source_id') LIKE 'CAMP-%'
filter(metadata__has_key="source_id")                 # metadata ? 'source_id'
annotate(source_id=KT("metadata__source_id"))         # metadata ->> 'source_id' (Django 4.2+)

Key equality compares jsonb, so metadata__count=5 and metadata__count="5" are different queries.

The silent typo: metadata__source_id__startwith= (missing an "s") raises no error. On a JSON field, Django treats the unknown word as a nested key, so the query runs and matches nothing. If a JSON query returns nothing unexpectedly, print qs.query before doubting the data.

The right index for each query

An index only serves the operators its class supports.

An expression index only helps when the query uses the same expression. An index on ->> won't serve the default metadata__k= lookup, which uses ->.

Confirm with print(qs.explain(analyze=True)). Look for Index Scan, not Seq Scan, and test on realistic row counts.

If a sync matches records by an ID stored in JSON, enforce it with UniqueConstraint(KT("metadata__source_id"), name=...). That makes re-running the same payload idempotent at the database level. If you filter on a key constantly, though, it probably belongs in a real column.

What I took away

The ORM makes queries easy to write. Reading the SQL makes them easy to understand. My order now: count the queries, print qs.query, run qs.explain(), and only then guess.

Further reading

• Django: Database access optimization
• Django: Querying JSONField
• PostgreSQL: JSON Functions and Operators
• PostgreSQL: GIN Indexes