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.

Practice

1. What is the main purpose of a CREATE MATERIALIZED VIEW in PostgreSQL?

easy

A. To create a temporary table that disappears after the session

B. To create an index on a table column

C. To define a virtual table that always runs the query on access

D. To store the result of a query physically for faster access

Solution

1. Step 1: Understand what a materialized view does

   A materialized view stores the result of a query physically, unlike a normal view which runs the query every time.

2. Step 2: Compare options with this definition

   To store the result of a query physically for faster access matches this behavior exactly. Options A and C describe temporary tables and normal views, not materialized views. To create an index on a table column is unrelated.

3. Final Answer:

   To store the result of a query physically for faster access -> Option D

4. Quick Check:

   Materialized view = stored query result [OK]

Hint: Materialized views save query results physically for speed [OK]

Common Mistakes:

• Confusing materialized views with normal views
• Thinking materialized views are temporary tables
• Assuming materialized views auto-update without refresh

2. Which of the following is the correct syntax to create a materialized view named sales_summary from a table sales?

easy

A. CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

B. CREATE VIEW MATERIALIZED sales_summary AS SELECT * FROM sales;

C. CREATE MATERIALIZED VIEW sales_summary SELECT * FROM sales;

D. CREATE MATERIALIZED VIEW sales_summary FROM sales;

Solution

1. Step 1: Recall the correct syntax for creating materialized views

   The correct syntax is: CREATE MATERIALIZED VIEW view_name AS SELECT ...

2. Step 2: Check each option against this syntax

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; matches exactly. CREATE VIEW MATERIALIZED sales_summary AS SELECT * FROM sales; swaps keywords incorrectly. CREATE MATERIALIZED VIEW sales_summary SELECT * FROM sales; misses the AS keyword. CREATE MATERIALIZED VIEW sales_summary FROM sales; uses FROM instead of AS and SELECT.

3. Final Answer:

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; -> Option A

4. Quick Check:

   Syntax = CREATE MATERIALIZED VIEW ... AS SELECT ... [OK]

Hint: Use 'AS SELECT' after materialized view name [OK]

Common Mistakes:

• Omitting the AS keyword
• Swapping VIEW and MATERIALIZED keywords
• Using FROM instead of AS SELECT

3. Given the following commands:

CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 100), (2, 200);
CREATE MATERIALIZED VIEW cheap_products AS SELECT * FROM products WHERE price < 150;
SELECT * FROM cheap_products;

What will be the output of the SELECT query?

medium

A. Rows with id=1 and price=100

B. Rows with id=2 and price=200

C. No rows returned

D. Syntax error

Solution

1. Step 1: Understand the data and materialized view filter

   The table has two rows: (1,100) and (2,200). The materialized view selects rows where price < 150, so only (1,100) qualifies.

2. Step 2: Query the materialized view

   Since the materialized view stores the filtered rows, selecting from it returns the row with id=1 and price=100.

3. Final Answer:

   Rows with id=1 and price=100 -> Option A

4. Quick Check:

   Filter price < 150 = row with id=1 [OK]

Hint: Materialized view stores filtered rows, returns matching data [OK]

Common Mistakes:

• Expecting all rows instead of filtered ones
• Confusing materialized view with normal view behavior
• Assuming syntax error due to multiple commands

4. Consider this SQL:

CREATE MATERIALIZED VIEW recent_orders AS SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days';
REFRESH MATERIALIZED VIEW recent_orders;

Which of the following is a common mistake that would cause an error or unexpected behavior?

medium

A. Using REFRESH MATERIALIZED VIEW without CONCURRENTLY

B. Creating the materialized view without a SELECT statement

C. Not refreshing the materialized view after data changes

D. Using CURRENT_DATE in the WHERE clause

Solution

1. Step 1: Analyze each option for errors

   Not refreshing the materialized view after data changes causes stale data but no error. Using REFRESH MATERIALIZED VIEW without CONCURRENTLY is allowed but locks the view during refresh. Creating the materialized view without a SELECT statement is invalid syntax because materialized views require a SELECT query. Using CURRENT_DATE in the WHERE clause is valid usage.

2. Step 2: Identify the syntax error

   Creating a materialized view without a SELECT statement causes a syntax error, so Creating the materialized view without a SELECT statement is the mistake causing error.

3. Final Answer:

   Creating the materialized view without a SELECT statement -> Option B

4. Quick Check:

   Materialized view requires SELECT query [OK]

Hint: Materialized views must have a SELECT query [OK]

Common Mistakes:

• Skipping the SELECT query in CREATE MATERIALIZED VIEW
• Ignoring need to refresh after data changes
• Assuming REFRESH must use CONCURRENTLY always

5. You have a large table sales_data that updates daily. You want to create a materialized view monthly_summary that shows total sales per month. Which approach correctly creates and maintains this materialized view efficiently?

hard

A. CREATE MATERIALIZED VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1; REFRESH MATERIALIZED VIEW monthly_summary;

B. CREATE VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1;

C. CREATE MATERIALIZED VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1; REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_summary;

D. CREATE MATERIALIZED VIEW monthly_summary AS SELECT * FROM sales_data; REFRESH MATERIALIZED VIEW monthly_summary;

Solution

1. Step 1: Understand the goal and data size

   We want a summary per month from a large table that updates daily. Using a materialized view with aggregation is good for performance.

2. Step 2: Choose the best refresh method

   CREATE MATERIALIZED VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1; REFRESH MATERIALIZED VIEW monthly_summary; refreshes but locks the view, blocking reads. CREATE MATERIALIZED VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1; REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_summary; uses REFRESH MATERIALIZED VIEW CONCURRENTLY, allowing reads during refresh, which is efficient for large data. CREATE VIEW monthly_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) FROM sales_data GROUP BY 1; is a normal view, recalculating every time, slow for large data. CREATE MATERIALIZED VIEW monthly_summary AS SELECT * FROM sales_data; REFRESH MATERIALIZED VIEW monthly_summary; stores all raw data, not aggregated, so not efficient.

3. Final Answer:

   CREATE MATERIALIZED VIEW with aggregation and REFRESH MATERIALIZED VIEW CONCURRENTLY -> Option C

4. Quick Check:

   Use CONCURRENTLY to refresh without blocking [OK]

Hint: Use CONCURRENTLY to refresh materialized views without locking [OK]

Common Mistakes:

• Using normal views for large aggregated data
• Refreshing materialized views without CONCURRENTLY causing locks
• Creating materialized views without aggregation for summaries