N+1 Query #

The N+1 query problem is one of the most common performance problems in web applications using relational databases, and also one of the most often undetected until it’s too late. The code looks clean, unit tests are green, and in a development environment with little data everything is fast. But when data grows and traffic rises in production, pages that used to feel instant start feeling slow — and when debugged, it turns out one API request produces hundreds of database queries. This article covers why N+1 happens, how to detect it, and four different solution approaches for different situations.

What Is an N+1 Query? #

An N+1 query happens when an application runs one main query to fetch N items, then runs one additional query per item to fetch related data — producing N+1 total queries for what should have been solvable with 1 or 2 queries.

sequenceDiagram
    participant App as Application
    participant DB as Database

    Note over App,DB: N+1 Query — for 100 articles

    App->>DB: SELECT * FROM articles LIMIT 100
    DB-->>App: 100 articles

    loop For each of the 100 articles
        App->>DB: SELECT * FROM users WHERE id = ?
        DB-->>App: 1 user
    end

    Note over App,DB: Total: 1 + 100 = 101 queries

    Note over App,DB: What should happen: only 2 queries

    App->>DB: SELECT * FROM articles LIMIT 100
    DB-->>App: 100 articles

    App->>DB: SELECT * FROM users WHERE id IN (1,2,3,...,50)
    DB-->>App: all users at once

This number may not look big for 100 items. But the problem is linear: 1000 items = 1001 queries, 10,000 items = 10,001 queries. And every query has overhead: network round-trips to the database, SQL parsing, execution, sending results back.

Why N+1 Happens #

Invisible ORM Lazy Loading #

Most ORMs — GORM, ActiveRecord, Hibernate, SQLAlchemy, Sequelize — use lazy loading by default or easily fall into the N+1 pattern. The code looks clean and simple, but hides expensive behavior.

// GORM example producing N+1
articles, _ := db.Find(&[]Article{})
for _, article := range articles {
    fmt.Println(article.Author.Name)  // ← every .Author access triggers a new query!
}

// SQL logs hidden behind this "clean" code:
// SELECT * FROM articles;
// SELECT * FROM users WHERE id = 1;
// SELECT * FROM users WHERE id = 5;
// SELECT * FROM users WHERE id = 5;  ← the same ID can be queried again!
// SELECT * FROM users WHERE id = 2;
// ... 100 more queries

// The same example with the GORM ORM
articles, _ := db.Find(&[]Article{})
for _, article := range articles {
    fmt.Println(article.Author.Name)  // ← implicit database query per article
}
// Result: 1 + N queries

Invisible in Development #

N+1 is almost undetectable in a development environment because:

Development vs Production — why N+1 stays hidden:

Development:
  Data: 10-50 rows in every table
  N+1 on 10 articles = 11 queries → response time: ~30ms → "it's fast"
  Developers don't actively watch SQL logs

Production:
  Data: 50,000 rows in the articles table
  N+1 with 50-item pagination = 51 queries
  But every query needs an index scan on a large table...
  → response time: 2-5 seconds → user complaints

The trap: N+1 is a problem that's O(n) against the amount of DATA,
          not O(n) against the number of requests.

The Real Impact in Production #

Before discussing solutions, it’s important to understand the quantitative impact of N+1.

N+1 impact calculation:

A product list page with 50 items per page:
  With N+1: 1 query (products) + 50 queries (category) + 50 queries (brand) = 101 queries
  Without N+1:  1 query (products + JOIN category + brand) = 1 query

If one query = 2ms (conservative estimate):
  With N+1: 101 × 2ms = 202ms for the database alone
  Without N+1:  1 × 5ms = 5ms (JOINs are more complex but still far faster)

If there are 100 concurrent users on the same page:
  With N+1: 100 × 101 = 10,100 database queries per second
  Without N+1:  100 × 1 = 100 database queries per second

Database connection pool (e.g. limit 100):
  With N+1: 100 concurrent users × 101 queries = pool exhausted → queue → timeouts
  Without N+1:  100 concurrent users × 1 query = pool still safe

How to Detect N+1 #

SQL Logging in Development #

Enable SQL logging and watch the patterns while developing new features. Clear N+1 signs: the same query (with different parameters) appearing repeatedly.

// Enable SQL logging in GORM
db, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
})

// Output showing N+1:
// [2.031ms] SELECT * FROM "articles" ORDER BY created_at DESC LIMIT 50
// [0.823ms] SELECT * FROM "users" WHERE "users"."id" = 5
// [0.791ms] SELECT * FROM "users" WHERE "users"."id" = 12
// [0.808ms] SELECT * FROM "users" WHERE "users"."id" = 5  ← DUPLICATE!
// [0.815ms] SELECT * FROM "users" WHERE "users"."id" = 7
// ... 46 more queries
// Total: 51 queries to display 50 articles

Counting Total Queries per Request #

A simple way to detect N+1 programmatically:

// Middleware to count queries per request
type QueryCounter struct {
    db    *gorm.DB
    count int
}

func (qc *QueryCounter) Middleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        qc.count = 0
        // Register a callback to count queries
        qc.db.Callback().Query().After("gorm:query").Register("count_queries",
            func(db *gorm.DB) { qc.count++ })

        next.ServeHTTP(w, r)

        if qc.count > 10 {  // the threshold you define
            log.Warnf("High query count: %d queries for %s %s",
                qc.count, r.Method, r.URL.Path)
        }
    })
}

Use APM or Query Profilers #

For production, tools like Datadog APM, New Relic, or pgBadger (for PostgreSQL) can show the query count distribution per request endpoint, making it easy to identify endpoints with N+1.

PostgreSQL has the pg_stat_statements extension that records statistics for all executed queries, including how often the same query runs. This is very useful for detecting N+1 in production: a SELECT * FROM users WHERE id = $1 query executed 10,000 times in one minute is a clear red flag.

Four N+1 Query Solutions #

Solution 1: Eager Loading with JOINs #

The most direct solution: fetch the parent data and its relations in one query using a JOIN.

-- ANTI-PATTERN: N+1
SELECT * FROM articles LIMIT 50;
SELECT * FROM users WHERE id = ?;  -- ×50

-- CORRECT: JOIN in one query
SELECT
    a.id, a.title, a.content, a.published_at,
    u.id AS author_id, u.name AS author_name, u.avatar_url
FROM articles a
JOIN users u ON u.id = a.author_id
ORDER BY a.published_at DESC
LIMIT 50;
-- 1 query, all data available

// GORM with eager loading
var articles []Article
db.Preload("Author").
   Preload("Tags").
   Preload("Category").
   Find(&articles)
// GORM uses an efficient strategy:
// 1 query for articles
// 1 IN query for all needed authors
// 1 IN query for all tags
// Total: 3-4 queries, not 1 + 3N queries

When to use JOIN vs Preload:

• Use JOIN if you need to filter based on relation data (WHERE author.role = 'admin')
• Use Preload if you only need the relations for display without filtering

Solution 2: Batch Queries with IN Clauses #

If you’re not using an ORM or need more control, implement batching yourself.

// ANTI-PATTERN: Query per item
func GetArticlesWithAuthors(articleIDs []string) ([]ArticleWithAuthor, error) {
    var result []ArticleWithAuthor
    for _, id := range articleIDs {
        var article Article
        db.Where("id = ?", id).First(&article)

        var author User
        db.Where("id = ?", article.AuthorID).First(&author)  // ← N+1

        result = append(result, ArticleWithAuthor{Article: article, Author: author})
    }
    return result, nil
}

// CORRECT: Batch query with IN
func GetArticlesWithAuthors(articleIDs []string) ([]ArticleWithAuthor, error) {
    // Fetch all articles at once
    var articles []Article
    db.Where("id IN ?", articleIDs).Find(&articles)

    // Collect all unique author IDs
    authorIDSet := make(map[string]bool)
    for _, a := range articles {
        authorIDSet[a.AuthorID] = true
    }
    authorIDs := maps.Keys(authorIDSet)

    // Fetch all authors at once
    var authors []User
    db.Where("id IN ?", authorIDs).Find(&authors)

    // Build a map for efficient lookup
    authorMap := make(map[string]User)
    for _, u := range authors {
        authorMap[u.ID] = u
    }

    // Merge the results
    var result []ArticleWithAuthor
    for _, a := range articles {
        result = append(result, ArticleWithAuthor{
            Article: a,
            Author:  authorMap[a.AuthorID],
        })
    }
    return result, nil
}
// Total: 2 queries, no matter how many articles

Solution 3: DTO Projection Queries #

Instead of fetching full objects and then accessing their fields, use SELECTs specific to only the needed fields. This avoids N+1 while also reducing data transfer.

// ANTI-PATTERN: Load full objects then access fields
articles, _ := db.Find(&[]Article{})
for _, a := range articles {
    // For an API response only needing title and author name,
    // we load every article field and every user field
    response = append(response, map[string]interface{}{
        "title":  a.Title,
        "author": a.Author.Name,  // ← triggers N+1
    })
}

// CORRECT: Targeted projection query
type ArticleListItem struct {
    ID          string `json:"id"`
    Title       string `json:"title"`
    AuthorName  string `json:"authorName"`
    PublishedAt string `json:"publishedAt"`
}

var items []ArticleListItem
db.Table("articles a").
    Select("a.id, a.title, a.published_at, u.name as author_name").
    Joins("JOIN users u ON u.id = a.author_id").
    Order("a.published_at DESC").
    Limit(50).
    Scan(&items)
// 1 query, only the needed fields, no N+1

Projection queries are very effective for list endpoints where views usually only need a small data subset. No need to load entire relations when only the author’s name is needed.

Solution 4: The DataLoader Pattern #

DataLoader is a pattern developed by Facebook to solve N+1 in GraphQL resolvers, but its principles apply anywhere. It does batching and caching automatically.

// DataLoader: collect all requests within one event loop tick,
// then execute one batch query

type UserLoader struct {
    mu      sync.Mutex
    pending []string         // collection of requested user IDs
    result  map[string]User  // result cache
    once    sync.Once
}

// Load requests one user — doesn't query the database immediately
func (l *UserLoader) Load(userID string) (*User, error) {
    l.mu.Lock()
    l.pending = append(l.pending, userID)
    l.mu.Unlock()

    // Wait one event loop tick, then flush all pending requests
    time.AfterFunc(1*time.Millisecond, l.flush)

    // Wait for the result
    return l.result[userID], nil
}

// Flush executes one batch query for all pending IDs
func (l *UserLoader) flush() {
    l.mu.Lock()
    ids := l.pending
    l.pending = nil
    l.mu.Unlock()

    // One query for all IDs
    var users []User
    db.Where("id IN ?", ids).Find(&users)

    for _, u := range users {
        l.result[u.ID] = u
    }
}

DataLoader is most useful in GraphQL resolvers (already covered in the GraphQL article), but the batching and per-request caching concepts apply to any context.

N+1 Beyond ORMs — Often-Missed Patterns #

N+1 doesn’t only happen through ORMs. Similar patterns can appear in code that doesn’t use an ORM at all.

// N+1 in a service layer without an ORM
func (s *OrderService) GetOrdersWithDetails(userID string) ([]OrderDetail, error) {
    orders, _ := s.orderRepo.GetByUserID(userID)

    var details []OrderDetail
    for _, order := range orders {
        // Fetch the product for every order item
        for _, item := range order.Items {
            product, _ := s.productRepo.GetByID(item.ProductID)  // ← N+1!
            item.Product = product
        }
        details = append(details, OrderDetail{Order: order})
    }
    return details, nil
}

// Solution: Collect all product IDs, fetch them at once
func (s *OrderService) GetOrdersWithDetails(userID string) ([]OrderDetail, error) {
    orders, _ := s.orderRepo.GetByUserID(userID)

    // Collect all product IDs from all order items
    productIDs := make([]string, 0)
    for _, order := range orders {
        for _, item := range order.Items {
            productIDs = append(productIDs, item.ProductID)
        }
    }

    // Batch query — one query for all products
    products, _ := s.productRepo.GetByIDs(productIDs)
    productMap := make(map[string]Product)
    for _, p := range products {
        productMap[p.ID] = p
    }

    // Merge
    for i, order := range orders {
        for j, item := range order.Items {
            orders[i].Items[j].Product = productMap[item.ProductID]
        }
    }
    return toDetails(orders), nil
}

How to Prevent N+1 in Code Reviews #

Code reviews are the best opportunity to catch N+1 before it reaches production. Here are the questions to ask during reviews:

Questions to answer for every PR touching data access:

1. "Is there a loop with database access inside it?"
   → Look at every for loop, each(), map() that calls repositories or ORMs

2. "If there were 1000 items, how many queries would that produce?"
   → Calculate mentally: 1 main query + N queries per item = problem

3. "Are the accessed relations already preloaded?"
   → If there's `article.Author.Name` but no Preload("Author"), alarm!

4. "Is there SQL log evidence for this endpoint?"
   → Ask the developer to attach SQL logs from the development environment

5. "Is there a test checking the query count?"
   → Verify that efficient behavior is maintained as code changes

// Test verifying the query count
func TestGetArticles_QueryCount(t *testing.T) {
    db := setupTestDB()

    // Seed test data
    for i := 0; i < 20; i++ {
        createTestArticleWithAuthor(db, i)
    }

    // Count the executed queries
    queryCount := 0
    db.Callback().Query().After("gorm:query").Register("test_counter",
        func(db *gorm.DB) { queryCount++ })

    repo := NewArticleRepository(db)
    articles, err := repo.ListWithAuthors(ctx, 20)
    assert.NoError(t, err)
    assert.Len(t, articles, 20)

    // Verify no N+1: must be ≤ 3 queries (articles + authors + tags)
    assert.LessOrEqual(t, queryCount, 3,
        "Expected at most 3 queries, got %d — possible N+1", queryCount)
}

N+1 already in production is very hard to fix without regression risk because query behavior changes can affect displayed data. Detection in code reviews or development is far cheaper than production fixes. Make “is there an N+1?” a standard question on every PR touching data access.

Solution Comparison #

Solution         When to Use                              Trade-off
─────────────────────────────────────────────────────────────────────
Eager Loading    Relations always needed with the parent  Can over-fetch if relations aren't always needed
JOIN Queries     Need filters based on relations          More complex queries, but very efficient
Batch Queries    More control over fetch strategies       More code but flexible
DTO Projections  List endpoints needing field subsets     Best performance, but more verbose
DataLoader       GraphQL resolvers, async contexts        Complex but the most flexible for dynamic queries
Cache            Relation data rarely changes             Cache invalidation complexity, a supplementary solution not the main one

N+1 Query Checklist #

DEVELOPMENT:
  □ SQL logging enabled during development
  □ Every database-accessing loop suspected and examined
  □ Total query counts checked for every endpoint built

CODE REVIEW:
  □ Ask "how many queries with 1000 items?" on every data access PR
  □ Verify accessed relations are already eager-loaded
  □ Request SQL logs as evidence when in doubt

TESTING:
  □ Query-counting tests exist for critical endpoints
  □ Tests run with datasets larger than the minimum
  □ Query strategy changes update the query count assertions

PRODUCTION MONITORING:
  □ pg_stat_statements or equivalent enabled
  □ Alerts for queries executed too often (> threshold per minute)
  □ APM shows query counts per request endpoint
  □ Slow query log enabled with a reasonable threshold (> 100ms)

PATTERNS ALWAYS SUSPECTED:
  □ Loops + ORM relation access → possible N+1
  □ Loops + repository calls inside the loop → possible N+1
  □ GraphQL resolvers without DataLoader → almost certainly N+1
  □ "Lazy loading" not explicitly disabled → N+1 risk

Summary #

• N+1 is an O(n) problem against data, not against requests — 100 concurrent requests with N+1 can produce 10,000+ queries, while without N+1 only 100 queries.
• N+1 is invisible in development but painful in production — small datasets hide this problem. Enable SQL logging and inspect patterns during development.
• Loops + database access are an alarm — every time a loop contains database access, question whether this is N+1.
• Eager loading is the most common solution — preload relations that are almost always needed. Modern ORMs already optimize this with batch IN queries, not expensive joins.
• JOINs for filtering, Preload for display — use JOINs when filtering on relation data, Preload when only displaying relation data.
• DTO projections for list endpoints — list-displaying endpoints usually only need a fraction of fields. Specific queries avoid N+1 while reducing data transfer.
• Batch with IN clauses without an ORM — collect all needed IDs, fetch at once with WHERE id IN (...), then build a map for O(1) lookups.
• DataLoader for GraphQL and async contexts — a batching and per-request caching pattern very effective for resolvers called repeatedly.
• Code reviews are the best line of defense — the question “how many queries with 1000 items?” should be standard on every PR touching data access.
• Tests verifying query counts — an assert.LessOrEqual(t, queryCount, 3) assertion prevents N+1 from returning through future refactoring.

#

← Previous: DB Transaction   Next: Idempotency