mediumPerformanceNode.js / SQL

The N+1 performance killer

An AI wrote the following code to fetch all posts along with their author details. The developer complains that the database CPU spikes and API response time is very slow when there are hundreds of posts. Why?

fetch-posts.js

async function getPostsWithAuthors(db) {
  const posts = await db.query('SELECT * FROM posts');
  
  for (let post of posts) {
    const author = await db.query('SELECT * FROM users WHERE id = ?', [post.authorId]);
    post.author = author[0];
  }
  
  return posts;
}

What performance issue is causing database load?

N+1 Query Problem: the code triggers one database query for posts, and then a separate query for each post to fetch the author.

Database Locking: using 'SELECT * FROM users' locks the user table for write access while the loop executes.

Promise.all is missing; since JavaScript loops run concurrently, it triggers too many simultaneous connections.

JavaScript loops cannot run async/await code, resulting in unresolved promises inside the return statement.