Copied My Perfect SQL Query Into Rails. Got N+1 Performance Hell. Here’s Why.

Primary keyword: N+1 query problem Rails tutorial

“SQL mindset: write the perfect query. ActiveRecord mindset: define relationships once, query anywhere. Different game 🎯”

Your SQL is immaculate. You port it line‑by‑line to ActiveRecord, ship the feature, and suddenly your user dashboard is doing 101 queries for 100 users. P95 jumps from 920ms to 1.8s, database CPU spikes to 75%, and pagination crawls. That’s N+1—and it happens precisely because we translated SQL instead of modeling relationships. In this tutorial, I’ll show the relationship‑first way to think in Rails 7+, how to pick includes vs preload vs eager_load, and the guardrails I enforce so this never ships again.

The Performance Problem: SQL Thinking vs. Rails Relationships

When you’re fluent in SQL, the instinct is to craft a single “perfect” query and mirror it in ActiveRecord scopes. Rails rewards a different instinct: define associations once, then compose lightweight queries that let Rails load related data efficiently. If you skip the association work and just “do the SQL,” you’ll read attributes on associated records later—which triggers the classic N+1.

In one SaaS app (50K DAU, 2M orders rows), a naive port of a 4‑join SQL produced 1 query per user plus a handful of lookups—~520 queries for a paginated page of 500 users. After moving to relationship‑first code with correct eager loading, total queries dropped to 2, and P95 fell to 240ms. The Postgres instance went from 75% average CPU to 38%, letting us downgrade one instance class and save **$150/month**.

Real talk: Rails doesn’t reward clever SQL first. It rewards explicit relationships first. Then you optimize the few remaining queries.

How N+1 Sneaks In When You Translate SQL Literally

You copy the SQL: select users, join orders, join profiles, filter, group. In Rails, you write a scope with joins to match it. Looks tidy—until your view accesses user.profile.avatar_url and user.orders.count. Those reads aren’t in the scope, so Rails goes back to the database per user.

Before: the N+1 translation

# Rails 7+, Ruby 3.2+
# This mirrors the SQL joins but forgets how the view will use the data
# Result: 1 query for users + N queries for profile + N queries for orders
users = User.joins(:orders, :profile) # looked good in review
            .where("orders.status = ?", "paid")
            .order("users.created_at DESC")
            .limit(100)

users.each do |user|
  # Accessing associations after the fact triggers extra queries
  puts "#{user.profile.avatar_url}: #{user.orders.count}"
end

After: relationship‑first, zero N+1

# Load what the view will touch; don't assume joins alone are enough
# includes lets Rails choose the optimal strategy based on filters
users = User.includes(:profile, :orders)
            .where(orders: { status: :paid })
            .order(created_at: :desc)
            .limit(100)

users.each do |user|
  # Associations are already loaded; no per-user queries
  puts "#{user.profile.avatar_url}: #{user.orders.size}"
end

Metrics from production: queries per request went from ~201 to 3; P95 from 1.8s to 320ms on a page hit 12K times/day.

Watch out: joins + reading associations in Ruby is the fastest path to N+1. Either eager load associations you will read or restructure the query to compute what you need in SQL. fileciteturn0file4

The Relationship‑First Rewrite: includes vs preload vs eager_load

These three aren’t synonyms. The right one depends on whether you need to filter/order by association columns and on data density.

includes (smart default)

# Why: Lets Rails pick JOIN vs separate queries depending on filters/data
# Use when you may or may not filter by association, or want the optimizer to decide
users = User.includes(:orders, :profile).where(active: true)

• ✅ Pros: Rails chooses JOIN or 2 queries; great for most pages.
• ❌ Cons: Strategy can change with data; WHERE on association sometimes triggers extra queries.

preload (force separate queries)

# Why: Avoid cartesian row explosions when associations are sparse
# Example: many users; only 5% have recent orders
users = User.preload(:recent_orders)

• ✅ Pros: Predictable 2+ queries; avoids big JOIN result sets when few children.
• ❌ Cons: More roundtrips; cannot filter in WHERE by child columns.

eager_load (force LEFT OUTER JOIN)

# Why: You need to filter/order by child columns in SQL
users = User.eager_load(:orders).where(orders: { status: :paid })

• ✅ Pros: One query; filter by child columns.
• ❌ Cons: Duplicate parent rows; dangerous with multiple has_many.

Rule of thumb from my logs: includes solved ~80% of N+1 cases. Reach for eager_load only when you’re filtering by child columns and the association is dense; choose preload when children are sparse to avoid JOIN bloat. fileciteturn0file3

Performance note: Duplicate parent rows from eager_load can add 150–300ms just to instantiate models. Use select("DISTINCT users.id") or compute in SQL and pluck only what you need.

Counting Without Loops: Aggregations, Counter Caches, and Select Lists

Many N+1s come from counting in Ruby. Count in SQL or cache counts.

1) Do the count in SQL

# Why: Aggregating in SQL avoids per-record queries and Ruby loops
users = User.joins(:orders)
            .where(orders: { status: :paid })
            .group("users.id")
            .select("users.*, COUNT(orders.id) AS paid_orders_count")

users.first.paid_orders_count
# => 3

This dropped one dashboard from 101 queries to 2 and cut view rendering from 420ms to 90ms.

2) Use counter_cache when exact, always‑updated counts matter

# db/migrate/20240220_add_orders_count_to_users.rb
class AddOrdersCountToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :orders_count, :integer, default: 0, null: false
    add_index :users, :orders_count # fast sorts and thresholds
  end
end

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :user, counter_cache: true
  # Why: Let the DB maintain counts transactionally; avoids expensive COUNT(*) at runtime
end

We used orders_count for an admin list sorted by “most active buyers” and shaved ~230ms off that page at 5K requests/day.

3) Select fewer columns when you don’t need full models

# Why: Reduce memory and object allocations on list pages
user_emails = User.where(active: true).pluck(:id, :email)
# => [[1, "a@example.com"], [2, "b@example.com"]]

Pro tip: Use pluck/select when you don’t need callbacks/validations; fetch full models only when you need behavior. Your GC will thank you.

Detection & Guardrails: Bullet, Strict Loading, and CI

You can’t fix what you don’t see. Add Bullet, turn on strict loading, and make failures break CI.

Bullet in development

# config/environments/development.rb
Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = true     # Why: raise noisy alerts so devs see N+1 immediately
    Bullet.rails_logger = true
  end
end

Strict loading to fail fast (Rails 6.1+)

# app/models/user.rb
class User < ApplicationRecord
  has_many :orders
  has_one :profile

  # Why: Blow up when a dev touches an association that isn't preloaded
  self.strict_loading_by_default = true if Rails.version.to_f >= 6.1
end

An RSpec guard that fails CI

# spec/support/n_plus_one_guard.rb
RSpec.configure do |config|
  config.before(:each, :n_plus_one) do
    Bullet.start_request
  end

  config.after(:each, :n_plus_one) do
    Bullet.perform_out_of_channel_notifications
    expect(Bullet.notification?).to be(false), "N+1 detected — add eager loading"
    Bullet.end_request
  end
end

With these in place, we caught a background job that fanned out 600 extra queries during nightly billing. Fixing it (preloading invoices for each subscription) dropped the job runtime from 8m 40s to 52s on 120K subscriptions.

Watch out: includes + or can generate messy OUTER JOINs with duplicates. If you need complex OR logic, fetch IDs separately and merge, then where(id: ids) with a single eager load. I learned this the hard way when a dashboard pulled 2M rows and timed out.

When Each Approach Fails (Trade‑offs You Should Actually Use)

Eager loading everything, always

• ✅ Pros: Eliminates N+1 by brute force.
• ❌ Cons: Wastes time/memory on pages that render only a subset. On users#show, we added ~200ms by eager loading orders, profile, and subscriptions no one used. Use eager loading on collections, not one‑offs.

Counting in Ruby vs. SQL

• Use SQL for counts on list pages (JOIN + GROUP BY) and counter_cache for always‑available counts.
• Avoid Ruby loops that call .count or .size on associations per record. This is the textbook N+1.

includes vs eager_load with filters

• If you filter by child columns (e.g., orders.status = 'paid'), test eager_load vs includes + references. On dense associations we saw 1.3x faster with eager_load; on sparse data, preload won by 2x due to JOIN explosion.

Tools I rely on

• Bullet for detection, PgHero for query analysis, New Relic/Skylight for timings, Pagy for pagination to keep result sets lean, StandardRB to keep diffs honest.

Final Thoughts

Think relationships first, not SQL first. Start by defining the associations your view will touch, pick includes/preload/eager_load based on filters and data density, and wire Bullet + strict loading to fail fast in dev and CI. You’ll ship fewer queries, lower P95 by hundreds of milliseconds, and keep your Postgres bill boring.