Row-level vs statement-level triggers in PostgreSQL - Performance Comparison

Time Complexity: Row-level vs statement-level triggers

O(n) for row-level triggers, O(1) for statement-level triggers

Understanding Time Complexity

When using triggers in PostgreSQL, it's important to know how their execution time changes as more rows are affected.

We want to understand how row-level and statement-level triggers behave as input size grows.

Scenario Under Consideration

Analyze the time complexity of these two trigger types.

-- Row-level trigger example
CREATE FUNCTION trg_row_func() RETURNS trigger AS $$
BEGIN
  -- some operation per row
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_row AFTER INSERT ON my_table
FOR EACH ROW EXECUTE FUNCTION trg_row_func();

-- Statement-level trigger example
CREATE FUNCTION trg_stmt_func() RETURNS trigger AS $$
BEGIN
  -- some operation once per statement
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_stmt AFTER INSERT ON my_table
FOR EACH STATEMENT EXECUTE FUNCTION trg_stmt_func();

The first trigger runs once for every inserted row; the second runs once per insert statement regardless of rows.

Identify Repeating Operations

Look at how often the trigger function runs.

• Primary operation: Trigger function execution
• How many times: Row-level: once per row; Statement-level: once per statement

How Execution Grows With Input

As the number of inserted rows increases, the row-level trigger runs more times, but the statement-level trigger runs only once.

Input Size (rows inserted)	Row-level Trigger Calls	Statement-level Trigger Calls
10	10	1
100	100	1
1000	1000	1

Pattern observation: Row-level trigger calls grow linearly with rows; statement-level trigger calls stay constant.

Final Time Complexity

Time Complexity: O(n) for row-level triggers, O(1) for statement-level triggers

This means row-level triggers take more time as more rows are affected, while statement-level triggers take about the same time no matter how many rows.

Common Mistake

[X] Wrong: "Row-level and statement-level triggers run the same number of times for an insert."

[OK] Correct: Row-level triggers run once per row, so their work grows with rows. Statement-level triggers run once per statement, so their work stays the same regardless of rows.

Interview Connect

Understanding how triggers scale helps you design efficient database logic and shows you can think about performance as data grows.

Self-Check

What if we changed a row-level trigger to a statement-level trigger? How would the time complexity change when inserting many rows?