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?"