One of the best things about Rails is how easy it is to interact with the database. Unfortunately, that's also one of the worst things about Rails if you care about performance (which you hopefully do). Inevitably, you'll find your application slowing down and track down the root cause to a recent change that spawned far more database queries than you expected, or hits the database when you least expected it.
One of the best things about Rails is how easy it is to interact with the database. Unfortunately, that's also one of the worst things about Rails if you care about performance (which you hopefully do). Inevitably, you'll find your application slowing down and track down the root cause to a recent change that spawned far more database queries than you expected, or hits the database when you least expected it.In our experience building Salsify, we've run into a number of situations where we needed to optimize our database usage. Most of these are handled out-of-the-box by Rails with eager loading (see the Rails Active Record Query Interface Guide for an introduction) and other techniques, but we've seen a recurring pattern around computed properties where eager loading wasn't sufficient.
To address those cases we've found that a combination of database views and eager loading simplified our code and boosted performance in a big way. We're excited to apply the approach to more areas of our codebase, and hope it is helpful to some of you that are running into similar challenges.
The Problem: Eager Loading Product Profile Assets
Let's start off with a recent example from our codebase: picking the image to use as a thumbnail for each product. In Salsify, every product can have digital assets (images, videos, instruction manuals, etc.) and the users can pick a specific image asset to use as a product thumbnail. But if no specific image is selected, we want to pick the first image asset to use as a thumbnail. Here are the relevant portions of our Product model before refactoring:
| class Product < ActiveRecord::Base | |
| has_many :digital_assets | |
| belongs_to :specified_profile_asset, class_name: "DigitalAsset" | |
| has_one :first_image_asset, class_name: "DigitalAsset", | |
| conditions: "digital_assets.data -> 'resource_type' = 'image'", | |
| order: "created_at ASC, id ASC" | |
| def profile_asset | |
| specified_profile_asset.present? ? specified_profile_asset : first_image_asset | |
| end | |
| end |
With this setup, we had to eager load the first image asset for each product even if it had a specified profile asset. In fact, we ended up having to eager load all digital assets for our products due to some limitations with the way that Rails (at least as of v3.2.13) handles eager loading associations with conditions.
The Solution: Eager Loading with Database Views
So how to eager load just the thumbnail asset for each product while also simplifying the code? In short, build a database view and a read-only model on top of it, and leverage Rails' built-in capabilities for the rest. For our example, we started by writing a migration to create the database view (in Postgres):
| class CreateProductProfileAssetAssociation < ActiveRecord::Migration | |
| def up | |
| execute <<-SQL | |
| CREATE VIEW product_profile_asset_associations AS | |
| SELECT p.id product_id, COALESCE(p.specified_profile_asset_id, (SELECT id FROM digital_assets WHERE product_id = p.id AND (digital_assets.data -> 'resource_type' = 'image') ORDER BY created_at ASC, id ASC LIMIT 1)) profile_asset_id | |
| FROM products p | |
| SQL | |
| end | |
| def down | |
| execute <<-SQL | |
| DROP VIEW product_profile_asset_associations | |
| SQL | |
| end | |
| end |
After running the migrations, we updated our Product model for the new approach:
| class Product < ActiveRecord::Base | |
| has_many :digital_assets | |
| belongs_to :specified_profile_asset, class_name: "DigitalAsset" | |
| has_one :profile_asset_association, readonly: true, class_name: "ProductProfileAssetAssociation" | |
| has_one :profile_asset, through: :profile_asset_association | |
| end |
Finally, we had to build a model to sit on top of the database view and support the profile_asset_association:
| class ProductProfileAssetAssociation < ActiveRecord::Base | |
| include ReadOnlyModel | |
| belongs_to :product | |
| belongs_to :profile_asset, class_name: "DigitalAsset" | |
| def ==(other) | |
| self.class == other.class && | |
| product_id == other.product_id && | |
| profile_asset_id == other.profile_asset_id | |
| end | |
| alias :eql? :== | |
| def hash | |
| code = product_id.hash | |
| code = code * 13 + profile_asset_id.hash | |
| code | |
| end | |
| end |
At its core, this is just a join table between products and specific digital assets with a few tweaks. First off, when you have a model that doesn't have an id you'll need to overwrite ==, eql? and hash so we've implemented straightforward versions of each. The other difference you might notice is the ReadOnlyModel mixin -- we needed to make sure Rails understood this wasn't a table it could write to (or delete from):
| module ReadOnlyModel | |
| def readonly? | |
| true | |
| end | |
| def self.delete_all | |
| raise ActiveRecord::ReadOnlyRecord | |
| end | |
| def delete | |
| raise ActiveRecord::ReadOnlyRecord | |
| end | |
| def self.destroy_all | |
| raise ActiveRecord::ReadOnlyRecord | |
| end | |
| def destroy | |
| raise ActiveRecord::ReadOnlyRecord | |
| end | |
| end |
With these pieces in place, system performance is far better and we're loving the improvements to the code. We'd love to hear if you're using a similar pattern anywhere in your code.