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 SQL INNER/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 batched WHERE order_id IN (...) query instead of N individual 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.


About the Author — Hire Aldian Fazrihady (Cloud Engineer, GCP Professional Cloud Architect, GCP Professional Data Engineer, Backend, AI & Full Stack Engineer)

Aldian Fazrihady is a Top-Rated Senior Cloud Engineer, GCP Professional Cloud Architect (Google Cloud Certified), GCP Professional Data Engineer (Google Cloud Certified), Backend Engineer (Python, Django, FastAPI, Go, PostgreSQL), AI Application Engineer (LLMs, RAG, MCP, Google ADK, LangGraph), and Full Stack Engineer (React, Next.js, TypeScript) with 15+ years of production software engineering experience.

Hire Aldian Fazrihady on Upwork | Book 1-Day Programming Service ($100 USD / Rp 1.000.000) | View Live AI Portfolio | Send a Direct Message

Related Engineering Articles

Engineering Role Pillar Guides & Category Hubs