DELETE with RETURNING clause in PostgreSQL - Time & Space Complexity

Time Complexity: DELETE with RETURNING clause

O(n)

Understanding Time Complexity

When we delete rows from a table and ask the database to return the deleted rows, it takes some time to find and remove those rows.

We want to understand how the time needed grows as the number of rows to delete grows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DELETE FROM employees
WHERE department_id = 5
RETURNING employee_id, name;
    

This code deletes all employees in department 5 and returns their IDs and names.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning the table or index to find rows matching the condition.
• How many times: Once for each row in the table or index scanned.

How Execution Grows With Input

As the number of rows to delete grows, the database must check more rows and delete more data.

Input Size (n)	Approx. Operations
10	About 10 row checks and deletes
100	About 100 row checks and deletes
1000	About 1000 row checks and deletes

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

Final Time Complexity

Time Complexity: O(n)

This means the time to delete and return rows grows linearly with how many rows match the condition.

Common Mistake

[X] Wrong: "Deleting rows with RETURNING is instant no matter how many rows match."

[OK] Correct: The database must find and remove each matching row and prepare the returned data, so more rows mean more work and more time.

Interview Connect

Understanding how DELETE with RETURNING scales helps you explain database behavior clearly and shows you think about efficiency in real tasks.

Self-Check

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