13
.
04
.
2024
15
.
04
.
2024
Ruby on Rails
Ruby
Backend

N+1 in Ruby on Rails

Katarzyna Melon-Markowska
Ruby Developer

What is N + 1 problem in Rails?

In the Rails, Active Record Object-Relational Mapping (ORM) library is used to fetch records from database. Ruby code can be written with loops, where objects inside a loop commonly join data from multiple tables, generating queries behind the scenes.

Loading associated models without proper care may easily lead to so-called N + 1 problem.

The N + 1 problem is common performance issue. It occurs when inside loop the ORM executes a separate query for each associated model instead of using a single query to load them upfront.

Where N + 1 could be?

Lets create simple two models, Team and User:

class Team < ApplicationRecord
  has_many :users
end

class User < ApplicationRecord
  belongs_to :team
end

Run in console query to get team names for 3 users:

User.take(3).each do |user|
  puts user.team.name
end
  User Load (0.5ms)  SELECT "users".* FROM "users" LIMIT $1  [["LIMIT", 3]]
  Team Load (0.4ms)  SELECT "teams".* FROM "teams" WHERE "teams"."id" = $1 LIMIT $2  [["id", 437724769], ["LIMIT", 1]]
First Team
  Team Load (0.2ms)  SELECT "teams".* FROM "teams" WHERE "teams"."id" = $1 LIMIT $2  [["id", 437724769], ["LIMIT", 1]]
First Team
  Team Load (0.1ms)  SELECT "teams".* FROM "teams" WHERE "teams"."id" = $1 LIMIT $2  [["id", 527789713], ["LIMIT", 1]]
Second Team

ActiveRecord first executed 1 query to fetch 3 users next did 3 other queries for getting a team name for each user, so 4 queries in total were executed.

Viewing this as a 1 + N query can be helpful, with the initial query (1) loading the collection of users, followed by the execution of N additional queries to fetch each user's team name.

What if there are 10 000 users in the database? We will get 1 + 10 000 queries to get team name for each user. This approach could result in slow performance and high database load.

How to fix N + 1 in Rails?

To fix N + 1 queries, you can use the includes method. This loads all related data in a single query, making it easier to fetch both user data and teams at once. Let's fix our example using includes.

User.includes(:team).take(3).each do |user|
  puts user.team.name
end
  User Load (0.3ms)  SELECT "users".* FROM "users" LIMIT $1  [["LIMIT", 3]]
  Team Load (0.3ms)  SELECT "teams".* FROM "teams" WHERE "teams"."id" IN ($1, $2)  [["id", 437724769], ["id", 527789713]]
First Team
First Team
Second Team

ActiveRecord first executed 1 query to fetch 3 users. Next did 1 other query for getting a team name for each user, so 2 queries in total were executed.

Be careful!

Sometimes, it's unexpected, but running a few straightforward queries can be faster than handling one complex query with many joins and conditions.

Read up on the difference between eager loading and preloading, how includes can turn into a big join. Article: Joins vs Preload vs Includes vs Eager load in Rails

The important thing to remember is that when we're trying to make things faster, we should look at the actual data from our live systems and aim for specific improvements.

What are the possible levels of N + 1 in Rails?

Let's create the Project model and create an association in the Team model, also add delegate method to User model.

class Team < ApplicationRecord
  has_many :users
  has_many :projects
end

class User < ApplicationRecord
  belongs_to :team

  delegate :name, to: :team, prefix: true
end

class Project
  belongs_to :team
end

Explicit usage of an associated model

In this example, it's directly visible that the model's association is used:

User
  .take(3)
  .select { |u| u.team == current_team }

# Fixed query:

User
  .includes(:team)
  .take(3)
  .select { |u| u.team == current_team }

Implicit usage of an associated model

In the following example, team_name method loads the team association:

User
  .take(3)
  .map(&:team_name)

# Fixed query:

User
  .includes(:team)
  .take(3)
  .map(&:team_name)

Using associations of associations

In some use cases, business logic fetches deeper layers of associations. In this example, we not only preload team association of loaded User. We also preload team' association projects.

User
  .take(3)
  .map { |u| u.team.projects } 

# Fixed query:

User
  .includes(team: [:projects])
  .take(3)
  .map { |u| u.team.projects }

Metaprogramming - using associations dynamically

Metaprogramming and associations are areas where it's easy to introduce N + 1 issues, which can be difficult to find or fix later on. Therefore, we recommend not combining them.

How to prevent and find N + 1?

There are a lot of tools that can help with preventing or finding N + 1. Each tool has its strengths and weaknesses. Take your time to check them out and see which one works best for you and your application.

  • To prevent N + 1 queries use the strict_loading mode in Rails. This feature became available starting from Rails 6.1. It can be enabled per record, association, model, or across the entire application.

Let’s run in console our example:

User.strict_loading**.**limit(3).each do |user|
  puts user.team.name
end
User Load (0.4ms)  SELECT "users".* FROM "users" LIMIT $1  [["LIMIT", 3]]
/Users/kasia/.rbenv/versions/3.1.3/lib/ruby/gems/3.1.0/gems/activerecord-7.0.8.1/lib/active_record/core.rb:242:in
`strict_loading_violation!': `User` is marked for strict_loading. The Team association named `:team` cannot be lazily loaded.
(ActiveRecord::StrictLoadingViolationError)

Thanks to the strict_loading mode being enabled, we received an error, which indicates that we should use eager loading(includes method) for the associated queries to prevent N + 1.

  • Use the bullet gem. It allows for automatic detection and alerting of N + 1 queries in the application, as well as offering optimization suggestions for these queries.
  • Use the rspec-sqlimit gem. It is Test-Driven way of fighting N + 1 queries.
  • Use the query_track gem. It is a gem that logs and tracks database queries in your Rails application. It helps to identify N + 1 query problems by recording all database queries executed during a request.
  • Use performance monitoring tools such as New Relic, Scout APM, Sentry` or Skylight. These tools not only provide insights into overall application performance but also help in detecting and addressing common issues such as N + 1 queries.
  • Use PostgreSQL's database tools. Employing the pgstatstatements extension enables analysis of the queries executed by your application.
  • Use application’s log files. Search for repeated queries or queries executed multiple times for related records.

Keep in mind! You can't always get rid of all N + 1 queries in a Rails app, but using these tools and techniques should help you find the ones causing problems.

What are potential advantages of N + 1?

The N + 1 typically signifies a situation where a database query is executed repeatedly within a loop or iterative process, resulting in inefficient database access patterns. However, there are potential scenarios where the N + 1 approach may offer advantages:

  • Lazy Loading: N + 1 querying allows for lazy loading of associated records, which can be beneficial when dealing with large datasets or scenarios where loading all associated records upfront would consume excessive memory.
  • Caching: Effective caching mechanisms can mitigate the overhead of N + 1 queries by caching frequently accessed data, leading to improved performance and reduced database load. You can listen more about it here: Interview: David Heinemeier Hansson (DHH) on Rails performance and speed

Choose the right tools and test on production-like data

The N + 1 problem in Rails occurs when database queries are repetitively executed within loops or iterative processes, leading to inefficient database access patterns. This issue can severely impact application performance, especially in scenarios involving large datasets or high concurrency.

However, keep in mind, in certain, rare scenarios N + 1 isn’t a problem, but could be even a solution.

Choosing the right tools for your app is important, especially for handling the N + 1 pattern. Making sure your solution works well for N + 1 is crucial. Since there's no one-size-fits-all solution, it is essential to test your approach with real data to confirm if it actually works.

Katarzyna Melon-Markowska
Ruby Developer

Check my Twitter

Check my Linkedin

Did you like it? 

Sign up To VIsuality newsletter

READ ALSO

Handling Errors in Concurrent Ruby, Michał Łęcicki

Handling Errors in Concurrent Ruby

14
.
11
.
2023
Michał Łęcicki
Ruby
Ruby on Rails
Tutorial
Recap of Friendly.rb 2024 conference

Insights and Inspiration from Friendly.rb: A Ruby Conference Recap

02
.
10
.
2024
Kaja Witek
Conferences
Ruby on Rails

Covering indexes - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Ruby on Rails
Postgresql
Backend
Ula Sołogub - SQL Injection in Ruby on Rails

The Deadly Sins in RoR security - SQL Injection

14
.
11
.
2023
Urszula Sołogub
Backend
Ruby on Rails
Software
Michal - Highlights from Ruby Unconf 2024

Highlights from Ruby Unconf 2024

14
.
11
.
2023
Michał Łęcicki
Conferences
Visuality
Cezary Kłos - Optimizing Cloud Infrastructure by $40 000 Annually

Optimizing Cloud Infrastructure by $40 000 Annually

14
.
11
.
2023
Cezary Kłos
Backend
Ruby on Rails

Smooth Concurrent Updates with Hotwire Stimulus

14
.
11
.
2023
Michał Łęcicki
Hotwire
Ruby on Rails
Software
Tutorial

Freelancers vs Software house

02
.
10
.
2024
Michał Krochecki
Visuality
Business

Table partitioning in Rails, part 2 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
Ruby on Rails

N+1 in Ruby on Rails

14
.
11
.
2023
Katarzyna Melon-Markowska
Ruby on Rails
Ruby
Backend

Turbo Streams and current user

29
.
11
.
2023
Mateusz Bilski
Hotwire
Ruby on Rails
Backend
Frontend

Showing progress of background jobs with Turbo

14
.
11
.
2023
Michał Łęcicki
Ruby on Rails
Ruby
Hotwire
Frontend
Backend

Table partitioning in Rails, part 1 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend
Ruby on Rails

Table partitioning types - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend

Indexing partitioned table - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
SQL Views in Ruby on Rails

SQL views in Ruby on Rails

14
.
11
.
2023
Jan Grela
Backend
Ruby
Ruby on Rails
Postgresql
Design your bathroom in React

Design your bathroom in React

14
.
11
.
2023
Bartosz Bazański
Frontend
React
Lazy Attributes in Ruby - Krzysztof Wawer

Lazy attributes in Ruby

14
.
11
.
2023
Krzysztof Wawer
Ruby
Software

Exporting CSV files using COPY - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Ruby
Ruby on Rails
Michał Łęcicki - From Celluloid to Concurrent Ruby

From Celluloid to Concurrent Ruby: Practical Examples Of Multithreading Calls

14
.
11
.
2023
Michał Łęcicki
Backend
Ruby
Ruby on Rails
Software

Super Slide Me - Game Written in React

14
.
11
.
2023
Antoni Smoliński
Frontend
React
Jarek Kowalewski - ILIKE vs LIKE/LOWER - Postgres Stories

ILIKE vs LIKE/LOWER - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Ruby
Ruby on Rails
Postgresql

A look back at Friendly.rb 2023

14
.
11
.
2023
Cezary Kłos
Conferences
Ruby