See comments from Jenn below
The trigger process_marc_records_lb_delete_trigger was added to the table marc_records_lb in mod-source-record-storage v5.1.0-SNAPSHOT makes it extremely slow to delete records in the records_lb table. (records_lb is related to marc_records_lb by ways of foreign key.) Deleting 25K records from records_lb takes over 12 minutes with this trigger enabled, but after disabling this trigger, deleting 75K records from records_lb only took 6.1 seconds. This trigger deletes records from marc_indexers and marc_indexers_leader row by row by matching up the IDs, which makes it painfully slow. Could these two tables be hooked up to marc_records_lb by foreign keys and take advantage of CASCADE Delete to have the records deleted without calling the delete statement explicitly on each row?
Steps to Reproduce:
Delete any number of records (25K or even 1K) in marc_records_lb of mod-source-record-storage v5.1.0-SNAPSHOT with a time constraint, ex:
delete FROM fs08000000_mod_source_record_storage.records_lb where created_date > '2021-05-16 22:00:00.00+00'
It will take minutes to delete
Delete operation would take seconds
Delete operation takes more than 10 minutes!
- This story requires the preparation in a database to be implemented first
- The approach is to implement scheduled deletion from marc_indexers & marc_indexers_leaders. The scheduler will monitor marc_indexers & marc_indexers_leaders and clean up the records marked deleted = 'true'. The time settings for the scheduler for deletion should be clarified with the PO