PostgreSQL - Materialized Views

Materialized Views in PostgreSQL are a powerful tool that can significantly enhance query performance by physically storing the result of a complex query. Unlike standard views, which compute their result set every time they are queried, materialized views store the query result and can be refreshed periodically. This makes them ideal for scenarios where fast data access is required.

In this article, we will look into PostgreSQL Materializes views in detail.

Syntax:

CREATE MATERIALIZED VIEW your_view_name AS your_query WITH [NO] DATA;

Parameters:

Let's see what we did in the above query:

• 'CREATE MATERIALIZED VIEW your_view_name': Specifies the name of the materialized view.
• 'AS your_query': Defines the query whose result will be stored in the materialized view.
• 'WITH [NO] DATA': Indicates whether to populate the materialized view with data immediately ('WITH DATA') or to create the view without populating it (WITH NO DATA).

For the sake of this article, we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link.

PostgreSQL Materialized Views Example

Let us take a look at an example of Materialized Views in PostgreSWL to better understand the concept.

Creating a Materialized View

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.

This query creates a materialized view named 'comedy_movie_list' that stores the 'film_id' of all comedy movies.

Querying a Materialized View

Now if we query for the data in the 'comedy_movie_list' view as follows:

SELECT * FROM comedy_movie_list;

Output:

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;

Output:

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; 

Output:

Conclusion

PostgreSQL Materialized Views offer a robust solution for scenarios requiring fast data access and improved query performance. By understanding how to create, refresh, and manage materialized views effectively, you can leverage their full potential to enhance your database performance and support your application’s data needs.