What this usually means
The N+1 query problem occurs when your Django ORM code fetches a parent object and then, for each parent, executes a separate query to load related children. This is often caused by lazily evaluated ForeignKey, ManyToMany, or reverse relations in templates or serializers. The classic pattern is iterating over a queryset and accessing a related field inside the loop, triggering a new SQL query for each iteration. With 100 authors, that's 1 query for the authors list + 100 queries for their books = 101 queries instead of 2. The fix involves using select_related() for ForeignKey and OneToOne fields (SQL JOINs) or prefetch_related() for ManyToMany and reverse ForeignKey (separate queries batched with WHERE IN).
The first ten minutes — establish facts before touching code.
- 1Install django-debug-toolbar: add to INSTALLED_APPS, MIDDLEWARE, and configure INTERNAL_IPS.
- 2Open the slow page in your browser and look at the SQL panel: count the number of queries.
- 3Sort queries by 'Similar' or 'Duplicates' in the toolbar; identical queries appearing >10 times indicate N+1.
- 4Check the stack trace for each duplicate query to find the exact template line or Python code causing it.
- 5Run EXPLAIN on the repeated query in your database to see if it's a simple SELECT with WHERE id = ?.
- 6If you can't use the toolbar, add logging: from django.db import connection; print(connection.queries) in the view.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchtemplates/ — look for for loops accessing related fields (e.g., book.author.name)
- searchserializers.py (DRF) — check SerializerMethodField or nested serializers without prefetch
- searchviews.py — examine querysets passed to templates or serializers, especially inside loops
- searchadmin.py — list_display or list_select_related may be missing
- searchdjango-debug-toolbar SQL panel — the 'Queries' tab with duplicate detection
- searchDatabase logs (postgresql.conf log_min_duration_statement = 0) — capture all queries during a request
Practical causes, not theory. These are the things you will actually find.
- warningUsing .all() on a related manager inside a template loop (e.g., for book in author.books.all)
- warningMissing select_related on ForeignKey fields that are accessed in the same view
- warningMissing prefetch_related on ManyToMany or reverse ForeignKey relations
- warningDRF serializers that nest related serializers without prefetch_related in the view's queryset
- warningCustom properties or methods on the model that access related data (e.g., @property def book_count(self): return self.books.count())
- warningUsing .values() or .values_list() which bypass select_related and can cause extra queries
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd select_related('fk_field1', 'fk_field2') to the queryset for ForeignKey and OneToOne fields that are accessed.
- buildAdd prefetch_related('related_name') for ManyToMany and reverse ForeignKey fields; use Prefetch objects for filtering.
- buildIn templates, use {% with %} to cache related data or move the logic to the view with prefetch.
- buildFor DRF, set queryset = Model.objects.select_related(...).prefetch_related(...) in the view or use @action decorators.
- buildUse django-optimizer or nplusone package to detect N+1 during development automatically.
- buildReplace property methods that hit the database with annotated fields (e.g., .annotate(book_count=Count('books')))
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter applying select_related/prefetch_related, reload the page and check django-debug-toolbar: count should drop from hundreds to single digits.
- verifiedVerify the generated SQL using connection.queries or by running .query on the queryset: you should see JOINs or a single extra query with WHERE IN.
- verifiedMeasure page load time before and after using browser DevTools network tab or Django's time middleware.
- verifiedRun the view with django.test.Client and assert that the number of queries is below a threshold using assertNumQueries().
- verifiedMonitor production database CPU and connection count after deploy; they should drop significantly.
Things that make this bug worse or harder to find.
- warningApplying select_related to ManyToMany fields — it only works for ForeignKey and OneToOne, use prefetch_related instead.
- warningOver-fetching: using select_related on all relations blindly can cause huge JOINs and slow queries. Only include fields you actually use.
- warningForgetting that prefetch_related creates an extra query per relation, so 3 prefetches = 4 total queries (1 main + 3 batch). That's normal and good.
- warningUsing prefetch_related with .only() or .defer() — it can cause additional queries if the deferred fields are accessed later.
- warningNot testing with realistic data volume: N+1 may not show in dev with 5 rows but kills in prod with 10k rows.
- warningPutting select_related inside a loop — call it once on the initial queryset, not per object.
The Author List Page That Took 12 Seconds
Timeline
- 09:15Alert: PagerDuty notifies that /api/authors/ endpoint p99 latency jumped from 200ms to 12s.
- 09:17Check RDS CloudWatch: DB Connections at 500, CPU at 95%.
- 09:20SSH into a web server, run strace on a gunicorn worker: see repeated SELECT ... FROM books WHERE author_id = ?.
- 09:22Pull latest deploy diff: commit added a nested serializer for author.books in DRF view.
- 09:25Check the view: AuthorViewSet uses queryset = Author.objects.all() — no prefetch_related.
- 09:28Hotfix: add .prefetch_related('books') to the queryset and redeploy.
- 09:32Deploy complete, p99 drops to 250ms. DB CPU back to 20%.
- 09:35Add assertNumQueries test to prevent regression.
I was on-call when PagerDuty lit up at 9:15 AM. The /api/authors/ endpoint had been serving 200ms p99 for months, and suddenly it was 12 seconds. My first thought was a database lock or a bad migration. I checked RDS CloudWatch and saw connections maxed out at 500 and CPU pegged at 95%. Not a lock — something was generating massive load.
I SSH'd into a web server and used strace to see what the gunicorn workers were doing. They were all executing the same pattern: SELECT * FROM books WHERE author_id = ?, over and over. Classic N+1. I checked the latest deploy and saw the team had added a nested BookSerializer inside AuthorSerializer to include books per author. The view queryset was Author.objects.all() — no prefetch_related. With 5000 authors, that's 1 + 5000 queries.
I pushed a hotfix adding .prefetch_related('books') to the queryset and redeployed. Within minutes, p99 dropped to 250ms and DB CPU went to 20%. The real lesson: always review serializer changes for missing prefetch. I also added a unit test with assertNumQueries to catch it next time. We now run a CI check that flags views with high query counts.
Root cause
DRF nested serializer for Author.books was added without prefetch_related in the view's queryset, causing 5001 SQL queries per request.
The fix
Added .prefetch_related('books') to the AuthorViewSet queryset, reducing queries to 2 (one for authors, one batch for books).
The lesson
Always add select_related or prefetch_related when using nested serializers or accessing related fields in templates. Use assertNumQueries in tests to enforce query limits.
In production, you can't always install debug toolbar. Use database logs: set log_min_duration_statement = 0 temporarily in PostgreSQL and grep for repeated identical queries. For example, tail -f /var/log/postgresql/postgresql-13-main.log | grep 'SELECT.*FROM.*books' | sort | uniq -c. If you see the same query thousands of times, that's N+1.
Another approach: monitor pg_stat_statements. Run SELECT queryid, calls, rows, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 10. If a query like 'SELECT ... FROM books WHERE author_id = $1' has millions of calls after a deploy, you've found it. In MySQL, use the slow query log or performance_schema.
Django QuerySets are lazy — they don't hit the database until evaluated (iteration, slicing, len(), list(), etc.). When you access a related field like author.books, Django returns a RelatedManager that is also lazy. If you then iterate over it in a template ({% for book in author.books.all %}), each iteration triggers a separate SQL query because the manager doesn't know the full set of authors. This is by design for simplicity, but it's the root of N+1.
select_related works by performing a SQL JOIN in the initial query, fetching all related objects in one round trip. It only works for ForeignKey and OneToOne because they are single-object relations. prefetch_related does a separate query for each relation but uses a WHERE IN clause to fetch all related objects for all parent objects at once, then caches them. The caching is key: after prefetch_related, accessing author.books for any author in the queryset uses the cache, not the database.
Django's TestCase provides assertNumQueries(context_manager, num) to assert the exact number of queries executed. Write tests for your views: with self.assertNumQueries(2): response = self.client.get('/api/authors/'). If a developer adds a nested serializer without prefetch, the test fails. Set a threshold slightly above the expected count to allow for session or auth queries.
For more advanced detection, use the nplusone library. It hooks into Django's query execution and raises an error if it detects an N+1 pattern. Add it to INSTALLED_APPS and set NPLUSONE_RAISE = True in dev. This catches the problem during development before it hits production.
In one production case, a view with 2000 authors and 3 related models (books, profile, settings) without prefetch caused 1 + 3*2000 = 6001 queries. Each query took ~2ms, so total query time was 12 seconds. After adding prefetch_related('books', 'profile', 'settings'), the query count dropped to 1 + 3 = 4 queries, taking ~20ms total. The page load time went from 15s to 300ms. Database CPU dropped from 90% to 15%.
The cost is not just time — each query uses a database connection. With 100 concurrent requests, N+1 can exhaust the connection pool, causing connection timeouts and cascading failures. This is why N+1 is a common cause of production outages. Always profile under realistic load.
Frequently asked questions
What's the difference between select_related and prefetch_related?
select_related uses a SQL JOIN to fetch related objects in the same query. It works only for ForeignKey and OneToOne fields. prefetch_related does a separate query for each relation but uses a WHERE IN clause to fetch all related objects at once, then caches them. It works for ManyToMany and reverse ForeignKey. Use select_related when you have a small number of related objects; use prefetch_related when the relation can have many objects per parent.
How can I see the actual SQL queries in Django?
In development, use django-debug-toolbar. In code, you can print connection.queries from django.db. For a specific queryset, print(queryset.query). To log all queries, add LOGGING config for 'django.db.backends' to your settings. In production, enable database query logging (e.g., log_min_duration_statement in PostgreSQL) and parse the logs.
Does prefetch_related always reduce the number of queries?
Yes, compared to N+1, but it adds one extra query per relation instead of per row. For 1000 authors with 10 books each, N+1 would be 1001 queries; prefetch_related gives 2 queries (1 for authors, 1 batch for books). However, if you prefetch a relation that is not used, you waste a query. Always prefetch only what you need.
Can N+1 happen in Django admin?
Yes. If you use list_display fields that are foreign keys or related fields, Django will fetch each related object per row. Fix by setting list_select_related in ModelAdmin: list_select_related = ('author', 'category'). For ManyToMany, use list_prefetch_related. Also, avoid custom methods that hit the database in list_display.
How do I handle N+1 in Django REST Framework?
The most common cause is a nested serializer. Ensure your view's queryset includes select_related or prefetch_related before the serializer accesses related fields. For example: class AuthorViewSet(ModelViewSet): queryset = Author.objects.prefetch_related('books'). Also, consider using SerializerMethodField sparingly. Use the nplusone package to catch issues during development.