The Comb: N+1 as a Visible Shape
One query to fetch the users, then one query per user to fetch their orders. Every individual query is fast, every dashboard is green, and the endpoint takes 268 ms because it made 101 round trips instead of 2.
Frame the diagnosis
Performance work starts from a symptom and a signal — never from a resource dashboard.
One hundred fast queries is a slow endpoint
The arithmetic is unforgiving. A query that takes 2.4 ms round trip — genuinely fast, well-indexed, nothing to optimize — executed once per user on a page of 100 users costs 240 ms of wall clock, because the calls are sequential: each one must return before the loop proceeds. The database is not the bottleneck. The *round trips* are, and every layer of monitoring that measures per-query duration will report perfect health.
This is why N+1 is the canonical example of a bug that only tracing catches. Database metrics show 101 fast queries. Application metrics show one slow endpoint. Neither connects the two. The trace shows both facts in the same picture, and the shape — a dense comb of identical narrow spans — is recognizable at a glance without reading a single number.
The multiplier is the item count, which is why this bug ships successfully: with 5 users in development it costs 12 ms and nobody notices. With 500 users in production it costs 1.2 seconds. Latency proportional to page size is the diagnostic fingerprint, and it is worth checking directly (Load Testing: What Question Is This Test Answering? with realistic data volumes catches this before users do).
Why every other signal says the system is healthy
Walk the signals. Database CPU: low, because 101 indexed point lookups are trivial work. Slow-query log: empty, because the threshold is 100 ms and each query is 2.4 ms. Connection pool: fine, because the queries are sequential and only ever hold one connection. Application CPU: low, because the process is waiting on the network. Error rate: zero. Every conventional health check passes while the endpoint takes a quarter of a second.
The one metric that catches it without a trace is spans per trace — or, if you have no tracing, queries per request, which many ORMs and database drivers can report. A jump from 3 to 103 is unambiguous and needs no interpretation. It is worth exporting as a metric precisely because it survives sampling and can be alerted on, unlike the trace itself (Sampling Without Throwing Away the Evidence).
The related trap is that N+1 is not only a database phenomenon. The same shape appears with per-item HTTP calls to another service (100 round trips at 8 ms each is 800 ms), per-item cache lookups, and per-item calls to a model provider in an agent loop — which is Reading an Agent Run as a Trace with a different label. The fix is the same in every case: make one batched call instead of N sequential ones.
| Signal | Value | What it tells you | Verdict |
|---|---|---|---|
| db CPU utilization | 18% | The database is barely working | normal |
| db slow query log (>100 ms) | 0 entries | No individual query is remotely slow | normal |
| db queries/sec | 4,100 (was 90) | 45× more queries for the same traffic | smoking gun |
| connection pool wait | 0 ms | Sequential queries never contend for connections | normal |
| spans per trace, GET /users | 104 (was 4) | One query per returned item | smoking gun |
| endpoint p99 vs page size | linear | Latency tracks item count exactly — the fingerprint | smoking gun |
Batch, join, or preload — and what each costs
Three fixes, with different trade-offs. Join fetches everything in one query and is the fewest round trips, but multiplies rows when the relationship is one-to-many (100 users with 20 orders each returns 2,000 rows with user columns repeated), which trades network bytes and deserialization cost for round trips. Batch load issues one extra query with WHERE user_id IN (...) and stitches in application code — two round trips, no row multiplication, slightly more code. Preload/dataloader is the same thing done automatically by the framework, which is the most maintainable and the easiest to accidentally disable.
For per-item calls to another *service*, the equivalent is a batch endpoint, which is an API design decision with its own costs — partial failure semantics, request size limits, and the question of what a batch of 10,000 should do (Fan-Out: Waiting for the Slowest of Seven for the tail implications).
Whichever you choose, the validation is the same and is not "the endpoint feels faster": queries per request should drop from N+1 to a small constant, and it should stay constant as the page size grows. Testing at one page size proves nothing, because the bug is defined by its slope, not its value.
1-- 12SELECT id, name FROM users LIMIT 100;3 4-- then, in a loop, 100 times:5SELECT * FROM orders WHERE user_id = 41;6SELECT * FROM orders WHERE user_id = 42;7SELECT * FROM orders WHERE user_id = 43;8-- ...9 10-- 101 round trips. Each ~2.4 ms. Total ~240 ms of pure latency.1-- 12SELECT id, name FROM users LIMIT 100;3 4-- 2 — one query for every user's orders5SELECT * FROM orders6WHERE user_id = ANY($1); -- $1 = the 100 ids7 8-- 2 round trips. ~12 ms + ~9 ms. Total ~21 ms.9-- Requires an index on orders(user_id) to stay fast as10-- the batch grows -- see the database domain for why.The batched version is not faster because the queries are better — the original queries were already fast. It is faster because it stopped paying network round-trip latency 100 times, and it stays fast as the page grows, which the original could never do.
Key points
- N+1 is a round-trip problem, not a query problem: 100 individually fast sequential queries cost 100 × round-trip latency.
- Every conventional signal stays green — low DB CPU, empty slow-query log, no pool contention — which is why only a trace or a queries-per-request metric finds it.
- The fingerprint is latency proportional to item count; it ships successfully because development datasets are small.
- The same comb shape appears with per-item HTTP calls, cache lookups and model calls, and has the same fix: one batched call instead of N.
- Validate on the slope, not the value — queries per request must stay constant as page size grows.
Follow the diagnosis
The causal chain, hop by hop — and the readings that invite the wrong conclusion.
- 1ORM → application: accessing
user.ordersinside the serialization loop triggers a lazy load per user. - 2Application → database: 100 sequential point queries, each fast and correctly indexed.
- 3Database → application: 100 round trips at ~2.4 ms each accumulate 240 ms of latency that no single query owns.
- 4Endpoint → user: p99 tracks page size linearly while every database health signal stays green.
- • "The database is slow." The database served 101 queries in 240 ms while 82% idle. It is the round trips that are slow.
- • "We need a bigger connection pool." Sequential queries use one connection; the pool is not involved (Connection Pool Saturation: Waiting in Front of an Idle Database is a different diagnosis with a different signature).
- • "Add an index." The queries are already indexed. Indexing a 2.4 ms point lookup to 2.1 ms saves 30 ms of 240.
- • "It is fine, it passed load testing." It passed load testing with a small dataset. The bug is proportional to data volume, not request rate.
Measure, fix, validate
An optimization is not finished until the metric that motivated it has moved.
- • Export queries (or outbound calls) per request as a metric and compare against the number of items returned.
- • Count spans per trace for the endpoint and diff against a known-good trace; 4 → 104 needs no further interpretation.
- • Measure endpoint latency at several page sizes (10, 100, 500) — linear growth confirms N+1 without opening a trace.
- • Check the database's total queries/sec against request rate; a 45× ratio is the same finding from the other side.
- • Batch the N queries into one `IN`/`ANY` query and stitch in application code — two round trips, no row multiplication, the safest default.
- • Use the framework's preload/dataloader mechanism so the batching survives future code changes rather than depending on one careful loop.
- • Join when the relationship is small and bounded and you would rather pay row duplication than a second round trip.
- • For per-item calls to another service, add a batch endpoint — and define partial-failure semantics before you ship it.
- • Queries per request must drop to a constant and stay constant at 10, 100 and 500 items — the slope is the proof.
- • Spans per trace should return to single digits for the endpoint.
- • Endpoint p99 should fall by roughly `(N-1) × round-trip latency`; a much smaller improvement means something else is also on the path.
- • Database queries/sec should fall proportionally at unchanged request rate, confirming the load left the system rather than moving.
- • Joins multiply rows and can move the cost from round trips to network bytes and deserialization — a real trade, not a free win.
- • Batch queries with large `IN` lists have their own planner behaviour and can degrade past a certain batch size.
- • Dataloaders add a layer of indirection that makes the data-access path harder to read and debug.
- • Batch endpoints introduce partial-failure semantics that the single-item version never had to define.
- • Alert on queries-per-request exceeding a per-endpoint threshold — it catches every future N+1 generically.
- • Add a test that asserts a bounded query count for the endpoint at a realistic page size; ORMs make it easy to reintroduce this in a one-line change.
- • Load test with production-scale data volumes, not production-scale request rates alone.
- • Track spans per trace as a metric so the regression is visible even when latency is masked by low traffic.
Accuracy
Performance numbers are conditional. These are the conditions.
- ILLUSTRATIVEThe 2.4 ms per-query and 268 ms total are constructed to make the arithmetic legible; real per-query round-trip cost depends on network topology, connection reuse and query complexity.
- DATABASE-SPECIFICHow well a large
IN/ANYlist performs, and at what size the planner changes strategy, varies by engine and version.