PostgreSQL Materalized View

In today’s article, you will learn about PostgreSQL Materialized Views that store the result of a query physically and refresh the data from base tables periodically.

We said that the difference between Materalized View and normal view is that no data is kept in the virtual table for the view, but in the materalized view, the data containing the query we prepared is physically kept on the disk.

Unlike the normal view, indexing can be added to the Materalized View.

Let’s create a materalized view on the Pagila database that shows which actor starred in which movies.

We created our view as seen in the picture above, and as a result of this query, 5460 rows were returned as written at the bottom right.

The materalized view takes the data when it is created and keeps it on disk.

After creating the materalized view, the data in the materalized view does not change when data is added, removed or updated.

To update the changed data, it is necessary to update the materalized view with the “refresh materalized view” command.