Eager loading Active Storage models to avoid N+1 issues

Active Storage is a great way to manage file attachments in Rails. It abstracts away a lot of tedious configuration behind a clean interface for managing file attachments.

Active Storage uses ActiveStorage::Attachment to associate model records with ActiveStorage::Blob which stores the attached file info.

Let’s say we have a User model where each user can have one avatar. The model looks something like this.

# app/models/user.rb 
class User < ApplicationRecord
  has_one_attached :avatar, dependent: :destroy
end

When we add has_one_attached :avatar to User model, Active Storage implicitly adds 2 associations to the User model:

has_one :avatar_attachment, -> { where(name: "avatar") }, class_name: "ActiveStorage::Attachment", as: :record, inverse_of: :record, dependent: :destroy
has_one :avatar_blob, through: :avatar_attachment, class_name: "ActiveStorage::Blob", source: :blob

These 2 models are queried when we try to access any of the attachment details. We can confirm this in rails console:

irb(main):031:0> User.first.avatar.filename
  User Load (3.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
  ActiveStorage::Attachment Load (1.5ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 2], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ActiveStorage::Blob Load (0.4ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
=> #<ActiveStorage::Filename:0x00007feee8bd4758 @filename="dl.jpg">

The Problem

The n + 1 query issue occurs when you are dealing with attachments for multiple records. For instance, consider we want to build a user list page displaying all the registered users. The controller and the view look something like below.

# app/controllers/users_controller.rb 
class UsersController < ApplicationRecord
  def index
    # Using will_paginate gem for pagination support. 
    @users = User.paginate(page: params[:page])
  end
end

# app/views/users/index.html.erb
... 
<% @users.each do |user| %>
  <%= image_tag user.avatar %>    
<% end %>
...

The above implementation causes an n + 1 query issue. We can confirm this from the logs.

  ...
  ActiveStorage::Attachment Load (0.8ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 2], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Blob Load (0.3ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Attachment Load (0.2ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 3], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Blob Load (0.3ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 4], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 8], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Attachment Load (0.2ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 5], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
    ActiveStorage::Blob Load (0.2ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
    ↳ app/views/users/index.html.erb:2
  ...

Eager loading associated records to prevent n + 1

To solve ActiveRecord n + 1 issues one of the approach is to use includes.

We can fix the issue by eager loading ActiveStorage::Attachment, and ActiveStorage::Blob tables. Updated controller code is as below:

# app/controllers/users_controller.rb 
class UsersController < ApplicationRecord
  def index
    # Using will_paginate gem for pagination support. 
    @users = User.paginate(page: params[:page]).includes(avatar_attachment: :blob)
  end
end

Now when we load the user list page, and check the logs the n + 1 query issue is no more.

Started GET "/users" for ::1 at 2020-02-24 20:03:49 +0530
Processing by UsersController#index as HTML
  Rendering users/index.html.erb within layouts/application
  User Load (1.8ms)  SELECT "users".* FROM "users"
  ↳ app/views/users/index.html.erb:1
  ActiveStorage::Attachment Load (0.8ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? AND "active_storage_attachments"."record_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["record_type", "User"], ["name", "avatar"], ["record_id", 2], ["record_id", 3], ["record_id", 4], ["record_id", 5], ["record_id", 6], ["record_id", 7], ["record_id", 8], ["record_id", 9], ["record_id", 10], ["record_id", 11], ["record_id", 12], ["record_id", 13]]
  ↳ app/views/users/index.html.erb:1
  ActiveStorage::Blob Load (0.7ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["id", 6], ["id", 7], ["id", 8], ["id", 9], ["id", 10], ["id", 11], ["id", 12], ["id", 13], ["id", 14], ["id", 15], ["id", 16], ["id", 17]]
  ↳ app/views/users/index.html.erb:1
  Rendered users/index.html.erb within layouts/application (Duration: 17.2ms | Allocations: 5876)
[Webpacker] Everything's up-to-date. Nothing to do
Completed 200 OK in 32ms (Views: 27.0ms | ActiveRecord: 3.4ms | Allocations: 10379)

`with_attached_#{name}`

Along with the 2 associations, Active Storage also creates the following scope:

`scope :"with_attached_#{name}", -> { includes("#{name}_attachment": :blob) }`

In the case of our User model, the scope would be

`scope :with_attached_avatar, -> { includes(avatar_attachment: :blob) }`

For non-nested includes we can use this scope. The refactored controller is as shown below:

# app/controllers/users_controller.rb 
class UsersController < ApplicationRecord
  def index
    # Using will_paginate gem for pagination support. 
    @users = User.paginate(page: params[:page]).with_attached_avatar
  end
end

Eager loading in case of `has_many_attached` association

There is not much difference when compared to has_one_attached.

You have to use has_many :"#{name}_attachments" instead of has_one :"#{name}_attachment. For example if User has many attached avatars we can eager load all of them as below

irb(main):007:0> users = User.includes(avatar_attachments: :blob).to_a

with_attached_#{name} still works in the same way.

Conclusion

Active Storage implicitly defines some associations, and scopes that can be used to eager load models used by Active Storage.

Eager loading Active Storage models to avoid N+1 issues

The Problem

Eager loading associated records to prevent n + 1

with_attached_#{name}

Eager loading in case of has_many_attached association

Conclusion

Rails 7 adds disable_joins: true option to has_many :through association

Rails 7 adds disable_joins: true option to has_one :through association

Improving Database performance and overcoming common N+1 issues in Active Record using includes, preload, eager_load, pluck, select, exists?

`with_attached_#{name}`

Eager loading in case of `has_many_attached` association