Fix the N+1 query problem in Firestore and SQL: spot hidden per item reads, cut latency with parallel fan out, and kill fan out for good with denormalization.
The scariest performance bugs aren't the ones that throw. They're the ones where every individual query is fast, every unit test passes, and the profiler shows nothing hot — yet your feed screen takes 1.8 seconds to load and your Firestore bill creeps up every month. That's the N+1 query problem, and the reason it survives code review is that it isn't visible in the shape of any single line. It's visible only in the shape of the loop around it.
I've shipped this bug. I've also spent a lot of hours since then learning to see it before it ships. This is a deep dive on how query-shape problems hide, how to surface them in both Firestore and SQL, and the denormalization strategy that actually kills them for good.
The name comes from SQL ORMs like Hibernate, ActiveRecord, and Prisma: you run 1 query to fetch a list of N parents, then — because you access a relation inside a loop — the ORM silently fires N more queries, one per parent. One list of 50 posts becomes 51 database round trips. The "1" is the list query; the "N" is the fan-out you never explicitly wrote.
The Firestore version wears different clothes but has the same skeleton. You read a collection, then inside the loop you do a .doc(...).get() per item to hydrate an author, a like count, or a "did I favorite this" flag. Nothing looks wrong. Each get() returns in a few milliseconds. But you paid for N document reads you didn't need, and you serialized N network round trips that the UI is now blocked on.
The insidious part: it scales with your data, not your code. In development, N is 3. In production, N is 200, and the loop you wrote a year ago is now the slowest thing on the screen. Your code never changed — your data did. That's why N+1 is a query-shape problem, not a slow-query problem, and why every tool built to find slow queries walks right past it.
Here's the pattern I've caught most often — hydrating related documents inside a loop:
// The N+1 hiding in plain sight.final posts = await db.collection('posts') .orderBy('createdAt', descending: true) .limit(30) .get();final feed = <FeedItem>[];for (final post in posts.docs) { // One extra read PER post. 30 posts = 30 round trips. final author = await db.doc('users/${post['authorId']}').get(); feed.add(FeedItem(post: post, author: author));}Thirty posts is 31 reads and 31 serialized round trips. On a mobile connection with 80ms of latency, the author fetches alone add roughly 2.4 seconds if they run sequentially. And because Firestore bills per document read, you're paying for the fan-out on every single feed load, for every user. Multiply that by a daily-active-user count and the N+1 stops being a latency story and becomes a line item on your invoice.
The trap is that this code is correct. It returns the right data. Tests pass. It only reveals itself as a problem under real latency and real data volume — exactly the conditions your local emulator doesn't reproduce, because the emulator has no network and a nearly empty dataset.
Other shapes of the same bug I actively watch for:
likes subcollection just to show "42 likes". That's not one extra read per post; it's dozens, because you pull every like document to call .length on the result..get() per item to answer "has the current user saved this?".ListView whose item builder kicks off a FutureBuilder that reads a document. The fan-out is now spread across the widget tree, which makes it even harder to see in one place.You can't grep for "N+1". But you can look for its fingerprints, and every one of them is learnable.
1. Look for await inside a loop that touches the database. This is the single highest-signal pattern. An await db...get() in the body of a for, a .map(), or a Future.forEach is an N+1 until proven otherwise. In SQL-land, the equivalent is accessing a lazy relation — order.customer.name — inside a loop. Train your eye to flag database call under iteration the way you'd flag an unclosed resource.
2. Watch reads, not time. Latency lies to you locally because the emulator has no network. Instrument the actual read count per screen instead. If loading one feed of 30 items costs 60+ document reads, the number itself is the smell — you don't need a stopwatch. The Firebase console's usage dashboard and the local emulator's request log both make read counts visible; wire a counter into your data layer if you want it per-screen.
3. Turn on SQL query logging in staging. For Postgres, set log_min_duration_statement = 0 for one request (or use auto_explain); for MySQL, enable the general query log. If a single "load orders page" request emits 40 near-identical SELECT ... WHERE customer_id = $1 lines, that's your N+1, printed in plain text. ORMs like ActiveRecord and Prisma can also log the queries they generate, which is often the fastest way to catch a lazy-loaded association.
4. Trust the shape of the numbers. A healthy screen's read count is roughly constant as the list grows — a bit of pagination overhead, then flat. An N+1 screen's read count grows linearly with list length. Plot reads against item count across a few loads; a straight diagonal line is the diagnosis. This is the most reliable test I know, because it's immune to how fast any single query runs.
Not every N+1 deserves the same treatment. Here are the three fixes I reach for, from cheapest to most durable.
If you truly need N related documents, at least don't serialize them. Firing the reads concurrently collapses N round trips into roughly one round trip's worth of wall-clock time:
final posts = await db.collection('posts') .orderBy('createdAt', descending: true) .limit(30) .get();// Same N reads, but concurrent — one round trip of latency, not N.final authors = await Future.wait( posts.docs.map((p) => db.doc('users/${p['authorId']}').get()),);This fixes latency but not cost — you still pay for N reads. It's the right first move when the related data genuinely changes often and you can't cache it. There's a better middle option too: if the related documents share a collection, Firestore's whereIn / FieldPath.documentId lets you batch up to a bounded set of IDs into a single query, and getAll() in the Admin SDK fetches many documents in one call. In SQL, the equivalent is collapsing the per-row lookup into a single WHERE id IN (...) or a proper JOIN — which is exactly what "eager loading" (includes in ActiveRecord, include in Prisma, JOIN FETCH in Hibernate) does under the hood.
The durable answer is to stop the fan-out from existing. Store the data in the shape you read it in. If every feed render needs the author's name and avatar, put those on the post document at write time:
// At write time — pay once, on the rare event.await db.collection('posts').add({ 'authorId': user.uid, 'authorName': user.displayName, // denormalized 'authorAvatar': user.photoUrl, // denormalized 'likeCount': 0, // maintained, not counted 'text': text, 'createdAt': FieldValue.serverTimestamp(),});Now the feed is one query, N docs, zero fan-out. Read cost drops from N + 1 operations to 1 query returning N documents. This is the trade at the heart of NoSQL data modeling: you shift work from the frequent read path to the rare write path, and you accept some duplication. In a system where reads outnumber writes by orders of magnitude — which is nearly every social or content feed — that trade is almost always correct.
The obvious objection: what about consistency? If a user renames themselves, stale authorName copies linger. My rule of thumb:
likeCount field incremented with FieldValue.increment(1) beats reading an entire subcollection to .length it — every single time. Atomic increments are contention-safe up to a point; for very hot counters, shard the counter across a handful of documents and sum the shards.For read-heavy, expensive-to-assemble screens, go one step further and materialize the entire view — a per-user feed document, or a SQL materialized view. You do the join once, on write or on a schedule, and reads become a single document fetch. This is fan-out-on-write taken to its conclusion: the leaderboard, the personalized feed, the "trending" list are all assembled ahead of time so the read path is trivial.
This is heavier machinery, and I only reach for it when Fixes 1 and 2 aren't enough. It costs you write amplification and a staleness window, and it usually needs a background worker or a scheduled job to keep the materialized view current. But for something like a global leaderboard or a personalized home feed at scale, precomputation is often the only shape that stays cheap as N climbs into the thousands.
Say a feed of 30 posts shows each author's name and each post's like count.
authorName and likeCount are already on each post. Zero fan-out, one round trip, and the cost no longer grows with what each post references.Same screen, same data on the glass. The difference is entirely in the shape of how the data was stored and read.
await that reads the database inside a loop, and a read count that grows linearly with your list length.Find one N+1, fix its shape, and both your p95 latency and your monthly bill will thank you.