PostgreSQL Materialized Views

Summary: in this tutorial, you will learn about PostgreSQL materialized views that store the result of a query physically and refresh the data from base tables periodically.

Introduction to the PostgreSQL materialized views

In PostgreSQL, views are virtual tables that represent data of the underlying tables. Simple views can be updatable.

PostgreSQL extends the view concept to the next level which allows views to store data physically. These views are called materialized views.

Materialized views cache the result sets of a complex and expensive query and allow you to refresh data periodically.

The materialized views can be useful in many cases that require fast data access. Therefore, you often find them 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;Code language: SQL (Structured Query Language) (sql)

How it works.

• First, specify the view_name after the CREATE MATERIALIZED VIEW clause
• Second, add the query that gets data from the underlying tables after the AS keyword.
• Third, if you want to load data into the materialized view at the creation time, use the WITH DATA option; otherwise, you use WITH NO DATA option. If you use the WITH NO DATA option, 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:

REFRESH MATERIALIZED VIEW view_name;Code language: SQL (Structured Query Language) (sql)

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 view_name;Code language: SQL (Structured Query Language) (sql)

With CONCURRENTLY option, PostgreSQL creates a temporary updated version of the materialized view, compares two versions, and performs INSERT and UPDATE only the differences.

PostgreSQL allows you to query data against a materialized view while it is being updated. One requirement for using CONCURRENTLY option is that the materialized view must have a UNIQUE index.

Notice that CONCURRENTLY option is only available from PostgreSQL 9.4.

Removing materialized views

To remove a materialized view, you use the DROP MATERIALIZED VIEW statement:

DROP MATERIALIZED VIEW view_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the materialized view that you want to drop after the DROP MATERIALIZED VIEW keywords.

PostgreSQL materialized views example

The following statement creates a materialized view named 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;Code language: PHP (php)

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;Code language: SQL (Structured Query Language) (sql)

[Err] ERROR: materialized view "rental_by_category" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Now, if you query the data again, you will get the result as expected:

 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
(16 rows)Code language: PHP (php)

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);Code language: SQL (Structured Query Language) (sql)

Let’s refresh data concurrently for the rental_by_category view.

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;Code language: SQL (Structured Query Language) (sql)

Summary

• A materialized view is a view that stores data that comes from the base tables.
• Use the CREATE MATERIALIZED VIEW statement to create a materialized view.
• Use the REFRESH MATERIALIZED VIEW statement to load data from the base tables into the view.
• Use the DROP MATERIALIZED VIEW statement to drop a materialized view.