The N+1 problem

In backend development, one of the most frequent mistakes that catastrophically tanks API performance is the infamous "N + 1 Queries" problem.

It usually creeps in unknown to the developer who is using library abstractions like "ORMs" (Object-Relational Mappers: e.g. Prisma or Sequelize) to talk to the database from Node.js.

Anatomy of the Disaster

Imagine an app wants to return the entire e-commerce homepage listing: All products together with the string of the category name each one belongs to. Seen from the Node.js side, an inexperienced programmer would write the iterative loop:

1. products = await db.query("SELECT * FROM products"); (One big, bulky operation 1)
2. The developer iterates (for..of) over each product in the array of 100 found
3. On each step, in Node.js they await an iterative sub-query categoryName = await db.query("SELECT name FROM categories WHERE id = " + product.category_id); and embed it into the result (N secondary iterations of slow, tiny queries!)

Total requests to the Postgres daemon: 1 + 100 local iterations = 101 micro network round trips between Node.js and the DB. 101 round trips cause horrendous network overhead and ugly latency. At real scale, N might not be 100 but 10,000 rows of total invoices to explore! The API wouldn't return before hitting a 5xx timeout.

The Real Native Answer: JOIN or Aggregation in a Single Query

Masterfully using DBMS abstractions written by hand, instead of trivial ORM commands in Node, fuses the executions together in milliseconds in a single, unbeatable query that solves the "N+1" mess:

SELECT
  p.id,
  p.name,
  c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id;

Total API/DB round trips: 1 unique query, fused, mathematically optimized by Postgres's own indexes, handled entirely with 0 milliseconds of extra network latency.

JSON aggregation alternative

Modern Postgres versions even let you return the entire complex join nested as JSON with json_agg, allowing your JavaScript to receive the products array including their sub-array of formatted reviews without any local script glue! (In the advanced paths and challenges you'll study Postgres's JSONb clause.)

Your turn

Exploring GROUP BY efficiency