PostgreSQL: How to create a Materialized View and increase the Query Performance

What is Materialized View?

It is a one type of view which contains the result of a query.

It caches the result of complex query and you can access the result data like a normal table.

It requires physical space to store generated data. Once we put any complex query in Materialized View, we can access that query and data without disturbing a physical base table.

Only one thing you should do is: Periodically refresh your Materialized View to get newly inserted data from the base table.

In PostgreSQL, You can create a Materialized View and can refresh it.
Let me show you, full practical on this.

Create a table with sample data:

Create a materialized view to select only Male – Employee data:

Select a data of this materialized view:

Refresh a materialized view:

Once you create a materialized view, you should also refresh it otherwise newly inserted values of the table will not update in this view.

Because It acts like a physical table and once your base table update, you should refresh the data of the materialized view.

Create another non-materialized view to select only Male – Employee data:

Check the size of both views and find size difference between both views:
Non-Materialized view size is 0 byte and Materialized view size is around 8000 bytes because It stores the generated data.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com