Detecting N+1 Queries with Traces

The N+1 pattern — one query for a list, then one more for every item in it — is the most common database performance problem in ORM-based Python services, and it is invisible to every per-query tool. Each query is fast; the slow query log is empty; the database's statistics look healthy. Only at the level of a single request does the problem appear, as three hundred near-identical statements where two would do. This page covers detecting it from traces, alerting on it per route, and failing tests when it is reintroduced. It is a task article under database and I/O performance observability, part of the Python profiling and performance observability section.

Prerequisites

pip install "sqlalchemy>=2.0.0,<3.0.0" \
            "opentelemetry-instrumentation-sqlalchemy>=0.48b0,<1.0.0" \
            "prometheus-client>=0.20.0,<1.0.0" \
            "pytest>=8.0.0,<9.0.0"

Implementation

Step 1 — Count statements per request. Repetition is invisible at the level of individual queries and obvious at the level of the request, so the count has to be accumulated per request. A mutable counter stored in a context variable, incremented by the driver's execute hook and read when the request ends, gives it — and a histogram per route shows which endpoints are affected.

import collections
import contextvars
from sqlalchemy import event
from prometheus_client import Histogram

_stmts: contextvars.ContextVar[collections.Counter | None] = \
    contextvars.ContextVar("stmts", default=None)

QPR = Histogram("db_queries_per_request", "Statements per request", ["route"],
                buckets=(1, 2, 5, 10, 20, 50, 100, 200, 500, 1000))


def begin_request():
    _stmts.set(collections.Counter())


@event.listens_for(engine, "before_cursor_execute")
def _count(conn, cursor, statement, parameters, context, executemany):
    counter = _stmts.get()
    if counter is not None:
        counter[statement] += 1        # template, because parameters are separate

Step 2 — Report the most repeated template. Knowing a request issued three hundred queries is useful; knowing that two hundred and ninety-eight of them were the same statement names the loop. Putting both on the server span means every trace carries its own diagnosis.

from opentelemetry import trace

def end_request(route: str):
    counter = _stmts.get()
    if not counter:
        return
    total = sum(counter.values())
    QPR.labels(route).observe(total)
    template, repeats = counter.most_common(1)[0]
    span = trace.get_current_span()
    span.set_attribute("db.query_count", total)
    span.set_attribute("db.most_repeated_count", repeats)
    span.set_attribute("db.most_repeated_statement", template[:200])
    if repeats >= 10:
        span.set_attribute("db.suspected_n_plus_one", True)

Expected Output: a server span that identifies the pattern without anyone opening its children.

{
  "name": "GET /orders",
  "attributes": {
    "db.query_count": 51,
    "db.most_repeated_count": 50,
    "db.most_repeated_statement": "SELECT line_items.id, line_items.sku, … WHERE line_items.order_id = %(param_1)s",
    "db.suspected_n_plus_one": true
  }
}

Step 3 — Alert per route. Different endpoints legitimately issue different numbers of queries, so a single global threshold is either too loose for simple endpoints or too tight for complex ones. A threshold per route, or an alert on a sudden increase relative to the same route's own history, catches the regression that matters: the deploy after which an endpoint went from four queries to four hundred.

# a route's p95 query count jumped relative to its own last week
histogram_quantile(0.95, sum by (route, le) (rate(db_queries_per_request_bucket[30m])))
  >
3 * histogram_quantile(0.95, sum by (route, le) (rate(db_queries_per_request_bucket[30m] offset 7d)))

Step 4 — Fix with eager loading or batching. The fix is to fetch the related rows in one query for all items rather than one per item. In SQLAlchemy that is a loader option on the original query; in hand-written code it is a single query with an IN list followed by grouping in Python.

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = (
    select(Order)
    .where(Order.customer_id == customer_id)
    .options(selectinload(Order.line_items))     # one extra query, not one per order
)
orders = session.scalars(stmt).all()

Step 5 — Fail tests that exceed a query budget. The pattern is reintroduced constantly, usually by a template or serialiser that accesses a relationship nobody thought of as a query. A test that renders the endpoint with a representative number of related records and asserts on the query count catches it in review. The key detail is using enough records that the difference between constant and linear is unmistakable — ten is a good minimum.

# test_query_budget.py
import pytest
from sqlalchemy import event

@pytest.fixture
def query_counter(engine):
    count = {"n": 0}
    def _inc(*args, **kwargs):
        count["n"] += 1
    event.listen(engine, "before_cursor_execute", _inc)
    yield count
    event.remove(engine, "before_cursor_execute", _inc)

def test_order_list_query_count_is_constant(client, make_orders, query_counter):
    make_orders(count=25, items_each=4)
    client.get("/orders")
    # 1. Constant regardless of order count; a linear pattern would be ~26.
    assert query_counter["n"] <= 3, f"issued {query_counter['n']} queries"

Expected Output: a failing test when the pattern is introduced.

FAILED test_query_budget.py::test_order_list_query_count_is_constant
AssertionError: issued 27 queries

Where the pattern hides

N+1 queries are rarely written deliberately. They emerge from code that looks innocent in isolation, and four locations account for most of them.

Templates and serialisers. A template that loops over orders and displays each order's customer name triggers a lazy load per order. The template author may not know the relationship is lazy, and the view author may not know the template accesses it. Neither piece of code looks wrong on its own.

Properties and computed fields. A model property that computes something from a relationship — a total from line items, a status from related records — issues a query every time it is accessed. Accessing it in a list view is a loop of queries hidden behind attribute syntax.

Permission and ownership checks. A per-item check that loads the item's owner or team to decide visibility is a query per item, often added late, often in a decorator or middleware that the list endpoint's author never sees.

Background jobs processing batches. A job iterating over a batch of records and accessing a relationship on each is the same pattern with no user-facing latency to reveal it — only a job that takes an hour when it should take a minute, and a database under constant low-grade load.

The first three are caught by the per-request count and the test budget. The fourth needs the same counting applied per job run, which is the counting shown in telemetry from serverless and batch Python applied to database statements.

Making lazy loading loud during development

The measurements above detect the pattern after it exists. The cheapest prevention stops it being written in the first place, and SQLAlchemy offers a direct mechanism.

Setting the default loader strategy to raise on lazy access means that any code touching an unloaded relationship fails immediately with an error naming the relationship, rather than silently issuing a query. In development and in tests, that turns every potential N+1 into a visible exception at the moment it is written. The developer then chooses deliberately: eager-load the relationship in the query that needs it, or explicitly allow the lazy load where a single access is genuinely intended.

from sqlalchemy.orm import raiseload

# development and test: any unplanned lazy load is an error, not a query
stmt = select(Order).options(raiseload("*"))

This is too strict for production, where an unexpected lazy load is better served slowly than not at all, and it is exactly right for the environments where code is written and reviewed. Combined with the query budget tests from step 5, it closes the loop: new code cannot introduce a lazy load without noticing, and existing endpoints cannot regress without failing a test.

The same principle — make the expensive thing loud where it is cheap to fix — applies well beyond ORMs. Any access pattern whose cost scales with data volume is worth surfacing in development rather than discovering through a customer with unusually large data.

Configuration options

Verification

Confirm the fix by checking that the query count no longer scales with the data.

for n in (10, 100, 1000):
    make_orders(count=n, items_each=4)
    with count_queries() as c:
        client.get("/orders")
    print(f"{n:5d} orders -> {c.n} queries")

Expected Output: a constant count after the fix, where before it was N+1.

   10 orders -> 2 queries
  100 orders -> 2 queries
 1000 orders -> 2 queries

Common mistakes

Looking for it in the slow query log. Error signature: an empty slow query log on a slow endpoint. Root cause: each individual query is fast. Remediation: count queries per request.

Testing with too few records. Error signature: a query budget test that passes with the pattern present. Root cause: with two or three records the linear and constant cases barely differ. Remediation: use at least ten related records.

A global query count threshold. Error signature: an alert that fires constantly for complex endpoints and never for simple ones. Root cause: legitimate query counts vary by route. Remediation: alert per route, relative to its own history.

Fixing one access and missing another. Error signature: a query count that drops but still scales with the data. Root cause: a second relationship accessed in the same loop. Remediation: re-run the scaling check after each fix until the count is constant.

Eager loading everything. Error signature: list endpoints that got slower after a blanket change. Root cause: loading relationships nobody displays. Remediation: eager-load only what the endpoint actually uses.

Frequently Asked Questions