When I first heard about materialized views, my mind was kind of blown. “So, I can write a SQL query and the results can be stored as its own table (and live on disk?)”. If that puppy lives in-memory, it’s GOT TO BE faster then having to dig into the deep, dark depths of the database. Right?

Yeah, it’s true. Grabbing data from disk, just sitting there, ready for you, is pretty fast. But when is it right to use a materialized view?

From my perspective, if you have a fairly large set of data that you often will need access to but doesn’t often change, it may be a good place for a mat view.

Here’s one of the more simple, real-life use cases where I implemented a materialized view to increase performance.

Our app serves up songs, all over the place. The songs we serve up to our users need to meet a certain criteria: they aren’t archived, they have a valid audio file, and a few others, as you will see below. Here’s what we used to do:

# models/song_version.rb scope :ready, where("song_versions.state='active' AND song_versions.transcoding_meta -> 'state'='finished' AND artists.state='active'").includes(song: :artist)

All over the place in our code, you’d see SongVersion.ready (often with other conditions chained on, etc).

This isn’t horrible, but being it gets called so much, and it is very important, this is a prime candidate for optimization.

So, let me show you how to begin by creating the materialized view. This will take some knowledge of writing good ol’ raw sql. Luckily, this one is a pretty simple select query. We will create this in a rails migration .

Right on. We now have a materialized view. This migration actually ran the SQL query, and because of this line, CREATE MATERIALIZED VIEW , it now is stored on disk. As you see from our select statement, this will return nothing but a table of song_version_id ‘s, and, for us, over 50k of them.

Cool, but what if some data changes? What if, say, a song_version’s state attribute changes and is no longer eligible be a “ready song version”? Oh me oh my I’m so glad you asked. For this, we can create a function. In our case, a PostgreSQL function, also known as “Stored Procedure”. Note, this lives in the same rails migration as the mat view creation.

The key here is the line REFRESH MATERIALIZED VIEW ready_song_versions; . Whenever that function is called, it will re-run the SQL query that is stored in the mat view, with the new data results, and continue to live on disk as expected.

So now we have a stored procedure, but when to call it? Whenever any of the conditions change as seen in the where statement in the mat view query.

So we create a trigger . A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.

I created triggers for every column mentioned in the where but I will show just one for brevity. They can all be modeled after this:

So this says, if any song_version has an update, delete, or truncate (which is also essentially a ‘delete’) in the database of its state then run the mat view refresher we just created.

So this in your application would now trigger a refresh:

song_version = SongVersion.last

song_version.update_attributes(state: :archived)

The mat view refreshes (and VERY quickly!), with the id of this song_version no longer present in the table.

Ok, so how to really use this in your Rails project? Let’s give ActiveRecord the knowledge of this new mat view (and its trigger to refresh it).

Here’s how:

You create a model that inherits from ActiveRecord. Setting the table_name is important (I’ll show you in a minute). Set it to readonly so it can’t be written to from ActiveRecord, and create a class method refresh so you have the ability to call a refresh from Rails, if needed.

Here’s where we use the table_name (in models/song_version.rb):

Now when we run SongVersion.ready we get the same results as before, but much quicker, as we have mostly circumvented the hard work ActiveRecord would have to do to find the data that meets the conditions we have set for “ready” song versions.

Let’s look at the performance gains!