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_loadingmode 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.
Check my Twitter
Check my Linkedin
