Materialized Views: The Cost-Effective Way To Extract Insights | by Aashish Nair | Aug, 2022

Why you shouldn’t use regular views for every task

It’s easy to get accustomed to using views in SQL as a tool of convenience. Views are versatile and save users the trouble of writing complex queries repeatedly.

That being said, they are not always the best tool for the job.

In fact, depending on the use case, views can be inferior to the underrated alternative: materialized views.

Here, we provide a brief overview of materialized views, discuss ways they can trump views, and explain why it is in your best interest to embrace them even if you’ve been managing well without them so far.

Materialized Views

Materialized views resemble views, but there is a clear difference between them.

Unlike views, which store queries, materialized views store the output of queries.

This might seem like a trivial distinction. After all, what difference does storing a query output instead of a query make if it returns the same information?

Benefits

Materialized views can trump views when computation is the deciding factor. The key perk of materialized views is that they won’t require as much computation since the query result is already predetermined.

Imaging performing analyses on large quantities of data where the same information is periodically obtained through joins and aggregation. This approach is valid, but running the same queries repeatedly to acquire the same information is simply a waste of computation.

With materialized views, users can extract the same information without needing to repeatedly run computationally intensive queries.

Limitations

Of course, materialized views come with their own drawbacks.

Since they only store the query output, they don’t adjust to changes made to the underlying database. This can be an issue as materialized views would then be prone to reporting potentially outdated values.

For materialized views to remain usable, one would have to manually update them whenever the data is modified in many manner.

Furthermore, unlike views, materialized views require memory usage as the query results that they contain are stored on the disk.

Case Study

The procedure for creating and maintaining materialized views in SQL is very similar to that of views, but there are distinctions worth mentioning. So, let’s carry out a simple case study to demonstrate the materialized view.

We have a simple table named Animals, which shows records of animals and their corresponding ages.

Suppose that we plan on frequently checking the count of each animal type throughout a study. We can write the query that produces this count and store it in a materialized view named animal_count.

With this materialized view, we can inspect the animal count at any time.

Since the query output is now precomputed, accessing it now through the materialized view will require less time and computation.

Unfortunately, unlike views, which run stored queries and adjust to any change in the underlying data, materialized views need to be manually refreshed.

To showcase the importance of refreshing materialized views, we can remove all records of rabbits in the table and see what the materialized view shows after the modification.

As shown by the output, the count for rabbits is still remaining despite all records of rabbits already being removed. This means that the materialized view is now reporting outdated information.

To ensure that the materialized view keeps up with changes made to the database, users have to use the REFRESH command.

Now, the materialized view matches the underlying data.

Why Use Materialized Views

Materialized views are similar to views in both syntax and functionality. However, they require additional maintenance to remain usable. So why should users even rely on materialized views when they can stick to using regular views?

This can be a valid argument for users accustomed to writing queries using free RDBMS systems (e.g., PostgreSQL), where the computational demand and execution time of queries are inconsequential.

Users of such software can write queries for free and can develop the habit of using views alone for convenience. They may use views indiscriminately to run and rerun queries regardless of their computational demand.

Unfortunately, such methodology does not translate well to analyses carried out in data warehouses. Data warehouses (on-prem or cloud) offer vast storage space and considerable compute power. In return, these warehouses charge users based on their own pricing models.

Since queries requiring higher computation will incur greater prices, users of such platforms need to be wary of how they carry out analyses as they can be charged by the query. For that reason, it is imperative to consider price optimization when planning analyses with data warehouses.

Materialized views serve as a much more price-friendly alternative to views for extracting information from databases (assuming that the data is not frequently modified).

Moreover, their main shortcoming (i.e., the need to manually maintain them) may not even be an issue depending on the data warehouse of choice. Many cloud data warehouses like Snowflake include features that automatically update materialized views after changes are made to the data without the need of any maintenance work from the user.

Conclusion

Materialized views are the superior alternative for analyzing data that is subject to little to no change as they enable users to obtain information without rerunning the same queries.

Businesses paying for data warehousing services will naturally adopt analytical approaches that are price-friendly.

Thus, even if you’re comfortable with analyzing data using free RDMBS software, you will likely have to migrate to big data solutions in the future, so there is no harm in getting familiar with materialized views and embracing the cost-optimization mindset.

Hopefully, you’ve come to understand materialized views and will incorporate them in your scripts more often. However, if you think you need to take a step back and explore regular views in general, check out the following article:

I wish you the best of luck in your data science endeavors!

Why you shouldn’t use regular views for every task

It’s easy to get accustomed to using views in SQL as a tool of convenience. Views are versatile and save users the trouble of writing complex queries repeatedly.

That being said, they are not always the best tool for the job.

In fact, depending on the use case, views can be inferior to the underrated alternative: materialized views.

Here, we provide a brief overview of materialized views, discuss ways they can trump views, and explain why it is in your best interest to embrace them even if you’ve been managing well without them so far.

Materialized Views

Materialized views resemble views, but there is a clear difference between them.

Unlike views, which store queries, materialized views store the output of queries.

This might seem like a trivial distinction. After all, what difference does storing a query output instead of a query make if it returns the same information?

Benefits

Materialized views can trump views when computation is the deciding factor. The key perk of materialized views is that they won’t require as much computation since the query result is already predetermined.

Imaging performing analyses on large quantities of data where the same information is periodically obtained through joins and aggregation. This approach is valid, but running the same queries repeatedly to acquire the same information is simply a waste of computation.

With materialized views, users can extract the same information without needing to repeatedly run computationally intensive queries.

Limitations

Of course, materialized views come with their own drawbacks.

Since they only store the query output, they don’t adjust to changes made to the underlying database. This can be an issue as materialized views would then be prone to reporting potentially outdated values.

For materialized views to remain usable, one would have to manually update them whenever the data is modified in many manner.

Furthermore, unlike views, materialized views require memory usage as the query results that they contain are stored on the disk.

Case Study

The procedure for creating and maintaining materialized views in SQL is very similar to that of views, but there are distinctions worth mentioning. So, let’s carry out a simple case study to demonstrate the materialized view.

We have a simple table named Animals, which shows records of animals and their corresponding ages.

Suppose that we plan on frequently checking the count of each animal type throughout a study. We can write the query that produces this count and store it in a materialized view named animal_count.

With this materialized view, we can inspect the animal count at any time.

Since the query output is now precomputed, accessing it now through the materialized view will require less time and computation.

Unfortunately, unlike views, which run stored queries and adjust to any change in the underlying data, materialized views need to be manually refreshed.

To showcase the importance of refreshing materialized views, we can remove all records of rabbits in the table and see what the materialized view shows after the modification.

As shown by the output, the count for rabbits is still remaining despite all records of rabbits already being removed. This means that the materialized view is now reporting outdated information.

To ensure that the materialized view keeps up with changes made to the database, users have to use the REFRESH command.

Now, the materialized view matches the underlying data.

Why Use Materialized Views

Materialized views are similar to views in both syntax and functionality. However, they require additional maintenance to remain usable. So why should users even rely on materialized views when they can stick to using regular views?

This can be a valid argument for users accustomed to writing queries using free RDBMS systems (e.g., PostgreSQL), where the computational demand and execution time of queries are inconsequential.

Users of such software can write queries for free and can develop the habit of using views alone for convenience. They may use views indiscriminately to run and rerun queries regardless of their computational demand.

Unfortunately, such methodology does not translate well to analyses carried out in data warehouses. Data warehouses (on-prem or cloud) offer vast storage space and considerable compute power. In return, these warehouses charge users based on their own pricing models.

Since queries requiring higher computation will incur greater prices, users of such platforms need to be wary of how they carry out analyses as they can be charged by the query. For that reason, it is imperative to consider price optimization when planning analyses with data warehouses.

Materialized views serve as a much more price-friendly alternative to views for extracting information from databases (assuming that the data is not frequently modified).

Moreover, their main shortcoming (i.e., the need to manually maintain them) may not even be an issue depending on the data warehouse of choice. Many cloud data warehouses like Snowflake include features that automatically update materialized views after changes are made to the data without the need of any maintenance work from the user.

Conclusion

Materialized views are the superior alternative for analyzing data that is subject to little to no change as they enable users to obtain information without rerunning the same queries.

Businesses paying for data warehousing services will naturally adopt analytical approaches that are price-friendly.

Thus, even if you’re comfortable with analyzing data using free RDMBS software, you will likely have to migrate to big data solutions in the future, so there is no harm in getting familiar with materialized views and embracing the cost-optimization mindset.

Hopefully, you’ve come to understand materialized views and will incorporate them in your scripts more often. However, if you think you need to take a step back and explore regular views in general, check out the following article:

I wish you the best of luck in your data science endeavors!