Vryjdfkk

So i have a table 'fruits', which has 2 stage deletion process with first being a soft deletion and the second being a hard deletion that deletes item at various times based on business rules.

This table is performing badly when there are large number of rows, easiest way seemed to be to delete all the fruits marked for deletion and the UI performance is restored almost immediately.

While partitioning fruits on the 'deleted' column would be a great idea, we cant do it because we have 3 Foreign key constraints that prevents from using the built-in partitioning flow.

So the idea was to simulate partitioning by hooking a BEFORE UPDATE trigger to check if the updated row marks the row for deletion(soft) then do an INSERT into the 'fruits_deleted' table and DELETE the row that was due for UPDATE.

Currently the fruits table is only UPDATE'd to mark items for deletion. Nevertheless the trigger has a condition to check for operations specifically setting deleted=true.

Now i want to be sure that doing this is alright.Esp, in terms of the insert and delete happening without issues with the insert and delete being separate ops.
Should i wrap the concerned UPDATE calls in a transaction ?
Or should i leave it as is, considering that the row is locked when the update is happening and assume that should do fine ?

CREATE FUNCTION move_deleted_fruits()

RETURNS TRIGGER AS $$

BEGIN

    IF ( NEW.deleted = true ) THEN

      INSERT INTO fruits_deleted col1,col2... VALUE (NEW.val1,NEW.val2....);

      DELETE FROM fruits where _id = OLD._id;

      RETURN NULL; -- prevent other triggers from firing on update that sets deleted=true.

    ELSE 

     UPDATE fruits SET col1=val1,col2=val2... WHERE _id = OLD._id;

     RETURN NEW.*;

   END IF;



END;

$$

LANGUAGE plpgsql;



--and then



CREATE TRIGGER trig_move_deleted_fruits

BEFORE UPDATE ON fruits

FOR EACH ROW EXECUTE PROCEDURE move_deleted_fruits();

But indeed other ideas are welcome to do the isolation differently. There is a lot of complex queries (many of them inefficient and are marked for improvement as tech debt) but this is something that needs to be fixed as of now, as is.