DEV Community

Toshita Pandey
Toshita Pandey

Posted on

Finding and Killing N+1 Queries in a Rails API Before Your Users Notice

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]]
...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 (...)
Enter fullscreen mode Exit fullscreen mode

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" })
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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, ... }
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 preload for reading associations and eager_load only when you filter or sort on them.
  • Push sums, counts and grouping into SQL.
  • Keep strict_loading on 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)