August 3, 2023
Summary: this tutorial introduces you to PostgreSQL materialized views that allow you to store the result of a query physically and update the data periodically.
In PostgreSQL view tutorial, you have learned that views are virtual tables that represent data of the underlying tables. Simple views can be also updatable.
PostgreSQL extends the view concept to the next level that allows views to store data physically. And these views are called materialized views.
Materialized views cache the result of a complex and expensive query and allow you to refresh this result periodically.
The materialized views are useful in many cases that require fast data access therefore they are often used in data warehouses and business intelligence applications.
Creating materialized views
To create a materialized view, you use the
CREATE MATERIALIZED VIEW statement as follows:
CREATE MATERIALIZED VIEW view_name AS query WITH [NO] DATA;
How it works.
- First, specify the
CREATE MATERIALIZED VIEWclause
- Second, add the query that gets data from the underlying tables after the
- Third, if you want to load data into the materialized view at the creation time, use the
WITH DATAoption; otherwise, you use
WITH NO DATA. In case you use
WITH NO DATA, the view is flagged as unreadable. It means that you cannot query data from the view until you load data into it.
Refreshing data for materialized views
To load data into a materialized view, you use the
REFRESH MATERIALIZED VIEW statement as shown below:
REFRESH MATERIALIZED VIEW view_name;
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
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;
You can query against a materialized view while it is being updated. One requirement for using
CONCURRENTLY option is that the materialized view must have a
CONCURRENTLY option is only available from PostgreSQL 9.4.
Removing materialized views
Removing a materialized view is pretty straightforward as we have done for tables or views. This is done using the following statement:
DROP MATERIALIZED VIEW view_name;
PostgreSQL materialized views example
The following statement creates a materialized view named
CREATE MATERIALIZED VIEW rental_by_category AS SELECT c.name AS category, sum(p.amount) AS total_sales FROM (((((payment p JOIN rental r ON ((p.rental_id = r.rental_id))) JOIN inventory i ON ((r.inventory_id = i.inventory_id))) JOIN film f ON ((i.film_id = f.film_id))) JOIN film_category fc ON ((f.film_id = fc.film_id))) JOIN category c ON ((fc.category_id = c.category_id))) GROUP BY c.name ORDER BY sum(p.amount) DESC WITH NO DATA;
Because of the
WITH NO DATA option, you cannot query data from the view. If you try to do so, you’ll get an error message as follows:
SELECT * FROM rental_by_category;
ERROR: materialized view "rental_by_category" has not been populated HINT: Use the REFRESH MATERIALIZED VIEW command.
PostgreSQL is helpful to give you a hint to ask for loading data into the view. Let’s do it by executing the following statement:
REFRESH MATERIALIZED VIEW rental_by_category;
Now, if you query data again, you will get the result as expected.
From now on, you can refresh the data in the
rental_by_category view using the
REFRESH MATERIALIZED VIEW statement.
However, to refresh it with
CONCURRENTLY option, you need to create a
UNIQUE index for the view first.
CREATE UNIQUE INDEX rental_category ON rental_by_category (category);
Let’s refresh data concurrently for the
REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;
In this tutorial, you have learned how to work with PostgreSQL materialized views, which come in handy for analytical applications that require quick data retrieval.