The "Rollup" Pattern

An experiment to remove expensive joins, inheritances, and methods from primary API endpoints.

Scaling quickly at a company yields an accelerated education in database management. I’m going to tell you about the time a single query on our main page started melting our database, and how we solved it with the rollup pattern.

rollup = a column in a database row that prevents expensive joins/functions from being called on a consistent basis.

In an Object Oriented architecture, this will help limit the amount of Objects being returned with their associated data. Trust me, your serializer and your database will thank you.

Rollup: Simple Example:

This implementation is based on Ruby on Rails, using Sidekiq for async processing. The examples remain true to Rails, but the concept is easily adaptable elsewhere.

Welcome to the popularity contest. For UI purposes we only care about a user’s count of friendship bracelets.

Original Setup:

QUERY
User.all.joins(:friendship_bracelets)

SERIALIZER 
def friendship_count
  user.friendship_bracelets.length
end

New Setup:

MIGRATION 
add_column :users, :rollup, :jsonb

DATA STORAGE
User.last.rollup => {friendship_count: 94}

QUERY
User.all

SERIALIZER
def friendship_count
  user.rollup['friendship_count'] || user.friendship_bracelets.count
end

We omit the joins in the query, and return on only the User. It also means that in the serializer, we aren’t loading 94 friendship_bracelet objects into memory.

I know you’re thinking “but my situation is so much more complex and this won’t work,” but bear with me. I promise that complicated logic still has a comfortable home with rollups.

Rollup: Real World Example:

The highest throughput call to our API was triggering a query that returned over 3GB worth of data. This was toppling our Postgres instance and locking tables across the board. Full meltdown status.

A. Rollup the low-value joins

Each card is referred to as an asset, which has a number of associations. An asset has_many Files, but File instances are huge from a size perspective (many rows, lots of nested jsonb columns). We were joining files in their entirety when the UI required just three simple data points from files.

1. Thumbnail Image for file
2. Count of files
3. Extension of first file

Asset.last.rollup => {"extension"=>"png","thumbnail"=>"https://bit.ly/2xLA2It", "file_count"=>1 }

File join eliminated. *Note that an ActiveRecord/SQL SELECT couldn’t cut it, since the thumbnail method requires a number of fields, metadata, inheritances to generate. Legacy codebase…

Next, we implemented this same logic on image auto-tagging records. Instead of joining on our Tag model and returning them in the query, we added an array of tag names to the rollup.

Asset.last.rollup => {"extension"=>"png","thumbnail"=>"https://bit.ly/2xLA2It", "file_count"=>1, tag_names: ['pizza', 'party'] }

Tag join eliminated.

At this point, the returns from our queries were literal fractions of their original size.

B. Rollup the intensive methods

If you live in a legacy Object Oriented Relational database, chances are you have some methods that reference Models through other Models. Things get complicated pretty fast, and certain methods in our Rails stack pull in a ridiculous amount of context.

You wanted a banana but what you got was a gorilla holding the banana and the entire jungle. — Joe Armstrong, creator of Erlang, on software reusability.

If these methods live in the serializer, you’re pulling a whole lot of context into memory and slowing down what is often the rate-limiting piece of your stack. Move this complicated logic to an async worker and store it in a rollup field, so that recall is “memoized” and available at an instant.

Asset.last.rollup => {"extension"=>"png","thumbnail"=>"https://bit.ly/2xLA2It", "file_count"=>1, tag_names: ['pizza', 'party'], background_color: 'white', has_video_files: true, template_editor_link: nil }

For example, the template_editor_link needs to reference a Feature Model. To get there, it quietly climbs up 5 Models through inheritance. Not great, we know. But a worse idea would be calling the method on asset in the serializer time and time again, only to return what is essentially a constant value.

C: Craft a rollup job & async callers

Reliability is the name of the game here. This job that can be called asynchronously or synchronously, and it only accepts an asset_key/id. There is immense value in trusting you can execute this job at any time, in any order, without any context — sort of like a state machine.

D: Adjust serializers and handle fallbacks

Reference the rollup columns, but also include fallbacks so your data is accurate even if your async processes breakdown for some reason. You should notice N+1 queries in your analytics tracker if your rollup ecosystem is down.

Speed Implications:

To see the speed implications, checkout my article on benchmarking:

Spoiler alert: 391ms => 86.4ms average response times

4 semi-pro tips for your implementation:

1. Do not rollup sensitive data. If your app lives or dies by the accuracy and immediacy of the data, it doesn’t belong in the rollup. Think UI helpers for high traffic endpoints, on large data sets. Rollups won’t be a game changer at small scales, so it’s best to perform fresh computations when working with a single instance.
2. Use a jsonb column. If you haven’t heard of jsonb, it behaves like a standard JSON Object in the database. The upside is that it’s incredibly flexible and can accommodate as many key/value pairs as you’d like, meaning it scales very well. The downside is that it is harder to query, and without discipline your rows can quickly bloat. Also, it isn’t available in all databases (MySQL released jsonb in version 8.0, MS SQL doesn’t have it at the time of publication).
3. Write a functionally pure RollupJob. There is immense value in trusting you can this job at any time, in any order, without any context. We even schedule a nightly task to trigger rollups on every asset, in case a dev bypassed something from the console.
4. Develop an asynchronous ecosystem for updates. The hardest part will be finding and keeping up with all the touch points that affect the rollup fields. If you’re using callbacks, restrict them to the applicable updates. Rails/ActiveRecord includes a previous_changes method that informs which fields have been updated on a Model. For example:

trigger_rollup_job if
(self.previous_changes.keys & [‘thumbnail’, 'deleted_at']).present?

Notes:

This is an iterative approach to keep our app performant as it grows and scales. This was an appropriate solution for our current situation, and this particular endpoint.