PostgreSQL Materialized Views

Summary: in this tutorial, you’ll learn how to use PostgreSQL materialized views to physically store the result of a query.

Getting Started with PostgreSQL Materialized Views #

Regular views do not store data. When you query data from them, PostgreSQL retrieves data from the underlying tables.

When views have complex defining queries, you’ll encounter performance if you query data from them very frequently.

To address this issue, PostgreSQL introduces materialized views that physically store the result of queries. These materialized views can significantly improve performance for views with complex queries.

Creating PostgreSQL Materialized Views #

In PostgreSQL, you use the CREATE MATERIALIZED VIEW statement to create a materialized view:

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
AS
query
WITH DATA;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, define the view name in the CREATE MATERIALIZED VIEW clause. The IF NOT EXISTS option conditionally creates the view if it does not exist. It prevents an error from creating a view that already exists.
• Second, provide a query that defines the view after the AS keyword.
• Third, use the WITH DATA to load data from underlying tables into the view when you issue the CREATE MATERIALIZED VIEW.

The following example uses the CREATE MATERIALIZED VIEW to create a materialized view that summarizes data from the products and inventories tables:

CREATE MATERIALIZED VIEW inventory_view 
AS
SELECT
  p.product_name,
  SUM(i.quantity) AS quantity
FROM
  inventories i
  JOIN products p ON i.product_id = p.product_id
GROUP BY
  p.product_name
WITH DATA;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Querying Data from Materialized Views #

Since you use the WITH DATA in the CREATE MATERIALIZED VIEW statement, PostgreSQL loaded the data into the view when we executed the statement.

The following example uses a SELECT statement to retrieve data from the inventory_view:

SELECT * FROM inventory_view
ORDER BY quantity DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | quantity
----------------------------+----------
 Dell Inspiron 27           |      330
 Apple iMac 24"             |      320
 Lenovo ThinkPad X1 Carbon  |      310
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Refreshing Data #

Materialized views will not automatically update data from the underlying tables.

To replace the data of a materialized view with the new one from a table, you use the REFRESH MATERIALIZED VIEW statement:

REFRESH MATERIALIZED VIEW view_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example:

First, get the total quantity from the inventory_view:

SELECT
  SUM(quantity)
FROM
  inventory_view;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 sum
------
 5360

Second, increase the quantity of the product id 1 to 200:

UPDATE inventories
SET
  quantity = 200
WHERE
  product_id = 1 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 inventory_id | product_id | warehouse_id | quantity
--------------+------------+--------------+----------
            1 |          1 |            1 |      200

Third, refresh data to the materialized view inventory_view:

REFRESH MATERIALIZED VIEW inventory_view;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, verify the update by querying data from inventory_view:

SELECT SUM(quantity)
FROM inventory_view;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 sum
------
 5460Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Removing Materialized Views #

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

DROP MATERIALIZED VIEW [ IF EXISTS ] view_name
CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify one or more materialized view names in the DROP MATERIALIZED VIEW clause. The IF EXISTS prevents an error from removing an non-existent materialized view.
• Second, use the CASCADE option to drop a materialized view with dependent objects. The CASCADE option also removes the view as well as the dependent objects.

For example, the following uses the DROP MATERIALIZED VIEW statement to drop the inventory_view:

DROP MATERIALIZED VIEW inventory_view CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Why use materialized views #

Materialized views can improve query speed by storing precalculated result sets of complex queries.

In practice, the materialized views help aggregate data from multiple tables for reporting purposes.

Summary #

• A materialized view stores data physically.
• Use the CREATE MATERIALIZED VIEW statement to create a materialized view.
• Use the REFRESH MATERIALIZED VIEW statement to replace data in the materialized view with a new one.
• Use the DROP MATERIALIZED VIEW statement to drop a materialized view.

Quiz #