RSpec SQLimit

The Hydra

In the Rails ecosystem, every developer knows about the so called N+1 query problem and how to deal with it. It is about impossible today to overlook tons of articles, blog posts, and tutorials mentioning both the problem and the solution to it.

The N+1 problem is a common performance anti-pattern. It happens when we fetch records from an associated table not with a single SQL query but instead using individual queries for every single record.

class User < ActiveRecord::Base
  has_many :messages
end

class Message < ActiveRecord::Base
  belongs_to :user
end

Message.where(id: 1..3).each do |message|
  puts message.user.name
end

We have executed 1 query to retrieve 3 messages and then did 3 other queries for retrieving a user of every single message.

SELECT * FROM "messages" WHERE "messages"."id" IN (1, 2, 3)
SELECT * FROM "users" INNER JOIN "messages" ON "users"."id" = "messages"."user_id" WHERE "messages"."id" = 1
SELECT * FROM "users" INNER JOIN "messages" ON "users"."id" = "messages"."user_id" WHERE "messages"."id" = 2
SELECT * FROM "users" INNER JOIN "messages" ON "users"."id" = "messages"."user_id" WHERE "messages"."id" = 3

Ineffective communication with the database will take a toll on the performance of your application. The solution per se is simple: you should eagerly preload associated records:

Message.where(id: 1..3).includes(:user).each do |message|
  puts message.user.name
end

This time, ActiveRecord runs only 2 queries instead of N. Here is how it uses the result of the first request to retrieve all associated users at once:

SELECT * FROM "messages" WHERE "messages"."id" IN (1, 2, 3)
SELECT * FROM "users" INNER JOIN "messages" ON "users"."id" = "messages"."user_id" WHERE "messages"."id" IN (1, 2, 3)

However simple and well-known the solution it is, preventing N+1 in real-world long-lasting projects can be tricky. Sometimes the problem acts like the Lernaean Hydra with a capacity to regenerate lost heads every time a developer distracts. Like Hercules, we need special instrumentation, a sort of “sword and fire” to keep the monster under our control.

A Simple Example

Let’s take a look at an oversimplified example—enough to illustrate the heart of the problem.

To start, we create two models for users and messages between them: