PostgreSQL – Materialized Views
In PostgreSQL, Materialized Views are Views that can physically store data. These are generally opted for if there is a requirement for fast data access. Materialized View caches the result of complex queries(that need lots of computation and operations) and further supports refreshing of the cached data. Materialized views are defined by database queries similar to that of Views in PostgreSQL.
In this article, we will look into PostgreSQL Materializes views in detail.
Creating Materialized Views:
The following statement is used for creating a materialized view in PostgreSQL:
CREATE MATERIALIZED VIEW
Now the above statement can be used as a query on the database as shown below:
Syntax: CREATE MATERIALIZED VIEW your_view_name AS your_query WITH [NO] DATA;
Let’s see what we did in the above query:
- First, we need to specify the name of the view after the CREATE MATERIALIZED VIEW statement.
- Then we add the query for the data that we need to extract from the tables after the AS keyword.
- Finally, if you want to load the query results into the materialized view, use WITH DATA option else use WITH NO DATA option.
The dvdrental database has a table name film_category where all comedy films have a category_id of 4. In this example, we will use the concept of the materialized view to filter out the film_id of all comedy movies in the database.
CREATE MATERIALIZED VIEW comedy_movie_list AS SELECT film_id FROM film_category where category_id=4 WITH DATA ;
The view contains information retrieved from the film_category table about the movies with category_id of 4.
Now if we query for the data in the comedy_movie_list view as follows:
SELECT * FROM comedy_movie_list;
Refreshing Materialized View:
To refresh a materialized view we make use of the following command:
REFRESH MATERIALIZED VIEW your_view_name;
Here we will refresh the Materialized view (comedy_movie_list) created in the above example:
REFRESH MATERIALIZED VIEW comedy_movie_list;
When you refresh data for a materialized view, PostgreSQL locks the entire table therefore you cannot query data against it. To avoid this, you can use the CONCURRENTLY option.
REFRESH MATERIALIZED VIEW CONCURRENTLY your_view_name;
Notice that the CONCURRENTLY option is only available from PostgreSQL 9.4.
Dropping Materialized View:
To remove a materialized view, use the below statement:
DROP MATERIALIZED VIEW [ IF EXISTS ] your_view_name;
Here we will drop the comedy_movie_list materialized view created in the earlier example:
DROP MATERIALIZED VIEW comedy_movie_list;