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

Concept Flow - REFRESH MATERIALIZED VIEW

Start REFRESH MATERIALIZED VIEW

↓

Lock materialized view

↓

Recompute data from underlying tables

↓

Replace old data with new data

↓

Unlock materialized view

↓

End

The REFRESH MATERIALIZED VIEW command locks the view, recomputes its data from base tables, replaces old data, then unlocks it.

Execution Sample

PostgreSQL

REFRESH MATERIALIZED VIEW sales_summary;

This command updates the 'sales_summary' materialized view with fresh data from its source tables.

Execution Table

Step	Action	Details	Result
1	Start REFRESH	Command issued for sales_summary	Begin refresh process
2	Lock view	Prevent reads/writes during refresh	sales_summary locked
3	Recompute data	Query underlying tables for fresh data	New data computed
4	Replace data	Old data replaced with new data	sales_summary updated
5	Unlock view	Allow access again	sales_summary unlocked
6	End	Refresh complete	Materialized view is current

💡 Process ends after unlocking the materialized view with updated data.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
sales_summary_data	old data	new data computed but not applied	new data applied	new data

Key Moments - 3 Insights

Why is the materialized view locked during refresh?

It is locked (see execution_table step 2) to prevent users from reading or writing inconsistent data while the refresh updates the view.

Does REFRESH MATERIALIZED VIEW change the structure of the view?

No, it only updates the data inside the view (see execution_table steps 3 and 4), not the view's columns or definition.

What happens if the underlying tables change after refresh starts?

Changes after refresh starts are not included until the next refresh, because data is recomputed at step 3 and replaced at step 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the materialized view unlocked?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Check the 'Action' column for unlocking in execution_table step 5.

According to variable_tracker, what is the state of sales_summary_data after step 3?

A Old data

B New data computed but not applied

C New data applied

D Data is empty

💡 Hint

Look at the 'After Step 3' column for sales_summary_data in variable_tracker.

If the refresh process is interrupted before step 4, what data will the materialized view hold?

A Old data

B No data

C New data

D Partial new data

💡 Hint

Refer to execution_table steps 3 and 4 and variable_tracker to understand when data is replaced.

Concept Snapshot

REFRESH MATERIALIZED VIEW view_name;
- Locks the view to prevent access
- Recomputes data from base tables
- Replaces old data with fresh data
- Unlocks the view after update
Use to keep materialized views current with source data.

Full Transcript

The REFRESH MATERIALIZED VIEW command in PostgreSQL updates a materialized view by locking it, recomputing its data from the underlying tables, replacing the old data with the new, and then unlocking it. This ensures users see consistent, up-to-date data. The process starts by locking the view to prevent access, then runs the query to get fresh data, replaces the stored data, and finally unlocks the view so it can be accessed again. The structure of the view does not change during refresh, only the data inside. If the refresh is interrupted before data replacement, the view keeps its old data. This command is useful to keep summary or aggregated data current without recalculating on every query.