Postgres' conditional trigger: performance consideration



  • In my DB i need to make conditional trigger, and i stumbled upon https://stackoverflow.com/questions/36029446/postgres-insert-or-update-trigger-when-condition-old article. Discussed in the article, there are 2 options to implement the conditions:

    • Option A: condition resides in the trigger function rather than on the trigger itself
    • Option B: condition resides in trigger, not in the trigger function

    Option A

    Trigger:

    CREATE TRIGGER mytrigger
        BEFORE INSERT OR UPDATE ON "mytable"
        FOR EACH ROW 
        EXECUTE PROCEDURE mytrigger();
    

    Trigger function:

    CREATE OR REPLACE FUNCTION mytrigger()
       RETURNS trigger AS
    $BODY$
    begin
    if NEW.score > 5 then
       --code for Insert
       if  (TG_OP = 'INSERT') then
           YOUR CODE
       end if;
    

    --code for update
    if (TG_OP = 'UPDATE') then
    if OLD.score <> NEW.score then
    --YOUR CODE
    end if;
    end if;
    end if;
    return new;
    end;
    $BODY$
    LANGUAGE plpgsql VOLATILE

    Option B

    Triggers:

    CREATE TRIGGER mytrigger1
       BEFORE INSERT ON "mytable"
       FOR EACH ROW 
       WHEN NEW.score > 5
       EXECUTE PROCEDURE mytrigger();
    CREATE TRIGGER mytrigger2
       BEFORE UPDATE ON "mytable"
       FOR EACH ROW 
       WHEN (NEW.score > 5 AND OLD.score <> NEW.score)
       EXECUTE PROCEDURE mytrigger();
    

    Trigger function:

    CREATE OR REPLACE FUNCTION mytrigger()
       RETURNS trigger AS
    $BODY$
    begin
      --YOUR CODE
    return new;
    end;
    $BODY$
       LANGUAGE plpgsql VOLATILE 
    

    ! I copied the above code from the article referred.

    My question:

    • Performance-wise, which one is better ?
    • Is Option B better because trigger fires only when conditions met; while in Option A trigger will fire anyway in the first place?

    Version(s) used: PostgreSQL 9.6 and afterwards.



  • Precisely: option B is better, because it avoids the overhead of calling the trigger function for those rows where it is not necessary.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2