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.