UPDATE with RETURNING clause in PostgreSQL - Time & Space Complexity

Time Complexity: UPDATE with RETURNING clause

O(n)

Understanding Time Complexity

When we update rows in a database, it takes time depending on how many rows we change.

We want to know how the time grows when we update more rows and use the RETURNING clause to get results back.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales'
RETURNING id, salary;
    

This code updates salaries for all employees in the Sales department and returns their id and new salary.

Identify Repeating Operations

• Primary operation: Scanning and updating each matching row in the employees table.
• How many times: Once for each employee in the Sales department.

How Execution Grows With Input

As the number of employees in Sales grows, the update and returning work grows too.

Input Size (n)	Approx. Operations
10	About 10 updates and 10 rows returned
100	About 100 updates and 100 rows returned
1000	About 1000 updates and 1000 rows returned

Pattern observation: The work grows directly with the number of rows updated and returned.

Final Time Complexity

Time Complexity: O(n)

This means the time grows in a straight line with the number of rows updated and returned.

Common Mistake

[X] Wrong: "The RETURNING clause does not affect the time because it just shows results."

[OK] Correct: Returning rows means the database must collect and send data for each updated row, adding to the work done.

Interview Connect

Understanding how updates and returning results scale helps you explain database performance clearly and confidently.

Self-Check

"What if we removed the WHERE clause and updated all rows? How would the time complexity change?"