Introduction to the PostgreSQL materialized views

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

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

Materialized views cache the result set of an 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 theCREATE MATERIALIZED VIEW statement as follows:

CREATE MATERIALIZED VIEW [IFNOT EXISTS] view_nameASqueryWITH [NO] DATA;

How it works.

• First, specify theview_name after theCREATE MATERIALIZED VIEW clause
• Second, add the query that retrieves data from the underlying tables after theAS keyword.
• Third, if you want to load data into the materialized view at the creation time, use theWITH DATA option; otherwise, you useWITH NO DATA option. If you use theWITH 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.
• Finally, use the IF NOT EXISTS option to conditionally create a view only if it does not exist.

Refreshing data for materialized views

To load data into a materialized view, you use the  REFRESH MATERIALIZED VIEW statement:

REFRESH MATERIALIZED VIEW view_name;

When you refresh data for a materialized view, PostgreSQL locks the underlying tables. Consequently, you will not be able to retrieve data from underlying tables while data is loading into the view.

To avoid this, you can use theCONCURRENTLY option.

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

With theCONCURRENTLY option, PostgreSQL creates a temporary updated version of the materialized view, compares two versions, and performsINSERT andUPDATE only the differences.

PostgreSQL allows you to retrieve data from a materialized view while it is being updated. One requirement for usingCONCURRENTLY option is that the materialized view must have aUNIQUE index.

Notice thatCONCURRENTLY option is only available in PostgreSQL 9.4 or later.

Removing materialized views

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

DROP MATERIALIZED VIEW view_name;

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

PostgreSQL materialized views example

We’ll use the tables in thesample database for creating a materialized view.

First, create a materialized view namedrental_by_category using theCREATE MATERIALIZED VIEW statement:

CREATE MATERIALIZED VIEW rental_by_categoryAS SELECT c.nameAS category,    sum(p.amount)AS total_sales   FROM (((((payment p     JOIN rental rON ((p.rental_id= r.rental_id)))     JOIN inventory iON ((r.inventory_id= i.inventory_id)))     JOIN film fON ((i.film_id= f.film_id)))     JOIN film_category fcON ((f.film_id= fc.film_id)))     JOIN category cON ((fc.category_id= c.category_id)))  GROUP BY c.name  ORDER BY sum(p.amount)DESCWITH NO DATA;

Because of theWITH NO DATA option, you cannot query data from the view. If you attempt to do so, you’ll get the following error message:

SELECT* FROM rental_by_category;

Output:

[Err] ERROR: materialized view"rental_by_category" hasnot been populatedHINT:Use the REFRESH MATERIALIZED VIEW command.

PostgreSQL is helpful to give you a hint to ask for loading data into the view.

Second, load data into the materialized view using theREFRESH MATERIALIZED VIEW statement:

REFRESH MATERIALIZED VIEW rental_by_category;

Third, retrieve data from the materialized view:

SELECT * FROM rental_by_category;

Output:

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)

From now on, you can refresh the data in therental_by_category view using theREFRESH MATERIALIZED VIEW statement.

However, to refresh it withCONCURRENTLY option, you need to create aUNIQUE index for the view first.

CREATE UNIQUE INDEX rental_categoryON rental_by_category (category);

Let’s refresh data concurrently for therental_by_category view.

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;

Summary

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

Last updated onMarch 16, 2024

On this page

• Introduction to the PostgreSQL materialized views
• PostgreSQL materialized views example
• Summary