Database Optimization

# Database Optimization Techniques

# 1. Query Optimization
from django.db.models import Prefetch, Count, Sum, Avg, Min, Max
from django.db import connection

# Use select_related for ForeignKey relationships
posts = Post.objects.select_related('author', 'category').all()

# Use prefetch_related for ManyToMany and reverse ForeignKey
posts = Post.objects.prefetch_related(
    'tags',
    Prefetch('comments', queryset=Comment.objects.select_related('author'))
).all()

# Use only() to limit fields
posts = Post.objects.only('title', 'created_date', 'author__username').all()

# Use defer() to exclude heavy fields
posts = Post.objects.defer('content', 'author__bio').all()

# Use values() for dictionary results
post_data = Post.objects.values('title', 'created_date', 'author__username').all()

# Use values_list() for tuple results
post_titles = Post.objects.values_list('title', flat=True).all()

# 2. Advanced Query Optimization
class PostQuerySet(models.QuerySet):
    def with_related_data(self):
        """Optimize queries with all related data"""
        return self.select_related('author', 'category').prefetch_related(
            'tags',
            Prefetch('comments', queryset=Comment.objects.select_related('author')),
            Prefetch('likes', queryset=Like.objects.select_related('user'))
        )
    
    def published(self):
        """Filter only published posts"""
        return self.filter(status='published')
    
    def by_category(self, category_id):
        """Filter by category with optimization"""
        return self.filter(category_id=category_id).select_related('category')
    
    def with_stats(self):
        """Add computed statistics"""
        return self.annotate(
            comment_count=Count('comments'),
            like_count=Count('likes'),
            view_count=Count('views')
        )

# Use the custom QuerySet
class Post(models.Model):
    # ... fields ...
    
    objects = PostQuerySet.as_manager()
    
    class Meta:
        indexes = [
            models.Index(fields=['status', 'created_date']),
            models.Index(fields=['author', 'created_date']),
            models.Index(fields=['category', 'published_date']),
            models.Index(fields=['title']),  # For LIKE queries
        ]

# 3. Database Indexing Strategies
class AdvancedPost(models.Model):
    title = models.CharField(max_length=200, db_index=True)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE, db_index=True)
    category = models.ForeignKey(Category, on_delete=models.CASCADE, db_index=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, db_index=True)
    created_date = models.DateTimeField(auto_now_add=True, db_index=True)
    published_date = models.DateTimeField(null=True, blank=True, db_index=True)
    is_featured = models.BooleanField(default=False, db_index=True)
    
    class Meta:
        indexes = [
            # Composite indexes for common query patterns
            models.Index(fields=['status', 'published_date', 'created_date']),
            models.Index(fields=['author', 'status', 'created_date']),
            models.Index(fields=['category', 'status', 'published_date']),
            models.Index(fields=['is_featured', 'published_date']),
            
            # Partial indexes for specific conditions
            models.Index(
                fields=['published_date'],
                condition=models.Q(status='published')
            ),
            
            # functional indexes (PostgreSQL)
            models.Index(
                fields=['title'],
                name='post_title_gin',
                opclasses=['gin_trgm_ops']  # For trigram search
            ),
        ]

# 4. Query Analysis and Debugging
from django.db import connection
from django.test.utils import CaptureQueriesContext

def analyze_queries(func):
    """Decorator to analyze database queries"""
    def wrapper(*args, **kwargs):
        with CaptureQueriesContext(connection) as context:
            result = func(*args, **kwargs)
        
        print(f"Function {func.__name__} executed {len(context.captured_queries)} queries:")
        for i, query in enumerate(context.captured_queries):
            print(f"Query {i+1}: {query['sql']}")
            print(f"Time: {query['time']}s")
        
        return result
    return wrapper

# Usage
@analyze_queries
def get_posts_with_analysis():
    return Post.objects.select_related('author').prefetch_related('tags').all()

# 5. Database Connection Management
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
        
        # Connection optimization
        'CONN_MAX_AGE': 60,  # Keep connections alive for 60 seconds
        'OPTIONS': {
            'MAX_CONNS': 20,  # Maximum connections
            'MIN_CONNS': 5,   # Minimum connections
        },
        
        # Query optimization
        'ATOMIC_REQUESTS': False,  # Disable for better performance
        'AUTOCOMMIT': True,
    }
}

# 6. Raw SQL Optimization
from django.db import connection

def get_posts_with_raw_sql():
    """Use raw SQL for complex queries"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                p.id,
                p.title,
                p.created_date,
                u.username as author_name,
                c.name as category_name,
                COUNT(cm.id) as comment_count,
                COUNT(l.id) as like_count
            FROM blog_post p
            JOIN auth_user u ON p.author_id = u.id
            JOIN blog_category c ON p.category_id = c.id
            LEFT JOIN blog_comment cm ON p.id = cm.post_id
            LEFT JOIN blog_like l ON p.id = l.post_id
            WHERE p.status = 'published'
            GROUP BY p.id, p.title, p.created_date, u.username, c.name
            ORDER BY p.created_date DESC
        """)
        
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

# 7. Database Partitioning (PostgreSQL)
class PartitionedPost(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    created_date = models.DateField()
    
    class Meta:
        managed = False  # Django won't manage this table
        
        # Create partitioned table
        db_table = 'blog_post_partitioned'

# Migration for partitioning
from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [
        ('blog', '0001_initial'),
    ]
    
    operations = [
        migrations.RunSQL("""
            -- Create partitioned table
            CREATE TABLE blog_post_partitioned (
                id SERIAL,
                title VARCHAR(200),
                content TEXT,
                created_date DATE
            ) PARTITION BY RANGE (created_date);
            
            -- Create partitions for each month
            CREATE TABLE blog_post_2024_01 PARTITION OF blog_post_partitioned
                FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
            
            CREATE TABLE blog_post_2024_02 PARTITION OF blog_post_partitioned
                FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
        """),
    ]

# 8. Query Result Caching
from django.core.cache import cache
from django.db.models.query import QuerySet

class CachedQuerySet(QuerySet):
    def cache_results(self, timeout=300):
        """Cache the results of this queryset"""
        cache_key = self._get_cache_key()
        results = cache.get(cache_key)
        
        if results is None:
            results = list(self)
            cache.set(cache_key, results, timeout)
        
        return results
    
    def _get_cache_key(self):
        """Generate a cache key for this queryset"""
        query_str = str(self.query)
        return f"queryset_{hash(query_str)}"

# Usage
posts = Post.objects.filter(status='published').cache_results(timeout=600)