Backend Engineer Guide: Logging, Profiling & Fixing N+1 Queries in Django ORM
Django's Object-Relational Mapper (ORM) accelerates feature development, but treating it as a black box frequently leads to catastrophic production slowdowns: a single serializer loop can silently emit 400 sequential SELECT queries (the classic N+1 problem), or an unindexed queryset filter can trigger a full sequential table scan. As a Senior Backend Engineer, here is how I configure zero-overhead SQL query logging, request-scoped query profiling via connection.execute_wrapper, and automated CI query-count assertions in Django and PostgreSQL.
1. Enabling Real-Time SQL Logging via django.db.backends
During local development and staging diagnostics, configuring the django.db.backends logger in settings.py prints every generated SQL statement alongside its execution duration in milliseconds:
LOGGING = {
"version": 1,
"disable_existing_loggers": False,
"formatters": {
"sql_formatter": {
"format": "[SQL %(duration).3fs] %(sql)s | params=%(params)s",
},
},
"handlers": {
"console": {
"level": "DEBUG",
"class": "logging.StreamHandler",
"formatter": "sql_formatter",
},
},
"loggers": {
"django.db.backends": {
"handlers": ["console"],
"level": "DEBUG",
"propagate": False,
},
},
}
2. Production-Safe Query Profiling & N+1 Detection with connection.execute_wrapper
Turning on DEBUG = True or global django.db.backends debug logging in production will flood your disks and leak sensitive parameters. Instead, Django provides database execution wrappers (`connection.execute_wrapper`) that intercept SQL execution at the cursor layer with negligible overhead—allowing us to detect duplicate fingerprint queries (N+1 patterns) and log slow queries exceeding a 150ms threshold:
import time
import logging
from collections import Counter
from contextlib import contextmanager
from django.db import connection
logger = logging.getLogger("performance.sql")
@contextmanager
def profile_request_sql(endpoint_name: str, slow_ms_threshold: float = 150.0):
sql_Counts: Counter[str] = Counter()
total_ms = 0.0
def wrapper(execute, sql, params, many, context):
nonlocal total_ms
start = time.perf_counter()
try:
return execute(sql, params, many, context)
finally:
elapsed_ms = (time.perf_counter() - start) * 1000.0
total_ms += elapsed_ms
sql_Counts[sql] += 1
if elapsed_ms >= slow_ms_threshold:
logger.warning(
"Slow SQL on %s (%.1fms): %s", endpoint_name, elapsed_ms, sql[:300]
)
with connection.execute_wrapper(wrapper):
yield
repeated = {q: c for q, c in sql_Counts.items() if c >= 5}
if repeated:
logger.warning(
"Potential N+1 query detected on %s (total %.1fms, %d queries): %d repeated templates",
endpoint_name,
total_ms,
sum(sql_Counts.values()),
len(repeated),
)
3. Eliminating N+1 Queries with select_related, prefetch_related & Prefetch
- Single-Valued Foreign Keys (
OneToOneField/ForeignKey): Always attach.select_related("organization", "author")so Django fetches related objects in a single SQLINNER/LEFT OUTER JOIN. - Multi-Valued Relations (
ManyToManyField/ Reverse Foreign Keys): Use.prefetch_related(Prefetch("items", queryset=OrderItem.objects.only("id", "order_id", "price")))to execute one batchedWHERE order_id IN (...)query instead ofNindividual queries.
4. Hire a Senior Backend Engineer for Django & PostgreSQL Optimization
Dealing with slow Django REST / GraphQL endpoints, database CPU spikes, or unoptimized ORM queries? Explore my Backend Engineer Pillar Guide, review my Cloud SQL PostgreSQL Architecture Guide, or book a 1-Day Database Performance Sprint.