N+1 Queries That Pass Tests but Fail Under Production Traffic

The endpoint has tests. They pass in CI, they pass locally, and the pull request is approved. In production the same endpoint turns into hundreds of database queries per request, and at peak traffic it takes the connection pool and the database with it. Nothing about the code changed between the two. The data and the traffic did.

An N+1 query is a pattern where code runs one query to load a list of N items, then one more query per item to load related data, for 1 + N queries where one or two would do. The latency arithmetic, why round trips that are free on localhost cost real time in production, is covered in Why an API Is Slow in Production but Fast Locally. This post is about the other two questions: why the test suite never notices, and why the pattern gets dramatically worse under traffic rather than just slower per request.

Why do N+1 queries pass tests?

They pass because every property of a test environment shrinks the cost of the pattern toward zero, and because tests assert on results, not on the work done to produce them. An N+1 returns exactly the right data. It just asks for it one row at a time.

That last row of the figure is the key point for testing. With N = 1, an N+1 is indistinguishable from correct code. Most fixtures, factories, and seed files create one parent with a couple of children because that is enough to test the behaviour. It is exactly not enough to test the query pattern.

Why does an N+1 fail under traffic, not just run slower?

A single slow request is a latency problem. Under traffic, an N+1 turns into three capacity problems that feed each other. That is why it can look fine in a staging smoke test, pass a manual check in production at 9 a.m., and fall over at the daily peak.

1. Query volume multiplies twice

The database sees requests per second × (1 + N) statements. Each statement carries fixed overhead that has nothing to do with how much data it returns: a network round trip, parsing or plan lookup, executor startup, and a result message. Fifty tiny queries cost the database far more than one query returning fifty rows. At 200 requests per second, the difference between 400 and 10,200 statements per second is often the difference between a quiet database and one pinned on CPU.

2. Connections are held for the whole loop

The request usually holds one pooled connection, or one transaction, from the first query to the last. If the loop takes 60 ms instead of 3 ms, each request holds its connection 20 times longer, and the pool needs about 20 times as many connections for the same traffic. When it does not have them, callers queue and time out. The error looks like pool exhaustion, and telling it apart from a connection leak is the first step of that investigation.

3. The tail gets much worse than the average

Every query has some small chance of being slow: a lock wait, a cache miss that goes to disk, a checkpoint, a noisy neighbour, a garbage-collection pause in the driver. A request that issues one query is exposed to that chance once. A request that issues 51 queries in sequence is exposed 51 times.

Put the three together and the failure mode is a cliff, not a slope. Higher traffic means more queries per second, which makes each query a little slower, which makes each request hold its connection longer, which leaves fewer connections for the next request. Retries from clients add more requests at exactly the wrong moment (the retry storm pattern). The N+1 was there all along; the traffic peak is when it becomes the incident.

Where do N+1 queries hide in the code?

The classic example is an explicit for loop with a query inside it. Reviewers catch those. The ones that reach production are usually split across files: the list is loaded in one place, and the per-item query is triggered somewhere that does not look like data access at all.

Here is the version that most often ships, in Django REST Framework. The view looks efficient, and the serializer looks like pure presentation:

class OrderSerializer(serializers.ModelSerializer):
    # lazy-loads the customer once per order
    customer_name = serializers.CharField(source="customer.name")
    item_count = serializers.SerializerMethodField()

    def get_item_count(self, order):
        return order.items.count()   # a COUNT query per order

    class Meta:
        model = Order
        fields = ["id", "created_at", "customer_name", "item_count"]

class OrderList(generics.ListAPIView):
    queryset = Order.objects.order_by("-created_at")
    serializer_class = OrderSerializer
    pagination_class = PageSize50

class OrderSerializer(serializers.ModelSerializer):
    customer_name = serializers.CharField(source="customer.name")
    item_count = serializers.IntegerField(read_only=True)   # annotated

    class Meta:
        model = Order
        fields = ["id", "created_at", "customer_name", "item_count"]

class OrderList(generics.ListAPIView):
    queryset = (Order.objects
                .select_related("customer")             # JOIN
                .annotate(item_count=Count("items"))     # SQL COUNT
                .order_by("-created_at"))
    serializer_class = OrderSerializer
    pagination_class = PageSize50

The two queries left are the pagination COUNT and the page itself. Note that the fix touched the view, not the serializer where the problem was introduced. That split is why the pattern survives review: the file that causes the N+1 and the file that can prevent it are different files.

How do you make tests catch N+1 queries?

Two techniques work across frameworks. The first makes the test fail on the shape of the data access. The second makes lazy loading impossible in tests so the code has to declare what it loads.

Technique 1: assert the query count does not grow with the data

An absolute count (“this endpoint runs 4 queries”) is brittle, since authentication, sessions, and middleware add queries that change for unrelated reasons. A scaling test is sturdier: run the same request with a small fixture and a larger one, and assert that the count is the same.

from django.db import connection
from django.test.utils import CaptureQueriesContext

def queries_for(client, url):
    with CaptureQueriesContext(connection) as ctx:
        assert client.get(url).status_code == 200
    return len(ctx.captured_queries)

def test_order_list_query_count_is_constant(api_client, make_orders):
    make_orders(count=1, items_per_order=3)
    one = queries_for(api_client, "/api/orders/")

    make_orders(count=19, items_per_order=3)       # 20 in total, still one page
    many = queries_for(api_client, "/api/orders/")

    assert many == one, f"query count grew from {one} to {many}"

The same idea works elsewhere. With Hibernate, enable hibernate.generate_statistics in the test profile and compare getPrepareStatementCount() from the SessionFactory statistics before and after the call. With SQLAlchemy, count statements with a before_cursor_execute event listener on the engine. Django also offers assertNumQueries and pytest-django’s django_assert_num_queries when an exact count is stable enough to pin, and recent Rails versions include query-count assertions in Active Record’s test helpers.

Technique 2: make lazy loading raise in tests

Most N+1 queries are lazy loads: a relation that was not loaded up front gets loaded on first access. If lazy loading raises an error in tests, every such access fails immediately, with a stack trace pointing at the line that caused it, even with a one-row fixture.

# Rails: config/environments/test.rb
config.active_record.strict_loading_by_default = true
# order.customer.name now raises ActiveRecord::StrictLoadingViolationError
# unless the query declared it:
Order.includes(:customer, :line_items).order(created_at: :desc).limit(50)

# SQLAlchemy 2.x: declare what is loaded, raise on everything else
stmt = (
    select(Order)
    .options(joinedload(Order.customer), selectinload(Order.items), raiseload("*"))
    .order_by(Order.created_at.desc())
    .limit(50)
)

Strict loading in the test environment is the cheaper of the two techniques to adopt. It will surface a backlog of existing lazy loads the day you turn it on, so many teams enable it per model or per new query first and widen it over time.

How do you find N+1 queries already in production?

In production, an N+1 has a recognizable signature in every telemetry source: many near-identical statements per request. You are looking for a ratio, not a slow query. Each individual query is usually fast, which is why slow-query logs miss it.

SELECT calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric)    AS total_ms,
       left(query, 90)                    AS statement
FROM   pg_stat_statements
ORDER BY calls DESC
LIMIT  15;
-- An N+1 looks like: very high calls, sub-millisecond mean_ms,
-- and a statement such as SELECT ... FROM customers WHERE id = $1

The pg_stat_statements documentation describes the columns. If you already trace with OpenTelemetry, the database spans carry the statement text, which makes the repeated statement easy to group; the OpenTelemetry database semantic conventions list the attributes. When you find one, correlating the trace with the code change usually points at a serializer, template, or resolver change rather than the query code.

How do you fix an N+1 without over-fetching?

The fix is always to replace N per-item queries with a constant number of queries. The choice is between joining and batching, and the wrong choice can create a different problem.

For GraphQL, field resolvers run once per parent object by design, so N+1 is the default behaviour rather than a mistake. The standard fix is a DataLoader-style batcher created per request: resolvers ask for a key, the loader collects every key requested in the same tick, and issues one query for all of them. The DataLoader project describes the pattern; most GraphQL servers have an equivalent.

After the fix, verify two things: the scaling test passes, and the batched version does not fetch far more data than the page renders. A fix that loads every item of every order to display a count is an N+1 traded for an over-fetch.

What should a reviewer look for in the diff?

Because the list query and the per-item access usually live in different files, reviewing for N+1 means reading the diff with one question: is this line executed once per item of a collection, and does it touch a relation or a remote call?

• New serializer fields, template expressions, or resolvers that read a relation (order.customer, item.product.price).
• New model properties or methods that query, when they are called from a list view or a loop.
• Loops over query results that call a repository, a client, or a cache per item.
• Changes to the list query that remove a select_related, includes, or fetch plan, or add a field the old fetch plan does not cover.
• Background jobs iterating over large result sets with a per-row lookup; N there is the table size, not a page size.
• Tests whose fixtures create one parent row for a list endpoint. Ask for a scaling test or strict loading.

This is one item in a broader production review; How to Review a Pull Request for Production Reliability Risks has the full checklist, and the sibling guide on missing database indexes in a PR covers the other common data-size regression. For the broader question of why green CI does not predict production behaviour, see A PR Passed CI but Broke Production.

How Tomosu helps

The reason N+1 queries survive review is that the loop and the query are in different places. Tomosu analyzes the repository as a whole, and each pull request against it, for production reliability risks, so it can follow a collection from where it is loaded to where each item is used:

• Per-item data access: relation reads, repository calls, and remote calls reached once per element of a query result, including through serializers, templates, and resolvers.
• Fetch plans that do not cover the access: a list query without the eager loading its consumers need, or a change that removes it.
• Traffic context: whether the path is a paginated user-facing endpoint, a hot API, or a background job over a whole table, so findings are weighed by exposure.
• Test gaps: list endpoints whose tests only ever use a single-row fixture, with a suggested scaling test.

Findings feed the Production Reliability Index and appear on the pull request, where the fix is a one-line change to a fetch plan rather than an incident.

Scan your repository with Tomosu →

Key takeaways

• N+1 queries pass tests because fixtures have one or two parent rows, the database is local, and tests assert on results, not query counts.
• With N = 1, an N+1 issues the same number of queries as correct code. Single-row fixtures cannot catch it.
• Under traffic, an N+1 multiplies statements per second, stretches connection hold time, and compounds tail latency; the result is a cliff at peak, not a gradual slowdown.
• Catch it with a scaling test (query count at 20 rows equals the count at 1) or by making lazy loads raise in tests.
• In production, look for ratios: identical DB spans per request, and statements whose call count dwarfs request count.
• Fix to-one relations with a JOIN, collections with a batched second query, and counts with SQL aggregates; avoid trading an N+1 for an over-fetch.

Frequently asked questions

An N+1 is correct code at the wrong scale. Tests that grow the data, and reviews that follow each collection to where it is used, find it before traffic does. Assess your repository →