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:

 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):

After running the migrations, we updated our Product model for the new approach:

Finally, we had to build a model to sit on top of the database view and support the profile_asset_association:

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):

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.