Returning modified rows with RETURNING in PostgreSQL - Time & Space Complexity

Time Complexity: Returning modified rows with RETURNING

O(n)

Understanding Time Complexity

When we update rows in a database and ask it to return the changed rows, we want to know how the time it takes grows as the data grows.

How does the database handle returning modified rows as the number of rows changes?

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 increases the salary by 10% for all employees in the Sales department and returns their id and new salary.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning all rows in the employees table to find those in the Sales department.
• How many times: Once per row in the table, to check the department and update if needed.

How Execution Grows With Input

As the number of employees grows, the database must check each one to see if they belong to Sales and update their salary if so.

Input Size (n)	Approx. Operations
10	About 10 checks and some updates
100	About 100 checks and more updates
1000	About 1000 checks and many updates

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to update and return rows grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "The RETURNING clause makes the update run faster because it returns data immediately."

[OK] Correct: Returning rows adds work because the database must collect and send the updated rows, so it does not reduce the time needed to find and update rows.

Interview Connect

Understanding how returning modified rows affects performance helps you explain database behavior clearly and shows you think about real data sizes and costs.

Self-Check

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