DEV Community

Raju Sarkar
Raju Sarkar

Posted on

Silent Performance Killer: N+1 Query Problem

#postgres #programming #sql

The N+1 query problem is a database anti-performance pattern, where an application executes one query to fetch a lists of items (N items in 1 query) and then makes N additional individual queries to fetch related data for each item(N number of queries).

This results in N+1 total database queries for what should ideally be a single efficient or a few queries.

Analogy: The Library

Imagine you go to a library and you ask the librarian to give you a list 100 books of your favorite genre. The librarian gives you the list of 100 books(this is 1 query).

Now you have your list, but you don't know author's name. To know the name of the authors you go to the librarian and ask him hey Librarian what is the name of the author of this book?. You ask this question for each book on the list and guess what you end up asking him 100 more questions.

The analogy is that you are making 1 query to get the list and then you are making N number of queries to get the authors name.

Technical Example

Assume that you own a e-commerce platform, where the sellers can list products and users can buy them. In your admin dashboard you want to list all the sellers and their products. There are two tables you have to query 1.seller, 2.product.

Let's see both efficient and inefficient ways.

Inefficient:

The inefficient way is to first get the list of sellers by querying seller table.
The

const sellers = await db.select().from(seller);
Enter fullscreen mode Exit fullscreen mode

And then for each seller you get the products

const allProducts = [];
for (const s of sellers) {
  const products = await db
    .select()
    .from(product)
    .where(eq(product.createdBy, s.id));
  allProducts.push(...products);
}
Enter fullscreen mode Exit fullscreen mode

This is inefficient way, you make a api call, then the api do the following:

  • First it query every seller from seller table
  • Then it fetches products for each seller. So, if there were 100 users you will make the first(1 query) and then 100 more queries, in total 101 queries to get what you need.

Efficient:

The efficient version of the above is you tell the db hey please give me all the sellers and their products, this way you are going to db only once and making a single query.

const sellersAndProducts = await db
  .select()
  .from(seller)
  .innerJoin(product, eq(seller.id, product.createdBy));
Enter fullscreen mode Exit fullscreen mode

Here, instead of fetching products one by one for each seller, we use a single SQL join between seller and product table to get everything in one query.

Why you should care about the N+1 query problem?

The answer always will be the performance. If you want to get best performance out of your db, you have to perform db queries or db transactions very carefully, otherwise you will end up spending database resources, performance on unnecessary queries.

Top comments (0)