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.