Updatable views in PostgreSQL - Time & Space Complexity

Time Complexity: Updatable views

O(n)

Understanding Time Complexity

When working with updatable views in PostgreSQL, it's important to understand how the time to update data changes as the size of the underlying tables grows.

We want to know how the cost of updating through a view scales with more data.

Scenario Under Consideration

Analyze the time complexity of updating data through a simple updatable view.

CREATE VIEW employee_view AS
  SELECT id, name, salary FROM employees;

UPDATE employee_view
SET salary = salary * 1.1
WHERE id = 123;
    

This code updates the salary of one employee through a view that directly reflects the employees table.

Identify Repeating Operations

Look for repeated actions that affect performance.

• Primary operation: Searching for the row with the matching id in the employees table.
• How many times: Once per update, but the search may scan multiple rows depending on indexing.

How Execution Grows With Input

As the employees table grows, finding the row to update can take longer if there is no index.

Input Size (n)	Approx. Operations
10	10 (search through 10 rows)
100	100 (search through 100 rows)
1000	1000 (search through 1000 rows)

Pattern observation: Without an index, the search grows linearly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to update grows in direct proportion to the number of rows in the table when no index is used.

Common Mistake

[X] Wrong: "Updating through a view always takes constant time regardless of table size."

[OK] Correct: The update must find the correct row in the underlying table, which can take longer as the table grows if no index helps.

Interview Connect

Understanding how updates through views scale helps you explain database behavior clearly and shows you grasp practical performance considerations.

Self-Check

What if we added an index on the id column? How would the time complexity change?