N+1 Problem in ORM

systemdesign database/databaseengineering

It’s a common performance issue in application that interact with databases (ORMs).

Problem

Example:

A list of Authors.
Each author has books.

erDiagram
    AUTHORS ||--o{ BOOKS : writes

    AUTHORS {
        int id PK
        string name
        string email
    }

    BOOKS {
        int id PK
        string title
        int author_id FK
        date published_at
    }

If we want to fetch author and their books,

authors := db.FindAllAuthors()  // 1 query
 
for _, author := range authors {
    books := db.FindBooksByAuthorID(author.ID) // N queries (1 per author)
}

1 query to get all authors.
Then for each author, do 1 query to get their books.
Total Queries = N + 1 for N authors.
Inefficient: Each database call has overhead (network, parsing, execution).
Slow: If you have 1000 authors, that’s 1001 queries.
Scales poorly: Latency and load increase rapidly with data size.

Solutions

Eager Loading (Preloading, Fetch Join)

Load related data at the same time as the main query.

SELECT authors.*, books.* 
FROM authors 
LEFT JOIN books ON authors.id = books.author_id;

ORMs provide prebuilt method for Eager loading.

// User represents a user in the system.
// gorm.Model includes fields: ID, CreatedAt, UpdatedAt, DeletedAt.
type User struct {
  gorm.Model
  Name      string      // User's name
  Email     string      // User's email address
  Addresses []Address   // One-to-many relationship: a user has many addresses
}
 
// Address represents a physical address associated with a user.
type Address struct {
  gorm.Model
  UserID uint    // Foreign key referencing User.ID
  Street string  // Street name of the address
  City   string  // City of the address
}
 
// getAllUserWithAddress retrieves all users from the database
// and preloads their associated addresses to avoid the N+1 query problem.
func getAllUserWithAddress(db *gorm.DB) ([]User, error) {
  var users []User
  // Use Preload to fetch related addresses in a single optimized query
  if err := db.Preload("Addresses").Find(&users).Error; err != nil {
    return nil, err // Return error if query fails
  }
  return users, nil // Return the list of users with their addresses
}

with eager loading,

SELECT * FROM users;  
SELECT * FROM addresses WHERE user_id IN (1, 2, 3, 4);

without eager loading,

SELECT * FROM users;  
  
SELECT * FROM addresses WHERE user_id = 1;  
SELECT * FROM addresses WHERE user_id = 2;  
SELECT * FROM addresses WHERE user_id = 3;  
SELECT * FROM addresses WHERE user_id = 4;

Batch Loading

This technique is similar to eager loading, but queries are performed in batches.

Manual Join Queries

SELECT a.id, a.name, b.title
FROM authors a
JOIN books b ON a.id = b.author_id;

Dataloader

Dataloader is a utility often used in GraphQL APIs that batched and cached database or API requests per request lifecycle. It used to prevent issues like the N+1 problem.

The GraphQL Dataloader Cookbook | Parabol

Nimendra's Notes 🪴

Explorer