Materialized view vs regular view decision in PostgreSQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to use a materialized view instead of a regular view?

You have a large table with millions of rows that rarely changes. You want to speed up complex queries that aggregate data from this table. Which option best explains when to use a materialized view instead of a regular view?

A Use a materialized view when you want faster query results by storing the query output physically, but you must refresh it manually or on schedule.

B Use a materialized view when you want the query to always show the latest data without any delay.

C Use a materialized view when you want to avoid storing any data and always compute results on the fly.

D Use a materialized view only if the underlying tables are updated every second.

💡 Hint

Think about whether the data is stored or computed each time you query.

A materialized view stores the query result physically, which speeds up queries but requires manual or scheduled refresh to update data. A regular view computes data on demand and always shows the latest data but can be slower.

❓ query_result

intermediate

Output difference between materialized view and regular view

Given a table sales with columns id, amount, and sale_date, you create a regular view and a materialized view that calculate total sales per day. If a new sale is inserted after creating both views, what will be the output of querying each view?

PostgreSQL

CREATE VIEW daily_sales_view AS SELECT sale_date, SUM(amount) AS total FROM sales GROUP BY sale_date;

CREATE MATERIALIZED VIEW daily_sales_matview AS SELECT sale_date, SUM(amount) AS total FROM sales GROUP BY sale_date;

-- After inserting a new sale:
INSERT INTO sales VALUES (101, 500, '2024-06-15');

-- Query both views:
SELECT * FROM daily_sales_view WHERE sale_date = '2024-06-15';
SELECT * FROM daily_sales_matview WHERE sale_date = '2024-06-15';

A Neither view shows the new sale until the materialized view is refreshed.

B Both views show the new sale total immediately after insertion.

C The regular view shows the new sale total including the inserted row; the materialized view does not show the new sale until refreshed.

D The materialized view shows the new sale total immediately; the regular view does not.

💡 Hint

Remember how materialized views store data versus regular views.

Regular views compute results on demand, so they reflect the latest data immediately. Materialized views store results physically and require manual refresh to update.

❓ optimization

advanced

Optimizing query performance with materialized views

You have a complex query joining multiple large tables and aggregating data. The query runs slowly when executed frequently. Which approach will best improve performance using materialized views?

A Avoid using views and run the complex query directly every time.

B Create a materialized view with the complex query and schedule regular refreshes during low-traffic hours.

C Create a regular view with the complex query and index the underlying tables heavily.

D Create a materialized view but never refresh it after creation.

💡 Hint

Think about balancing query speed and data freshness.

Materialized views store query results physically, speeding up queries. Scheduling refreshes during low-traffic times keeps data reasonably fresh without slowing down users.

🔧 Debug

advanced

Why does querying a materialized view show outdated data?

You created a materialized view from a table and query it multiple times. You notice the data does not reflect recent changes in the base table. What is the most likely cause?

A The query on the materialized view is missing a WHERE clause.

B The base table is locked and cannot update the materialized view automatically.

C Materialized views always show outdated data by design.

D The materialized view has not been refreshed since the base table was updated.

💡 Hint

Consider how materialized views update their data.

Materialized views do not update automatically when base tables change. They require manual or scheduled refresh to show current data.

🧠 Conceptual

expert

Choosing between materialized view and regular view for real-time analytics

You are designing a dashboard that shows real-time analytics updated every second. The underlying data changes frequently. Which is the best choice and why?

A Use a regular view because it always shows the latest data without needing refresh.

B Use a materialized view because it stores data and speeds up queries even if data is slightly outdated.

C Use a materialized view and refresh it every second to keep data real-time.

D Use neither; export data to a flat file for faster access.

💡 Hint

Think about data freshness and update frequency.

Regular views compute data on demand, so they always reflect the latest changes, which is essential for real-time analytics. Materialized views require refresh and may lag behind.

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