The N+1 Query Problem

One query for fifty posts, then fifty more for their authors, and none of it shows in tests.

The N+1 query problem is a page that runs one query to load a list of N rows, then one more query for each row. Every one of those queries is fast, so none of them looks wrong on its own, but their round trips add up, and the page grows slower as the list grows.

The symptom. A blog's front page lists 50 posts with each author's name. It was fast in testing and is slow in production. No single query in the database's slow log takes more than a millisecond.

The trigger. The code loads the posts, then loops over them and asks for each author. Many ORMs, libraries that turn rows into objects, do this by default when the loop reads post.author.name. The query log for one page load shows what reached the database.

sql

SELECT id, title, author_id FROM posts ORDER BY id LIMIT 50
SELECT name FROM authors WHERE id = 50
SELECT name FROM authors WHERE id = 49
SELECT name FROM authors WHERE id = 48
-- 46 more lines, one per post, ids 47 down to 2
SELECT name FROM authors WHERE id = 1
-- statements: 51

The query log for one page load, captured from SQLite's trace callback, run with python:3.12-slim (Python 3.12.13, SQLite 3.46.1)

The mechanism. Each statement is a round trip between the application server and the database, about 0.5 ms inside one data centre before the query itself runs. Fifty-one round trips is over 25 ms of waiting for a single list. A test database with three posts runs four queries, which is why the problem hides until real data arrives.

The cost. The page slows in proportion to the list, and every reader multiplies the load on the database by the length of the page.

The remedy. Fetch the related rows in one query: a join, or a second query with WHERE id IN (...) over all the author ids at once. The same run with a join sent one statement and returned all 50 rows, the first being post 1 by Author 50.

sql

SELECT posts.id, posts.title, authors.name
FROM posts JOIN authors ON authors.id = posts.author_id
ORDER BY posts.id LIMIT 50

The fix: one statement for the whole page

So count the statements a page sends, not only how long each takes: a number of queries that grows with the list is the sign of N+1.

System Design
SQL
Performance

← → move through sections and cards