Materialized views concept in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Refreshing a Materialized View

Consider a materialized view mv_sales_summary created from a sales table. After new sales data is inserted, what will be the output of the following query immediately after running REFRESH MATERIALIZED VIEW mv_sales_summary;?

SELECT COUNT(*) FROM mv_sales_summary;

PostgreSQL

CREATE MATERIALIZED VIEW mv_sales_summary AS SELECT product_id, SUM(quantity) AS total_quantity FROM sales GROUP BY product_id;
-- Assume 100 rows in sales before insert
INSERT INTO sales (product_id, sale_date, quantity) VALUES (101, '2024-06-01', 5);
REFRESH MATERIALIZED VIEW mv_sales_summary;
SELECT COUNT(*) FROM mv_sales_summary;

A The query causes an error because REFRESH MATERIALIZED VIEW is not allowed

B The count remains the same as before the insert because the view is not refreshed

C The query returns zero rows because materialized views do not store data

D The count reflects the updated number of distinct products including the new sale

💡 Hint

Think about what REFRESH MATERIALIZED VIEW does to the stored data.

Refreshing a materialized view updates its stored data to reflect the current state of the underlying tables. After inserting new sales data and refreshing, the count will include the new data.

🧠 Conceptual

intermediate

Understanding Materialized View Storage

Which statement best describes how a materialized view stores data compared to a regular view?

A A regular view stores data physically, but a materialized view only stores the query definition

B A materialized view stores the query result physically on disk, while a regular view does not store data but runs the query on demand

C Neither materialized nor regular views store any data

D Both materialized and regular views store data physically on disk

💡 Hint

Think about when the data is saved and when the query runs.

Materialized views save the query results physically, so they can be queried quickly without rerunning the original query. Regular views just store the query and run it each time you select from them.

📝 Syntax

advanced

Correct Syntax to Create a Materialized View

Which of the following SQL statements correctly creates a materialized view named mv_customer_orders that summarizes total orders per customer?

A CREATE MATERIALIZED VIEW mv_customer_orders AS SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;

B CREATE VIEW MATERIALIZED mv_customer_orders AS SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;

C CREATE MATERIALIZED VIEW mv_customer_orders SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id;

D CREATE MATERIALIZED VIEW mv_customer_orders AS SELECT customer_id, COUNT(*) total_orders FROM orders;

💡 Hint

Remember the correct order of keywords and the need for AS.

The correct syntax requires CREATE MATERIALIZED VIEW view_name AS SELECT .... Option A follows this exactly.

❓ optimization

advanced

Performance Benefit of Materialized Views

Why might using a materialized view improve query performance compared to querying the base tables directly?

A Because materialized views use less disk space than base tables

B Because materialized views automatically update in real-time with every base table change

C Because the materialized view stores precomputed results, queries run faster without recalculating data

D Because querying materialized views bypasses the database optimizer

💡 Hint

Think about what happens when you query a materialized view versus the original tables.

Materialized views store the results of expensive queries, so when you query them, the database does not need to recompute the data, improving performance.

🔧 Debug

expert

Error When Refreshing a Materialized View Concurrently

Given the command REFRESH MATERIALIZED VIEW CONCURRENTLY mv_report;, which error is most likely if the materialized view does not have a unique index?

A ERROR: materialized view "mv_report" must have a unique index to use CONCURRENTLY

B ERROR: syntax error near CONCURRENTLY

C ERROR: cannot refresh materialized view because it is locked

D No error; the refresh runs successfully

💡 Hint

Check the requirements for using CONCURRENTLY with materialized views.

PostgreSQL requires a unique index on the materialized view to allow concurrent refreshes. Without it, the command fails with an error.

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