NEW and OLD record access in PostgreSQL - Time & Space Complexity

Time Complexity: NEW and OLD record access

O(n)

Understanding Time Complexity

When using triggers in PostgreSQL, we often access the NEW and OLD records to see changes.

We want to understand how the time to access these records grows as the number of rows changes.

Scenario Under Consideration

Analyze the time complexity of this trigger function snippet.

CREATE FUNCTION trg_update_example() RETURNS trigger AS $$
BEGIN
  IF NEW.value IS DISTINCT FROM OLD.value THEN
    INSERT INTO audit_log(table_name, old_value, new_value) VALUES (TG_TABLE_NAME, OLD.value, NEW.value);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This trigger checks if a value changed and logs the old and new values.

Identify Repeating Operations

In this trigger, the main repeating operation is the trigger firing for each row affected.

• Primary operation: Accessing NEW and OLD records and inserting into audit_log.
• How many times: Once per affected row in the table.

How Execution Grows With Input

As the number of rows affected increases, the trigger runs once per row.

Input Size (n rows)	Approx. Operations
10	10 trigger executions
100	100 trigger executions
1000	1000 trigger executions

Pattern observation: The work grows directly with the number of rows affected.

Final Time Complexity

Time Complexity: O(n)

This means the time to process grows linearly with the number of rows the trigger handles.

Common Mistake

[X] Wrong: "Accessing NEW and OLD records is a single operation regardless of rows affected."

[OK] Correct: The trigger runs once per row, so accessing NEW and OLD happens repeatedly, scaling with row count.

Interview Connect

Understanding how triggers scale with row count helps you write efficient database logic and shows you grasp how databases handle data changes.

Self-Check

"What if the trigger was defined as FOR EACH STATEMENT instead of FOR EACH ROW? How would the time complexity change?"