Materialized views

Database Design

Lis Sulmont

Curriculum Manager

Two types of views

Views

• Also known as non-materialized views
• How we've defined views so far

Two types of views

Views

• Also known as non-materialized views
• How we've defined views so far

Materialized views

• Physically materialized

Materialized views

• Stores the query results, not the query
• Querying a materialized view means accessing the stored query results
  • Not running the query like a non-materialized view
• Refreshed or rematerialized when prompted or scheduled

When to use materialized views

• Long running queries
• Underlying query results don't change often
• Data warehouses because OLAP is not write-intensive
  • Save on computational cost of frequent queries

Implementing materialized views

(in PostgreSQL)

$$

CREATE MATERIALIZED VIEW my_mv AS SELECT * FROM existing_table;

$$

REFRESH MATERIALIZED VIEW my_mv;

Managing dependencies

• Materialized views often depend on other materialized views

Dependency example

$$

Managing dependencies

• Materialized views often depend on other materialized views
• Creates a dependency chain when refreshing views
• Not the most efficient to refresh all views at the same time

Tools for managing dependencies

• Use Directed Acyclic Graphs (DAGs) to keep track of views $$ $$
• Pipeline scheduler tools

Let's practice!

Database Design