RSpec SQLimit

Cover for RSpec SQLimit

Services & Skills

Share on

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

class Message < ActiveRecord::Base
  belongs_to :user
Message.where(id: 1..3).each do |message|

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|

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:


In the same orbit

Explore more open source projects

How can we help you?

Martians at a glance
years in business

We transform growth-stage startups into unicorns, build developer tools, and create open source products.

If you prefer email, write to us at