N+1 Query Problem

### The "N" and "1" in N+1 When using an ORM (Object-Relational Mapping) or query builder, it is easy to fall into this problem without realizing it. The pattern goes like this: 1. Fetch a list from the parent table — this is the "1" query 2. For each of the N records retrieved, query the child table for related data — this results in "N" queries Consider, for example, a page that displays a list of glossary entries. First, 50 records are SELECTed from the `glossaries` table, then the translation data for each term is fetched one by one from the `translations` table — resulting in a total of 51 queries. If the record count grows to 500, that becomes 501 queries. ### Why It Is Serious Even if each individual query returns in a few milliseconds, the number of queries grows linearly, causing response time to degrade in proportion to the amount of data. It is not uncommon for a table with only a few dozen records in local development to swell to thousands in production, at which point the latency finally becomes apparent. Furthermore, in environments where the DB server and application server are separated across a network, the round-trip overhead accumulates with each additional query. Even if the queries themselves are lightweight, network latency can become the dominant factor. ### Approaches to Resolution The two primary solutions are **eager loading** and **JOIN**. | Approach | Framework Examples | Overview | |---|---|---| | Eager loading | Rails `includes` / Django `select_related` / Prisma `include` | The ORM automatically generates queries that fetch related data in bulk | | Explicit JOIN | Raw SQL / Supabase `.select("*, children(*)")` | Joins parent and child in a single query | | DataLoader pattern | GraphQL DataLoader / Next.js `cache()` | Aggregates keys within a request and converts them into a batch query | When using Supabase, leveraging PostgREST's embedded queries (`select("*, translations(*)")`) allows you to retrieve related data in a single request without writing loops on the application side. ### Detection and Prevention Monitoring query logs is an effective way to detect N+1 issues during development. With PostgreSQL, setting `log_min_duration_statement` to 0 logs all queries, allowing you to check whether queries with the same pattern are being executed consecutively. Detection tools are also available, such as the `bullet` gem for Rails and `django-debug-toolbar` for Django. Even simply being mindful during code review of whether `await` appears inside a loop can prevent a significant number of N+1 issues before they occur.