https://www.visuality.pl/posts/n-1-in-ruby-on-rails Contact Services Case Studies About us Consultants Workshops Blog Videos Contact Contact 13 . 04 . 2024 15 . 04 . 2024 Ruby on Rails Ruby Backend N+1 in Ruby on Rails [654405138a] Katarzyna Melon-Markowska Ruby Developer [661a980416] 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. [654405138a] Katarzyna Melon-Markowska Ruby Developer Check my Twitter [65f76bca9c] Check my Linkedin [653245b952] Did you like it? Sign up To VIsuality newsletter [ ] [Subscribe] [ ] READ ALSO [661a980416] N+1 in Ruby on Rails 14 . 11 . 2023 Katarzyna Melon-Markowska Ruby on Rails Ruby Backend [6613bf2bbe] Turbo Streams and current user 29 . 11 . 2023 Mateusz Bilski Hotwire Ruby on Rails Backend Frontend [660d4ee235] Showing progress of background jobs with Turbo 14 . 11 . 2023 Michal Lecicki Ruby on Rails Ruby Hotwire Frontend Backend [660212e98c] Table partitioning in Rails, part 1 - Postgres Stories 14 . 11 . 2023 Jaroslaw Kowalewski Postgresql Backend Ruby on Rails [66020bd05c] Table partitioning types - Postgres Stories 14 . 11 . 2023 Jaroslaw Kowalewski Postgresql Backend [66020fdbec] Indexing partitioned table - Postgres Stories 14 . 11 . 2023 Jaroslaw 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 Bazanski Frontend React Lazy Attributes in Ruby - Krzysztof Wawer Lazy attributes in Ruby 14 . 11 . 2023 Krzysztof Wawer Ruby Software [65a7ac5da8] Exporting CSV files using COPY - Postgres Stories 14 . 11 . 2023 Jaroslaw Kowalewski Postgresql Ruby Ruby on Rails Michal Lecicki - From Celluloid to Concurrent Ruby From Celluloid to Concurrent Ruby: Practical Examples Of Multithreading Calls 14 . 11 . 2023 Michal Lecicki Backend Ruby Ruby on Rails Software [658190b312] Super Slide Me - Game Written in React 14 . 11 . 2023 Antoni Smolinski Frontend React Jarek Kowalewski - ILIKE vs LIKE/LOWER - Postgres Stories ILIKE vs LIKE/LOWER - Postgres Stories 14 . 11 . 2023 Jaroslaw Kowalewski Ruby Ruby on Rails Postgresql [6577200070] A look back at Friendly.rb 2023 14 . 11 . 2023 Cezary Klos Conferences Ruby [656711be6f] Debugging Rails - Ruby Junior Chronicles 14 . 11 . 2023 Piotr Witek Ruby on Rails Backend Tutorial [6544be7750] GraphQL in Ruby on Rails: How to Extend Connections 14 . 11 . 2023 Cezary Klos Ruby on Rails GraphQL Backend Tutorial [6544be77b8] Tetris on Rails 17 . 03 . 2024 Pawel Strzalkowski Ruby on Rails Backend Frontend Hotwire [6544be78b9] EURUKO 2023 - here's what you've missed 14 . 11 . 2023 Michal Lecicki Ruby Conferences [6544be76b5] Easy introduction to Connection Pool in ruby 14 . 11 . 2023 Michal Lecicki Ruby on Rails Backend Ruby Tutorial [6544be75f1] When crazy ideas bring great time or how we organized our first Conference! 04 . 12 . 2023 Alexander Repnikov Ruby on Rails Conferences Visuality [6544be752a] Stacey Matrix & Takeaways - why does your IT project suck? 14 . 11 . 2023 Wiktor De Witte Project Management Business [6544be7543] A simple guide to pessimistic locking in Rails 14 . 11 . 2023 Michal Lecicki Ruby on Rails Backend Ruby Tutorial [6544be7558] Poltrax design - story of POLTRAX (part 3) 04 . 12 . 2023 Mateusz Wodyk Startups Business Design [6544be7598] Concurrency and parallelism in Ruby (Processes, Threads, Fibers and Ractors) 14 . 11 . 2023 Jan Grela Ruby on Rails Ruby Backend [6544be7406] Writing Chrome Extensions Is (probably) Easier Than You Think 14 . 11 . 2023 Antoni Smolinski Tutorial Frontend Backend Next let's talk Send us a message contact@visuality.pl Your name [ ] Email [ ] Message [ ] [Send] Thank you! Your submission has been received! Oops! Something went wrong while submitting the form. Home SERVICES Engineers Consultants Project Managers Ruby on Rails React Mobile Applications UX/UI Design New Product Refactor case Studies Fin-Tech Software E-commerce Software Auditing Software Legal-Tech Software Digital Memorial Page Virtual Meetings Platform ABOUT US Roots Culture People Privacy Policy CAREER Ruby Developer Senior Ruby Developer Company Blog Posts Videos Workshops Consultants Webinar DDD Test Optimization [653245b952] 5.0 RATING ON CLUTCH (c) Copyright 2023