Database query performance issue: N+1 problem

@ Database

Introduction

I’ve heard of the “N+1 problem” since I first encountered databases, but I never took notes or thought it through thoroughly. This time I document the causes, detailed solutions, and diagrams.

What is the N+1 problem?

The “N+1 problem” mainly refers to performing one initial query (1) and then executing N additional related queries for each of the N items returned by the first query. This pattern easily causes serious performance issues.

  • 1: One query to find all records matching certain criteria
  • N: For each record from 1, perform a related query

Here’s the translated Mermaid diagram:

DatabaseApplicationDatabaseApplicationN+1 Query Problem ExampleProblematic Approach: Generates N+1 QueriesLoop through each user...... Repeated 7 more times ...Total: 11 queries (1 + 10)Better Approach: Only 2 Queries NeededTotal: 2 queries1. SELECT * FROM usersFetch 10 usersReturns 10 users2. SELECT * FROM posts WHERE user_id=1Returns posts for User 13. SELECT * FROM posts WHERE user_id=2Returns posts for User 24. SELECT * FROM posts WHERE user_id=3Returns posts for User 311. SELECT * FROM posts WHERE user_id=10Returns posts for User 101. SELECT * FROM usersReturns 10 users2. SELECT * FROM postsWHERE user_id IN (1,2,3,...,10)Returns all related posts

How to solve the N+1 problem?

The issue is the repeated and excessive access to the database; if you can fetch the required data in one go, you can solve the problem.

  1. Data modeling (denormalization)
  2. Perform related-data queries at the DB layer: $lookup or JOIN
db.users.aggregate([
  {
    $lookup: {
      from: "posts",
      localField: "_id",
      foreignField: "userId",
      as: "posts"
    }
  }
])
  1. Batch queries and assemble in the application layer: $in or IN
// Step 1: Query all posts
const posts = await Post.find(); // 100 posts


// Step 2: Collect all author IDs
const userIds = [...new Set(posts.map(p => p.userId))];


// Step 3: Batch query all authors (execute only once)
const users = await User.find({ _id: { $in: userIds } });


// Step 4: Assemble at the application layer
const userMap = new Map(users.map(u => [u._id, u]));
posts.forEach(post => {
  post.author = userMap.get(post.userId);
});


userMap.get(userId)
  1. ORM/ODM Eager Loading

Summary

  1. Avoid making database queries inside loops.
  2. Understand how your ORM issues queries: Many ORMs default to lazy loading and can generate inefficient queries that lead to N+1 problems. They usually provide configuration or features to avoid this—check the documentation to ensure your ORM generates efficient queries.