How to Improve the Performance of Your Rails App by Eliminating N+1 Database Queries

My observation is that when developers start complaining that Rails can't scale, they are running into the typical problem: the application worked just fine with a small number of records/requests, but now that our business is growing, our code is having trouble keeping up.

N+1 queries are one of the common causes of performance degradation.

What exactly is the N+1 query problem?

One of the frequent performance antipatterns in ORMs is the N+1 query problem. It occurs when an application retrieves data from the database and then loops through the results of that data, or when a query is run on each result of the prior query.

If your application is small enough and there are few requests and queries, this works just fine. However, if you want to keep your application scalable, you must remove the N+1 problem from it.

Let's look at an example with two linked models:

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

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

Let's print the title and user's username for all the companies.

Company.all.each do |Company|
  puts "#{Company.title} was written by #{Company.user.username}"
end

The above code works, but it makes far too many independent database queries:

Company Load (0.5ms)  SELECT "companies".* FROM "companies"
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 5], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 10], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 15], ["LIMIT", 1]]

That is 7 separate database queries. In each iteration, one query is used to load the builds, and N queries are used to load the branch, hence the name N+1 queries. This loading method is extremely inefficient.

We'll need to connect to the database 10001 times if we have 10000 users. Remote database connections are not free, and each one imposes a significant performance penalty.

Eager Loading in Rails

In contrast to lazy loading, eager loading occurs when a query loads a resource as soon as the code is executed. As part of the query, it also loads related entities.
We need to reduce the number of independent database queries in order to improve the performance of the previous example.

This is accomplished in Rails by eager loading associated relations, or collecting related data with a single query.

This is very easy in Rails. In our example, all we need to do is attach .includes(:user) to our existing database query

Company.includes(:user).each do |Company|
  puts "#{Company.title} was written by #{Company.user.username}"
end

Company Load (103.7ms)  SELECT "companies".* FROM "companies"
  User Load (32.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?)  [["id", 1], ["id", 5], ["id", 6], ["id", 9], ["id", 10], ["id", 15]]

We now got the same result with only two queries. This time, the number of queries is much higher. To load all of the data into memory, we only used two queries: one to load the companies and another to load the associated users.
Even after 10,000 users, our app would only use two database queries.

Eliminating N+1 Queries with the Bullet Gem

The first step toward eliminating N+1 queries is to ensure that your engineering team understands the problem and is aware of new queries that can be optimised.

Reviewing pull requests and sharing knowledge is a good place to start, but errors can still find their way into production code even with meticulous attention to detail. To combat this, we always strive to automate and have the CI step alert us to potential problems. Bullet is a fantastic tool for this purpose. It monitors and reports on inefficient queries.

Setting up Bullet Gem

• To set up Bullet on your Rails project, first add it to your Gemfile

gem "bullet"
or
gem install bullet

• Enable the Bullet gem with generate command
  bundle exec rails g bullet:install

• Then, add the following in your app/environments/development.rb to enable Bullet in development mode:
  

config.after_initialize do
  Bullet.enable = true
  Bullet.bullet_logger = true
  Bullet.console = true
  Bullet.alert = true
  Bullet.airbrake = true
end

With the above setup, every N+1 query will be logged in the log/bullet.log log file. For example, our example with builds and branches will produce:

USE eager loading detected
  Company => [:user]
  Add to your finder: :includes => [:user]

N+1 Prevention with exception handling

We can instruct Bullet to throw an exception, which will result in red builds if a N+1 query is accidentally introduced into the code. We can almost guarantee that no N+1 query will enter production code by raising an exception and failing your builds.

Need to add this config in your file
Bullet.raise: raise errors, useful for making your specs fail unless they have optimized queries

  config.after_initialize do
      Bullet.enable = true
      Bullet.bullet_logger = true
      Bullet.console = true
      Bullet.alert = true
      Bullet.raise = true # raise an error if an n+1 query occurs
    end

If you have any suggestions or feel that this blog could be improved, please leave a comment.

Thanks,
Harsh Umaretiya