N+1 Query Problem
The N+1 query problem is a performance anti-pattern in which, after retrieving a list of records with a single query, the related data for each record is fetched individually, resulting in a total of N+1 database accesses.
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:
- Fetch a list from the parent table — this is the "1" query
- 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.
