Solving the N+1 Problem in Go: Efficient Querying Made Easy

TL;DR: This article explores how to efficiently solve the N+1 Query problem in Golang. The N+1 Query problem occurs when additional queries are made for each child's data, resulting in slow response times. Techniques like Eager Loading, Batch Loading, and Manual Join are discussed to address this issue. Examples of Eager Loading and Manual Join are provided.

Tackling the N+1 Query Problem

Overcoming the N+1 Query problem is crucial in application development to improve performance and avoid slow response times. This article explores how to solve this problem using Golang.

The N+1 Query problem occurs when an application queries to retrieve parent data and then performs additional queries for each child data related to the parent. In this case, each child data will require an additional query, resulting in N+1 queries needed. For instance, if an application intends to showcase a list of articles and their respective comments, it might send one query to retrieve the articles and then make additional queries for each article to display their corresponding comments.

There are several ways to solve the N+1 Query problem:

1. Eager Loading: This technique allows querying to retrieve data from several tables at once. In Golang, the Preload feature in ORM libraries such as Gorm can be used for easy and efficient eager loading.
2. Batch Loading: This technique is similar to eager loading, but queries are performed in batches. In Golang, libraries such as Dataloader can be used for batch loading.
3. Manual Join: This technique involves the manual use of join queries to retrieve data from several tables at once.

The implementation of the chosen technique depends on the complexity of the case and the libraries used in the application.

In this article, we will focus on giving examples of the techniques commonly used to solve the N+1 Query problem, namely Eager Loading and Manual Join.

Code Unveiled: Let’s Dive In!

In the provided code snippet, we delve into the practical implementation of two techniques used to solve the N+1 Query problem. Let’s take a look at the example for these tables:

Eager Loading

Here is an example implementation of eager loading using the GORM library:

type User struct {
  gorm.Model
  Name      string
  Email     string
  Addresses []Address
}

type Address struct {
  gorm.Model
  UserID uint
  Street string
  City   string
}

func getAllUserWithAddress(db *gorm.DB) ([]User, error) {
  var users []User
  if err := db.Preload("Addresses").Find(&users).Error; err != nil {
    return nil, err
  }
  return users, nil
}

In this example, eager loading is done by using Preload on db. Preload retrieves data from the Addresses relationship of the User model together with the data of the User itself. Here is the resulting query produced by using this method:

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

Compare if not using 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;
...

The number of queries generated is the same as the number of users, which can cause N+1 query problems and affect application performance and response time.

Manual Join

In Golang, this technique can be implemented using the database/sql package.

type User struct {
  ID    int
  Name  string
  Email string
  Addresses []Address
}

type Address struct {
  UserID int
  Street string
  City   string
}

func getAllUserWithAddress(db *sql.DB) ([]User, error) {
  query := "SELECT u.id, u.name, u.email, a.street, a.city FROM users u JOIN addresses a ON a.user_id = u.id"
  rows, err := db.Query(query)
  if err != nil {
    return nil, err
  }
  defer rows.Close()
  var users []User
  for rows.Next() {
    var user User
    var address Address
    err := rows.Scan(&user.ID, &user.Name, &user.Email, &address.Street, &address.City)
    if err != nil {
      return nil, err
    }
    user.Addresses = append(user.Addresses, address)
    users = append(users, user)
  }
  return users, nil
}

In this example, manual join is done by writing the join query manually in the query which is then executed using Query. The result of the query will be scanned into User and Address. Next, the found results will be merged into one by adding the Address into the Addresses slice in User.

Our Approach in Action

At BerandaToko, we prioritize query performance in our application. To achieve this, we employ a combination of techniques, including eager loading and manual joins.

For example, in our nearest store function, we use eager loading to retrieve the necessary information about the store’s address and operating hours along with its location data. By doing so, we avoid the N+1 query problem and significantly enhance the function’s performance.

Furthermore, we leverage manual joins in various parts of our backend. This enables us to craft custom queries tailored to our specific data requirements, granting us better control over query performance and optimization for different use cases.

By incorporating eager loading and manual joins, we successfully boost query performance and deliver an improved user experience. We hope this article has provided you with valuable insights on efficiently addressing the N+1 Query problem in Golang. Thank you for reading!