A dashboard that loads in 300 ms on your laptop and 9 seconds in production usually has the same cause: an N+1 query. With ten rows of seed data it is invisible. With two thousand orders, each pulling its customer, its address and its line items, one request quietly becomes six thousand database round trips.
This tutorial walks through how to spot N+1 queries in a Rails API, the three loading strategies Active Record gives you and when each one is right, and how to stop new N+1s from reaching production at all.
You will need a Rails 7 app and PostgreSQL. The examples use a small order-dispatch domain, but the patterns apply to any app.
The setup
class Order < ApplicationRecord
belongs_to :customer
belongs_to :delivery_slot
has_many :line_items
end
class Customer < ApplicationRecord
has_many :orders
has_one :address
end
And a controller that powers a dispatch screen:
class Api::OrdersController < ApplicationController
def index
orders = Order.where(status: "ready").order(:created_at).limit(200)
render json: orders.map { |o|
{
id: o.id,
customer: o.customer.name,
city: o.customer.address.city,
slot: o.delivery_slot.starts_at,
items: o.line_items.sum(&:quantity)
}
}
end
end
It reads cleanly. It also runs 1 query for the orders, then 200 for customers, 200 for addresses, 200 for slots and 200 for line items: 801 queries for one page.
Step 1: See it
The development log already tells you. Hit the endpoint and look for the same SELECT repeated with a different id each time:
Customer Load (0.3ms) SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2 [["id", 41], ["LIMIT", 1]]
Customer Load (0.2ms) SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2 [["id", 42], ["LIMIT", 1]]
...
Each query is fast. The problem is the count, and the network round trip each one costs in production, where the database is not on localhost.
For a less manual signal, add the Bullet gem in development:
# Gemfile
group :development, :test do
gem "bullet"
end
# config/environments/development.rb
config.after_initialize do
Bullet.enable = true
Bullet.rails_logger = true
Bullet.raise = false
end
Bullet logs each N+1 it detects, names the association, and tells you what to add. It also flags the opposite mistake: eager loading something you never use.
Step 2: Pick the right loading strategy
Active Record gives you three tools, and they are not interchangeable.
preload runs one extra query per association and stitches the results together in Ruby.
Order.where(status: "ready").preload(:customer, :line_items)
# SELECT "orders".* ... WHERE status = 'ready'
# SELECT "customers".* WHERE id IN (41, 42, ...)
# SELECT "line_items".* WHERE order_id IN (...)
Use it when you only need to read the associated records, not filter by them.
eager_load builds a single query with LEFT OUTER JOINs.
Order.eager_load(:customer).where(customers: { city: "Pune" })
Use it when the WHERE or ORDER BY depends on the association. Be careful with several has_many joins at once: the result set multiplies and can get slower than the N+1 you were fixing.
includes lets Rails choose. It behaves like preload unless your query references the joined table, then switches to eager_load. It is a sensible default, but when performance matters, being explicit makes the behaviour obvious to the next person reading the code.
For our endpoint, nothing filters on associations, so preload is the right call, including the nested address:
orders = Order.where(status: "ready")
.order(:created_at)
.limit(200)
.preload(:delivery_slot, :line_items, customer: :address)
801 queries become 5.
Step 3: Stop computing in Ruby what the database can compute
o.line_items.sum(&:quantity) loads every line item just to add up one column. If that is all you need, ask the database:
totals = LineItem.where(order_id: orders.map(&:id))
.group(:order_id)
.sum(:quantity)
# => { 101 => 7, 102 => 3, ... }
Then look up totals[o.id] while rendering. One grouped query instead of loading thousands of rows into memory. For counts shown on every request, a counter_cache column is cheaper still.
Step 4: Make N+1s fail loudly
Fixing today's N+1 is half the job. The other half is making sure a new one cannot slip in with next week's feature.
Rails 6.1 added strict_loading. On a relation, it raises an error if any association is lazy-loaded:
orders = Order.strict_loading.where(status: "ready").preload(:customer)
orders.first.line_items
# => ActiveRecord::StrictLoadingViolationError
You can turn it on per model (self.strict_loading_by_default = true), or app-wide in development and test only:
# config/environments/test.rb
config.active_record.strict_loading_by_default = true
Now a request spec that touches an un-preloaded association fails in CI, long before it reaches production. In production itself, prefer logging over raising:
config.active_record.action_on_strict_loading_violation = :log
If you want detection on code paths your specs do not cover, run Bullet with Bullet.raise = true in the test environment as well.
Step 5: Verify with numbers
Do not trust the fix until you have measured it. A simple request spec can pin the query count:
it "loads the dispatch list in a fixed number of queries" do
create_list(:order, 50, :with_items, status: "ready")
queries = 0
counter = ->(*) { queries += 1 }
ActiveSupport::Notifications.subscribed(counter, "sql.active_record") do
get "/api/orders"
end
expect(queries).to be <= 6
end
The test creates 50 orders, but the bound stays at 6. That is the point: the query count should not grow with the data.
A checklist for every new endpoint
- Watch the development log for repeated
SELECTs with changing ids. - Use
preloadfor reading associations andeager_loadonly when you filter or sort on them. - Push sums, counts and grouping into SQL.
- Keep
strict_loadingon in test so regressions fail in CI. - Pin the query count for your heaviest endpoints in a spec.
N+1 queries are rarely hard to fix. They are easy to miss, which is why the fix that matters most is the guardrail, not the preload.
Top comments (0)