Bài 3.3: Advanced QuerySets - N+1 Problem và Solutions

**Series Navigation:** - [Bài 3.1: QuerySet Fundamentals & Lazy Loading](/django-advance/advanced-querysets-optimization-phan-1) - [Bài 3.2: select_related() vs prefetch_related()](/django-advance/advanced-querysets-optimization-phan-2) - **Bài 3.3 (bài này): N+1 Problem và Solutions** 👈 - [Bài 3.4: Aggregation, Annotation & F/Q Objects](/django-advance/advanced-querysets-optimization-phan-4)

Mục Tiêu Bài 3.3

Sau khi hoàn thành Bài 3.3 này, bạn sẽ:

✅ Hiểu rõ N+1 problem là gì và tại sao nguy hiểm
✅ Detect N+1 problems với các tools
✅ Implement solutions với select_related/prefetch_related
✅ Handle complex N+1 scenarios
✅ Prevent N+1 trong templates và serializers
✅ Build defensive code patterns

What is N+1 Problem?

Definition

N+1 Problem = Executing 1 query to fetch N records, then N additional queries to fetch related data for each record.

Formula:

Total Queries = 1 (initial) + N (one per record) = N+1 queries

Classic Example

Models:

class Author(models.Model):    name = models.CharField(max_length=100) class Book(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, on_delete=models.CASCADE)

Problematic Code:

# Query 1: Fetch all booksbooks = Book.objects.all()  # SELECT * FROM book # Loop through booksfor book in books:    # Query 2, 3, 4, ..., N+1: Fetch author for each book    print(f"{book.title} by {book.author.name}")    # SELECT * FROM author WHERE id = ?

If you have 100 books:

Query 1: Get all books
Query 2-101: Get author for each book (100 queries)
Total: 101 queries 💥

Why It's Dangerous

Performance Impact:

10 records:     11 queries    (~100ms)100 records:    101 queries   (~1s)1,000 records:  1,001 queries (~10s)10,000 records: 10,001 queries (~100s = timeout!)

Real-World Consequences:

❌ Slow page loads
❌ Database overload
❌ Timeouts and errors
❌ Poor user experience
❌ Server crashes under load

Cost Impact:

More queries = more database load
More load = need bigger database
Bigger database = higher costs
Simple fix can save thousands of dollars!

Detecting N+1 Problems

Best Tool for Development

Installation:

pip install django-debug-toolbar

Configuration:

# settings.pyINSTALLED_APPS = [    # ...    'debug_toolbar',] MIDDLEWARE = [    'debug_toolbar.middleware.DebugToolbarMiddleware',    # ...] INTERNAL_IPS = ['127.0.0.1']

Features:

Visual query list in browser
Duplicate query detection
Query execution time
SQL EXPLAIN analysis
Stack trace for each query

Usage:

Load page in browser
Click Debug Toolbar panel
Check "SQL" section
Look for duplicate queries with only ID difference

Example Output:

Queries (153 total in 2.3s)┌─────────────────────────────────────────────┐│ Similar queries (100x)                      ││ SELECT * FROM author WHERE id = ?           ││ Time: 1.8s total                           ││ ⚠️ Possible N+1 Problem                    │└─────────────────────────────────────────────┘

Method 2: nplusone Package

Automatic N+1 Detection

Installation:

pip install nplusone

Configuration:

# settings.pyINSTALLED_APPS = [    'nplusone.ext.django',    # ...] MIDDLEWARE = [    'nplusone.ext.django.NPlusOneMiddleware',    # ...] # Raise exception on N+1 (strict mode)NPLUSONE_RAISE = True # Or just log warningsNPLUSONE_LOGGER = logging.getLogger('nplusone')NPLUSONE_LOG_LEVEL = logging.WARN

What It Does:

Monitors all queries
Detects N+1 patterns automatically
Raises exception or logs warning
Shows stack trace to problematic code

Example Warning:

NPlusOneError: Potential n+1 query detected on `Book.author`  File "/app/views.py", line 15, in book_list    print(book.author.name)

Method 3: Manual Query Counting

In Tests:

from django.test import TestCasefrom django.test.utils import override_settingsfrom django.db import connection class BookViewTest(TestCase):    def test_book_list_query_count(self):        """Ensure book list doesn't have N+1 problem"""        # Create test data        authors = [Author.objects.create(name=f'Author {i}') for i in range(10)]        books = [            Book.objects.create(title=f'Book {i}', author=authors[i % 3])            for i in range(100)        ]                # Count queries        with self.assertNumQueries(2):  # 1 for books + 1 for authors            response = self.client.get('/books/')                        # Force template rendering (triggers lazy queries)            _ = response.content

In Development:

from django.db import connection, reset_queriesfrom django.conf import settings def count_queries(func):    """Decorator to count queries in a function"""    def wrapper(*args, **kwargs):        reset_queries()        result = func(*args, **kwargs)        print(f"Queries: {len(connection.queries)}")        return result    return wrapper @count_queriesdef problematic_view():    books = Book.objects.all()    for book in books:        print(book.author.name)  # N+1 here!

Method 4: Database Query Log Analysis

Enable Query Logging:

# settings.pyLOGGING = {    'version': 1,    'handlers': {        'console': {            'class': 'logging.StreamHandler',        },    },    'loggers': {        'django.db.backends': {            'handlers': ['console'],            'level': 'DEBUG',        },    },}

Look for Patterns:

-- N+1 indicator: Same query with different IDsSELECT * FROM author WHERE id = 1;SELECT * FROM author WHERE id = 2;SELECT * FROM author WHERE id = 3;-- ... repeated many times

N+1 Problem Solutions

Solution 1: select_related() for ForeignKey

Problem:

# N+1 Problembooks = Book.objects.all()  # Query 1for book in books:    print(book.author.name)  # Query 2, 3, 4, ...

Solution:

# Fixed with select_relatedbooks = Book.objects.select_related('author').all()  # Single query with JOIN for book in books:    print(book.author.name)  # No additional queries!

SQL Comparison:

Before (N+1):

-- Query 1SELECT * FROM book; -- Query 2-101SELECT * FROM author WHERE id = 1;SELECT * FROM author WHERE id = 2;-- ... 100 times

After (Optimized):

-- Single query with JOINSELECT book.*, author.*FROM bookINNER JOIN author ON book.author_id = author.id;

Solution 2: prefetch_related() for Reverse Relations

Problem:

# N+1 Problemauthors = Author.objects.all()  # Query 1for author in authors:    books = author.book_set.all()  # Query 2, 3, 4, ...    print(f"{author.name}: {books.count()} books")

Solution:

# Fixed with prefetch_relatedauthors = Author.objects.prefetch_related('book_set').all() for author in authors:    books = author.book_set.all()  # No additional queries!    print(f"{author.name}: {books.count()} books")

SQL Comparison:

Before (N+1):

-- Query 1SELECT * FROM author; -- Query 2-11SELECT * FROM book WHERE author_id = 1;SELECT * FROM book WHERE author_id = 2;-- ... one per author

After (Optimized):

-- Query 1: AuthorsSELECT * FROM author; -- Query 2: All books in one querySELECT * FROM book WHERE author_id IN (1, 2, 3, 4, 5, ...);

Solution 3: Nested Relations

Problem:

# Triple N+1 Problem!publishers = Publisher.objects.all()  # Query 1 for publisher in publishers:  # N+1 on authors    for author in publisher.author_set.all():  # Query 2, 3, ...        for book in author.book_set.all():  # Query N, N+1, ...            print(book.title)

Solution:

# Fixed with nested prefetch_relatedpublishers = Publisher.objects.prefetch_related(    'author_set',              # Publisher → Authors    'author_set__book_set'     # Authors → Books).all() for publisher in publishers:    for author in publisher.author_set.all():  # No queries        for book in author.book_set.all():  # No queries            print(book.title)

Query Count:

Before: 1 + N + (N × M) queries
After: 3 queries total (publishers, authors, books)

Solution 4: ManyToMany Relations

Models:

class Book(models.Model):    title = models.CharField(max_length=200)    authors = models.ManyToManyField('Author', related_name='books')

Problem:

# N+1 Problembooks = Book.objects.all()  # Query 1 for book in books:    author_names = [a.name for a in book.authors.all()]  # Query 2, 3, ...    print(f"{book.title} by {', '.join(author_names)}")

Solution:

# Fixed with prefetch_relatedbooks = Book.objects.prefetch_related('authors').all() for book in books:    author_names = [a.name for a in book.authors.all()]  # No queries    print(f"{book.title} by {', '.join(author_names)}")

Complex N+1 Scenarios

Scenario 1: Mixed Forward and Reverse Relations

Models:

class Category(models.Model):    name = models.CharField(max_length=100) class Author(models.Model):    name = models.CharField(max_length=100) class Book(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author)  # Forward    category = models.ForeignKey(Category)  # Forward class Review(models.Model):    book = models.ForeignKey(Book, related_name='reviews')  # Reverse    rating = models.IntegerField()    text = models.TextField()

Complex Query:

# Display books with author, category, and reviewsbooks = Book.objects.select_related(    'author',      # Forward FK → select_related    'category'     # Forward FK → select_related).prefetch_related(    'reviews'      # Reverse FK → prefetch_related).all() for book in books:    print(f"Title: {book.title}")    print(f"Author: {book.author.name}")  # No query    print(f"Category: {book.category.name}")  # No query    print(f"Reviews: {book.reviews.count()}")  # No query

Queries: 2 total (1 with JOINs + 1 for reviews)

Scenario 2: Conditional Prefetching

Problem: Only want published books.

Naive Approach (Still Has N+1):

authors = Author.objects.prefetch_related('book_set') for author in authors:    # ❌ This filter breaks prefetch cache!    published_books = author.book_set.filter(status='published')    # Triggers N+1 queries again!

Solution: Prefetch Object with Filter:

from django.db.models import Prefetch authors = Author.objects.prefetch_related(    Prefetch(        'book_set',        queryset=Book.objects.filter(status='published'),        to_attr='published_books'    )).all() for author in authors:    # ✅ Uses prefetched, filtered data    for book in author.published_books:        print(book.title)

Scenario 3: Nested Prefetch with Filtering

Models:

class Author(models.Model):    name = models.CharField(max_length=100) class Book(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='books')    status = models.CharField(max_length=20) class Review(models.Model):    book = models.ForeignKey(Book, related_name='reviews')    rating = models.IntegerField()    is_approved = models.BooleanField(default=False)

Task: Get authors with published books and approved reviews only.

Solution:

from django.db.models import Prefetch authors = Author.objects.prefetch_related(    Prefetch(        'books',        queryset=Book.objects.filter(            status='published'        ).prefetch_related(            Prefetch(                'reviews',                queryset=Review.objects.filter(is_approved=True),                to_attr='approved_reviews'            )        ),        to_attr='published_books'    )).all() for author in authors:    for book in author.published_books:  # Only published        for review in book.approved_reviews:  # Only approved            print(f"{review.rating}/5: {review.text}")

Queries: 3 total (authors, filtered books, filtered reviews)

Scenario 4: Generic Relations

Models:

from django.contrib.contenttypes.fields import GenericForeignKey, GenericRelationfrom django.contrib.contenttypes.models import ContentType class Comment(models.Model):    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)    object_id = models.PositiveIntegerField()    content_object = GenericForeignKey('content_type', 'object_id')    text = models.TextField() class Article(models.Model):    title = models.CharField(max_length=200)    comments = GenericRelation(Comment) class Video(models.Model):    title = models.CharField(max_length=200)    comments = GenericRelation(Comment)

Solution:

# Prefetch comments for articlesfrom django.contrib.contenttypes.models import ContentType article_type = ContentType.objects.get_for_model(Article) articles = Article.objects.prefetch_related(    Prefetch(        'comments',        queryset=Comment.objects.filter(content_type=article_type)    )).all()

N+1 in Templates

Template-Induced N+1

Template:

<!-- books.html -->{% for book in books %}    <h2>{{ book.title }}</h2>    <p>Author: {{ book.author.name }}</p>  <!-- N+1 here! -->    <p>Category: {{ book.category.name }}</p>  <!-- N+1 here! -->{% endfor %}

View Without Optimization:

def book_list(request):    books = Book.objects.all()  # N+1 will happen in template!    return render(request, 'books.html', {'books': books})

Fixed View:

def book_list(request):    books = Book.objects.select_related('author', 'category').all()    return render(request, 'books.html', {'books': books})

Template Tags That Trigger Queries

Problematic Patterns:

<!-- Each of these can cause N+1 -->{{ book.author.name }}           <!-- Accesses related object -->{{ book.reviews.count }}         <!-- Counts related objects -->{{ book.reviews.all }}           <!-- Fetches related objects -->{% for review in book.reviews.all %}  <!-- Iteration -->

Solution: Prefetch in View:

def book_detail(request, pk):    book = Book.objects.select_related('author').prefetch_related('reviews').get(pk=pk)    return render(request, 'book_detail.html', {'book': book})

with Template Tag for Computed Values

Optimize Count Queries:

<!-- Bad: Triggers query every time -->Reviews: {{ book.reviews.count }}Ratings: {{ book.reviews.count }}  <!-- Query again! --> <!-- Good: Calculate once -->{% with review_count=book.reviews.count %}    Reviews: {{ review_count }}    Ratings: {{ review_count }}  <!-- No query -->{% endwith %}

N+1 in Django REST Framework

Serializer-Induced N+1

Models:

class Author(models.Model):    name = models.CharField(max_length=100) class Book(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author)

Problematic Serializer:

class BookSerializer(serializers.ModelSerializer):    author_name = serializers.CharField(source='author.name')  # N+1!        class Meta:        model = Book        fields = ['id', 'title', 'author_name']

View:

class BookList(generics.ListAPIView):    queryset = Book.objects.all()  # N+1 will happen!    serializer_class = BookSerializer

Fixed View:

class BookList(generics.ListAPIView):    queryset = Book.objects.select_related('author').all()  # ✅    serializer_class = BookSerializer

Nested Serializers

Nested Serializer (Can Cause N+1):

class ReviewSerializer(serializers.ModelSerializer):    class Meta:        model = Review        fields = ['id', 'rating', 'text'] class BookSerializer(serializers.ModelSerializer):    reviews = ReviewSerializer(many=True)  # N+1 if not prefetched!        class Meta:        model = Book        fields = ['id', 'title', 'reviews']

Fixed View:

class BookList(generics.ListAPIView):    queryset = Book.objects.prefetch_related('reviews').all()  # ✅    serializer_class = BookSerializer

SerializerMethodField with Queries

Problematic:

class BookSerializer(serializers.ModelSerializer):    review_count = serializers.SerializerMethodField()        def get_review_count(self, obj):        return obj.reviews.count()  # N+1 if not prefetched!        class Meta:        model = Book        fields = ['id', 'title', 'review_count']

Solution 1: Prefetch in View:

class BookList(generics.ListAPIView):    queryset = Book.objects.prefetch_related('reviews').all()    serializer_class = BookSerializer

Solution 2: Use Annotation:

from django.db.models import Count class BookList(generics.ListAPIView):    queryset = Book.objects.annotate(review_count=Count('reviews')).all()    serializer_class = BookSerializer class BookSerializer(serializers.ModelSerializer):    review_count = serializers.IntegerField()  # From annotation        class Meta:        model = Book        fields = ['id', 'title', 'review_count']

DRF with select_related/prefetch_related Mixin

Reusable Pattern:

from rest_framework import generics class OptimizedListMixin:    """Mixin to add select_related and prefetch_related to queryset"""    select_related_fields = []    prefetch_related_fields = []        def get_queryset(self):        queryset = super().get_queryset()                if self.select_related_fields:            queryset = queryset.select_related(*self.select_related_fields)                if self.prefetch_related_fields:            queryset = queryset.prefetch_related(*self.prefetch_related_fields)                return queryset class BookList(OptimizedListMixin, generics.ListAPIView):    queryset = Book.objects.all()    serializer_class = BookSerializer    select_related_fields = ['author', 'category']    prefetch_related_fields = ['reviews']

Prevention Strategies

Strategy 1: Always Use select_related/prefetch_related

Code Review Checklist:

# ❌ Dangerous patternfor item in Model.objects.all():    item.foreign_key_field.name  # Potential N+1 # ✅ Safe patternfor item in Model.objects.select_related('foreign_key_field'):    item.foreign_key_field.name

Strategy 2: Enable nplusone in Development

settings.py:

if DEBUG:    NPLUSONE_RAISE = True  # Fail fast in development

Catches N+1 immediately during development!

Strategy 3: Query Count Tests

Write Tests for All Views:

class BookViewTest(TestCase):    def setUp(self):        # Create test data        authors = [Author.objects.create(name=f'Author {i}') for i in range(5)]        for i in range(20):            Book.objects.create(                title=f'Book {i}',                author=authors[i % 5]            )        def test_book_list_query_count(self):        """Book list should use constant queries regardless of data size"""        with self.assertNumQueries(2):  # 1 for books + 1 for authors            response = self.client.get('/api/books/')            self.assertEqual(response.status_code, 200)

Strategy 4: QuerySet Manager Methods

Encapsulate Optimization:

class BookQuerySet(models.QuerySet):    def with_author(self):        return self.select_related('author')        def with_reviews(self):        return self.prefetch_related('reviews')        def optimized(self):        """Fully optimized queryset for common use cases"""        return self.select_related('author', 'category').prefetch_related('reviews') class Book(models.Model):    # ... fields ...    objects = BookQuerySet.as_manager() # Usage - always optimized by defaultbooks = Book.objects.optimized()  # ✅ No N+1 possible

Strategy 5: Monitoring in Production

Track Query Counts:

import timefrom django.db import connection class QueryCountMiddleware:    """Log queries per request in production"""        def __init__(self, get_response):        self.get_response = get_response        def __call__(self, request):        # Reset query log        queries_before = len(connection.queries)                # Process request        start_time = time.time()        response = self.get_response(request)        duration = time.time() - start_time                # Count queries        queries_after = len(connection.queries)        query_count = queries_after - queries_before                # Log if excessive        if query_count > 20:            logger.warning(                f"High query count: {query_count} queries "                f"in {duration:.2f}s for {request.path}"            )                return response

Tóm Tắt Bài 3.3

Trong Bài 3.3 này, bạn đã học:

✅ N+1 Problem:

Definition: 1 + N queries (1 initial, N for related)
Performance impact: 10 records = 11 queries, 10,000 = 10,001 queries!
Real-world consequences: slow pages, timeouts, high costs

✅ Detection Tools:

Django Debug Toolbar (visual, browser-based)
nplusone package (automatic detection)
Manual query counting in tests
Database query log analysis

✅ Solutions:

select_related() for forward ForeignKey/OneToOne
prefetch_related() for reverse FK/ManyToMany
Nested prefetching với __ notation
Prefetch objects for conditional queries

✅ Complex Scenarios:

Mixed forward/reverse relations
Conditional prefetching
Nested prefetch with filtering
Generic relations

✅ N+1 in Templates:

Template-induced N+1 problems
Template tags that trigger queries
Optimization in views, not templates

✅ N+1 in DRF:

Serializer-induced N+1
Nested serializers
SerializerMethodField optimization
Annotation vs prefetch

✅ Prevention Strategies:

Always use select_related/prefetch_related
Enable nplusone in development
Write query count tests
Encapsulate optimization in managers
Monitor production queries

Exercises

Exercise 1: Detect and Fix N+1

Given Code:

def blog_homepage(request):    posts = Post.objects.all()[:10]        context = {        'posts': posts    }    return render(request, 'blog/home.html', context)

Template:

{% for post in posts %}    <article>        <h2>{{ post.title }}</h2>        <p>By {{ post.author.name }}</p>        <p>Category: {{ post.category.name }}</p>        <p>{{ post.comments.count }} comments</p>        <p>{{ post.likes.count }} likes</p>    </article>{% endfor %}

Tasks:

Identify all N+1 problems
Calculate query count for 10 posts
Fix with minimal queries
Write test to ensure fix works

Exercise 2: E-commerce Product Page

Models:

class Brand(models.Model):    name = models.CharField(max_length=100) class Category(models.Model):    name = models.CharField(max_length=100)    parent = models.ForeignKey('self', null=True) class Product(models.Model):    name = models.CharField(max_length=200)    brand = models.ForeignKey(Brand)    category = models.ForeignKey(Category) class Review(models.Model):    product = models.ForeignKey(Product, related_name='reviews')    user = models.ForeignKey(User)    rating = models.IntegerField() class Image(models.Model):    product = models.ForeignKey(Product, related_name='images')    url = models.URLField()

View:

def product_list(request, category_id):    products = Product.objects.filter(category_id=category_id)        # Display:    # - Product name, brand, category (with parent)    # - Average rating    # - Review count    # - First image        return render(request, 'products.html', {'products': products})

Task: Optimize to maximum 5 queries regardless of product count.

Exercise 3: DRF API Optimization

Serializers:

class AuthorSerializer(serializers.ModelSerializer):    book_count = serializers.SerializerMethodField()        def get_book_count(self, obj):        return obj.books.count()        class Meta:        model = Author        fields = ['id', 'name', 'book_count'] class BookSerializer(serializers.ModelSerializer):    author = AuthorSerializer()    review_count = serializers.SerializerMethodField()    average_rating = serializers.SerializerMethodField()        def get_review_count(self, obj):        return obj.reviews.count()        def get_average_rating(self, obj):        from django.db.models import Avg        return obj.reviews.aggregate(Avg('rating'))['rating__avg']        class Meta:        model = Book        fields = ['id', 'title', 'author', 'review_count', 'average_rating'] class BookList(generics.ListAPIView):    queryset = Book.objects.all()    serializer_class = BookSerializer

Tasks:

Calculate current query count for 100 books
Identify all N+1 problems
Optimize queryset in view
Reduce to maximum 3 queries

Hints:

Use annotate() for counts/averages
Use select_related() for author
Consider prefetch_related() vs annotate()

Next Steps

Trong Bài 3.4, bạn sẽ học:

Aggregation với Count, Sum, Avg, Max, Min
Annotation để add computed fields
F() objects cho field references
Q() objects cho complex queries
Conditional expressions (Case, When)
Subqueries và OuterRef

Navigation:

← Bài 3.2: select_related() vs prefetch_related()
Bài 3.3: N+1 Problem và Solutions (current)
Bài 3.4: Aggregation, Annotation & F/Q Objects →

#n+1-problem

Advanced QuerySets và Database Optimization - Phần 3 - N+1 Problem

Database Optimization và N+1 Problem