This article introduces PostgreSQL materialized views, which are used to store physical query results and can be updated periodically.

A view is a virtual table of the underlying physical table, which itself does not save data. In order to distinguish it from the simple view, the underlying table data can also be updated through the view by default after Postgres version 9.5.

Materialized views extend the concept of simple views, allowing physical data to be stored. Materialized views cache complex query results and can refresh results periodically. It is mainly used in scenarios that require fast access to data, such as data warehouse and business intelligence applications.

Introduction to materialized view

Create materialized view

Use the CREATE MATERIALIZED VIEW statement. The syntax is as follows:

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

• First, specify the view name after the CREATE MATERIALIZED VIEW clause
• Secondly, add a query to obtain data after the AS keyword
• Finally, if you need to load data during creation, use the WITH DATA option; Otherwise, WITH NO DATA is used, and the view is marked as unreadable, that is, the materialized view cannot be queried before loading the data.

refresh data

Use the REFRESH MATERIALIZED VIEW statement to load data:

REFRESH MATERIALIZED VIEW view_name;

When the materialized view is refreshed, PostgreSQL locks the entire table, so you cannot query the table. You can avoid locking tables by using the CONCURRENTLY keyword:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

Using the CONCURRENTLY keyword option, PostgreSQL creates a temporary updated version of the materialized view. By comparing the two versions, insert and update the difference part. When updating the materialized view, you can query at the same time.

The condition using the concurrent option requires that the materialized view have a unique index.

Note that the concurrent option is only introduced from PostgreSQL version 9.4.

Delete materialized view

The syntax for deleting materialized views is straightforward. Syntax:

DROP MATERIALIZED VIEW view_name;

Materialized view example

Now create the materialized view rental_by_category :

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;

Using the WITH NO DATA option here, we cannot query the view. Direct query will generate errors:

select * from rental_by_category 

SQL error [55000]: error: materialized view 'rental_by_category' is not initialized
Suggestion: use the command REFRESH MATERIALIZED VIEW

The error message has been prompted. Let's execute the refresh statement.

REFRESH MATERIALIZED VIEW rental_by_category;

The query results are as follows:

category	total_sales
Sports	4892.19
Sci-Fi	4336.01
Animation	4245.31
Drama	4118.46
Comedy	4002.48
New	3966.38
Action	3951.84
Foreign	3934.47
Games	3922.18
Family	3830.15
Documentary	3749.65
Horror	3401.27
Classics	3353.38
Children	3309.39
Travel	3227.36
Music	3071.52

We can also use the REFRESH MATERIALIZED VIEW statement to refresh the view, but if we use the current option, we need to create a unique index first.

CREATE UNIQUE INDEX rental_category ON rental_by_category (category);

You can now refresh in parallel:

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;

summary

This article has learned the materialized view of PostgreSQL, how to create, delete and load data, and demonstrated it with examples.