Eager loading Active Storage models to avoid N+1 issues

5 minute read

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.8 ms ) 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.3 ms ) 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.2 ms ) 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.3 ms ) 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.1 ms ) 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.1 ms ) 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.2 ms ) 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.2 ms ) 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.