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.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of