Materialized views concept in PostgreSQL - Step-by-Step Execution

Concept Flow - Materialized views concept

Create Materialized View

↓

Store Query Result

↓

Query Materialized View

↓

Use Stored Data

↓

Refresh Materialized View?

Yes→Recompute and Store

No↓

End

Materialized views store query results physically. You query the stored data until you refresh it to update.

Execution Sample

PostgreSQL

CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

SELECT * FROM sales_summary;

Creates a materialized view summarizing sales by region, then queries the stored summary.

Execution Table

Step	Action	Query/Command	Result/State
1	Create materialized view	CREATE MATERIALIZED VIEW sales_summary AS SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region;	Materialized view 'sales_summary' created and data stored
2	Query materialized view	SELECT * FROM sales_summary;	Returns stored summary data quickly without re-running original query
3	Insert new sales data	INSERT INTO sales VALUES ('East', 100);	New data added to 'sales' table, materialized view not updated
4	Query materialized view again	SELECT * FROM sales_summary;	Returns old summary data, does not include new insert
5	Refresh materialized view	REFRESH MATERIALIZED VIEW sales_summary;	Materialized view data updated to include new sales
6	Query refreshed materialized view	SELECT * FROM sales_summary;	Returns updated summary including new sales data

💡 Execution stops after querying refreshed materialized view with up-to-date data

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 5	Final
sales table	[empty or initial data]	Initial data present	New row ('East', 100) added	No change	No change
sales_summary view data	None	Stored summary of initial data	No change (stale)	Updated summary including new row	Updated summary

Key Moments - 2 Insights

Why does the materialized view not show new data after inserting into the base table?

Because materialized views store a snapshot of data at creation or last refresh. The insert changes the base table but the view stays stale until refreshed (see execution_table rows 3 and 4).

What happens when you refresh a materialized view?

Refreshing recomputes the query and updates the stored data, so the view reflects the latest base table data (see execution_table row 5).

Visual Quiz - 3 Questions

Look at the execution table, what does the query in step 4 return?

A The updated summary including new sales data

B The old summary data without new sales data

C An error because data changed

D Empty result set

💡 Hint

Check execution_table row 4 where the materialized view is queried before refresh

At which step is the materialized view data updated to include new sales?

A Step 5

B Step 2

C Step 3

D Step 6

💡 Hint

Look at execution_table row 5 where REFRESH MATERIALIZED VIEW is executed

If you never refresh the materialized view, what happens when you query it after new data inserts?

A It shows the new data automatically

B It shows an error

C It shows the old data without new inserts

D It deletes the new data

💡 Hint

Refer to variable_tracker showing 'sales_summary view data' after step 3 and 4

Concept Snapshot

Materialized views store query results physically.
Querying them is fast but shows stored data only.
Base tables can change without updating the view.
Use REFRESH MATERIALIZED VIEW to update stored data.
Useful for expensive queries needing fast repeated access.

Full Transcript

Materialized views in PostgreSQL store the results of a query physically. When you create a materialized view, the query runs once and the results are saved. Later, when you query the materialized view, it returns the stored data quickly without running the original query again. If the base tables change, the materialized view does not update automatically. You must run REFRESH MATERIALIZED VIEW to update it with the latest data. This makes materialized views useful for speeding up expensive queries that do not need real-time data. The execution steps show creating the view, querying it, inserting new data into the base table, querying the stale view, refreshing it, and querying the updated view.

Practice

1. What is the main purpose of a materialized view in PostgreSQL?

easy

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

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

C. To automatically update data in real-time without manual refresh

D. To define a view that does not store any data

Solution

1. Step 1: Understand what a materialized view does

   A materialized view stores the result of a query physically on disk, unlike a normal view which only stores the query definition.

2. Step 2: Compare options with this definition

   To store the result of a query physically for faster access matches this exactly. Options A, B, and C describe other concepts like temporary tables or normal views.

3. Final Answer:

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

4. Quick Check:

   Materialized view = stored query result [OK]

Hint: Materialized views store data physically, unlike normal views [OK]

Common Mistakes:

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

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 TABLE sales_summary AS SELECT * FROM sales;

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

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

D. CREATE TABLE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

Solution

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

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

2. Step 2: Match options with correct syntax

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; matches exactly. Options A, B, and D mix keywords incorrectly or use wrong object types.

3. Final Answer:

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

4. Quick Check:

   CREATE MATERIALIZED VIEW = correct syntax [OK]

Hint: Remember: CREATE MATERIALIZED VIEW, not CREATE VIEW MATERIALIZED [OK]

Common Mistakes:

• Swapping keywords 'VIEW' and 'MATERIALIZED'
• Using CREATE MATERIALIZED TABLE instead of VIEW
• Adding TABLE keyword incorrectly

3. Given the materialized view mv_products created as CREATE MATERIALIZED VIEW mv_products AS SELECT id, price FROM products;, what will be the result of this query immediately after inserting a new product into products but before refreshing mv_products?

SELECT * FROM mv_products WHERE id = 101;

medium

A. The new product with id 101 will appear in the result

B. The materialized view will automatically refresh and show the new product

C. Query will cause a syntax error

D. No rows will be returned for id 101

Solution

1. Step 1: Understand materialized view data update behavior

   Materialized views do not update automatically when base tables change. They require manual refresh.

2. Step 2: Analyze query result after insert but before refresh

   The new product with id 101 is not yet in the materialized view, so the query returns no rows for id 101.

3. Final Answer:

   No rows will be returned for id 101 -> Option D

4. Quick Check:

   Materialized view data = stale until refresh [OK]

Hint: Materialized views need manual refresh to show new data [OK]

Common Mistakes:

• Assuming materialized views auto-update on insert
• Expecting syntax error from a valid SELECT
• Confusing materialized views with normal views

4. You have a materialized view mv_orders created as CREATE MATERIALIZED VIEW mv_orders AS SELECT * FROM orders;. You try to refresh it using REFRESH MATERIALIZED VIEW mv_orders; but get an error: ERROR: permission denied for relation orders. What is the most likely cause?

medium

A. The user running the refresh lacks SELECT permission on the base table orders

B. The materialized view name is misspelled

C. REFRESH MATERIALIZED VIEW syntax is incorrect

D. Materialized views cannot be refreshed after creation

Solution

1. Step 1: Understand permission requirements for refreshing

   Refreshing a materialized view requires SELECT permission on the underlying base tables.

2. Step 2: Analyze the error message

   The error says permission denied on relation orders, indicating the user lacks SELECT rights on that table.

3. Final Answer:

   The user running the refresh lacks SELECT permission on the base table orders -> Option A

4. Quick Check:

   Permission denied = missing SELECT rights [OK]

Hint: Check base table permissions if refresh gives permission denied [OK]

Common Mistakes:

• Assuming syntax error instead of permission issue
• Ignoring user privileges on base tables
• Thinking materialized views cannot be refreshed

5. You want to speed up a complex report query that aggregates sales data by month. You create a materialized view monthly_sales with the aggregation. However, the sales data updates daily. Which approach best keeps monthly_sales up to date with minimal performance impact?

hard

A. Manually drop and recreate the materialized view after each data change

B. Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day

C. Use a normal view instead of a materialized view for real-time data

D. Set the materialized view to auto-refresh on every query

Solution

1. Step 1: Consider update frequency and performance

   Since sales data updates daily, refreshing the materialized view nightly balances freshness and performance.

2. Step 2: Evaluate other options

   Dropping and recreating is costly and manual. Normal views are slow for complex queries. Auto-refresh on every query is not supported and would hurt performance.

3. Final Answer:

   Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day -> Option B

4. Quick Check:

   Nightly refresh = best balance for daily updates [OK]

Hint: Use scheduled refresh for daily updated materialized views [OK]

Common Mistakes:

• Trying to auto-refresh materialized views on every query
• Dropping and recreating instead of refreshing
• Using normal views for heavy aggregation queries