Views allow you to interact with the result of a query as if it were a table itself, but they do not provide a performance benefit, as the underlying query is still executed, perfect for sharing logic but still having real-time access to the source data. Views focus on abstracting away complexity and encouraging reuse.
Task mat_top_scorers : :environment do MatTopScorer.
#MATERIALIZE GEM UPDATE#
You will need to figure out how often it makes sense to update the data for your specific use-case, depending on how often the data is changing and how quickly those changes need to be reflected to the end user.ĭesc "Refresh materialized view for top scorers" Modifying the MatTopScorer model, let's add a refresh method that can be called any time the data is to be refreshed. The downside is that we have to control when the cache is refreshed. This is what gives us the speed improvements and the ability to add indexes. "team_name" = $ 1 ] = > 30 Refreshing a materialized viewĪs mentioned previously, materialized views cache the underlying query's result to a temporary table. ( 1.3ms ) SELECT COUNT ( * ) FROM "mat_top_scorers" WHERE "mat_top_scorers". where (team_name : 'Toronto Maple Leafs' ). ( 60.2ms ) SELECT COUNT ( * ) FROM "top_scorers" WHERE "top_scorers". Also notice that we are able to add indexes to the materialized view. The difference is in the migration, passing in materialized: true to the create_view method. This produces two files, the first of which contains the SQL to produce the underlying view of the data. Just like we saw with our regular view, materialized views begin the same way, by executing a command to generate a new view migration: rails g scenic:view mat_top_scorers. We can resolve this by refreshing the materialized view, which we'll get to in a bit. But, when the underlying data from the source tables is updated, the materialized view becomes out of date, serving up an older cached version of the data. This can provide serious performance benefits, especially considering you can index materialized views. When you query a materialized view, you aren't querying the source data, rather the cached result. The difference is that they save the result of the original query to a cached/temporary table. Materialized views take regular views to the next level, though they aren't without their drawbacks. This means, if the larger query discussed above takes 450ms to execute, executing SELECT * FROM top_scorers will also take 450ms. It will only be as efficient as its underlying query is. order (goal_count : :desc ) What makes a view materialized?Ī regular view still performs the underlying query which defined it. Scenic gives us the ability to define migrations that create, update, or drop views, just as you're used to doing with regular tables in Rails. How does that look like? First things first, we'll create a view using Scenic. Instead, let's try something thats closer to how Rails does things. But, as we all know, one-off schema changes are hard to keep track of.
Now, we could make a new view by running CREATE VIEW in Postgres. They allow for the succinct abstraction of a complicated query, and allow us to re-use this logic in a simple to understand way. You can see why views can come in handy in many different scenarios. seasonĪ view allows us to take the final result of this query, and query against that as if it were any other table. a little unrealistic, but I wanted the dataset used to be substantial! I am using seed data that generates 31 teams, each playing 200 games in a season, scoring 20 goals per game. It takes approximately 450ms to execute on my computer. Looking at the query below, it isn't overly complex, but it does include 3 joins, grouping by a number of fields to aggregate the numbers of goals scored for a player each season.
#MATERIALIZE GEM FULL#
We'll also talk a bit about the performance benefits that a Materialized View can bring to your application:Ī view allows us to query against the result of another query, providing a powerful way of abstracting away a complex query full of joins, conditions, groupings, and any other clause that can be added to an SQL query.