Django 6.1 added fetch modes. With FETCH_PEERS, when you touch a foreign key on one object from a queryset, Django loads that relation for every object the queryset returned, in one query. The classic N+1 loop becomes 2 queries without you writing select_related.
My football quiz site, ExtraTime, has 621 select_related calls and 103 prefetch_related calls in its Django API, outside the tests. I wanted to know how much of that work the new mode would have done for me.
How I measured it
The API has 6,635 tests, and a lot of them call the real endpoints through Django's test client. I installed Django 6.1.3 in a copy of the project, added a middleware that counts the SQL queries behind each request, and ran the whole suite six times:
- the code as written
- with every
select_relatedcall turned into a no-op - the same, with
FETCH_PEERSon everywhere - with both
select_relatedandprefetch_relatedturned off - the same, with
FETCH_PEERSon - the code as written, with
FETCH_PEERSadded on top
Turning a method into a no-op was a small patch on QuerySet. One exception: a Prefetch with to_attr stayed, because the code reads that attribute and would break without it. There's no setting for a global fetch mode. You set it per queryset with .fetch_mode() or on a model's manager. For the experiment I changed the internal default that every new queryset starts with, which isn't a public API.
To compare like with like, I kept only the routes the tests called the same number of times in every run: 467 routes and 3,963 requests.
The totals
- As written: 59,198 queries
- Without
select_related: 76,144 - Without
select_related, withFETCH_PEERS: 64,416
So taking out my select_related calls added 16,946 queries, and FETCH_PEERS won back 11,728 of them, about 69%. That's a lot for something you switch on rather than write.
With both kinds of hand tuning removed, it was 74,955 queries, and 62,887 with FETCH_PEERS.
Route by route, it never fully caught up
The total hides how it gets there. select_related made a difference on 179 routes. On none of them did FETCH_PEERS get back to the hand-tuned count. It did better on 5, partly closed the gap on 59, and made no difference at all on 115.
The biggest win was the endpoint that starts a Read the Room run: 2,245 queries over 153 requests as written, 8,018 without select_related, and 2,725 with FETCH_PEERS. That's the case it's made for, a list of objects that all touch the same relations. It still lands about 3 queries per request above the hand-tuned version, because each relation costs one extra query instead of being part of a join.
The 115 routes where it did nothing come down to two things, which I checked separately on Django's own Permission model, 16 rows:
- One object has no peers. Loading a single permission and reading its content type takes 2 queries with or without
FETCH_PEERS, and 1 withselect_related. A lot of my endpoints load one game session and then walk its foreign keys.FETCH_PEERScan't help there. -
.iterator()switches it off in practice. Looping over the list and reading each content type takes 17 queries normally and 2 withFETCH_PEERS. The same loop over.iterator()takes 17 either way. The peers are the objects already loaded, and an iterator hasn't loaded the others yet when the first one asks.
The second one showed up in a place I didn't expect. The admin's "add group" page went from 14 queries to 1,198 without select_related, and FETCH_PEERS didn't change it at all. Form choice fields iterate over their queryset with .iterator().
The prefetch that cost more than it saved
I expected that removing prefetch_related could only add queries. On 34 routes it removed them, 2,762 in total, almost all on the multiplayer game rooms.
Inviting a player to a room took 668 queries over 45 requests as written, and 162 without the prefetches. The SQL showed why. The room viewset's queryset prefetches the config table of every game type, 8 of them, so that the lobby list, which mixes game types, doesn't query per room. But the same queryset is used to look up a single room for actions like invite, leave and ready. Each of those requests paid 8 queries for config tables it never read.
It's easy to miss in the code, because the prefetch lives in a shared helper. It only stood out once each request's queries were counted.
Turning it on over code that's already tuned
With FETCH_PEERS on top of the code as written, the suite had exactly the same failures as without it, and the query count dropped by 412, under 1%. 22 routes got cheaper and 6 got slightly more expensive, by between 1 and 5 queries over all their requests. I didn't track down why those 6 went up.
The tests noticed
Without select_related, 76 tests failed against 7 as written. With FETCH_PEERS, 50 did. Many of the extra failures weren't wrong answers. They were tests that pin a query count, and this codebase has a lot of them: 92 uses of assertNumQueries and 286 of CaptureQueriesContext. That's what makes hand tuning hold up over time. Without those tests, a select_related quietly stops covering what the view reads after the next change.
Two things about upgrading to 6.1 itself
Two things came up that have nothing to do with fetch modes. django-celery-beat still declares it doesn't support Django 6.1, so I had to force the install. And five tests broke for real. Querysets combined with .union() now apply the model's default ordering, and three places in the code union values_list() queries on models with Meta.ordering. Postgres rejects the result with "ORDER BY term does not match any column in the result set". An .order_by() on each part fixes it.
What I got wrong
I started on my Mac with the test runner's --parallel. Forked workers crashed twice, once inside macOS's proxy lookup and once inside the Postgres client library, and a third run died when a failing test's result couldn't be sent back to the main process. I moved the runs into a Linux container, the way the project's CI does it, and they went through in about four minutes each.
I also thought "turning it on" would be a setting. It isn't, and patching an internal default is fine for an experiment but not something I'd ship.
And I lost raw data. After the main runs, I reran the game room tests in two of the modes to capture their SQL, and the script cleared each mode's output folder first. The totals and per-route numbers above were already computed and saved by then, but the raw per-request logs for those two runs are gone.
Caveats
Test data is small. Most tests create a handful of rows, so an N+1 costs a few queries here and far more in production. That cuts both ways. FETCH_PEERS keeps a loop at 2 queries however many rows there are, so in production it would win back more of the gap than 69%. But it still can't help a single object or an .iterator().
The patch also turned off select_related inside third-party packages like the admin, not only in my code. Only requests through the test client were counted, so Celery tasks and websockets aren't in these numbers. And this is one codebase.
What I'd take from it
-
FETCH_PEERSon top of tuned code is cheap and broke nothing here. It's a reasonable safety net for the loops nobody has looked at yet. - It doesn't replace
select_relatedfor views that load one object and follow its foreign keys. Keep those. - If a loop uses
.iterator(),FETCH_PEERSwon't help it. - Count queries per request in your tests. That's what found 8 wasted queries on every room action, which no fetch mode would have fixed.
