Killing N+1 Queries with select_related and prefetch_related

An endpoint looks fine in development with ten rows of seed data. In production with ten thousand rows it takes two seconds. Most of the time the cause is the same: an N+1 query, where the ORM quietly runs one extra query per row.

What an N+1 looks like

This loop runs one query for the orders, then one more every single time the loop touches a related vendor.

orders = Order.objects.all()
for order in orders:
    print(order.vendor.name)  # a fresh query on every iteration

One thousand orders becomes one thousand and one queries. The code reads innocently, which is exactly why this is so easy to ship.

select_related for forward relations

Use select_related for ForeignKey and OneToOne fields. It pulls the related rows in the same query using a SQL join.

orders = Order.objects.select_related("vendor")
for order in orders:
    print(order.vendor.name)  # no extra queries

prefetch_related for reverse and many-to-many

A join cannot fan out across a one-to-many or many-to-many relation without duplicating rows, so prefetch_related runs a second query and stitches the results together in Python.

vendors = Vendor.objects.prefetch_related("orders")
for vendor in vendors:
    print(vendor.name, vendor.orders.count())  # two queries total

Catch it before production

The reliable way to keep N+1 out is to assert the query count in a test. If a future change adds a query, the test fails before the slow endpoint ever ships.

def test_order_list_query_count(client, django_assert_num_queries):
    OrderFactory.create_batch(20)
    with django_assert_num_queries(3):
        client.get("/api/v1/orders/")

Prefetch with a filtered queryset

When you only need some of the related rows, a Prefetch object lets you filter the second query instead of loading everything.

from django.db.models import Prefetch

vendors = Vendor.objects.prefetch_related(
    Prefetch(
        "orders",
        queryset=Order.objects.filter(status="dispatched"),
        to_attr="dispatched_orders",
    )
)

Trim the row with only and defer

Once the query count is right, only() fetches just the columns you use and defer() skips heavy ones you do not. On wide tables that is a real cut to the bytes moved per request.

The habit that matters

Measure, do not guess. Turn on a query inspector while you build the endpoint, watch the count, and treat any number that grows with the data as a bug to fix now rather than a mystery to debug later.