Trigger function creation in PostgreSQL - Time & Space Complexity

Time Complexity: Trigger function creation

O(n)

Understanding Time Complexity

When we create a trigger function in PostgreSQL, it runs automatically when certain events happen. Understanding how the time it takes to run grows helps us know if it will slow down our database.

We want to see how the work done by the trigger function changes as the data it handles grows.

Scenario Under Consideration

Analyze the time complexity of the following trigger function.

CREATE OR REPLACE 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 function updates a timestamp column every time a row in the table is updated.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The trigger runs once for each row updated.
• How many times: It runs exactly once per updated row, no loops inside the function.

How Execution Grows With Input

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

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

Pattern observation: The work grows linearly as more rows are updated.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the trigger grows directly in proportion to the number of rows updated.

Common Mistake

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

[OK] Correct: In PostgreSQL, row-level triggers run once for each row affected, so the time grows with the number of rows, not just the statement.

Interview Connect

Knowing how triggers scale helps you design efficient database logic that won't slow down as data grows. This skill shows you understand how database internals affect performance.

Self-Check

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