Killing N+1 Queries: From Per-Row Lookups to Flat, Batched Reads

You ship a read endpoint. It lists some things, say devices or orders or invoices, and for each row it pulls in a bit of related data. With five rows in the demo it's instant. Six months and a few thousand rows later, the same endpoint takes seconds and sits at the top of your slow-query dashboard.

Usually the culprit isn't a slow query. It's a fast query run thousands of times. That's the N+1 problem. I recently fixed two different versions of it in the same codebase, so this post walks through both, plus the fix, plus a test that stops it coming back.

The examples use a made-up domain: a dashboard listing active devices, where each row needs the device's site, that site's latest billing window, a premium-plan flag, and an install date. The domain doesn't matter. The shapes do.

What N+1 means

You run 1 query to fetch a list of N items, then N more queries (one per item) for the related data. One plus N.

// 1 query: get the list
val devices = deviceRepo.findActive()

val rows = devices.map { device ->
    // N queries: one lookup per device, inside the loop
    val site   = siteRepo.findById(device.siteId)
    val window = billingRepo.latestWindow(device.siteId)
    val date   = assetRepo.installDate(device.serialNumber)
    Row(device, site, window, date)
}

Three lookups per device and 1,000 devices is 3,001 queries to render one page. Each query is tiny. But each one pays for a network round-trip, connection checkout, planning, and result marshalling, and they run sequentially: query 2 waits for query 1 to come back. So latency ends up dominated by the number of round-trips rather than the work inside any single one. This is why the code feels fine in a test against localhost and falls over against a database one network hop away.

What makes it hard to catch in review is that nothing looks wrong. Every line is a reasonable single-row fetch. The problem only becomes visible once you notice where the loop is.

Shape 1: the assembly loop

The example above is the classic form. You have a collection, and inside the map or for you reach back to the database once or more per element to assemble the final object.

The real version I fixed did five things per row: fetch the parent record, fetch the parent's latest status, re-fetch an id it already had through a second table, fetch an attribute record, and run an "is this premium?" check that turned out to be several queries by itself. On a few hundred rows that's thousands of statements per request.

Shape 2: the N+1 hiding inside a predicate

This one is sneakier. Somewhere there's a helper like isPremium(planId), and at the call site it reads like plain business logic:

fun isPremium(planId: UUID): Boolean {
    val prices = priceRepo.findByPlan(planId)                  // query 1
    return prices.any { price ->
        val component = componentRepo.find(price.componentId)  // query per price!
        component.type in PREMIUM_TYPES
    }
}

Two problems stacked on top of each other. First, the function is itself an N+1: one query for the plan's prices, then one more per price to resolve the component type. Second, it gets called inside a loop:

val premiumPlanIds = planIds.filterTo(mutableSetOf()) { isPremium(it) }

Total cost: plans × (1 + prices per plan). A predicate that looks like a single if is doing a nested fan-out. These are the worst N+1s to spot, because the expensive part is one function call deep.

The fix: batch first, assemble in memory

Both shapes have the same cure. Do all the fetching up front, in a fixed number of set-based queries, then build the object graph from in-memory maps. The loop stops touching the database entirely.

1. Collect the keys

Before the loop, gather every id you'll need, and de-duplicate. If 1,000 devices sit on 50 sites, you want to fetch 50 sites, not 1,000.

val devices = deviceRepo.findActive()
val siteIds = devices.map { it.siteId }.toSet()

2. One batched query per relationship

Replace findById(x) in a loop with a single IN query:

// Kotlin + Exposed, but every ORM has an equivalent (WHERE id IN (...))
fun findSitesByIds(ids: Set<UUID>): List<Site> = transaction {
    if (ids.isEmpty()) return@transaction emptyList()   // never send IN ()
    SiteTable.selectAll()
        .where { SiteTable.id inList ids }
        .map { it.toSite() }
}

Note the empty-input guard. Depending on your driver, an IN () with no values is either a SQL error or an accidental full-table scan, so short-circuit before you build the query.

Then turn each result into a lookup map keyed on whatever you'll match by:

val sitesById  = siteRepo.findByIds(siteIds).associateBy { it.id }
val datesByKey = assetRepo.installDatesBySiteIds(siteIds)   // Map<Pair<SiteId, Serial>, Date>

3. Assemble against the maps

val rows = devices.map { device ->
    val site = sitesById[device.siteId]
    val date = datesByKey[device.siteId to device.serialNumber]
    Row(device, site, date)
}

The query count no longer depends on the number of devices. It's a fixed handful, one per relationship. 5 rows or 50,000, same count.

"Latest per group" without a query per group

One per-row lookup deserves special mention: "the most recent record for this parent". Done naively that's one ORDER BY created_at DESC LIMIT 1 per parent. You can get all of them in one query with PostgreSQL's DISTINCT ON (elsewhere, ROW_NUMBER() OVER (PARTITION BY ...)):

fun latestWindowsBySiteIds(siteIds: Set<UUID>): List<BillingWindow> = transaction {
    if (siteIds.isEmpty()) return@transaction emptyList()
    BillingWindowTable.selectAll()
        .where { BillingWindowTable.siteId inList siteIds }
        .orderBy(
            BillingWindowTable.siteId to SortOrder.ASC,
            BillingWindowTable.endDate to SortOrder.DESC,   // newest first within each site
        )
        .withDistinctOn(BillingWindowTable.siteId)          // keep first row per site
}

One caveat: check your NULL ordering. If a NULL end-date means "still open", you need to know how your database sorts NULLs. Postgres puts NULLs first under DESC, so an open window is correctly picked as the latest here. Get this wrong and the batched version silently disagrees with the old per-row one, which is exactly the kind of bug that deserves a comment and a test.

Collapsing the nested predicate

The isPremium fan-out disappears once you stop asking one plan at a time. A plan is premium iff any of its price components is a premium type. So fetch all prices for all plans in query 1, fetch all the referenced components in query 2, and match in memory:

fun findPremiumPlanIds(planIds: Set<UUID>): Set<UUID> {
    if (planIds.isEmpty()) return emptySet()

    val prices = priceRepo.findByPlanIds(planIds)            // query 1
    if (prices.isEmpty()) return emptySet()

    val premiumComponentIds = componentRepo
        .findByIds(prices.map { it.componentId }.toSet())    // query 2
        .filter { it.type in PREMIUM_TYPES }
        .mapTo(mutableSetOf()) { it.id }

    return prices                                            // in-memory join
        .filter { it.componentId in premiumComponentIds }
        .mapNotNullTo(mutableSetOf()) { it.planId }
}

Two queries, total, no matter how many plans or components are involved. The old loop cost plans × (1 + prices).

Don't lose per-item error isolation

There's a behavioural trap when you hoist work out of a loop. In the old code, if one item's dependency was missing, you could catch that iteration, mark the item as skipped, and keep going. Batch everything up front carelessly and a single bad row can now fail the whole request.

The fix is to batch the fetching but keep the validation per item:

val (successful, skipped) = devices.map { device ->
    runCatching {
        val site = checkNotNull(sitesById[device.siteId]) {
            "No site for device ${device.id}"
        }
        val window = checkNotNull(windowsBySite[device.siteId]) {
            "No billing window for site ${device.siteId}"
        }
        buildRow(device, site, window)
    }
}.partition { it.isSuccess }

The maps are shared, but each item still gets its own verdict. A device with no billing window lands in skipped with a clear reason and everything else succeeds. Same semantics as the old loop, at the cost of a single batched read.

Prove it with a test that counts statements

A performance fix you can't measure is one you can't defend. "It feels faster" gets refactored away in six months. Milliseconds are noisy and machine-dependent. The durable, deterministic metric is how many SQL statements the operation issues, which happens to be exactly the thing an N+1 blows up.

So I wrote an integration test that runs both implementations against the same seeded data at several sizes, counting statements with a logger that increments on every executed statement:

@Test
fun `batched resolver issues far fewer statements than the per-item loop`() {
    val sizes = listOf(5, 20, 40)
    val newCounts = mutableListOf<Int>()

    sizes.forEach { n ->
        seedItems(upTo = n)

        // 1. Both implementations must agree on the result. Correctness first.
        val oldResult = oldPerItemImplementation()
        val newResult = newBatchedImplementation()
        newResult shouldBe oldResult

        // 2. Then compare statement counts.
        val oldStatements = countStatements { oldPerItemImplementation() }
        val newStatements = countStatements { newBatchedImplementation() }
        newCounts += newStatements

        println("[perf] n=$n | old=$oldStatements | new=$newStatements")
        newStatements shouldBeLessThan oldStatements
    }

    // The real assertion: the new count is flat across every size.
    newCounts.toSet().size shouldBe 1
}

Output looks like this:

[perf] n=5  | old=11 | new=4
[perf] n=20 | old=41 | new=4
[perf] n=40 | old=81 | new=4

Two assertions carry the story. shouldBeLessThan proves the fix is strictly better at every size. The flat-count check proves the new count is identical at 5, 20, and 40 items: O(N) vs O(1) round-trips, encoded as a regression test. If someone reintroduces a per-row lookup, that assertion fails on the spot.

Two notes that saved me grief. Assert equivalence before performance, because a fast wrong answer is still wrong. And count statements with the logger attached, but measure wall-clock in a separate run without it, since the logger distorts timings.

Where this stops being academic

At 5 rows nobody notices an N+1. The reason to fix it is what happens as data grows. Since the queries run sequentially, latency is roughly statements × round-trip time.

Take the assembly loop with 5 lookups per row (1 + N×5 statements) against a batched version at a fixed 6, and assume a healthy 2 ms per round-trip:

Rows (N)	Old statements	New	Old wall-clock	New wall-clock
100	501	6	~1 s	~12 ms
1,000	5,001	6	~10 s	~12 ms
10,000	50,001	6	~100 s	~12 ms

Ten seconds at 1,000 rows means timeouts and a paged on-call engineer. The nested-predicate shape is worse because it multiplies: put isPremium (4 queries per plan) inside the assembly loop and you're at roughly N × M, thousands of statements from that one check before counting anything else. Batching collapses the whole product back to a constant. The win here isn't shaving 20% off a query. It's changing the growth curve.

Wall-clock is also just the symptom the user sees. A request that holds a connection for 10 seconds while it drips out 5,000 queries starves the connection pool for everyone else, so healthy endpoints start failing because of one unbatched loop somewhere else in the service. Every statement gets parsed and planned, and thousands of trivial WHERE id = ? calls burn more database CPU in aggregate than two set-based queries doing the same work. Transactions and locks stay open longer. And the pain concentrates at p99: the average user with a small result set is fine, while the power user with the biggest account hits the worst case every single time. That power user is often your most valuable customer.

A checklist

When an endpoint's latency scales with its result size:

1. Find the loop that touches the database. Any map/for/filter whose body calls a repository is a suspect. Look inside helper functions too, since the worst N+1s hide one call deep.
2. Name the shape: 1 + N? 1 + N × k? A nested N × M?
3. Hoist the fetching out of the loop: collect ids, de-duplicate, one IN query per relationship.
4. Build maps and assemble in memory. The loop reads from Maps, never the database.
5. Use DISTINCT ON or window functions for "latest per group".
6. Collapse nested predicates into "fetch all, match in memory".
7. Guard empty inputs so you never emit IN ().
8. Preserve per-item error isolation if the old loop had it.
9. Lock it in with a statement-count test that asserts a flat count across sizes.

N+1 is a round-trip problem. The individual queries are fine; the count is the enemy. The fix is always the same shape, and the metric that keeps it fixed is statements per operation. Count it, assert it stays flat, and the win becomes a property of the system instead of a mood.

Comments

[Mads Hansen]

The flat statement-count test is a great regression guard. I would add one boundary to the “5 or 50,000, same count” claim: round trips become O(1), but rows, bytes, parameters, memory, and planner work are still O(N). A single enormous IN (...) can hit driver limits or simply move the bottleneck into one oversized query and a very large in-memory map.

For large sets, page the parent query and batch keys in bounded chunks, or pass IDs as an array/temporary relation and join server-side. Track rows returned, bytes, peak memory, p95 latency, and query plans alongside statement count. There is also a consistency detail: if sites, windows, and prices are fetched in separate statements while writes continue, assemble them under one transaction snapshot (or an explicit as-of boundary) so the maps describe the same logical moment. The best invariant is not only “fixed query count,” but “bounded batch size, consistent snapshot, and equivalent results.”