By ChuqianOn

One day at a work dinner, one of my favorite designers asked “so what is this N+1 thing that everyone talks about here?” Then I realized that I stumbled. I found it hard explaining the exact definition as well as why we call it that way. Even though we are using the phrase everyday and I even worked on removing some N+1s. In this article, I strive to explain this mysterious “N+1” in a way that hopefully everyone can understand.

What is N+1?

In essence, “N+1” is a performance anti-pattern

when your code executes N additional query statements to fetch the same data that could have been retrieved when executing the primary query.

(source: https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89)

This performance anti-pattern usually happens in the context of database queries but can apply to other context such as rest api queries.

When I searched online articles, “N+1” is also referred to as “N+1 query” and “select N+1” and are often defined in slightly different ways depending on what context you are talking about. See appendix if you are interested in how other sources define “N+1”s

What exactly does that mean.. also why is it called N+1?

The definition can feel a bit abstract. The best analogy to understand this concept for me is the car-wheel analogy.

Let’s say you need to assemble cars. As a part of the manufacturing, you need to make trips to different locations to fetch various car parts. To simplify, let’s say you need car bodies from city A and wheels from city B. Now the goal is to fulfill your orders of manufacturing N cars.

An “N+1” way of doing this would be this (in which case you make a total of N+1 trips):

  • (1 trip) Make a trip to city A to get all the car bodies
  • (N trips) For each of the N car bodies, you do these:
    • Check order details to find out what 4 wheels the car needs
    • Make a trip to city B to fetch those 4 wheels
    • come back to your warehouse to finish assembling this 1 car

However you realized that there’s a much more efficient way:

  • (1 trip) Make a trip to city A to get all the car bodies
  • Check all orders to find out all the corresponding wheels for the N cars you need to make (4N wheels in total)
  • (1 trip) Make a single trip to city B to get all the wheels
  • come back to the warehouse to finish assembling all N cars

Now you only need to make 2 trips in total!

Why do we care about N+1?

Using the same car-wheel analogy. You can see that the total time you need increase significantly as N grows, especially if the distance of the trips are long. If each trip to city A and B takes 3 days and you need to manufacture 20 cars. Then with “N+1” you’ll need 63 days ((20+1)*3) when you only need 6 days ((1+1)*3)!

This works similarly for applications that need to access DB. Making a trip to DB is an expensive operation (compared to accessing cache and performance operations in memory) so we want to make as few trips as possible.

Because of this scaling factor of the N (moreover the cost can grow exponentially as you have nested N+1), your data fetching performance can improve significantly by avoiding N+1s. This means better user experience and most likely better conversion rate.

N+1 & 1-to-many relationships

Some articles online couples “N+1” with 1-to-many relationships and regard N as the number of associations in the 1-to-many relationship (1:n association). This can be true in some cases (and maybe a lot of the cases in ORMs) but not always the case.

N+1 can happen when there’s 1-to-1 relationship too. Using the same car-wheel example above, we can imagine building a new single wheeler vehicle (with a 1:1 relationship) and still encountering the same N+1 issue when building N such single wheelers. In our car-wheel example, the N is actually the number of the parent objects.

For this reason, I think those definitions of the “N” in the “N+1” is more accurate:

When do N+1 usually happen?

  • N+1 & ORM
    • ORM (Object-Relational Mapping) is an extremely helpful tool to allow you work with data and write object-oriented application without writing the underlying db queries. But because it abstract away data access, it can be prone to inefficient queries that lead to N+1
    • This article https://digma.ai/blog/n1-query-problem-and-how-to-detect-it/ explains pretty well how ORM can lead to N+1
  • N+1 & GraphQL
  • N+1 & Rest apis

Detecting N+1

N+1 is “infamous” because it can be tricky to detect from the DB query logs only, especially in a distributed system because unlike slow queries logs, each individual unnecessary queries appears healthy on the surface

The best tool would be trace/span analysis which records the hierarchical information between the queries. Read more at:

Fixing N+1

You can find a pretty good summary at https://www.freecodecamp.org/news/n-plus-one-query-problem/

  1. Eager Loading (Using SQL Joins, for example). Learn more at:
  2. Batch Loading. Learn more at:
  1. Caching
  2. Lazy Loading
  3. GraphQL Dataloader. Learn more at:

Reference

Appendix

Here are some of the definitions/explanations of N+1

N+1 queries are a performance problem in which the application makes database queries in a loop, instead of making a single query that returns or modifies all the information at once.

https://docs.sentry.io/product/issues/issue-details/performance-issues/n-one-queries/

The N + 1 problem is a performance anti-pattern in which an application makes N + 1 database calls (where N is the number of objects fetched).

https://www.appdynamics.com/blog/product/common-application-problems-and-how-to-fix-them-the-select-n-1-problem/

The chief symptom of this problem is that there are many, many queries being performed.

https://planetscale.com/blog/what-is-n-1-query-problem-and-how-to-solve-it

The problem typically arises when your entity-relationship model has a 1:n or n:m association. It exists when application code executes one query to get objects of one entity and then executes another query for each of these objects to get the objects of an associated entity.

https://schneide.blog/2021/12/06/understanding-identifying-and-fixing-the-n1-query-problem/

N+1 query problem is a problem in database retrieval where the related entities of an object are queried individually from a database, leading to O(n) queries where n is the number of related entities of the object.

https://signoz.io/blog/N+1-query-distributed-tracing/

The infamous N + 1 query problem is an anti-pattern that stems from the leaky abstraction that ORMs provide

https://digma.ai/blog/n1-query-problem-and-how-to-detect-it/

TL;DR: The N+1 query problem happens when your code executes N additional query statements to fetch the same data that could have been retrieved when executing the primary query.

https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89

In the case of web APIs, the N+1 problem is a situation where client applications are required to call the server N+1 times to fetch one collection resource + N child resources.

https://restfulapi.net/rest-api-n-1-problem/