N+1 Queries & ORMs

The 30-second version

Here's the classic case. A blog's home page shows the 50 newest posts, each with its author's name. The code does this:

1. One query: "give me the 50 newest posts". Each post knows its author's id, but not the name.
2. Then, for each of the 50 posts: "give me the author with id 920", "…id 839", "…id 758", … 50 more queries.

That's 51 queries for one page. Each query has to travel to the DatabaseDatabaseA program whose whole job is storing data safely and finding it again quickly — Postgres, MySQL, MongoDB, Redis. Your app usually talks to it over a network connection; embedded databases like SQLite run inside your program instead. and back. That's a round trip (Round-trip timeRound-trip time (RTT)How long it takes for a message to go from one machine to another and for the reply to come back. Roughly 1 ms inside a data center, 70–150 ms across an ocean.), roughly 1 ms inside a data center (approximate; it varies with your network). The trips happen one after another, so the page waits about 51 × 1 ms ≈ 50 ms before it can even start rendering. The same data could arrive in 2 queries (all posts, then all their authors at once) or 1 (both together). That's 2 ms instead of 50.

It's called "the most common performance bug" for a reason: the code looks perfectly innocent (a for loop and what looks like reading a field), each query is individually fast, and it works fine on your laptop with 5 posts. It only hurts when N grows, or the database is a few milliseconds away instead of on the same machine.

Play with it. Push N to 200 and the round trip to 5 ms, then compare the strategies:

What you'll learn

• Why it happens: how an ORMORMObject-relational mapper: a library (Prisma, GORM, Hibernate…) that maps database rows to objects in your code and writes much of the SQL for you. hides queries behind innocent-looking code (§03).
• How to see it: query logs, query counters and tracing "staircases" (§04).
• How to fix it: JOINsJOINSQL that combines rows from two tables by matching columns — e.g. each order with its user's name: … FROM orders JOIN users ON users.id = orders.user_id., batched IN queries, ORM eager loading, nested JSON from Postgres, and DataLoader (§05–§08).
• What doesn't fix it: running the queries in parallel (§09), and the related traps nearby (§10).
• How to keep it fixed: tests that count queries (§11), and the staff-level view: when N+1 is fine, and the same bug between microservices (§12).

Primer: tables, SQL and round trips

Tables, rows and ids

A relational DatabaseDatabaseA program whose whole job is storing data safely and finding it again quickly — Postgres, MySQL, MongoDB, Redis. Your app usually talks to it over a network connection; embedded databases like SQLite run inside your program instead. (one that stores data in tables that point at each other) such as PostgreSQLPostgreSQL (Postgres)A popular open-source relational SQL database, known for correctness and features. Used throughout this site's database examples. stores data in tablesTable / row / columnHow a relational database stores data: a table (users) is like a spreadsheet; each row is one record (one user); each column is one field (name, email) with a fixed type., which work like spreadsheets: one row per thing, one column per field. Our blog has two:

Each row has an id that no other row in that table shares: its Primary keyPrimary keyThe column (or columns) that uniquely identifies each row, like users.id. The database refuses two rows with the same primary key and indexes it automatically.. A post doesn't store its author's name. It stores the author's id in author_id, a Foreign keyForeign keyA column that points at another table's primary key — orders.user_id → users.id — and lets the database refuse rows that point at nothing. that points at a row in authors. That's like a library card that says "see shelf 920" instead of copying the whole book. Storing the name once means renaming an author is one change, not thousands. But it also means showing a post with its author's name needs data from two tables, and that's where N+1 comes from.

SQL in five lines

SQLSQLStructured Query Language: the language for asking relational databases questions — SELECT name FROM users WHERE id = 7. Postgres, MySQL and SQLite all speak it. is the language you use to ask. These are the only forms this page needs:

SELECT id, title, author_id FROM posts ORDER BY created_at DESC LIMIT 50;

SELECT name FROM authors WHERE id = 920;

SELECT id, name FROM authors WHERE id IN (920, 839, 758);

SELECT p.title, a.name
FROM posts p
JOIN authors a ON a.id = p.author_id;

The result of a SELECT is always a flat table of rows, even after a JOIN. Keep that in mind; it matters in §05.

Every query is a round trip

The database is a separate program, usually on another machine. Your code sends the SQL text over a network connection, the database does the work, and the rows come back. Until they do, your code waits.

Here's the number that explains this whole topic. On our test Postgres, finding one author by id took the database 0.03–0.06 ms. A round trip inside a data center costs around 0.5–1 ms. So for a simple lookup, more than 90% of the time is spent travelling, not working. Ten lookups cost ten trips. One query that asks for ten rows costs one trip.

ORMs, lazy loading and the innocent loop

What an ORM is

Writing SQL by hand and copying every column into a struct or object gets tedious. An ORMORMObject-relational mapper: a library (Prisma, GORM, Hibernate…) that maps database rows to objects in your code and writes much of the SQL for you. (object-relational mapper) does that for you. You describe your tables as classes or structs, including how they relate ("a post belongs toBelongs-to / has-manyThe two common kinds of relation. A post belongs to one author (posts.author_id points at authors.id). An author has many posts. Has-many relations are the ones that multiply rows in a JOIN. an author, an author has many posts"), and the ORM writes the SQL. Popular ones: Prisma, TypeORM and Sequelize in JavaScript; GORM and ent in Go (where many teams use plain SQL instead, via database/sql or code generators like sqlc).

That's genuinely useful. But it moves the SQL out of sight, and SQL you can't see is SQL you don't count.

The innocent loop

Here's the blog page with two real ORMs. Neither line inside the loop looks like database access, but each one is a query:

type Author struct {
    ID   int64
    Name string
}
type Post struct {
    ID       int64
    Title    string
    AuthorID int64
    Author   Author // filled in only if you ask
}

var posts []Post
// (errors ignored for brevity; check .Error in real code)
db.Order("created_at DESC").Limit(50).Find(&posts) // 1 query

for i := range posts {
    db.First(&posts[i].Author, posts[i].AuthorID) // ← 1 query per post
}

With GORM's logger switched on, our test database printed exactly what you'd fear (trimmed to 3 posts to save space):

[1.592ms] [rows:3] SELECT * FROM "posts" ORDER BY created_at DESC LIMIT 3
[0.618ms] [rows:1] SELECT * FROM "authors" WHERE "authors"."id" = 920 ORDER BY "authors"."id" LIMIT 1
[0.162ms] [rows:1] SELECT * FROM "authors" WHERE "authors"."id" = 839 ORDER BY "authors"."id" LIMIT 1
[0.142ms] [rows:1] SELECT * FROM "authors" WHERE "authors"."id" = 758 ORDER BY "authors"."id" LIMIT 1

With 50 posts it's 51 lines. Notice the timings: every query is well under a millisecond, so none of them looks slow. The problem is how many there are.

Lazy loading: the query that looks like a field

In those two examples you can at least see a method call in the loop. Many ORMs go further with Lazy loadingLazy loading (ORM)The ORM doesn't fetch related data (a post's author) until your code first touches it — and then runs a query on the spot. Convenient, but it hides a query behind what looks like reading a field.: post.author itself runs the query, the first time you read it. Rails (ActiveRecord), Django, Hibernate and TypeORM's "lazy relations" all work this way (details vary by library and version). It isn't magic. Here's the whole trick in a few lines of plain JavaScript:

class Post {
  constructor(row, db) {
    Object.assign(this, row); // copy id, title, author_id onto the object
    this.db = db;
  }
  get author() {              // a getter: runs code when you READ post.author
    return this.db
      .query("SELECT * FROM authors WHERE id = $1", [this.author_id])
      .then((res) => res.rows[0]); // ← a query hiding behind a "field"
  }
}

for (const post of posts) {
  const author = await post.author; // looks like a field. It's a query.
}

Step through it and watch the SQL log on the right:

1const posts = await Post.findAll({ limit: 50 });
2 
3for (const post of posts) {
4  const author = await post.author; // a field?
5  html += `<li>${post.title} by ${author.name}</li>`;
6}

Go has the same bug, just without the magic

Go ORMs like GORM don't lazy-load: a relation stays empty unless you ask for it. But Go code hides queries in a different place: helper functions. A function like authorName(ctx, id) that runs a SELECT is perfectly reasonable on its own. Call it from a loop, or from a function that's called from a loop three files away, and you've written an N+1 without ever seeing the loop and the query together.

Seeing it: logs, counters and staircases

1. Ask the database to log every statement

Postgres can write every statement it receives to its log file. The setting is log_statement = 'all'. Turn it on for a local or staging database (in production it's far too noisy), load the page once, and read the log. To switch it on, run ALTER DATABASE blog SET log_statement = 'all'; as a superuser (new connections pick it up), or start a Docker Postgres with postgres -c log_statement=all. This is real output from our test database while a plain database/sql version of the §03 loop (SELECT name FROM authors WHERE id = $1 per post) loaded 4 posts:

LOG:  execute stmtcache_d2bf…: SELECT id, title, author_id FROM posts ORDER BY created_at DESC LIMIT $1
DETAIL:  parameters: $1 = '4'
LOG:  execute stmtcache_14e3…: SELECT name FROM authors WHERE id = $1
DETAIL:  parameters: $1 = '920'
LOG:  execute stmtcache_14e3…: SELECT name FROM authors WHERE id = $1
DETAIL:  parameters: $1 = '839'
LOG:  execute stmtcache_14e3…: SELECT name FROM authors WHERE id = $1
DETAIL:  parameters: $1 = '758'
LOG:  execute stmtcache_14e3…: SELECT name FROM authors WHERE id = $1
DETAIL:  parameters: $1 = '677'

That's the fingerprint: the same statement, over and over, with only the parameter changing. (The stmtcache_… name is the Go driver, pgx, reusing a Prepared statementPrepared statementA query the database has already parsed and planned, kept under a name on that connection. Re-running it only sends the parameter values. Drivers like pgx do this automatically.: the database parsed the SQL once and now gets only the new $1 value each time. That makes each query a bit cheaper, but it's still one round trip each.)

Your ORM can print the same thing from your side: GORM with Logger: logger.Default.LogMode(logger.Info) (output shown in §03), Prisma with new PrismaClient({ log: ["query"] }). For a whole database there's also pg_stat_statements, a Postgres extension that keeps a running count of calls per distinct query. An authors-by-id query with a call count 50× higher than the posts query that feeds it is a strong hint.

2. Count queries per request

Even simpler: count. Wrap whatever sends queries so it increments a counter, and log the total at the end of each request, e.g. GET /posts → 51 queries, 54 ms. A page that should need 2 queries and runs 51 stands out at once. Better still, put the count in a test so it can never creep back (§11 shows the code in Go and JS).

3. Look at a trace: the staircase

In production you usually see N+1 through an APMAPM (application performance monitoring)A service (Datadog, New Relic, Grafana Tempo, Honeycomb, Sentry…) that records traces of real requests — every handler, database query and outgoing call, with timings — so you can see where time goes in production. or tracing tool. It records each request as a set of timed spansTracing / spanDistributed tracing records the path of one request through your code and services. Each timed step (one middleware, one DB query) is a span, so you can see where the time went., one per database query, and draws them as a waterfall. N+1 has an unmistakable shape: a staircase of short, identical bars, each starting when the previous one ends.

Switch between the modes. The staircase is the loop: N queries, each waiting for the previous one. The batch and JOIN versions collapse the stairs into one or two bars. (The parallel mode looks better than the loop, but it's not a fix; §09 explains why.)

Fix 1: let the database JOIN

The fix

Ask for posts and authors together. The database pairs them up and sends back finished rows:

rows, err := db.QueryContext(ctx, `
    SELECT p.id, p.title, a.name
    FROM posts p
    JOIN authors a ON a.id = p.author_id
    ORDER BY p.created_at DESC
    LIMIT 50`)
if err != nil {
    return nil, err
}
defer rows.Close()

var posts []Post // Post here has an AuthorName string field
for rows.Next() { // one row = one post WITH its author's name
    var p Post
    if err := rows.Scan(&p.ID, &p.Title, &p.AuthorName); err != nil {
        return nil, err
    }
    posts = append(posts, p)
}
return posts, rows.Err()

What the database does with it

Prefix any query with EXPLAIN ANALYZEEXPLAIN / EXPLAIN ANALYZESQL commands that show the planner's chosen plan. EXPLAIN only predicts; EXPLAIN ANALYZE really runs the query and adds actual times and row counts. and Postgres runs it and shows its plan: the steps it took, how many rows each produced, and the time. (Reading plans properly is taught in Indexes; here we only need one line.) This is the plan for the query above, from our test database with 100,000 posts and 1,000 authors:

Limit  (actual time=0.028..0.122 rows=50 loops=1)
  ->  Nested Loop  (actual time=0.027..0.118 rows=50 loops=1)
        ->  Index Scan using posts_created_at_idx on posts p  (… rows=50 loops=1)
        ->  Memoize  (actual time=0.002..0.002 rows=1 loops=50)
              ->  Index Scan using authors_pkey on authors a  (… rows=1 loops=50)
                    Index Cond: (id = p.author_id)
Execution Time: 0.230 ms

Read it from the inside out. Index Scan means "find rows using an indexIndex (database)An extra, sorted data structure the database keeps next to a table so it can find rows by a column without reading the whole table — like the index at the back of a book."; Nested Loop means "for each post, look up its author"; Memoize means "remember authors already found, in case they come up again". Now look at loops=50 on the last line. The database still looks up 50 authors one by one. It's the very same loop as the N+1. The difference is that each lookup happens inside the database, in about 0.002 ms, instead of across the network at ~1 ms. The whole thing took 0.23 ms.

Measured from Go through a connection with a 1 ms round trip (median of 30 runs):

The catch: one row per child

A post has exactly one author, so a posts-JOIN-authors result has one row per post. That's a belongs-toBelongs-to / has-manyThe two common kinds of relation. A post belongs to one author (posts.author_id points at authors.id). An author has many posts. Has-many relations are the ones that multiply rows in a JOIN. relation, and it's where JOIN shines.

Real blogs have more. Our test database has three more tables:

A comment belongs to one post (comments.post_id), so a post has many comments. Tags are different: a post can have many tags and a tag can be on many posts (many-to-many). One tag_id column on posts can't hold that, so a small Join tableJoin tableA table whose rows just connect two other tables, one row per link — order_items(order_id, product_id, qty). It's how relational databases store many-to-many relationships. stores one row per (post, tag) pair.

Now JOIN a has-many relation, posts with their comments. The result can only be flat, so you get one row per comment, and the post's title and body are repeated on every one of them.

Add a second has-many (tags) and it gets much worse: the database returns every combination of comment and tag. This is the Cartesian explosionCartesian explosionWhat happens when one query JOINs two independent has-many relations: every combination becomes a row. A post with 4 comments and 3 tags comes back as 4 × 3 = 12 rows.. Here's post 1 from our test data, which has 4 comments and 3 tags:

SELECT p.id, p.title, c.id AS comment_id, t.name AS tag
FROM (SELECT * FROM posts ORDER BY created_at DESC LIMIT 1) p
LEFT JOIN comments  c  ON c.post_id  = p.id
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags      t  ON t.id       = pt.tag_id;

 id | title  | comment_id |  tag
----+--------+------------+-------
  1 | Post 1 |     120506 | tag16
  1 | Post 1 |     120506 | tag2
  1 | Post 1 |     120506 | tag9
  1 | Post 1 |     220506 | tag16
  1 | Post 1 |     220506 | tag2
  … 7 more rows: every comment repeated once per tag …

For the full 50-post page, the database returned 452 rows instead of 50. Each post's 2.4 KB body was copied into every row: 1.06 MB of bodies instead of about 120 KB. Your code then has to walk the rows and throw the duplicates away. Try it on one post, including what an inner JOIN does when a count drops to 0:

Fix 2: batch with IN / ANY, stitch in memory

This is the most useful fix to know by heart. It works for every kind of relation, even across two different databases, and it's exactly what most ORMs do under the hood when you ask them to eager-load (§07).

The recipe: collect, fetch, index, stitch

First watch it on 6 posts, then read the full code:

1const ids = [...new Set(posts.map((p) => p.author_id))];
2 
3const { rows: authors } = await pool.query(
4  "SELECT id, name FROM authors WHERE id = ANY($1)", [ids]);
5 
6const byId = new Map(authors.map((a) => [a.id, a]));
7 
8for (const post of posts) {
9  post.authorName = byId.get(post.author_id)?.name;
10}

func attachAuthors(ctx context.Context, db *sql.DB, posts []Post) error {
    // 1. Collect each author id once.
    seen := make(map[int64]bool)
    var ids []int64
    for _, p := range posts {
        if !seen[p.AuthorID] {
            seen[p.AuthorID] = true
            ids = append(ids, p.AuthorID)
        }
    }

    // 2. One query for all of them.
    rows, err := db.QueryContext(ctx,
        `SELECT id, name FROM authors WHERE id = ANY($1)`, ids)
    if err != nil {
        return err
    }
    defer rows.Close()

    // 3. Index the answers by id…
    names := make(map[int64]string, len(ids))
    for rows.Next() {
        var id int64
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            return err
        }
        names[id] = name
    }
    if err := rows.Err(); err != nil {
        return err
    }

    // 4. …and stitch them onto the posts, in memory.
    for i := range posts {
        posts[i].AuthorName = names[posts[i].AuthorID]
    }
    return nil
}

posts here comes from the plain list query (SELECT id, title, author_id FROM posts …), so the whole page is 2 queries. Our test Postgres used the primary-key index for the batch, exactly as it would for one id:

Index Scan using authors_pkey on authors  (actual time=0.039..0.045 rows=10 loops=1)
  Index Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::bigint[]))
Execution Time: 0.073 ms

Ten authors in 0.073 ms of database time, versus 0.059 ms for one. The batch costs almost nothing extra; the round trips you saved were the whole cost. Measured over a 1 ms connection, the 50-post page went from 55 ms (51 queries) to 2.7 ms (2 queries).

Has-many: group instead of index

For comments, each post has a list. Same recipe, but the map goes from post id to a slice/array:

rows, err := db.QueryContext(ctx,
    `SELECT post_id, id, body FROM comments WHERE post_id = ANY($1) ORDER BY id`, postIDs)
// … if err != nil, defer rows.Close() as before …

byPost := make(map[int64][]Comment) // post id → its comments
for rows.Next() {
    var postID int64
    var c Comment
    if err := rows.Scan(&postID, &c.ID, &c.Body); err != nil {
        return err
    }
    byPost[postID] = append(byPost[postID], c) // append works on a missing (nil) entry
}
// … check rows.Err() …
for i := range posts {
    posts[i].Comments = byPost[posts[i].ID]
}

Do the same for tags and the full page (posts + authors + comments + tags) is 4 queries, no duplicated rows, whatever N is. On our test setup at 1 ms per round trip, the N+1 version of that page ran 151 queries in 176 ms. The batched version took 5.2 ms.

Limits worth knowing

• Very long lists. IN ($1, $2, …) with one placeholder per id hits protocol limits: Postgres allows at most 65,535 parameters per statement. One array parameter with ANY($1) avoids that, but a list of 100,000 ids is still a huge query. If your N can be that big, process the ids in chunks of a few thousand, or rethink the page (nobody reads 100,000 posts).
• Order isn't guaranteed. WHERE id = ANY($1) returns rows in whatever order the database likes, not the order of your ids. That's why we stitch through a map instead of assuming row 1 belongs to post 1.
• Missing rows. If an author was deleted, the map just has no entry. Decide what the page shows ("unknown author"?) instead of crashing on undefined.

Fix 3: eager loading, and nested JSON from Postgres

Eager loading: tell the ORM up front

With Eager loadingEager loading (ORM)Telling the ORM up front which related data you'll need (Prisma include, GORM Preload), so it fetches it for the whole list at once — usually one extra query per relation, or a JOIN., you list the relations you'll need when you fetch the list, and the ORM loads them for all rows at once. These are the real statements our test database received (3 posts, so the lists are short):

// Preload: one extra query per relation
db.Preload("Author").Order("created_at DESC").Limit(3).Find(&posts)
// SELECT * FROM "posts" ORDER BY created_at DESC LIMIT 3
// SELECT * FROM "authors" WHERE "authors"."id" IN (920,839,758)

// Joins: a single LEFT JOIN (belongs-to / has-one only)
db.Joins("Author").Order("posts.created_at DESC").Limit(3).Find(&posts)
// SELECT "posts"."id", …, "Author"."name" AS "Author__name"
// FROM "posts" LEFT JOIN "authors" "Author" ON "posts"."author_id" = "Author"."id"
// ORDER BY posts.created_at DESC LIMIT 3

(Prisma always adds OFFSET to its queries, here with the value 0, which skips nothing; ignore it.) So Preload and include are §06's recipe, automated: one query for the list, then one IN (…) query per relation, stitched together in memory. That's 3 queries whatever N is, and because each table comes back separately there's no Cartesian explosionCartesian explosionWhat happens when one query JOINs two independent has-many relations: every combination becomes a row. A post with 4 comments and 3 tags comes back as 4 × 3 = 12 rows.. GORM's Joins is the §05 JOIN instead; GORM only allows it for to-one relations, where JOIN is the right tool.

Nested JSON straight from Postgres

The page doesn't want flat rows at all. It wants posts, each holding a list of comments and a list of tags: a tree. Postgres can build that tree itself. json_aggjson_aggA Postgres function that collects many rows into one JSON array. Used in a subquery, it returns each post with its comments already nested: [{"id":1,…},{…}]. collects rows into one JSON array, and a small SELECT inside the main one (a Correlated subqueryCorrelated subqueryA small SELECT inside a bigger one that refers to the outer row — (SELECT count(*) FROM comments c WHERE c.post_id = p.id) — so it is evaluated once per outer row, inside the database.) does it once per post:

SELECT p.id, p.title, a.name AS author,
  (SELECT coalesce(json_agg(json_build_object('id', c.id, 'body', c.body) ORDER BY c.id), '[]')
     FROM comments c WHERE c.post_id = p.id) AS comments,
  (SELECT coalesce(json_agg(t.name ORDER BY t.name), '[]')
     FROM post_tags pt JOIN tags t ON t.id = pt.tag_id
    WHERE pt.post_id = p.id) AS tags
FROM posts p
JOIN authors a ON a.id = p.author_id
ORDER BY p.created_at DESC
LIMIT 50;

One row per post, children already nested. This is real output (two posts shown, comments trimmed to fit):

 id | title  |   author   |                       comments                        |          tags
----+--------+------------+-------------------------------------------------------+-------------------------
  1 | Post 1 | Author 920 | [{"id" : 120506, "body" : "Comment 100000"}, … 3 more] | ["tag16", "tag2", "tag9"]
  2 | Post 2 | Author 839 | [{"id" : 115875, "body" : "Comment 95369"}, … 3 more]  | ["tag10", "tag3"]

Reading it back is easy in both languages. node-postgres turns json columns into JS objects automatically. In Go, wrap the whole query in one more json_agg so the page arrives as a single JSON array, then decode it into structs:

type CommentView struct {
    ID   int64  `json:"id"`
    Body string `json:"body"`
}
type PostView struct {
    ID       int64         `json:"id"`
    Title    string        `json:"title"`
    Author   string        `json:"author"`
    Comments []CommentView `json:"comments"`
    Tags     []string      `json:"tags"`
}

// pageQuery = `SELECT coalesce(json_agg(x ORDER BY x.created_at DESC), '[]')
//              FROM ( …query above, plus p.created_at… ) x`
// (the ORDER BY inside json_agg keeps the posts in page order)
var raw []byte // the whole page, as one JSON array
if err := db.QueryRowContext(ctx, pageQuery).Scan(&raw); err != nil {
    return nil, err
}
var page []PostView
if err := json.Unmarshal(raw, &page); err != nil {
    return nil, err
}

Which shape when?

Flip through the four result shapes for the same tiny data set:

SELECT p.id, p.title, a.name, c.id, c.body
FROM posts p JOIN authors a ON a.id = p.author_id
LEFT JOIN comments c ON c.post_id = p.id

Here are all strategies on our test database, for the full page (50 posts with author, comments and tags), measured from Go over a connection with a 1 ms round trip (median of 20 runs):

Over a 1 ms round trip, the three fixes are all within a millisecond of each other, and all about 35–40× faster than the N+1. On the same machine (almost no round trip), the batch version was the fastest (0.9 ms vs 3.9 ms for json_agg), because building JSON is real work for the database.

Fix 4: DataLoader, batching you don't have to plan

Where §06 doesn't fit: GraphQL resolvers

The batch recipe needs one place that sees the whole list. Sometimes there isn't one. The classic case is GraphQLGraphQLAn API style where the client sends a query describing exactly which fields and nested objects it wants (posts → author → name), and the server assembles the answer by calling a resolver function per field.. The client sends a query describing the shape it wants:

{
  posts(first: 50) {
    title
    author { name }
  }
}

The server answers it by calling a resolverResolver (GraphQL)The function a GraphQL server calls to produce one field's value — e.g. Post.author(post) returns that post's author. It runs once per object in a list, which is why naive resolvers cause N+1. function for each field of each object. Query.posts runs once and returns 50 posts. Then Post.author runs once per post, and each call only knows about its own post:

const resolvers = {
  Query: {
    posts: async () => (await db.query("SELECT * FROM posts ORDER BY created_at DESC LIMIT 50")).rows,
  },
  Post: {
    // called 50 times, once per post; each call can only see its own post
    author: async (post) =>
      (await db.query("SELECT * FROM authors WHERE id = $1", [post.author_id])).rows[0],
  },
};

That's an N+1 built into the architecture, and nested fields make it worse (each post's comments, each comment's author…). The same happens in any code split into small independent functions: React Server Components, serializers, "fetch what you need" helpers.

The idea: collect calls made in the same tick

DataLoaderDataLoaderA small utility (the npm package dataloader, and ports in other languages) that collects many load(id) calls made at about the same time into one batched fetch, and caches the results for the rest of the request. (the npm package dataloader, originally from Facebook's GraphQL team) works like a waiter taking orders for a table. Each person orders separately, but the waiter doesn't run to the kitchen after every order. They collect everyone's order and go once.

You give it one batch function: "given these ids, return their values". Resolvers call loader.load(id) instead of querying:

import DataLoader from "dataloader";

function makeLoaders(db) {
  return {
    author: new DataLoader(async (ids) => {
      const { rows } = await db.query(
        "SELECT id, name FROM authors WHERE id = ANY($1)", [ids]);
      const byId = new Map(rows.map((a) => [a.id, a]));
      // MUST return one result per id, in the same order as `ids`
      return ids.map((id) => byId.get(id) ?? new Error(`no author ${id}`));
    }),
  };
}

// The resolver no longer queries; it asks the loader:
const resolvers = {
  Post: { author: (post, _args, ctx) => ctx.loaders.author.load(post.author_id) },
};
// …and every request gets FRESH loaders, e.g. in your GraphQL context function:
// context: () => ({ loaders: makeLoaders(db) })

How does it know which calls belong together? JavaScript runs one piece of code at a time. When load is called, the loader adds the id to the current batch and schedules the dispatch to run right after the currently running code and its queued promise callbacks have finished (built on the MicrotaskMicrotaskA tiny job — like a promise's .then callback or the code after an await — that JS runs right after the current code finishes, before timers, clicks or I/O. queue, see the event loop). The GraphQL server calls all 50 Post.author resolvers in one go, so all 50 load calls land in the same batch. Step through it:

1const authorLoader = new DataLoader(async (ids) => {
2  const { rows } = await db.query(
3    "SELECT id, name FROM authors WHERE id = ANY($1)", [ids]);
4  const byId = new Map(rows.map((a) => [a.id, a]));
5  return ids.map((id) => byId.get(id) ?? new Error(id));
6});
7 
8// three resolvers run during the same tick:
9const a = authorLoader.load("920"); // post 1
10const b = authorLoader.load("839"); // post 2
11const c = authorLoader.load("920"); // post 3
12await Promise.all([a, b, c]);
13 
14await authorLoader.load("839"); // later

We ran this against our test database: 6 resolvers asking for authors produced one query, WHERE id = ANY('{920,839,758,677,596,515}'). Asking again for an id already loaded produced no query at all.

The three rules of DataLoader

Go: a time window instead of a tick

Go has no "tick". GoroutinesGoroutineA very lightweight task in Go, started with the go keyword. The Go runtime runs thousands of them on just a few OS threads. run truly at the same time, so there's no moment when "everything currently running has finished". A Go loader uses a short time window instead: the first Load opens a batch and starts a timer (say 1 ms), every Load that arrives before it fires joins, then one query runs.

Here's a minimal version, complete except for fetchAuthors, which is §06's WHERE id = ANY($1) query returning a map[int64]Author. (We ran it with Go's race detector: 50 goroutines, 1 query. When goroutines start slowly, a batch can split in two because some calls arrive after the window closed. That's the trade-off of a window.)

// AuthorLoader collects Load calls for a short window, then runs ONE query.
type AuthorLoader struct {
    db    *sql.DB
    wait  time.Duration // how long a batch collects ids, e.g. time.Millisecond
    mu    sync.Mutex
    batch *authorBatch // the batch currently collecting; nil = none
}

type authorBatch struct {
    ids  []int64
    done chan struct{} // closed when res/err are ready
    res  map[int64]Author
    err  error
}

func (l *AuthorLoader) Load(ctx context.Context, id int64) (Author, error) {
    l.mu.Lock()
    b := l.batch
    if b == nil { // first caller opens a new batch and starts its timer
        b = &authorBatch{done: make(chan struct{})}
        l.batch = b
        time.AfterFunc(l.wait, func() { l.fire(b) })
    }
    b.ids = append(b.ids, id)
    l.mu.Unlock()

    select { // wait for the batch's single query (or give up)
    case <-b.done:
    case <-ctx.Done():
        return Author{}, ctx.Err()
    }
    if b.err != nil {
        return Author{}, b.err
    }
    a, ok := b.res[id]
    if !ok {
        return Author{}, fmt.Errorf("author %d not found", id)
    }
    return a, nil
}

func (l *AuthorLoader) fire(b *authorBatch) {
    l.mu.Lock()
    l.batch = nil // later Loads start a fresh batch; b.ids is now final
    l.mu.Unlock()
    b.res, b.err = fetchAuthors(context.Background(), l.db, b.ids) // WHERE id = ANY($1)
    close(b.done) // wakes every waiting Load at once
}

This toy has no cache and no maximum batch size; production libraries add both. In Go, look at graph-gophers/dataloader (v7 uses generics) or vikstrous/dataloadgen, which the gqlgen GraphQL docs point to. Whatever you use, the window is a trade-off: every request waits up to that long before its first batch runs, in exchange for far fewer queries.

Running the queries in parallel is not a fix

The tempting "fix"

Once you know the loop waits for each query in turn, the obvious idea is to stop waiting:

var wg sync.WaitGroup
for i := range posts {
    wg.Add(1)
    go func() {
        defer wg.Done()
        // each goroutine fills in a different post, so they don't clash
        err := db.QueryRowContext(ctx, `SELECT name FROM authors WHERE id = $1`,
            posts[i].AuthorID).Scan(&posts[i].AuthorName)
        _ = err // (real code must collect errors, e.g. with errgroup)
    }()
}
wg.Wait() // still 50 queries, just not one after another

And it does look faster. Measured from Node with a Connection poolConnection poolA set of open database (or HTTP) connections that your program keeps and reuses instead of opening a new one per request. Pools usually have a size limit; when every connection is busy, the next caller waits for one to be returned. of 10 connections and a 1 ms round trip (median of 20 runs):

(These are Node numbers; node-postgres adds a little overhead per query, which is why its loop is slower than the 55 ms Go loop in §05.)

Watch the timeline. With only 4 connections, the queries that can't get one wait in line (striped):

Why it falls over under load

That 12 ms is what you get with one user. Here's the step people miss: running queries in parallel doesn't reduce how long connections are busy, it just squeezes the same busy time into a shorter period.

Do the arithmetic for a pool of 10 connections. Each query keeps a connection busy for about 1 ms, round trip included.

• N+1, loop or parallel: 51 queries × 1 ms = 51 connection-milliseconds per page. The pool has 10 connections × 1,000 ms = 10,000 connection-ms per second, so it can serve at most about 200 pages per second. Beyond that, requests queue for a connection and everyone gets slower.
• Batched: 2 queries × 1 ms = 2 connection-ms per page, or about 5,000 pages per second from the same pool.

Parallelism only changes who waits. When traffic is low, one page uses 10 connections and finishes sooner. When traffic is high, each page's 50 queries compete with every other page's queries for the same 10 connections, and the pool's waiting line grows. That's exactly the "start everything at once" failure from Worker Pools. Push the traffic past 200 pages per second and watch:

Pitfalls gallery: N+1's relatives

// postIDs []int64 and viewers []string: one entry per row
_, err := db.ExecContext(ctx, `
    INSERT INTO views (post_id, viewer)
    SELECT * FROM unnest($1::bigint[], $2::text[])`,
    postIDs, viewers) // ← two arrays, one round trip

SELECT post_id, count(*) AS comments
FROM comments
WHERE post_id = ANY($1)
GROUP BY post_id;   -- one row per post: (post_id, how many comments)

Keeping it fixed: tests that count queries

The trick is the same in every language: route all queries through something that counts them, run the code, and assert on the count. Run it against a real test database (an Integration testIntegration testA test that runs your code together with real pieces — a real database in Docker, a real HTTP server — to check they work together. Slower than a unit test.), since a fake database wouldn't run the real queries.

Go: count with a pgx tracer

pgx lets you attach a tracer to a connection config: an object that pgx calls at the start and end of every query. Ours just adds one to a counter:

// queryCounter counts every SQL statement pgx sends.
type queryCounter struct{ n atomic.Int64 }

func (c *queryCounter) TraceQueryStart(ctx context.Context, _ *pgx.Conn, _ pgx.TraceQueryStartData) context.Context {
    c.n.Add(1)
    return ctx
}
func (c *queryCounter) TraceQueryEnd(context.Context, *pgx.Conn, pgx.TraceQueryEndData) {}

func TestListPostsQueryCount(t *testing.T) {
    cfg, err := pgx.ParseConfig(os.Getenv("TEST_DATABASE_URL"))
    if err != nil {
        t.Fatal(err)
    }
    counter := &queryCounter{}
    cfg.Tracer = counter // ← every query now passes through the counter
    db := stdlib.OpenDB(*cfg) // a normal *sql.DB on top of pgx
    defer db.Close()

    before := counter.n.Load()
    if _, err := ListPosts(context.Background(), db, 50); err != nil {
        t.Fatal(err)
    }
    if got := counter.n.Load() - before; got > 2 {
        t.Fatalf("ListPosts ran %d queries for 50 posts, want at most 2 (N+1?)", got)
    }
}

Pointed at an N+1 ListPosts (a plain database/sql version of the §03 loop: one SELECT name FROM authors WHERE id = $1 per post), the test fails with exactly the message you want to see in CICI (continuous integration)A server that builds your code and runs its tests automatically on every push (GitHub Actions, GitLab CI, …), so mistakes are caught minutes after they're made. A flaky test there blocks everyone's merges.:

--- FAIL: TestListPostsQueryCount (0.04s)
    posts_test.go:37: ListPosts ran 51 queries for 50 posts, want at most 2 (N+1?)
FAIL

JavaScript: wrap the pool, or listen to Prisma

import { test } from "node:test";
import assert from "node:assert/strict";
import pg from "pg";
import { listPosts } from "./posts.js";

// Wrap a pool so it counts every query that goes through it.
function countingPool(pool) {
  const counter = { n: 0 };
  const db = {
    query: (...args) => { counter.n++; return pool.query(...args); },
  };
  return { db, counter };
}

test("listPosts does not run one query per post", async (t) => {
  const pool = new pg.Pool({ connectionString: process.env.TEST_DATABASE_URL });
  t.after(() => pool.end());
  const { db, counter } = countingPool(pool);

  await listPosts(db, 50);

  assert.ok(counter.n <= 2, `listPosts ran ${counter.n} queries for 50 posts (N+1?)`);
});

Run against the N+1 version, node's test runner reports error: 'listPosts ran 51 queries for 50 posts (N+1?)'. With Prisma, the loop version logged 6 queries for 5 posts and the include version 2.

Make the test about growth, not a magic number

"At most 2" breaks the day someone legitimately adds a third query. A sturdier test checks that the count doesn't depend on N: load a page with 2 posts, then with 20, and assert both ran the same number of queries. An N+1 fails that test whatever the exact numbers are.

Staff-level: when it's fine, caches, APIs and services

When N+1 is fine

The cost is N round trips, so the question is always: how big can N get, and how far away is the other side?

• N is small and bounded by your design, not your data. A settings page with 3 sections, a dashboard with 4 fixed widgets. Three extra 1 ms queries won't show up anywhere, and the simple code is worth more.
• Each "query" isn't a round trip. Lookups served from an in-memory cache in your own process cost microseconds. (A cache on another machine, like Redis, doesn't count; see below.)
• The page isn't hot. An internal admin report run twice a day can afford 200 ms.

The dangerous kind is the N that grows with your data: posts per page, items per order, projects per customer.

Caches are a band-aid

Putting each author lookup behind a CacheCacheA small, fast store of answers you already computed or fetched, so the next request for the same thing skips the slow work. The hard part is knowing when a cached answer is out of date. (say, Redis) feels like a fix. But if Redis is on another machine, you've swapped 50 database round trips for 50 cache round trips, each still around 0.2–1 ms. Faster, still a staircase. And now there's a second copy of the data that can go stale. If you do cache, batch the cache reads too: Redis MGET fetches many keys in one round trip, exactly like WHERE id = ANY. Fix the access pattern first; cache second. (More in Caching.)

N+1 over HTTP: the chatty API

The same shape between a client and your API costs far more per trip. Picture a mobile app showing 20 posts: GET /posts, then GET /authors/920, GET /authors/839… On a phone network a round trip is easily 50–100 ms (approximate; it varies widely).

Ways to give the client the whole screen in one trip:

• Embed related data: GET /posts?include=author returns each post with its author inside. The server does the batching (§06) close to the database, where round trips are cheap.
• Batch endpoints: GET /authors?ids=920,839,758 so a client that must fetch separately can at least fetch in one call.
• A backend for frontendBackend for frontend (BFF)A thin server built for one client (the web app, the iOS app) that calls the other services and returns exactly what each screen needs in one response, so the client doesn't make dozens of calls.: a thin server per client app that assembles each screen's data from your services and returns it in one response.
• GraphQL with DataLoaders (§08): the client describes the whole tree; the server batches each level.

These are API design choices, so they're much cheaper to make before clients depend on the chatty version; see API Design and HTTP for the request costs involved.

N+1 between microservices

Split a system into microservicesMicroserviceA small, separately deployed program that owns one part of a bigger system and talks to the others over the network (HTTP, gRPC, a message queue). and the database JOIN that used to take 0.2 ms becomes a network call to another service. An orders page that calls the users service once per order is an N+1 over HTTP: each call costs a few milliseconds (connection, JSON encoding and decoding, the other service's own database query), so 50 orders add 100–250 ms. Worse, it multiplies load: one page view becomes 50 requests to the users service, and if those calls retry on timeouts, a slow users service gets hit even harder.

The fixes are the same ideas one level up: batch endpoints (POST /users:batchGet with a list of ids; the colon is a Google-API naming style for "a custom method", and POST /users/batch works just as well), a DataLoader-style batcher in the calling service, or keeping a local, read-only copy of the few user fields the orders service needs.

Check yourself

Predict the answer before you click. Q1 is covered in §03, Q2 in §09, Q3 in §08, Q4 in §05, Q5 in §07 and §10, Q6 in §12.

// 20 orders, each with 3 line items; every item has a product
const orders = await Order.findAll({ limit: 20 });       // lazy-loading ORM
for (const order of orders) {
  for (const item of await order.items) {
    console.log((await item.product).name);
  }
}

const loader = new DataLoader(async (ids) => {
  console.log("batch", ids);
  return ids.map((id) => ({ id }));
});
for (const id of ["a", "b", "c"]) {
  await loader.load(id);
}

// GORM
var posts []Post
db.Preload("Author").Limit(50).Find(&posts)
for _, p := range posts {
    var n int64
    db.Model(&Comment{}).Where("post_id = ?", p.ID).Count(&n)
    fmt.Println(p.Title, p.Author.Name, n)
}

Cheat sheet