Materialized views concept in PostgreSQL - Time & Space Complexity

Time Complexity: Materialized views concept

O(n)

Understanding Time Complexity

Materialized views store query results to speed up repeated access.

We want to know how the time to refresh a materialized view grows as data grows.

Scenario Under Consideration

Analyze the time complexity of refreshing a materialized view.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

REFRESH MATERIALIZED VIEW sales_summary;
    

This code creates a materialized view summarizing sales by product and refreshes it.

Identify Repeating Operations

Look for repeated work done during refresh.

• Primary operation: Scanning all rows in the sales table to aggregate quantities.
• How many times: Once per refresh, processing every sales record.

How Execution Grows With Input

The refresh reads all sales rows to compute sums.

Input Size (n)	Approx. Operations
10	About 10 row reads and sums
100	About 100 row reads and sums
1000	About 1000 row reads and sums

Pattern observation: Operations grow roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means refreshing takes time proportional to the number of rows in the base table.

Common Mistake

[X] Wrong: "Refreshing a materialized view is instant no matter the data size."

[OK] Correct: The refresh must read and process all underlying data, so it takes longer as data grows.

Interview Connect

Understanding how materialized views refresh helps you explain database performance in real projects.

Self-Check

"What if we added an index on product_id? How would that affect the refresh time complexity?"

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