Performance Matters: N+1 Problem

Introduction to N+1 Problem and how to avoid a common performance pitfall.

What is the N+1 Problem?

The N+1 query problem is a common performance antipattern which occurs when the code needs to load a bunch of children from a parent data in the one-to-many relation. Queries are executed for the parent record, then one query for each child records. End up doing N+1 queries to the database to get each child belonging to the parent record.

Example

Let's assume you have a table, calls “category” to record the list of categories that available in your shop. Each category may have one or more products that recorded in another table calls “product”. In other words, “category” and “product” has a one-to-many relation.

In Go, usually, it looks like these statements:

categories, _ := repository.GetCategories()
for _, category := range categories {
   products, _ := repository.GetProducts(category.ID)
   // ....
}

Assuming GetCategories() has an underlying implementation like this:

SELECT * FROM "category"

Then, GetProducts() has an underlying implementation like this:

SELECT * FROM "product" WHERE category_id = ...

Each time previous statements executed, it will issue N+1 queries, where N is the number of categories, like these:

N+1 Problem Queries Sample

The Problem with that Example

You can see these queries created quite a bit of overhead. Remember that running 1 query which returns 100 results is faster than to run 100 queries with 1 result each. This is particularly true if you get the data from database or service which runs on a different machine.

Let’s assume to get data from the database or service it takes 100–200ms for each query. If you execute the queries for 100 times, it’s gonna take 10.000–20.000ms to get all data you required.

How to Avoid the N+1 Problem?

To put it simply, do the query in batch. Instead of running the query N+1 times, run it only twice, one for the parent records and one for the child records.

N+1 Problem Solution Queries Example

Voilà, and we are done. Now you understand what’s the N+1 problem is and how to avoid them.