Skip to content
← All posts
5 min read Dawid Skłodowski

Hunting and killing N+1 queries

The N+1 query is the most common performance bug in Rails apps, and it hides perfectly in development. A deep-dive on what causes it, the difference between preload/eager_load/includes, counter caches, and how to catch it automatically.

If you profile a slow Rails page, the odds are overwhelming that the culprit is an N+1 query. It is the single most common performance bug in Rails, and the cruellest, because it is invisible in development — with twenty records on your laptop the page feels instant, and only in production, with twenty thousand, does it crawl. Here is what causes it, how to fix it properly, and how to make sure it never sneaks back in.

What an N+1 actually is

The bug is a loop that triggers a database query on each iteration. Consider rendering a list of posts with each author’s name:

@posts = Post.all          # 1 query: SELECT * FROM posts
@posts.each do |post|
  puts post.author.name    # 1 query EACH: SELECT * FROM authors WHERE id = ?
end

That is 1 query for the posts, plus N more — one per post — to fetch each author. Hence “N+1”. With 20 posts it is 21 queries, fast enough to ignore. With 2,000 posts it is 2,001 queries, each with its own round-trip to the database, and your page takes seconds. The lazy-loading that makes ActiveRecord so convenient — post.author just works — is exactly what hides the cost: every one of those innocent-looking calls is a separate query you cannot see in the code.

The fix: eager loading

The solution is to tell ActiveRecord up front which associations you will need, so it loads them in bulk instead of one at a time. The headline tool is includes:

@posts = Post.includes(:author)   # loads all posts AND all their authors efficiently
@posts.each do |post|
  puts post.author.name           # no query here — already loaded
end

Now there is no per-iteration query at all. includes(:author) fetches the authors in one additional query (or a join), and post.author reads from memory. Twenty-one queries become two, and the page is fast at any scale. For nested associations you pass a hash:

Post.includes(author: :profile, comments: :user)

preload vs eager_load vs includes

This is where it pays to understand what ActiveRecord is actually doing, because there are three related methods and they behave differently:

  • preload always uses two separate queries: one for the posts, one WHERE author_id IN (...) for all the authors. Clean and predictable, but you cannot reference the associated table in a WHERE or ORDER BY, because it was never joined.
  • eager_load always uses a single LEFT OUTER JOIN, pulling everything in one query. This is what you need when you want to filter or sort by the associated table — Post.eager_load(:author).where(authors: { active: true }).
  • includes is the smart default: it normally behaves like preload (two queries), but if it detects that you reference the association in a where or order, it automatically switches to eager_load (the join).

Most of the time includes does the right thing and you should reach for it. Drop to preload or eager_load explicitly when you need to control the strategy — for example, when a join would multiply rows expensively and you would rather have two queries, or when includes guesses wrong about whether to join.

Not every N+1 needs eager loading

A counter is a special case worth calling out. If all you need is a count of an association — “23 comments” — eager-loading every comment to count them in Ruby is wasteful. A counter cache stores the count on the parent row and keeps it updated automatically:

class Comment < ActiveRecord::Base
  belongs_to :post, counter_cache: true   # maintains post.comments_count
end

Now post.comments_count is a column read, no query and no loaded associations. Counter caches are the right fix whenever you are loading records only to count them.

It is also worth saying: not every N+1 is worth fixing. If a page loads exactly one record and touches one association, the “N” is 1 and there is nothing to optimise. Premature eager-loading of associations you do not use just wastes memory loading data you throw away. Fix the N+1s that are actually on a hot path with a real N — measure first.

Catching it automatically

The reason N+1s are so persistent is that they pass every manual test on small data. The fix is to detect them mechanically. The bullet gem watches your queries in development and pops up a warning the moment it sees an N+1 (and, helpfully, when you eager-load something you never use):

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert  = true        # browser alert
  Bullet.bullet_logger = true # and a log file
end

Better still, make it fail your tests. Run Bullet in the test environment and have it raise on an N+1, so a new one breaks CI instead of shipping silently:

Bullet.raise = true   # in test config — an N+1 now fails the suite

And keep an eye on the development log: a wall of near-identical SELECT ... WHERE id = ? lines scrolling past on a single page load is the unmistakable fingerprint of an N+1. Reading your own query log is the cheapest detection tool there is.

Verdict

The N+1 query is the performance bug that will be in your app — not because anyone is careless, but because ActiveRecord’s lazy loading is convenient enough to hide it perfectly until production scale exposes it. The cure is not hard: eager-load the associations you iterate over with includes, understand preload vs eager_load for the cases where the strategy matters, use a counter cache when you only need a count, and skip the over-eager loading of data you do not use. The real win, though, is mechanical detection — run Bullet, fail your tests on N+1s, and watch your query log — so that the bug that hides in development gets caught in development, where it is cheap, instead of in production, where it is a page nobody can load.

#ruby #rails #postgresql #performance