A list endpoint that returns fifty orders issues more than a hundred queries, and rewriting it as an async endpoint made it slower. Explain both.

How fifty orders become a hundred and one queries

Django querysets are lazy, and so are the relations hanging off each instance. The queryset for the page is one query. Then the serialiser touches order.customer.name, and because that customer was not loaded, the ORM issues a SELECT for it — once per order. Touch order.items.count() as well and you have a second query per order. Fifty orders, two lazy relations, one page query: a hundred and one round trips, each one a network hop and a plan lookup, for data that three queries could have fetched.

The reason this is hard to see in review is that the offending line looks like an attribute access, not a database call. So do not reason about it: measure it. django.db.connection.queries with DEBUG on, django-debug-toolbar in development, and assertNumQueries in the test suite all show you the count directly. The last one is the important one, because it turns "we fixed the N+1" into a test that fails when someone reintroduces it.

One is a join, the other is not

select_related performs a SQL join and pulls the related rows back in the same result set. Because a join can only widen a row, it works where each object has at most one related object: a forward ForeignKey, a OneToOneField, and the reverse side of a one-to-one. It costs you nothing extra in round trips and it costs you duplicated parent columns in the result.

prefetch_related does something structurally different. It runs a second query that fetches all the related rows for the whole page in one IN (...), then joins them to the parents in Python. That is why it is the tool for the cardinalities a join cannot express: many-to-many, the reverse side of a foreign key, and generic relations. The query count goes from 1 + N to 1 + one per prefetched relation, regardless of page size.

# Three queries total for the page, whatever the page size:
#   1. the orders          2. the items (one IN query)      3. the products
orders = (
    Order.objects
    .select_related("customer")          # forward FK: joined into query 1
    .prefetch_related("items__product")  # reverse FK then FK: queries 2 and 3
    .order_by("-placed_at")[:50]
)

Two details separate a working fix from a fix that silently does nothing. First, the prefetch cache is keyed on the lookup you asked for, so calling order.items.filter(shipped=False) in the template ignores the cache entirely and issues a fresh query per order — you are back to N+1 with a prefetch_related in the code to reassure you. Use Prefetch("items", queryset=Item.objects.filter(shipped=False), to_attr="unshipped") and read order.unshipped, which is a plain list and cannot re-query. Second, only() and defer() interact badly with careless code: touching a deferred field triggers one query per instance to fetch it, which is an N+1 you created while trying to make the payload smaller.

Worth knowing where the fix stops being a win. prefetch_related loads every related row into memory before serialisation starts, so an order with ten thousand items turns a query problem into a memory problem. For large result sets, iterator() streams rather than caching, and since Django 4.1 it supports prefetch_related when you pass a chunk_size.

Where an async endpoint actually runs

FastAPI decides how to run your handler from how you declared it, and this is the part the rewrite got wrong. A handler declared async def is awaited directly on the event loop, in the same thread that is serving every other connection. A handler declared with a plain def is handed to a bounded worker thread pool by Starlette, so the loop stays free while it blocks.

That inverts the intuition. Marking a handler async def does not make it concurrent; it makes it responsible for not blocking. If the body then calls a synchronous database driver, requests, a password hash, or an image resize, the event loop is stuck inside that call and cannot progress any other request. Requests that used to overlap in the thread pool now execute one after another, which is precisely why the async version measured slower.

import asyncio, time

def blocking_lookup():                  # a sync driver, requests, bcrypt, Pillow...
    time.sleep(0.5)
    return "row"

async def handler_wrong():
    return blocking_lookup()            # holds the loop: nothing else progresses

async def handler_right():
    return await asyncio.to_thread(blocking_lookup)   # offloaded, loop stays free

async def main():
    for handler in (handler_wrong, handler_right):
        start = time.perf_counter()
        await asyncio.gather(*(handler() for _ in range(4)))
        print(handler.__name__, round(time.perf_counter() - start, 2))

asyncio.run(main())                     # wrong ≈ 2.0s serial, right ≈ 0.5s overlapped

So there are three honest fixes, in order of preference. Use a genuinely async library, so the handler awaits rather than blocks. Wrap the blocking call in asyncio.to_thread or Starlette's run_in_threadpool. Or, if the whole handler is synchronous, simply declare it def and let the framework do the offloading for you — that is not giving up, it is using the mechanism that exists for exactly this case. The one thing you must not do is leave a blocking call inside an async def, because unlike a slow query it degrades every other request on the process rather than only its own.

Django's version of the same boundary

Django has an async view layer with the same hazard drawn in a different place. The classic ORM API is synchronous and refuses to run in an async context: touching it from an async def view raises SynchronousOnlyOperation rather than silently blocking, which is a genuinely helpful choice. You either use the async ORM methods added in Django 4.1 — aget, acreate, afirst, async for over a queryset — or wrap the synchronous work with asgiref.sync.sync_to_async, keeping thread_sensitive=True so it runs in the single thread that owns the connection state.

The judgement to show here is that async is not a performance setting. It pays off when a handler spends its time waiting on several independent I/O operations you can overlap, or when you need very high connection counts. For a handler whose cost is one badly written query, going async converts a slow endpoint into a slow service.

Count the queries before you optimise them, and remember that async def is a promise you will not block — the framework will run a plain def handler off the loop for you.