The N+1 query problem

The N+1 problem happens when code loads a list with one query and then runs one more query for each item in it.

posts, _ := store.ListPosts(ctx, 50)            // 1 query
for i := range posts {
	posts[i].Author, _ = store.GetUser(ctx, posts[i].AuthorID) // +50 queries
}

Each query might take 1 ms, so this looks harmless in development. In production each one is a network round trip to the database, and they run one after another. Fifty authors become 50 round trips, plus 50 pooled connections borrowed and returned in turn. ORMs make it easy to write without noticing, because post.Author looks like a field access but runs a query.

#Fixes

Join. Fetch everything in one query when the relationship is one-to-one or many-to-one:

SELECT p.id, p.title, u.id, u.name
FROM posts p
JOIN users u ON u.id = p.author_id
ORDER BY p.created_at DESC
LIMIT 50;

Batch. For one-to-many relationships, where a join would repeat the parent row many times, collect the IDs and load them all with one query:

SELECT id, name FROM users WHERE id = ANY($1);   -- $1 = the 50 author IDs

Then match the results up in memory with a map. That's 2 queries in total, however long the list is. GraphQL servers do this automatically with a dataloader, which collects every lookup made during one tick and sends them as one batch.

#Finding it

• Count queries per request. Log or trace each database call (see logs, metrics and traces). A request whose query count grows with the page size has this problem.
• Look for the shape in traces: a long staircase of identical short queries.
• Write a test that loads a page of 50 items and fails if it makes more than a handful of queries.

The cure is never "add a cache in front of GetUser." That hides the extra round trips without removing them, and it adds an invalidation problem on top.