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.