Database Optimization Techniques

Database performance is crucial for web applications. Slow queries can significantly impact user experience. Here are essential optimization techniques for Django applications.

Understanding Query Performance

Use select_related() for Foreign Keys

# Bad: N+1 queries
posts = Post.objects.all()
for post in posts:
    print(post.author.name)  # New query for each post

# Good: Single query
posts = Post.objects.select_related('author').all()
for post in posts:
    print(post.author.name)  # No additional queries

Use prefetch_related() for Many-to-Many

# Bad: Multiple queries
posts = Post.objects.all()
for post in posts:
    print(post.tags.all())  # New query for each post

# Good: Optimized queries
posts = Post.objects.prefetch_related('tags').all()
for post in posts:
    print(post.tags.all())  # Prefetched

Database Indexing

Add indexes to frequently queried fields:

class Post(models.Model):
    title = models.CharField(max_length=200, db_index=True)
    slug = models.SlugField(unique=True, db_index=True)
    created_at = models.DateTimeField(db_index=True)

Query Optimization

Use only() and defer()

# Only fetch needed fields
posts = Post.objects.only('title', 'slug')

# Defer heavy fields
posts = Post.objects.defer('content')

Use exists() for boolean checks

# Bad: Fetches all objects
if Post.objects.filter(status='published'):
    pass

# Good: Just checks existence
if Post.objects.filter(status='published').exists():
    pass

Caching Strategies

View Caching

from django.views.decorators.cache import cache_page

@cache_page(60 * 15)  # Cache for 15 minutes
def my_view(request):
    ...

Query Caching

from django.core.cache import cache

def get_posts():
    posts = cache.get('all_posts')
    if posts is None:
        posts = list(Post.objects.all())
        cache.set('all_posts', posts, 3600)
    return posts

Monitoring and Profiling

Use Django Debug Toolbar in development:

# settings.py
if DEBUG:
    INSTALLED_APPS += ['debug_toolbar']
    MIDDLEWARE += ['debug_toolbar.middleware.DebugToolbarMiddleware']

Best Practices

• Always use select_related() and prefetch_related()
• Add indexes to frequently queried fields
• Use pagination for large datasets
• Monitor slow queries
• Use database connection pooling
• Consider read replicas for read-heavy applications

Optimizing database queries is an ongoing process. Profile your application regularly and identify bottlenecks.