Database Query Optimizer
Analyzes and optimizes Django ORM queries and database performance.
Project Context
-
Database: PostgreSQL (in Docker)
-
ORM: Django ORM
-
Key models: Project, User, Tool, Technology, Company
-
Search: Weaviate (vector DB) for semantic search
When to Use
-
"API response is slow"
-
"N+1 query problem"
-
"Database query optimization"
-
"Missing index"
-
"Migration failing"
-
"Slow queryset"
Common Performance Issues
N+1 Query Problem
Symptom: Many small queries instead of one efficient query
BAD - N+1 queries (1 + N queries)
projects = Project.objects.all() for project in projects: print(project.user.username) # Each access = 1 query!
GOOD - select_related for ForeignKey/OneToOne
projects = Project.objects.select_related('user').all()
GOOD - prefetch_related for ManyToMany/reverse FK
projects = Project.objects.prefetch_related('tools', 'technologies').all()
Missing select_related/prefetch_related
Check serializers for nested data:
If serializer includes nested user data:
class ProjectSerializer(serializers.ModelSerializer): user = UserSerializer() # Needs select_related!
View must optimize:
class ProjectViewSet(viewsets.ModelViewSet): def get_queryset(self): return Project.objects.select_related('user')
Unindexed Filters
If filtering frequently on a field, add index:
class Project(models.Model): slug = models.SlugField(db_index=True) # Has index is_private = models.BooleanField(db_index=True) # Add index!
Debugging Queries
- Django Debug Toolbar
Already configured - check bottom of page in development.
- Query Logging
settings.py - enable query logging
LOGGING = { 'loggers': { 'django.db.backends': { 'level': 'DEBUG', } } }
- Shell Analysis
Django shell
from django.db import connection, reset_queries from django.conf import settings settings.DEBUG = True
reset_queries()
Run your query
list(Project.objects.all()) print(f"Queries: {len(connection.queries)}") for q in connection.queries: print(q['sql'][:100])
- Explain Query
See query execution plan
print(Project.objects.filter(is_private=False).explain())
Key Optimizations
select_related (ForeignKey, OneToOne)
Single JOIN query
Project.objects.select_related('user', 'user__profile')
prefetch_related (ManyToMany, Reverse FK)
Separate query, joined in Python
Project.objects.prefetch_related('tools', 'likes')
only() / defer() - Load Specific Fields
Only load needed fields
Project.objects.only('id', 'title', 'slug')
Exclude heavy fields
Project.objects.defer('description', 'content')
values() / values_list() - Skip Model Instantiation
Returns dicts, not model instances
Project.objects.values('id', 'title')
Returns tuples
Project.objects.values_list('id', 'title')
Aggregation at DB Level
from django.db.models import Count, Avg
Do counting in database, not Python
Project.objects.annotate(like_count=Count('likes'))
Bulk Operations
BAD - N queries
for project in projects: project.views += 1 project.save()
GOOD - 1 query
Project.objects.filter(id__in=ids).update(views=F('views') + 1)
Bulk create
Project.objects.bulk_create([Project(...), Project(...)])
Migration Issues
Check Migration Status
docker compose exec web python manage.py showmigrations
Create Missing Migrations
docker compose exec web python manage.py makemigrations
Fake Problematic Migration
docker compose exec web python manage.py migrate --fake app_name 0001
Squash Migrations
docker compose exec web python manage.py squashmigrations app_name 0001 0010
Key Files to Check
core/ ├── projects/ │ ├── models.py # Project model, indexes │ ├── views.py # QuerySet optimization │ └── serializers.py # Nested serializers need optimization ├── users/ │ └── models.py # User model └── tools/ └── models.py # Tool model
config/ └── settings.py # DATABASE config, DEBUG
PostgreSQL Commands
Connect to database
docker compose exec db psql -U postgres -d allthriveai
List tables
\dt
Describe table
\d core_project
Show indexes
\di
Analyze slow query
EXPLAIN ANALYZE SELECT * FROM core_project WHERE is_private = false;
Show running queries
SELECT pid, query, state FROM pg_stat_activity WHERE state != 'idle';
Adding Indexes
In model
class Project(models.Model): class Meta: indexes = [ models.Index(fields=['is_private', 'created_at']), models.Index(fields=['user', 'is_private']), ]
Or on field
is_private = models.BooleanField(default=False, db_index=True)
Then run:
docker compose exec web python manage.py makemigrations docker compose exec web python manage.py migrate