AFTER trigger behavior in PostgreSQL - Time & Space Complexity

Time Complexity: AFTER trigger behavior

O(n)

Understanding Time Complexity

When using AFTER triggers in PostgreSQL, it's important to know how the time to run the trigger grows as the number of affected rows increases.

We want to understand how the trigger's execution time changes when more rows are inserted, updated, or deleted.

Scenario Under Consideration

Analyze the time complexity of this AFTER trigger example.

CREATE FUNCTION log_update() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(table_name, changed_at) VALUES (TG_TABLE_NAME, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION log_update();

This trigger runs after each row update and inserts a log record for that row.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger function runs once for every 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 (n)	Approx. Operations
10	10 trigger executions
100	100 trigger executions
1000	1000 trigger executions

Pattern observation: The number of trigger executions grows directly with the number of rows updated.

Final Time Complexity

Time Complexity: O(n)

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

Common Mistake

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

[OK] Correct: AFTER triggers defined FOR EACH ROW run once per row, so more rows mean more trigger executions.

Interview Connect

Understanding how triggers scale with data changes helps you write efficient database logic and explain performance impacts clearly.

Self-Check

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