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. 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 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. 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 Finally, we had to build a model to sit on top of the database view and support the 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 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.
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.The Problem: Eager Loading Product Profile Assets
Product model before refactoring:The Solution: Eager Loading with Database Views
Product model for the new approach:profile_asset_association: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):