Trigger for data validation in PostgreSQL - Time & Space Complexity

Time Complexity: Trigger for data validation

O(n)

Understanding Time Complexity

When using triggers for data validation in PostgreSQL, it's important to understand how the time to run the trigger changes as the amount of data grows.

We want to know how the trigger's work scales when many rows are inserted or updated.

Scenario Under Consideration

Analyze the time complexity of the following trigger function and trigger.

CREATE FUNCTION validate_age() RETURNS trigger AS $$
BEGIN
  IF NEW.age < 0 OR NEW.age > 120 THEN
    RAISE EXCEPTION 'Invalid age: %', NEW.age;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_age
BEFORE INSERT OR UPDATE ON persons
FOR EACH ROW EXECUTE FUNCTION validate_age();

This trigger checks that the age column is between 0 and 120 for every inserted or updated row.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The trigger function runs once for each row inserted or updated.
• How many times: It runs exactly as many times as there are rows affected by the operation.

How Execution Grows With Input

Each row causes the trigger to run once, so the total work grows directly with the number of rows.

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

Pattern observation: The work increases in a straight line as more rows are processed.

Final Time Complexity

Time Complexity: O(n)

This means the time to validate grows directly with the number of rows inserted or updated.

Common Mistake

[X] Wrong: "The trigger runs only once no matter how many rows are changed."

[OK] Correct: In PostgreSQL, row-level triggers run once per row, so the work adds up with each row.

Interview Connect

Understanding how triggers scale helps you design efficient database validations and shows you can think about performance in real projects.

Self-Check

"What if we changed the trigger to a statement-level trigger? How would the time complexity change?"