CREATE MATERIALIZED VIEW in PostgreSQL - Step-by-Step Execution

Concept Flow - CREATE MATERIALIZED VIEW

Write SELECT query

↓

CREATE MATERIALIZED VIEW with query

↓

Database runs query once

↓

Store results physically

↓

Use materialized view like a table

↓

Refresh manually to update data

You write a SELECT query and create a materialized view that stores the query results physically. You can query this stored data quickly and refresh it when needed.

Execution Sample

PostgreSQL

CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM sales
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

This creates a materialized view named top_customers that stores the top 5 customers by total spending.

Execution Table

Step	Action	Query Executed	Result Stored	Notes
1	Parse CREATE MATERIALIZED VIEW statement	CREATE MATERIALIZED VIEW top_customers AS SELECT ...	No data yet	Syntax checked
2	Execute SELECT query	SELECT customer_id, SUM(amount) AS total_spent FROM sales GROUP BY customer_id ORDER BY total_spent DESC LIMIT 5	Top 5 customers with total_spent	Query runs once
3	Store query result physically	N/A	Materialized view 'top_customers' contains 5 rows	Data saved on disk
4	Query materialized view	SELECT * FROM top_customers	Returns stored 5 rows quickly	No re-execution of original query
5	Refresh materialized view manually	REFRESH MATERIALIZED VIEW top_customers	Data updated with latest sales	Re-runs SELECT and replaces stored data
6	Stop	N/A	N/A	Process complete

💡 Process stops after materialized view is created and data stored; refresh updates data later.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 5
Materialized View Data	Empty	Query result (top 5 customers)	Stored physically	Updated with refreshed query result

Key Moments - 2 Insights

Why does the materialized view not update automatically when the underlying table changes?

Because the materialized view stores a snapshot of the data at creation or last refresh (see execution_table step 3 and 5). It must be refreshed manually to update.

What happens when you query the materialized view?

The database returns the stored data quickly without running the original SELECT query again (see execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution table, at which step is the SELECT query executed to get data for the materialized view?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Query Executed' column in the execution_table rows.

According to the variable tracker, what is the state of the materialized view data after step 3?

A Data refreshed

B Empty

C Query result stored physically

D Data deleted

💡 Hint

Look at the 'After Step 3' column for 'Materialized View Data' in variable_tracker.

If you do not run REFRESH MATERIALIZED VIEW, what will happen when the underlying sales table changes?

A Materialized view updates automatically

B Materialized view data stays the same

C Materialized view deletes data

D Materialized view throws an error

💡 Hint

Refer to key_moments about when materialized view updates.

Concept Snapshot

CREATE MATERIALIZED VIEW name AS SELECT ...;
- Runs SELECT once and stores results physically.
- Querying returns stored data quickly.
- Data does NOT update automatically.
- Use REFRESH MATERIALIZED VIEW to update data.
- Useful for expensive queries needing fast reads.

Full Transcript

Creating a materialized view in PostgreSQL involves writing a SELECT query and using CREATE MATERIALIZED VIEW to store its results physically. The database runs the query once and saves the output. When you query the materialized view, it returns the stored data quickly without rerunning the original query. However, if the underlying tables change, the materialized view does not update automatically. You must run REFRESH MATERIALIZED VIEW to update its data. This process helps improve performance for complex queries by avoiding repeated execution.