Materialized view vs regular view decision in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - Materialized view vs regular view decision

Start: Need to display data

↓

Is data static or changes rarely?

No→Use Regular View

|Yes↓

Is query expensive to run repeatedly?

No→Use Regular View

|Yes↓

Use Materialized View

↓

Materialized View stores data physically

↓

Refresh materialized view when needed

↓

End

Decide between regular and materialized views by checking data change frequency and query cost. Materialized views store data physically and need refresh; regular views run query live.

Execution Sample

PostgreSQL

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

CREATE MATERIALIZED VIEW summary_sales AS
SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;

Creates a regular view for recent orders and a materialized view for sales summary.

Execution Table

Step	Decision Point	Condition	Action Taken	Result
1	Check data change frequency	Data changes frequently	Use Regular View	View runs query live each time
2	Check query cost	Query is simple	Use Regular View	No stored data, always fresh
3	Check data change frequency	Data changes rarely	Evaluate query cost	Next step decision
4	Check query cost	Query is expensive	Use Materialized View	Data stored physically
5	Materialized view usage	Data updated	Refresh materialized view manually	Data stays current
6	Materialized view usage	No refresh	Data may be stale	Faster query response
7	End	Decision complete	Appropriate view chosen	Optimized performance and freshness

💡 Decision ends when appropriate view type is chosen based on data change and query cost

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 4	Final
Data Change Frequency	Unknown	Frequently changes	Rarely changes	Rarely changes	Rarely changes
Query Cost	Unknown	Unknown	Unknown	Expensive	Expensive
View Type	None	Regular View	Regular View	Materialized View	Materialized View
Data Storage	None	None	None	Stored physically	Stored physically
Data Freshness	Unknown	Always fresh	Always fresh	Depends on refresh	Depends on refresh

Key Moments - 3 Insights

Why choose a regular view if the data changes frequently?

Because regular views run the query live each time, they always show the latest data, as seen in execution_table row 1.

What happens if a materialized view is not refreshed?

The data in the materialized view becomes stale and does not reflect recent changes, shown in execution_table row 6.

Why use a materialized view for expensive queries on rarely changing data?

Because it stores query results physically, speeding up access without rerunning the expensive query every time, as in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the decision made to use a materialized view?

A Step 2

B Step 1

C Step 4

D Step 6

💡 Hint

Check the 'Action Taken' column for when 'Use Materialized View' is chosen.

According to variable_tracker, what is the 'View Type' after Step 3?

A None

B Regular View

C Materialized View

D Both

💡 Hint

Look at the 'View Type' row under 'After Step 3' column.

If the data changes frequently but the query is expensive, what does the flow suggest?

A Use Regular View

B Use Materialized View

C Use neither

D Use both

💡 Hint

Refer to concept_flow where frequent data changes lead to regular view choice despite query cost.

Concept Snapshot

Materialized View vs Regular View Decision:
- Regular views run query live, always fresh.
- Materialized views store data physically, need manual refresh.
- Use regular view if data changes often or query is simple.
- Use materialized view if data changes rarely and query is expensive.
- Refresh materialized views to update stored data.

Full Transcript

This visual execution shows how to decide between a materialized view and a regular view in PostgreSQL. First, check if the data changes frequently. If yes, use a regular view because it always shows fresh data by running the query live. If data changes rarely, check if the query is expensive. If the query is simple, use a regular view. If expensive, use a materialized view which stores data physically and speeds up access. Remember, materialized views need manual refresh to keep data current. The execution table and variable tracker illustrate these decisions step-by-step, helping beginners understand when and why to choose each view type.

Practice

1. Which statement best describes a regular view in PostgreSQL?

easy

A. It stores data physically and requires manual refresh.

B. It cannot be used in SELECT statements.

C. It runs the underlying query every time it is accessed.

D. It automatically updates data without running queries.

Solution

1. Step 1: Understand what a regular view does

   A regular view is a saved SQL query that runs fresh every time you use it.

2. Step 2: Compare with other options

   Storing data physically and requiring manual refresh describes a materialized view; saying it cannot be used in SELECT statements is false because views can be queried directly; automatically updating data without running queries is incorrect for regular views.

3. Final Answer:

   It runs the underlying query every time it is accessed. -> Option C

4. Quick Check:

   Regular view runs query each time [OK]

Hint: Regular views always run queries fresh on access [OK]

Common Mistakes:

• Confusing materialized views with regular views
• Thinking views store data physically
• Believing views auto-refresh data

2. Which of the following is the correct syntax to create a materialized view in PostgreSQL?

easy

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

B. CREATE VIEW my_view AS SELECT * FROM sales;

C. CREATE TABLE my_mat_view AS SELECT * FROM sales;

D. CREATE TEMP VIEW my_mat_view AS SELECT * FROM sales;

Solution

1. Step 1: Identify syntax for materialized view

   The correct syntax uses CREATE MATERIALIZED VIEW followed by the view name and query.

2. Step 2: Eliminate incorrect options

   CREATE VIEW my_view AS SELECT * FROM sales; creates a regular view, CREATE TABLE my_mat_view AS SELECT * FROM sales; creates a table, CREATE TEMP VIEW my_mat_view AS SELECT * FROM sales; creates a temporary view, not materialized.

3. Final Answer:

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

4. Quick Check:

   Materialized view syntax includes 'MATERIALIZED' keyword [OK]

Hint: Materialized views use 'CREATE MATERIALIZED VIEW' syntax [OK]

Common Mistakes:

• Using CREATE VIEW instead of CREATE MATERIALIZED VIEW
• Confusing tables with materialized views
• Using TEMP keyword incorrectly

3. Given a materialized view created as:
CREATE MATERIALIZED VIEW sales_summary AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
What will be the result of:
SELECT * FROM sales_summary;
immediately after creation?

medium

A. The aggregated total sales per region based on current data.

B. An error because materialized views need manual refresh first.

C. Empty result set because no data is stored yet.

D. The detailed sales rows without aggregation.

Solution

1. Step 1: Understand materialized view creation behavior

   When created, a materialized view stores the query result immediately.

2. Step 2: Determine output of SELECT from materialized view

   It returns the aggregated totals per region as per the query at creation time.

3. Final Answer:

   The aggregated total sales per region based on current data. -> Option A

4. Quick Check:

   Materialized view stores query result on creation [OK]

Hint: Materialized views store data on creation, so SELECT returns stored results [OK]

Common Mistakes:

• Thinking materialized views are empty until refreshed
• Expecting detailed rows instead of aggregated results
• Assuming errors occur without manual refresh

4. You created a materialized view but notice it shows outdated data after the underlying table changes. What is the best way to fix this?

medium

A. Use SELECT * FROM materialized_view to refresh automatically.

B. Drop and recreate the materialized view every time.

C. Restart the PostgreSQL server to refresh views.

D. Run REFRESH MATERIALIZED VIEW to update the data.

Solution

1. Step 1: Identify how to update materialized view data

   Materialized views do not auto-update; REFRESH MATERIALIZED VIEW command updates stored data.

2. Step 2: Evaluate other options

   Dropping and recreating the materialized view every time is inefficient, using SELECT * FROM materialized_view does not refresh it, restarting the PostgreSQL server is unrelated.

3. Final Answer:

   Run REFRESH MATERIALIZED VIEW to update the data. -> Option D

4. Quick Check:

   Use REFRESH MATERIALIZED VIEW to update data [OK]

Hint: Use REFRESH MATERIALIZED VIEW to update stored data [OK]

Common Mistakes:

• Expecting SELECT to refresh data automatically
• Dropping and recreating instead of refreshing
• Restarting server to fix view data

5. You have a large sales table updated hourly. You want fast reports that summarize sales by region but can tolerate data up to 1 hour old. Which is the best choice?

hard

A. Use a regular view to always get fresh data.

B. Use a materialized view refreshed every hour for faster queries.

C. Create a table with triggers to update summaries instantly.

D. Use a temporary view refreshed on each query.

Solution

1. Step 1: Analyze requirements for speed and data freshness

   Reports need fast access and can accept data up to 1 hour old.

2. Step 2: Match options to requirements

   Materialized views store results and can be refreshed hourly, balancing speed and freshness. Regular views run slow on large data. Triggers add complexity. Temporary views do not persist.

3. Final Answer:

   Use a materialized view refreshed every hour for faster queries. -> Option B

4. Quick Check:

   Materialized view + hourly refresh = best for speed and acceptable freshness [OK]

Hint: Materialized views with scheduled refresh balance speed and freshness [OK]

Common Mistakes:

• Choosing regular views despite slow queries
• Using triggers unnecessarily complex
• Confusing temporary views with materialized views