Materialized View — First Time Learning

5 min readApr 16, 2021

Last year, I was looking for what (new) thing that I could learn. One thing that I remembered is Materialized View. Why materialized view? Because based on PostgreSQL documentation, it is said that:

While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; yet sometimes current data is not needed. https://www.postgresql.org/docs/10/rules-materializedviews.html

So, I thought it might help to improve read performance from database on use case that I was dealing with.

Then, I took some portion of my time to start learning it. Disclaimer: It’s based on personal experience and discussion.

Source Image: https://www.subpng.com/png-nbgaxg/download.html

Here What’s I learnt from Materialized View

1. What is materialized view?

Materialized views allows us to store result of a query physically and update the data periodically. So, it consumes database server disk.

2. When to use materialized view?

2.1 Complex expensive query. For example, we have query which contains “join” more than one table. Instead of we write that long query, we can create a materialized view instead. Then, we can select from that materialized view. Also, it acts like a cache for expensive query.

2.2 Slow query. Because materialized view will create a physical table based on a filter(s) that we set when creating materialized view, it will be faster to select data from materialized view instead of original table.

2.3 Data that rarely changes or we don’t really need real time data. Materialized view needs to be refreshed when there is changes in data on original table. Otherwise, it won’t show latest data. But this can be a problem if we refresh too often. It can take some time to do it.

3. Difference between table, materialized view, and view

And of course, we need to refresh data on materialized view periodically. Based on what it is said that by PostgreSQL (that I mentioned earlier):

the (materialized view) data is not always current

If there is DML changes on original table, materialized view won’t show it directly. Data on materialized view won’t show the current data unless we refresh it.

4. Pro & Cons materialized view

5. How to Create a Materialized View?

As it is said in https://www.postgresql.org/docs/9.3/sql-creatematerializedview.html:

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

If we create materialized view with no data, we will get an error when trying to read from that view. So, we need to load the data first.

SQL Error [55000]: ERROR: materialized view view_name has not been populated
Hint: Use the REFRESH MATERIALIZED VIEW command.

If we want to drop a materialized view, run:

DROP MATERIALIZED VIEW view_name

6. How to Refresh a Materialized View?

This is where a problem just begins.

One of the way to do it is using query ‘Refresh Materialized View’ After DML data (recommended: after some period).

REFRESH MATERIALIZED VIEW view_name;
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

These below table is the comparison between running refresh materialized view concurrently and without concurrently.

That means while doing refresh materialized view without concurrently, we can’t even do select to the materialized view.

Other Things to be Considered

Not recommended to save whole data on materialized view, it’s better to use filter.
If the number of the dead tuple is high, we must often do vacuum to clean up before we load the data to materialized view.
Refresh process can take a long time. It’s related to point number 1. If we create a materialized view without filter, it can make refresh process becomes heavier and takes a longer time.
How many times ideally to refresh data in a day? For example, is once a day enough?
Does it take affect to CPU usage when refreshing materialized view?

Good Learning that I Got

Sometimes, what we expect is not always can be implemented. Based on the pro and cons also the refresh process is quite risky. I think for now, it doesn’t really match with my use case. But, I believe it can be a tool that might be useful someday for another use case that I might face in the future.

My mentor at my company, ever gave me very inspiring words:

When we are learning or doing research, the first thing that should we consider is what is the problem or issue that we are facing, not the opposite. Not what solution that we have, then we find out the problem.

When we research something that “might” be helpful for us, sometimes it can’t be implemented (depends on our use case and needs), but it is still worth doing.

By doing learning about materialized view, even though it can’t be implemented (in my use case), I still got something from that which is a good learning. At least by learning it, I got to know what is materialized view, how to use it, and when to use it.

Things that I learnt: when we learn something, it’s not always what we learn matches and can be implemented to our use case. But, it can be a tool that might be useful for other use case in the future.

Talking about materialized view, of course, can’t be done in one article. There is still many things about materialized view that can be explored. If you have any experience related to materialized view or any suggestion, don’t hesitate to comment in this post. Thank you.