Materialized Views

Materialized view is a type of view in SQL which offers performance improvement.

To learn what is a view and the basics of view, read this article: Views in SQL

First I would like to start this article by explaining you the difference between a Standard View and a Materialized View.

Standard View:

• It only stores the query given by a user.
• Executes the stored query given by the user
• This does not offer any performance improvements
• It always returns the latest data.

Materialized View:

• Stores both the query and the result of the query
• Offers performance improvement by storing the query result.
• It does not return the latest data always.

When to use Materialized Views over Normal views?

We can use Materialized views in places where we don’t need the most recent data and stale data can be tolerated to some extent.

Let’s query the number of records grouped by name in users table using SELECT statement. The users table contains about two crore records.

SELECT Name, COUNT(id) FROM users GROUP BY Name;

This query is taking around 0.7 seconds or 700 ms to complete(You can see that in the bottom of the above image). Can we optimize this further? Yes with the help of materialized views.

If you need data to follow along, here is the CSV file, Restore it in a table in your database. https://drive.google.com/drive/folders/1ZgnyExMX8qkpaAMbxh261tymLRHZPjQD?usp=sharing

How does Materialized View improve performance?

As I said earlier, the Materialized view stores both query and result of the query. So when a user queries the Materialized view, it returns the result quickly already retrieved from the query execution , hence resulting in a performance improvement

Note: This blog post makes use of PostgreSQL.

How to Create a Materialized View?

Just like a view, a Materialized view also takes a query as input and can be created using a similar command. The difference is we use CREATE MATERIALIZED VIEW statement instead of CREATE VIEW statment.

Syntax for creating a Materialized view.

CREATE MATERIALIZED VIEW view_name AS QUERY;

Now Lets create a Materialized for our users table using the following query.

CREATE MATERIALIZED VIEW name_count AS SELECT DISTINCT(Name),COUNT(id) FROM users GROUP BY Name;

Lets ‘s query the view using select statement.

SELECT * FROM name_count

Let’s take a look at the time taken to get data from a materialized view.

Wow, that was sweet. We reduced the query time from 700 ms to mere 7 ms using materialized views and had some terrific performance improvement.

Now it’s time to look at the downside of Materialized view. Now I will remove around 10 records in the original table and query both the original table and the materialized view.

First, lets have a look at the original table results for the query

SELECT Name,COUNT(id) FROM users GROUP BY Name;

Now, lets’s query our materialized view.

SELECT * FROM name_count;

Can you see, though we have removed records from the original table, the materialized view didn’t get updated. Because materialized stores whatever result set it gets at the time of its creation.

To update the Materialized view, we need to refresh it. we can do that by the following command.

REFRESH MATERIALIZED VIEW name_count;

Let’s take a look at the Materialized result now.

SELECT * FROM name_count;

Yeah! The Materialized view is updated now. So to update a materialized view, we just need to refresh that.

That’s a wrap!

Conclusion:

Materialized View is a type of view which offers signifcant performance improvement of view at the cost of not always having the latest data. Since we can’t get the latest data always we should not use materialized for every query that takes time, we have to analyze the business requirement of the query and decide it for ourselves.

Thanks for reading till the end of the article. To learn more data engineering related concepts follow me on LinkedIn and Medium.