Why triggers are needed in PostgreSQL - Performance Analysis

Time Complexity: Why triggers are needed

O(n)

Understanding Time Complexity

We want to understand how the work done by triggers grows as the data changes.

How does the time to run triggers change when more rows are affected?

Scenario Under Consideration

Analyze the time complexity of this trigger function and its use.

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

This trigger updates a timestamp column automatically whenever a row is updated.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger runs once for each updated row.
• How many times: Equal to the number of rows updated in the statement.

How Execution Grows With Input

As more rows are updated, the trigger runs more times.

Input Size (rows updated)	Approx. Trigger Runs
10	10
100	100
1000	1000

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the trigger grows linearly with the number of rows updated.

Common Mistake

[X] Wrong: "Triggers run only once per statement, so their cost is constant no matter how many rows change."

[OK] Correct: In PostgreSQL, row-level triggers run once per affected row, so more rows mean more trigger executions.

Interview Connect

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

Self-Check

"What if the trigger was a statement-level trigger instead of row-level? How would the time complexity change?"