What is an N+1 query and why does it hide in production?

An N+1 query happens when code loads a list of N parent records with one query and then issues one more query per record to fetch related data. A typical case is a template or serializer that touches order.customer inside a loop. The ORM makes this easy because lazy-loaded associations look like plain attribute access.

Slow-query logs and per-statement dashboards rarely catch it. Each child query is a primary-key or indexed foreign-key lookup that finishes well under any slow-query threshold. The cost comes from three things that statement-level tools don't add up: the number of queries, the network round-trip each one pays, and the ORM work of building an object from every row. Total cost grows linearly with N. A development fixture with five rows produces six fast queries. A production account with thousands of rows produces thousands of fast queries in a single request.

Illustrative estimate (assumed inputs, not a measurement): an endpoint renders 200 rows, and each query is assumed to cost 1.5 ms including round-trip.

Loading approach Queries issued Assumed DB time
Lazy loading (N+1) 1 + 200 = 201 ~301.5 ms
Preloading 2 ~3 ms, plus a larger result set

This tutorial is for backend and platform engineers who own slow endpoints in a monolith. These engineers need to prove an N+1 exists in production, find the line that causes it, and fix it without guessing.

When should you use APM traces to find N+1 queries?

Use trace-based detection when the symptoms point at query volume rather than one expensive statement:

  • An endpoint is slow in production but fast in development. The code is the same, so the difference is usually data size. N+1 cost follows data size directly.
  • Latency depends on the customer or page. If large accounts, long lists or high page sizes are slow and small ones are fine, the work scales with the result set.
  • The database is busy but the slow-query log is empty. Many fast queries can load the database and connection pool without any single query crossing the threshold.
  • A recent change added fields to a list response. A new nested serializer field, template partial or permission check inside a loop is a common way to introduce a lazy load.
  • An APM agent is already deployed. The traces you need are already being collected, so the investigation starts from data you have.

Trace-based detection is the wrong tool in a few cases. If one statement is slow on its own, its query plan is the problem, and you should read the database's EXPLAIN output rather than count spans. If the slow work runs in background jobs or scripts that the agent does not instrument, traces will not show it. In that case, use database statement statistics or temporary ORM query logging on that process. If you have no tracing in production, development-time lazy-load detectors and query-count tests (covered below) are the place to start. They will miss problems that depend on production data shapes, though.

Which slow endpoints should you investigate first?

Start with the APM's endpoint list, not the codebase. Sort transactions by total time consumed, which is throughput multiplied by average latency. Most APM tools expose this directly as "time spent" or "percent of wall-clock time." This ranking matters because an endpoint at 80 ms that serves 2,000 requests per minute costs more server time than a 3-second admin report that runs twice an hour. Illustrative estimate: 2,000 × 0.08 s = 160 s of work per minute, against roughly 0.1 s per minute for the report.

Next, look at p95 and p99 latency instead of the mean. N+1 cost grows with the size of the result set, so most requests look fine and a minority are very slow. The average hides that tail. A wide gap between p50 and p99 on a single endpoint suggests the work depends on the data, not on fixed overhead.

The most useful signal is the database span count per request. If one endpoint shows 6 queries on some traces and 400 on others, query volume is following the input. That pattern is the N+1 signature, and the next section shows how to read it in a waterfall.

This approach works best in mature monoliths where an APM agent is already collecting traces. Reviewing hundreds of controllers and serializers for lazy loads takes days and turns up problems that may never run under real traffic. Ranking by measured impact narrows the search to a few endpoints where a fix will make a visible difference.

How does APM tracing capture database queries?

An APM agent runs inside your application process and instruments the libraries the request passes through. These are usually the web framework, the database driver or ORM, HTTP clients and cache clients. When a request arrives, the agent starts a trace and opens a root span for the endpoint. Each instrumented operation during that request opens a child span. The child span records a start time, a duration and attributes such as the database system, the target table and the SQL statement.

The agent links child spans to the request through trace context, which it keeps in thread-local or async-context storage for the duration of the request. Every database call made on that request's execution path is recorded under the same trace. This is why a loop of lazy loads appears as a run of sibling spans under one parent. It is also why work that moves to another thread, task or job without carrying the context can drop out of the trace.

Before export, most agents normalize the SQL by replacing literal values with placeholders. This removes sensitive values, and it lets the backend group WHERE order_id = 41 and WHERE order_id = 97 as one statement. Some agents can also capture a stack trace or calling code location for database spans. Collecting stack traces costs extra, so agents often limit it to spans above a duration threshold or turn it off by default.

The agent then samples traces and sends them to a collector or backend. Sampling decides which requests are kept in full. The backend builds the waterfall view, per-endpoint aggregates and span counts from the traces it receives. Everything in the sections below rests on these pieces: per-request span counts, normalized statements, timing and call-site data.

How do you recognize the N+1 signature in a trace waterfall?

Open a trace for one of those endpoints and look at the shape of the waterfall before reading any span details. An N+1 has a recognizable form: one query that loads the parent rows, such as SELECT ... FROM orders WHERE customer_id = ?, followed by a long, evenly spaced run of child spans. Each child span runs after the previous one finishes, and each has the same normalized SQL, such as SELECT ... FROM line_items WHERE order_id = ?. Only the bind parameter changes. The run looks like a staircase or a comb. Its length usually matches the number of parent rows, so it grows with the data. That growth is why the endpoint seemed fine in development and degrades in production.

Compare that with healthy patterns. A preloaded association shows up as two spans: the parent query and a single WHERE order_id IN (...) query. A complex page might issue five or six distinct queries, each with a different shape. Neither pattern grows with the result size, and neither is a problem.

Three caveats affect what a trace can show you:

  • Sampling. At low sampling rates, the request that takes the bad path may never be recorded. The trace you open may be a cheap request with only a few rows.
  • Span limits. Many agents cap, truncate, or collapse spans per trace. A run of 400 queries may appear as 100 spans, or as a single aggregated entry with a count. Check the count field rather than trusting the visible length.
  • Detached context. Lazy loads that run in async tasks, background jobs, or code that does not propagate trace context can end up in separate traces or have no parent. The request trace then looks clean even though it caused the queries.
  N+1 SIGNATURE (grows with N)          HEALTHY: PRELOADED (constant)
  +----------------------------------+  +----------------------------------+
  | GET /customers/42/orders         |  | GET /customers/42/orders         |
  +----------------------------------+  +----------------------------------+
  |[====] orders cust_id=?           |  |[====] orders cust_id=?           |
  |      [=] items order_id=101      |  |      [=======] items             |
  |         [=] items order_id=102   |  |        WHERE order_id IN (...)   |
  |            [=] items order_id=103|  |                                  |
  |               ...  same SQL shape|  |  2 spans, independent of N       |
  |                  [=] order_id=N  |  |                                  |
  +----------------------------------+  +----------------------------------+
   time ------------------------------>  time ------------------------------>

  CAVEATS: why a trace may hide the staircase
  +----------------------+ +----------------------+ +----------------------+
  | SAMPLING             | | SPAN LIMITS          | | DETACHED CONTEXT     |
  | bad request never    | | 400 queries shown as | | async/jobs/pool lose |
  | sampled; you open a  | | 100 spans or one     | | trace context; spans |
  | cheap, few-row trace | | aggregate + count    | | land in other traces |
  +----------------------+ +----------------------+ +----------------------+

An N+1 appears as a staircase of sequential child spans that share one SQL shape and differ only in bind parameters, while a preloaded IN query stays at two spans; sampling, span caps and lost trace context can hide the staircase.

How does APM query tracing confirm an N+1 at scale?

A single trace shows a pattern, but it does not show whether that pattern is systematic. To confirm it, group database spans by normalized statement, meaning the SQL with literals replaced by placeholders, so that WHERE order_id = 41 and WHERE order_id = 97 collapse into one fingerprint. Then compute, per endpoint, how many times each fingerprint runs per request.

The main detection rule is that query count scales with result size. Plot executions of a fingerprint against the number of parent rows the request returned. A flat line means the query is a fixed cost. A line that rises with page size or account size means the code issues one query per row. This test separates a true N+1 from an endpoint that runs a fixed handful of expensive queries.

Instrumentation trade-offs

Capturing every span, bind parameter and stack location adds CPU and memory overhead in the application and increases payload size to the collector. Sampling reduces this cost but weakens per-request counts. Raw parameters can also contain emails, tokens or personal data. For that reason, enable SQL obfuscation and normalization before you increase capture detail. Normalization is also what makes grouping possible in the first place.

Source Can see Cannot see
APM tracing Per-request counts, endpoint, call site Unsampled requests, untraced async work
Database statement statistics Total executions per fingerprint, all clients Which endpoint or request issued them
ORM query logs Exact SQL in order Production scale (too noisy to keep on)
Development-time detectors Lazy loads during tests Data shapes that exist only in production

How do you trace the repeated query back to the code that triggers it?

Start with the repeated span itself. Most agents attach the normalized SQL statement, and the table name in the WHERE clause usually tells you the model being lazy-loaded: forty spans of SELECT ... FROM authors WHERE id = ? point at an author association. If your agent records code-location metadata, such as a stack trace or the calling file and line on database spans, read it from the top of the stack downward. The ORM frames will dominate, so skip past them to the first frame in your own code. That frame is the access point, and it is rarely the controller.

The controller typically issues one clean query for the collection. The lazy loads happen later, when something walks that collection:

  • Serializers that include a nested association for each item
  • Template loops that call item.owner.name
  • Per-item permission checks that load a role or membership record for every row

Fix the layer that owns the access. Adding eager loading in the controller does nothing if a shared serializer or helper builds its own query, or re-fetches the association through a different scope, for each object. The trace will look identical after your deploy. Check that the code you changed is the code that appears in the stack.

Not every repeated span is SQL. A per-item cache lookup that misses, or an HTTP call to another service inside the loop, produces the same waterfall shape. ORM eager loading cannot fix either one. They need batched cache reads or a bulk API endpoint.

from django.db.models import Prefetch
from rest_framework import generics, serializers

from .models import Comment, Post  # Comment.post = ForeignKey(Post, related_name="comments")


class PostSerializer(serializers.ModelSerializer):
    # The lazy load happens here, per item, not in the view.
    author_name = serializers.CharField(source="author.name")
    approved_comments = serializers.SerializerMethodField()

    class Meta:
        model = Post
        fields = ["id", "title", "author_name", "approved_comments"]

    def get_approved_comments(self, post):
        # BEFORE (still N+1 even with prefetch_related("comments") in the view):
        #   post.comments.filter(approved=True)
        # .filter() builds a new queryset, so each post issues
        # SELECT ... FROM comments WHERE post_id = ? AND approved
        # AFTER: read the list the view already loaded in one query.
        return [c.body for c in post.approved_comments_list]


class PostList(generics.ListAPIView):
    serializer_class = PostSerializer

    def get_queryset(self):
        return (
            Post.objects
            # JOIN authors once instead of SELECT ... FROM authors WHERE id = ? per post
            .select_related("author")
            # Move the serializer's filter into the prefetch so it runs once
            .prefetch_related(
                Prefetch(
                    "comments",
                    queryset=Comment.objects.filter(approved=True),
                    to_attr="approved_comments_list",
                )
            )
        )

A Django REST Framework view and serializer where the repeated queries come from the serializer, showing why the eager loading has to match how the serializer reads the data: a .filter() on a related manager bypasses the prefetch, so the filter is moved into a Prefetch with to_attr.

Join-based loading vs separate-query preloading: which ORM eager loading strategy fits?

When the repeated call comes from an ORM relationship, you have three loading patterns. Lazy loading runs SELECT * FROM orders, then SELECT * FROM customers WHERE id = ? once per order. Join-based eager loading does the work in one statement: SELECT ... FROM orders JOIN customers ON customers.id = orders.customer_id. Separate-query preloading runs two statements: SELECT * FROM orders, then SELECT * FROM customers WHERE id IN (...) using the collected foreign keys.

ORMs name these differently. Django has select_related (join) and prefetch_related (separate query). Rails has eager_load and preload, and includes picks between them. JPA and Hibernate use fetch joins for the join form.

Strategy Queries Fits Avoid when
Lazy loading 1 + N Associations accessed rarely or conditionally It runs inside a loop over a list
Join-based 1 Belongs-to and has-one associations Joining across has-many, because each parent row repeats once per child and rows multiply
Separate-query preload 2 (one per association) Has-many collections The IN list grows large enough to hit parameter limits or get a poor query plan. Paginate or chunk the IDs.

Both eager strategies waste memory if they load associations the response never serializes. Only preload what the endpoint actually renders.

Not every repeated query needs fixing. A lookup served from cache, a call on a rarely taken branch, or a fixed three calls per request may cost less than the extra rows an eager load brings in. Rank candidates by the total time your traces measure, not by query count alone.

The fix is usually a line or two on the query. That makes it cheaper to write and review than a new caching layer or a denormalized schema, and it adds no new invalidation or consistency problems.

orders = query("SELECT * FROM orders WHERE created_at > :since LIMIT 50")
for order in orders:
    # One round trip per order; shows up in traces as N identical spans
    order.customer = query("SELECT * FROM customers WHERE id = ?", order.customer_id)

rows = query("""
  SELECT o.*, c.name, c.email
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.created_at > :since LIMIT 50""")
orders = query("SELECT * FROM orders WHERE created_at > :since LIMIT 50")
ids = unique(o.id for o in orders)
items = []
for chunk in chunks(ids, size=500):
    items += query("SELECT * FROM order_items WHERE order_id IN (?...)", chunk)
attach_by_key(orders, items, key="order_id")

Pseudocode for the same orders endpoint written three ways (lazy loading, a single JOIN for a belongs-to association, and chunked WHERE id IN (...) preloading for a has-many association), with comments marking where each one goes wrong.

How do you verify the fix and prevent N+1 regressions?

Verify before and after deploy

Reproduce the endpoint locally with realistic data volume. An N+1 over three seeded rows looks harmless, so seed enough parent records and related children to match a typical production page. Then wrap the request in a test that counts the SQL statements it issues and asserts a fixed ceiling. The assertion should hold whether the page returns 10 records or 500. If the count grows with the record count, the fix did not take.

After deploying, compare the same endpoint in your APM over equivalent traffic windows. Look at database span count per request, then p50, p95 and p99 latency. The span count should drop from roughly one per record to a small constant. The tail percentiles matter most, because the largest result sets drove them.

Prevent regressions

  • Strict or lazy-load detection: If your ORM or framework offers it, enable it in development and CI. Unexpected lazy loads then raise errors or log warnings instead of passing silently.
  • Query-count budgets: Add them to tests for your highest-traffic endpoints.
  • Span-count alerts: Alert on database span count per request, so a regression shows up as a step change before latency degrades.

CI only catches code paths that tests exercise with more than one related record. Production tracing remains your backstop.

Illustrative estimate: if each repeat investigation costs an engineer half a day and these guards prevent four a quarter, they save about two engineer-days per quarter.

import pytest
from contextlib import contextmanager
from sqlalchemy import ForeignKey, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload

class Base(DeclarativeBase): pass

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    books: Mapped[list["Book"]] = relationship()

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))

@contextmanager
def count_queries(engine):
    # Count every SQL statement the engine sends while the block runs
    counter = {"n": 0}
    def on_execute(conn, cursor, statement, params, context, executemany):
        counter["n"] += 1
    event.listen(engine, "before_cursor_execute", on_execute)
    try:
        yield counter
    finally:
        event.remove(engine, "before_cursor_execute", on_execute)

def list_authors(session):
    # The fix: load all books in one batched query, not one query per author
    stmt = select(Author).options(selectinload(Author.books))
    return [(a.id, len(a.books)) for a in session.scalars(stmt).all()]

@pytest.mark.parametrize("n_authors", [10, 500])
def test_list_authors_query_budget(n_authors):
    engine = create_engine("sqlite://")
    Base.metadata.create_all(engine)
    with Session(engine) as session:
        session.add_all([Author(books=[Book(), Book(), Book()]) for _ in range(n_authors)])
        session.commit()
        session.expunge_all()  # empty the identity map so cached objects cannot hide queries
        with count_queries(engine) as q:
            list_authors(session)
    assert q["n"] <= 3, f"{q['n']} queries for {n_authors} authors"

A pytest query-count budget for SQLAlchemy that counts SQL statements through the engine's before_cursor_execute event and asserts the same small ceiling at 10 and at 500 parent records, so a returning N+1 fails the test.

How does a trace-driven workflow save engineering time?

Most of the time in an N+1 investigation goes to finding the problem, not fixing it. Traces cut the search down at each step:

  • No codebase-wide audit. Ranking endpoints by total time consumed points you at the few endpoints that matter. You don't have to read every serializer and template looking for lazy loads that may never run under real traffic.
  • No reproduction guesswork. The trace comes from a real slow request, so you already know the endpoint, the repeated statement and roughly how many rows triggered it. You don't need to guess which customer's data shape causes the slowdown.
  • A direct path to the line. The normalized statement identifies the association, and code-location metadata, where your agent records it, identifies the frame in your code. That replaces stepping through the request with a debugger or adding temporary logging.
  • Small, low-risk fixes. The usual fix is an eager-loading option on one query. It is quick to review and has no cache invalidation to design.
  • Objective verification. The same span-count metric that exposed the problem confirms the fix, so there is less back-and-forth about whether the deploy helped.
  • Fewer repeat investigations. Query-count tests and span-count alerts catch regressions in CI or soon after deploy, while the change is still fresh in the author's mind.

Illustrative estimate (assumed inputs, not a measurement): assume that finding an N+1 by manual code review and local reproduction takes an engineer two days. Assume that the trace-driven path takes half a day: one hour to rank endpoints and read traces, and the rest to fix, test and verify. With an assumed six such investigations a year, the trace-driven workflow saves about nine engineer-days a year, before counting regressions prevented by tests and alerts. Replace these assumptions with your own team's numbers before you use the estimate for planning.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to find n+1 queries production, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.