ActiveRecord performance: N+1 queries

Bond -

Reference: https://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations

What is N+1 query?

The N+1 query problem is one of the common performance antipatterns in ORMs. It happens when an application gets data from the database and then loop through the result of that data. Let's look at the following example:

We have two model Post and User:

# app/models/post.rb
class Post < ApplicationRecord
  belongs_to :user
end

# app/models/user.rb
class User < ApplicationRecord
  has_many: :posts
end

Let's suppose I want to print out title and user's username for all the posts. I can try something like this:

Post.all.each do |post|
  puts "#{post.title} - #{post.user.username}"
end

How does ActiveRecord execute the code above? If there are 7 posts in the database, in total 8 different queries would be executed:

Post Load (0.6ms)  SELECT "posts".* FROM "posts"
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "fe8b7def-58fa-46d1-8356-1760ff3fddfa"], ["LIMIT", 1]]
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]

Imagine if we have thousands of records. It's nightmare!

Eager loading

Let's try includes method:

Post.includes(:user).each do |post|
  puts "#{post.title} - #{post.user.username}"
end

POST Load (0.5ms)  SELECT "posts".* FROM "post"
  User Load (1.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3)  [[nil, "fe8b7def-58fa-46d1-8356-1760ff3fddfa"], [nil, "78dd480d-da51-453a-b890-69f800420145"], [nil, "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"]]

All users are preloaded for each post and is temporarily cached. This allows looping through all posts and calling .user without having to access the database multiple times => problem solved!

Prevent the N + 1 problem

To summary, in order to prevent the N+1 problem, we got two possible options:

  1. Use gem Bullet
  1. Use includes | preload | eager_load method

Thank you all for reading!