Optimizing Django ORM Queries

Practical patterns for eliminating N+1 queries, reducing p90 latency, and knowing when to split an API endpoint.

Introduction

I spent a significant chunk of my first 1.5 years as a backend engineer optimizing slow API endpoints in a Django application. The product is an ATS (Applicant Tracking System) where recruiters view candidate profiles, stages, events, and evaluations — all loaded via REST APIs.

Our monitoring showed several endpoints with p90 latencies exceeding 2 seconds. After investigating, the root causes were almost always the same patterns:

N+1 queries — Fetching related objects inside a loop
Redundant queries — Fetching the same data multiple times
Bloated endpoints — One API doing too much, blocking page load
Missing aggregation — Using Python to compute what the database can do

This post covers the patterns I applied repeatedly, with real (anonymized) examples.

The Problem

# Fetching candidate opportunities
opportunities = Opportunity.objects.filter(candidate_id=can_id)

for opp in opportunities:
    job_title = opp.job.title              # Query per iteration
    employer_name = opp.job.employer.name   # Another query per iteration
    stage_name = opp.action_stage.name     # Yet another query

For a candidate with 10 opportunities, this fires 30+ queries instead of 1.

The Fix

candidate_opps = list(
    Opportunity.objects.filter(candidate_id=can_id)
    .select_related('job', 'job__employer', 'action_stage', 'candidate')
    .distinct()
)

# Now all access is from memory — zero additional queries
for opp in candidate_opps:
    job_title = opp.job.title
    employer_name = opp.job.employer.name
    stage_name = opp.action_stage.name

select_related works by doing a SQL JOIN. For our case, the single query with JOINs was ~5ms vs the original ~150ms from 30 round-trips.

When to Use

One-to-one or many-to-one (ForeignKey) relationships
You know you’ll access the related object for most/all items
The related table isn’t enormous (JOINs on billion-row tables have their own issues)

When NOT to Use

Many-to-many or reverse foreign keys — use prefetch_related instead
You only need the related object’s ID (it’s already on the FK field: opp.job_id)

Pattern 2: Pre-fetching to Eliminate N+1 in Business Logic

The Problem

Sometimes the N+1 isn’t in the ORM layer — it’s in business logic:

for opp in candidate_opps:
    job_id = opp.job_id

    # This hits the cache or DB for each job
    if job_id not in recruiter.subscribed_job_ids:
        owned_stages = recruiter.get_owned_stages_for_job(job_id)

    # This queries events table for each opportunity
    upcoming_event = get_upcoming_event(opp.id, recruiter.id)

The Fix: Pre-fetch Outside the Loop

# Pre-fetch owned stages for ALL jobs in one pass
job_ids = list(set(opp.job_id for opp in candidate_opps))
subscribed_job_ids = set(recruiter.subscribed_job_ids)

owned_stages_map = {}
for job_id in job_ids:
    if job_id not in subscribed_job_ids:
        owned_stages_map[job_id] = \
            recruiter.get_owned_stages_position_for_job(job_id)

# Pre-fetch ALL upcoming events for this recruiter in one query
opp_ids = set(opp.id for opp in candidate_opps)
all_upcoming_events = list(
    Event.objects.filter(
        opportunity_id__in=opp_ids,
        participants=recruiter,
        start_time__gt=timezone.now()
    ).select_related('opportunity')
)

# Build a lookup dict
events_by_opp = defaultdict(list)
for event in all_upcoming_events:
    events_by_opp[event.opportunity_id].append(event)

# Now the loop is O(1) lookups
for opp in candidate_opps:
    owned_stages = owned_stages_map.get(opp.job_id, [])
    upcoming = events_by_opp.get(opp.id, [])

This transformed a 15-query loop into 2 queries + dictionary lookups.

Pattern 3: Aggregation Instead of Multiple Queries

The Problem

We needed to check if a candidate was shared via email OR via excel sheet:

# Original: Two separate queries
shared_via_email = ProfileShare.objects.filter(
    candidates=can_id, job_id=job_id,
    source=ProfileShare.SHARE_ACTION,
    to_emails__contains=recruiter_email
).exists()

shared_via_excel = ProfileShare.objects.filter(
    candidates=can_id, job_id=job_id,
    source=ProfileShare.EXCEL_SHEET,
    job__employer=employer
).exists()

Two database round-trips for something the DB can answer in one.

The Fix: Single Query with Case/When Aggregation

from django.db.models import Case, When, Value, IntegerField, Max

shares = ProfileShare.objects.filter(
    job__is_active=True,
    created_at__gte=last_30_days,
    candidates=can_id,
    job_id=job_id
)

result = shares.aggregate(
    has_email_share=Max(
        Case(
            When(shared_mail_query, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    ),
    has_excel_share=Max(
        Case(
            When(shared_via_excel_query, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    )
)

shared_with_email = bool(result.get('has_email_share'))
shared_via_excel = bool(result.get('has_excel_share'))
exists = shared_with_email or shared_via_excel

One query. The database evaluates both conditions in a single table scan.

When to Use

You need to check multiple conditions on the same queryset
You need counts/existence checks across categories
The alternative is multiple .filter().exists() or .filter().count() calls

Pattern 4: Reusing Already-Fetched Objects

The Problem

opp = Opportunity.objects.get(id=opp_id)

# Later in the same view...
job = Job.objects.get(id=opp.job_id)  # Unnecessary — we could have JOINed

The Fix

opp = Opportunity.objects.select_related(
    'candidate',
    'candidate__job_search_preferences',
    'candidate__user',
    'job', 'job__employer', 'action_stage',
).get(id=opp_id)

# Reuse the already-loaded relation
job = opp.job  # No query — loaded via select_related

This sounds obvious, but in a large codebase with many contributors, it’s common for code added later to re-fetch objects that are already available. A comment helps:

# Reuse opp.job (loaded via select_related) when available,
# fall back to separate query otherwise.
if opp is not None:
    job = opp.job
else:
    job = Job.objects.get(id=job_id)

Pattern 5: Splitting Heavy Endpoints

The Problem

Our candidate profile page loaded everything in one API call:

Candidate info
Job stages
Unread messages count
Starred messages
Upcoming events

The unread/starred messages query was slow (scanning a large messages table with complex filters), and it blocked the entire page from rendering.

The Fix: Separate Endpoint

# Before: Everything in SingleOpportunityResource.get_detail()
def get_detail(self, request):
    data = self.get_candidate_info()
    data['stages'] = self.get_stages()
    data['unread_count'] = self.get_unread_messages()  # SLOW
    data['starred'] = self.get_starred_messages()      # SLOW
    data['events'] = self.get_upcoming_events()
    return data

# After: Messages moved to their own endpoint
# GET /api/v1/candidate/messages_status/?candidate_id=123
class MessagesStatusResource(Resource):
    def get_detail(self, request):
        return {
            'unread_count': self.get_unread_messages(),
            'starred': self.get_starred_messages(),
        }

The frontend now fires both requests in parallel:

// Load in parallel — page renders as soon as the fast one returns
$q.all([
    singleOpportunityService.getCandidateProfile(candidateId),
    messagesService.getMessagesStatus(candidateId)
]).then(function([profile, messages]) {
    $scope.profile = profile;
    $scope.unreadCount = messages.unread_count;
});

When to Split

One sub-query is significantly slower than the rest
The slow data isn’t needed for initial render
The frontend can progressively load the data
The slow query operates on a different table/index

Pattern 6: Fixing LEFT OUTER JOINs

The Problem

A query to check if a candidate had an interview scheduled:

# This generates a LEFT OUTER JOIN on the events table
interviews = Opportunity.objects.filter(
    candidate_id=can_id
).filter(
    events__event_type='interview',
    events__status='scheduled'
)

LEFT OUTER JOINs are expensive when the right table (events) is large and doesn’t have the right composite index.

The Fix: Subquery or Exists

from django.db.models import Exists, OuterRef

# Subquery approach — generates EXISTS instead of JOIN
has_interview = Event.objects.filter(
    opportunity=OuterRef('pk'),
    event_type='interview',
    status='scheduled'
)

interviews = Opportunity.objects.filter(
    candidate_id=can_id
).annotate(
    has_scheduled_interview=Exists(has_interview)
).filter(
    has_scheduled_interview=True
)

EXISTS stops scanning the events table at the first match. A JOIN loads all matching rows.

Measuring Impact

Before and after each optimization, I checked:

Query count — Django Debug Toolbar or connection.queries
p50/p90/p99 — From our APM tool (Datadog)
Database time — Sum of all query durations in the request

A typical result:

Metric	Before	After
Queries per request	35	8
p50 latency	450ms	120ms
p90 latency	2100ms	350ms
DB time	380ms	85ms

Debugging Tools

Django Debug Toolbar — Shows all queries, duplicates highlighted
connection.queries — Programmatic access to query log in development
EXPLAIN ANALYZE — Run the raw SQL in MySQL/PostgreSQL to see the execution plan
APM (Datadog/New Relic) — Production p90/p99 percentiles
Silk — Django middleware that profiles requests and stores results

Quick snippet to log queries in development:

from django.db import connection, reset_queries
import functools

def query_debugger(func):
    @functools.wraps(func)
    def wrapper(*args, **kwargs):
        reset_queries()
        result = func(*args, **kwargs)
        queries = connection.queries
        print(f"Function: {func.__name__}")
        print(f"Number of Queries: {len(queries)}")
        print(f"Total Time: {sum(float(q['time']) for q in queries):.3f}s")
        return result
    return wrapper

Summary of Patterns

Pattern	When to Use	Typical Savings
`select_related`	FK/OneToOne access in loops	5-50x fewer queries
Pre-fetch outside loop	Business logic N+1	3-20x fewer queries
Aggregate with Case/When	Multiple exists/count checks	2-5x fewer queries
Reuse loaded objects	Same relation accessed twice	1 fewer query per occurrence
Split endpoint	One slow sub-query blocks render	50-80% faster perceived load
EXISTS instead of JOIN	Checking existence, not fetching data	2-10x on large tables

The key mindset shift: think in sets, not loops. Every time you write a for loop that accesses a related object, ask: “Can I fetch all of these in one query before the loop?”

Introduction

Pattern 1: select_related for Foreign Keys

The Problem

The Fix

When to Use

When NOT to Use

Pattern 2: Pre-fetching to Eliminate N+1 in Business Logic

The Problem

The Fix: Pre-fetch Outside the Loop

Pattern 3: Aggregation Instead of Multiple Queries

The Problem

The Fix: Single Query with Case/When Aggregation

When to Use

Pattern 4: Reusing Already-Fetched Objects

The Problem

The Fix

Pattern 5: Splitting Heavy Endpoints

The Problem

The Fix: Separate Endpoint

When to Split

Pattern 6: Fixing LEFT OUTER JOINs

The Problem

The Fix: Subquery or Exists

Measuring Impact

Debugging Tools

Summary of Patterns