The N+1 query problem, batching and DataLoader: an interactive query waterfall

The N+1 query problem is one query to load a list, then one more query for every item in it. It is easy to write by accident, an ORM relation touched inside a loop or a GraphQL resolver per field, and it is invisible with ten rows on a laptop next to the database. In production, each of those queries pays a network round trip, and the page time grows with the number of items times the distance to the database.

The playground loads a feed page of posts with each post’s author and comment count. Change the page size and how far away the database is.

What the waterfalls show

• One by one, 50 posts mean 101 queries and 233 ms with a 2 ms round trip, almost all of it waiting on the network. Against a database in another region, 20 ms away, the same page takes over 2 seconds.
• In parallel it drops to 26 ms, but it is still 101 queries per page, 101,000 a second at 1,000 pages a second, and one page occupies all 10 connections of the pool while it loads.
• Batched, the authors and the counts are each one query with all the ids: 3 queries and about 7 ms whatever the page size, because the round trips no longer multiply.
• One JOIN is a single round trip, 5 ms here. It suits one-to-one relations; joining one-to-many relations repeats the parent rows and can return far more data than separate batched queries.

The fix in code

// the N+1: one query for the list, then one per item, hidden inside a loop or a resolver
const posts = await db.query('SELECT * FROM posts ORDER BY created_at DESC LIMIT 50');
for (const p of posts) {
  p.author = await db.query('SELECT * FROM users WHERE id = $1', [p.author_id]);   // 50 queries
}

// batched: collect the keys, one query per kind of thing (what DataLoader automates)
const ids = [...new Set(posts.map((p) => p.author_id))];
const users = await db.query('SELECT * FROM users WHERE id = ANY($1)', [ids]);   // 1 query
const byId = new Map(users.map((u) => [u.id, u]));
posts.forEach((p) => (p.author = byId.get(p.author_id)));

// ORMs: ask for eager loading
Post.findAll({ include: [User] })            // Sequelize
Post.objects.select_related('author')        # Django (JOIN)
Post.objects.prefetch_related('comments')    # Django (one IN query per relation)

To catch it, log the number of queries per request in development and fail tests above a threshold, and look for many identical query shapes in a trace of one request. In GraphQL servers, a DataLoader per request batches and caches the lookups that resolvers make for each field.

In a system design interview

When an API returns lists with related data, show that it loads them in bulk: “The feed endpoint fetches the page of post ids, then loads authors and counts with one batched query each, or from a cache with a multi-get, so a page is a handful of round trips regardless of its size. For GraphQL we use a DataLoader per request.” Expect the follow-ups: how batching interacts with caching, how to bound the size of IN lists, and when to denormalize the author name into the post instead.

Read the guide to API design or REST vs gRPC vs GraphQL, see batching and connection pooling, or watch a strong candidate design a news feed.

Questions people ask