Django's select_related and prefetch_related are the most powerful tools for eliminating N+1 query problems and optimizing database access. This chapter provides comprehensive coverage of these optimization techniques, from basic usage to advanced patterns that can reduce query counts from hundreds to just a few.
Select Related: Uses SQL JOINs to fetch related objects in a single query
Prefetch Related: Uses separate queries and Python joins
# Example models for demonstration
class Author(models.Model):
name = models.CharField(max_length=100)
email = models.CharField(max_length=100)
bio = models.TextField()
class Category(models.Model):
name = models.CharField(max_length=50)
description = models.TextField()
class Tag(models.Model):
name = models.CharField(max_length=30)
class Article(models.Model):
title = models.CharField(max_length=200)
content = models.TextField()
author = models.ForeignKey(Author, on_delete=models.CASCADE)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
tags = models.ManyToManyField(Tag)
created_at = models.DateTimeField(auto_now_add=True)
class Comment(models.Model):
article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='comments')
author = models.ForeignKey(Author, on_delete=models.CASCADE)
content = models.TextField()
created_at = models.DateTimeField(auto_now_add=True)
# WITHOUT select_related: N+1 queries
def bad_article_list():
articles = Article.objects.all()[:10] # 1 query
for article in articles:
print(article.author.name) # 10 additional queries
print(article.category.name) # 10 additional queries
# Total: 21 queries
# WITH select_related: 1 query
def good_article_list():
articles = Article.objects.select_related('author', 'category').all()[:10] # 1 query
for article in articles:
print(article.author.name) # No additional query
print(article.category.name) # No additional query
# Total: 1 query
# Following relationships through multiple levels
articles = Article.objects.select_related(
'author',
'author__profile', # If Author has a profile
'category',
'category__parent' # If Category has a parent
).all()
# Generated SQL includes multiple JOINs:
# SELECT article.*, author.*, profile.*, category.*, parent_category.*
# FROM articles_article article
# INNER JOIN authors_author author ON article.author_id = author.id
# LEFT OUTER JOIN profiles_profile profile ON author.profile_id = profile.id
# INNER JOIN categories_category category ON article.category_id = category.id
# LEFT OUTER JOIN categories_category parent_category ON category.parent_id = parent_category.id
def get_articles_with_conditional_select_related(include_author=True, include_category=True):
"""Conditionally apply select_related based on needs"""
queryset = Article.objects.all()
select_related_fields = []
if include_author:
select_related_fields.extend(['author', 'author__profile'])
if include_category:
select_related_fields.append('category')
if select_related_fields:
queryset = queryset.select_related(*select_related_fields)
return queryset
# Usage
articles_with_author = get_articles_with_conditional_select_related(
include_author=True,
include_category=False
)
# Select related works with filtering
recent_articles = Article.objects.select_related(
'author', 'category'
).filter(
created_at__gte=timezone.now() - timedelta(days=30),
author__is_active=True,
category__is_published=True
).order_by('-created_at')
# The JOIN allows filtering on related fields efficiently
published_articles = Article.objects.select_related('author').filter(
author__is_staff=False,
is_published=True
)
# WITHOUT prefetch_related: N+1 queries
def bad_article_with_tags():
articles = Article.objects.all()[:10] # 1 query
for article in articles:
tags = list(article.tags.all()) # 10 additional queries
comments = list(article.comments.all()) # 10 additional queries
# Total: 21 queries
# WITH prefetch_related: 3 queries
def good_article_with_tags():
articles = Article.objects.prefetch_related('tags', 'comments').all()[:10]
# Query 1: Articles
# Query 2: All tags for these articles
# Query 3: All comments for these articles
for article in articles:
tags = list(article.tags.all()) # No additional query
comments = list(article.comments.all()) # No additional query
# Total: 3 queries
from django.db.models import Prefetch
# Prefetch with custom filtering and ordering
articles = Article.objects.prefetch_related(
Prefetch(
'comments',
queryset=Comment.objects.filter(is_approved=True).select_related('author'),
to_attr='approved_comments'
),
Prefetch(
'tags',
queryset=Tag.objects.filter(is_active=True).order_by('name'),
to_attr='active_tags'
)
).all()
# Access the prefetched data
for article in articles:
# Use the custom attribute names
for comment in article.approved_comments:
print(f"Comment by {comment.author.name}: {comment.content}")
for tag in article.active_tags:
print(f"Tag: {tag.name}")
# Prefetch related objects of related objects
articles = Article.objects.prefetch_related(
'comments__author', # Comments and their authors
'comments__author__profile', # Author profiles
'tags', # Article tags
'author__articles' # Other articles by the same author
).all()
# Complex nested prefetching
users = User.objects.prefetch_related(
'articles__tags', # User's articles and their tags
'articles__comments__author', # Comments on user's articles and comment authors
'profile__social_links' # User's profile and social links
).all()
from django.db.models import Count, Avg
# Prefetch with annotations
articles = Article.objects.prefetch_related(
Prefetch(
'comments',
queryset=Comment.objects.annotate(
reply_count=Count('replies')
).select_related('author')
)
).annotate(
comment_count=Count('comments'),
avg_rating=Avg('ratings__score')
).all()
# Access annotated data
for article in articles:
print(f"Article: {article.title}")
print(f"Total comments: {article.comment_count}")
print(f"Average rating: {article.avg_rating}")
for comment in article.comments.all():
print(f"Comment with {comment.reply_count} replies")
# Combine both for maximum efficiency
def optimized_article_view():
articles = Article.objects.select_related(
'author', # ForeignKey - use select_related
'category', # ForeignKey - use select_related
'author__profile' # OneToOne through ForeignKey - use select_related
).prefetch_related(
'tags', # ManyToMany - use prefetch_related
'comments__author', # Reverse FK with nested - use prefetch_related
Prefetch(
'comments',
queryset=Comment.objects.filter(is_approved=True).select_related('author'),
to_attr='approved_comments'
)
).all()
return articles
# This generates only 3-4 queries total regardless of result size:
# 1. Articles with author, category, and profile JOINs
# 2. Tags for all articles
# 3. Approved comments with authors for all articles
def get_blog_data_optimized():
"""Get comprehensive blog data with minimal queries"""
# Get articles with all related data
articles = Article.objects.select_related(
'author',
'author__profile',
'category'
).prefetch_related(
'tags',
Prefetch(
'comments',
queryset=Comment.objects.filter(
is_approved=True
).select_related('author').order_by('-created_at'),
to_attr='approved_comments'
)
).filter(
is_published=True
).order_by('-created_at')[:20]
# Get popular tags (separate query)
popular_tags = Tag.objects.annotate(
article_count=Count('article')
).filter(
article_count__gt=0
).order_by('-article_count')[:10]
# Get recent comments (separate query)
recent_comments = Comment.objects.select_related(
'author', 'article'
).filter(
is_approved=True
).order_by('-created_at')[:5]
return {
'articles': articles,
'popular_tags': popular_tags,
'recent_comments': recent_comments
}
# Total queries: 5 (articles, tags, comments, popular_tags, recent_comments)
# Without optimization: 1 + 20*3 + 20*N + 10 + 5 = 76+ queries
from django.db import connection
from django.test.utils import override_settings
@override_settings(DEBUG=True)
def analyze_query_performance():
"""Analyze query performance with detailed logging"""
# Clear previous queries
connection.queries_log.clear()
# Execute your optimized query
articles = Article.objects.select_related(
'author', 'category'
).prefetch_related(
'tags', 'comments__author'
).all()[:10]
# Force evaluation
for article in articles:
print(f"Article: {article.title}")
print(f"Author: {article.author.name}")
print(f"Category: {article.category.name}")
print(f"Tags: {[tag.name for tag in article.tags.all()]}")
print(f"Comments: {len(article.comments.all())}")
print("---")
# Analyze queries
print(f"\nTotal queries executed: {len(connection.queries)}")
for i, query in enumerate(connection.queries, 1):
print(f"\nQuery {i}:")
print(f"Time: {query['time']}s")
print(f"SQL: {query['sql'][:200]}...")
# Custom decorator for query counting
def count_queries(func):
"""Decorator to count queries executed by a function"""
def wrapper(*args, **kwargs):
initial_count = len(connection.queries)
result = func(*args, **kwargs)
final_count = len(connection.queries)
print(f"{func.__name__} executed {final_count - initial_count} queries")
return result
return wrapper
@count_queries
def test_optimized_view():
articles = Article.objects.select_related('author').prefetch_related('tags').all()[:5]
return list(articles)
import time
from django.test import TestCase
class SelectRelatedPerformanceTest(TestCase):
def setUp(self):
# Create test data
authors = [Author.objects.create(name=f"Author {i}") for i in range(10)]
categories = [Category.objects.create(name=f"Category {i}") for i in range(5)]
tags = [Tag.objects.create(name=f"Tag {i}") for i in range(20)]
for i in range(100):
article = Article.objects.create(
title=f"Article {i}",
content=f"Content for article {i}",
author=authors[i % 10],
category=categories[i % 5]
)
article.tags.set(tags[i % 5:(i % 5) + 3]) # 3 tags per article
def test_without_optimization(self):
"""Test performance without select_related/prefetch_related"""
start_time = time.time()
articles = Article.objects.all()[:20]
data = []
for article in articles:
data.append({
'title': article.title,
'author': article.author.name,
'category': article.category.name,
'tags': [tag.name for tag in article.tags.all()]
})
end_time = time.time()
unoptimized_time = end_time - start_time
unoptimized_queries = len(connection.queries)
print(f"Unoptimized: {unoptimized_time:.4f}s, {unoptimized_queries} queries")
return unoptimized_time, unoptimized_queries
def test_with_optimization(self):
"""Test performance with select_related/prefetch_related"""
start_time = time.time()
articles = Article.objects.select_related(
'author', 'category'
).prefetch_related('tags').all()[:20]
data = []
for article in articles:
data.append({
'title': article.title,
'author': article.author.name,
'category': article.category.name,
'tags': [tag.name for tag in article.tags.all()]
})
end_time = time.time()
optimized_time = end_time - start_time
optimized_queries = len(connection.queries)
print(f"Optimized: {optimized_time:.4f}s, {optimized_queries} queries")
return optimized_time, optimized_queries
def test_performance_comparison(self):
"""Compare optimized vs unoptimized performance"""
# Reset queries
connection.queries_log.clear()
unopt_time, unopt_queries = self.test_without_optimization()
connection.queries_log.clear()
opt_time, opt_queries = self.test_with_optimization()
# Calculate improvements
time_improvement = (unopt_time - opt_time) / unopt_time * 100
query_improvement = (unopt_queries - opt_queries) / unopt_queries * 100
print(f"\nPerformance Improvement:")
print(f"Time: {time_improvement:.1f}% faster")
print(f"Queries: {query_improvement:.1f}% fewer queries")
# Assert significant improvement
self.assertGreater(time_improvement, 50, "Should be at least 50% faster")
self.assertGreater(query_improvement, 80, "Should use at least 80% fewer queries")
class ArticleQueryOptimizer:
"""Dynamic query optimization based on context"""
def __init__(self):
self.base_queryset = Article.objects.all()
def for_list_view(self, include_comments=False):
"""Optimize for article list view"""
queryset = self.base_queryset.select_related(
'author', 'category'
).prefetch_related('tags')
if include_comments:
queryset = queryset.prefetch_related(
Prefetch(
'comments',
queryset=Comment.objects.select_related('author')[:5],
to_attr='recent_comments'
)
)
return queryset
def for_detail_view(self):
"""Optimize for article detail view"""
return self.base_queryset.select_related(
'author', 'author__profile', 'category'
).prefetch_related(
'tags',
Prefetch(
'comments',
queryset=Comment.objects.filter(
is_approved=True
).select_related('author').order_by('-created_at'),
to_attr='approved_comments'
)
)
def for_api_serialization(self):
"""Optimize for API serialization"""
return self.base_queryset.select_related(
'author', 'category'
).prefetch_related(
'tags',
'comments__author'
).only(
'id', 'title', 'slug', 'content', 'created_at',
'author__id', 'author__name',
'category__id', 'category__name'
)
# Usage
optimizer = ArticleQueryOptimizer()
# In list view
articles = optimizer.for_list_view(include_comments=True).filter(
is_published=True
)[:20]
# In detail view
article = optimizer.for_detail_view().get(slug=slug)
# In API view
articles = optimizer.for_api_serialization().filter(
is_published=True
)
def get_articles_with_complex_prefetch():
"""Complex prefetch patterns for advanced use cases"""
from django.db.models import Q, Count, Exists, OuterRef
# Prefetch only popular tags (used by multiple articles)
popular_tags_subquery = Tag.objects.annotate(
usage_count=Count('article')
).filter(usage_count__gte=5)
# Prefetch only recent approved comments
recent_comments_subquery = Comment.objects.filter(
is_approved=True,
created_at__gte=timezone.now() - timedelta(days=30)
).select_related('author')
# Prefetch related articles by same author (excluding current)
related_articles_subquery = Article.objects.filter(
is_published=True
).exclude(
id=OuterRef('id')
).select_related('category')[:5]
articles = Article.objects.select_related(
'author', 'category'
).prefetch_related(
Prefetch('tags', queryset=popular_tags_subquery, to_attr='popular_tags'),
Prefetch('comments', queryset=recent_comments_subquery, to_attr='recent_comments'),
Prefetch('author__articles', queryset=related_articles_subquery, to_attr='related_articles')
).filter(is_published=True)
return articles
from django.core.cache import cache
def get_cached_optimized_articles(cache_timeout=3600):
"""Cache the results of optimized queries"""
cache_key = 'optimized_articles_list'
articles = cache.get(cache_key)
if articles is None:
articles = list(
Article.objects.select_related(
'author', 'category'
).prefetch_related(
'tags',
Prefetch(
'comments',
queryset=Comment.objects.filter(is_approved=True)[:5],
to_attr='top_comments'
)
).filter(is_published=True)[:50]
)
cache.set(cache_key, articles, cache_timeout)
return articles
# Cache invalidation
from django.db.models.signals import post_save, post_delete
from django.dispatch import receiver
@receiver([post_save, post_delete], sender=Article)
def invalidate_article_cache(sender, instance, **kwargs):
cache.delete('optimized_articles_list')
@receiver([post_save, post_delete], sender=Comment)
def invalidate_comment_cache(sender, instance, **kwargs):
cache.delete('optimized_articles_list')
# ✅ DO: Use select_related for ForeignKey and OneToOne
articles = Article.objects.select_related('author', 'category')
# ❌ DON'T: Use select_related for ManyToMany or reverse ForeignKey
# This will raise an error
# articles = Article.objects.select_related('tags') # Wrong!
# ✅ DO: Use prefetch_related for ManyToMany and reverse ForeignKey
articles = Article.objects.prefetch_related('tags', 'comments')
# ✅ DO: Combine both when appropriate
articles = Article.objects.select_related('author').prefetch_related('tags')
# ❌ DON'T: Chain multiple select_related calls
# articles = Article.objects.select_related('author').select_related('category') # Inefficient
# ✅ DO: Use single select_related call with multiple fields
articles = Article.objects.select_related('author', 'category')
# ✅ DO: Use only() to limit fields when you don't need all data
articles = Article.objects.select_related('author').only(
'title', 'slug', 'author__name'
)
# ❌ DON'T: Access non-prefetched related objects in loops
articles = Article.objects.prefetch_related('tags')
for article in articles:
# This will cause additional queries!
comments = article.comments.all()
# ✅ DO: Prefetch all needed relationships
articles = Article.objects.prefetch_related('tags', 'comments')
for article in articles:
tags = article.tags.all() # No additional query
comments = article.comments.all() # No additional query
This comprehensive guide to select_related and prefetch_related provides the foundation for eliminating N+1 queries and building high-performance Django applications with optimized database access patterns.
Template Rendering Optimization
Template rendering can be a significant performance bottleneck in Django applications. This chapter covers comprehensive template optimization techniques, from reducing template complexity to implementing advanced caching strategies that dramatically improve rendering performance.
Caching Strategies
Caching is one of the most effective performance optimization techniques in Django applications. By storing frequently accessed data in fast storage systems, you can dramatically reduce database load, improve response times, and enhance user experience. This comprehensive guide covers multi-level caching strategies, cache backends, invalidation patterns, and advanced caching techniques.