Currently, previously existing rows in the "marc_indexers" table are deleted when a corresponding record is updated in the "marc_records_lb" table since they may not reflect the actual state of the corresponding record in the "marc_records_lb" after its update.
It makes sense to perform deletion of the "marc_indexers" rows associated with source marc records in the "OLD" state since they are not accounted when searching in the "marc_indexers" table.
As a possible approach, it can be considered a modification of the existing query for "marc_indexers" table cleanup.

Also add migration script to clean up rows from "marc_indexers" associated with "OLD" records in "marc_records_lb".

ORCHID Critical service patch details

1. Describe issue impact on business: to speed up queries, searching, and insert operation in SRS, plus size of database overall can be decreased

2. What institutions are affected? (field “Affected Institutions” in Jira to be populated): Any that use SRS or Data Import

3. What is the workaround if exists? None, really, since this is an infrastructure change

4. What areas will be impacted by fix (i.e. what areas need to be retested):

5. Brief explanation of technical implementation and the level of effort (in workdays) and technical risk (low/medium/high):

   • Summary: Work is already completed (3-4 days). This work cleaned up "marc_indexers" rows associated with "OLD" source records since they are not needed during search in the "marc_indexers" table.

   • Approach

     • Modify the existing query for "marc_indexers" table cleanup with condition by "OLD" records state.

     • Add index on the "records_lb"."state" field

     • Add migration script to clean up rows from "marc_indexers" associated with "OLD" records in "marc_records_lb".

     • Add test

   • Technical risk: Low

6. Brief explanation of testing required and level of effort (in workdays). Provide test plan agreed with by QA Manager and PO: After the MODSOURCE and MODSOURMAN patches are applied, we need to retest the Smoke and Critical Path Data Import tests (most of which are automated), and perhaps selected Extended Manual tests. Manual testing across these MODSOURCE and MODSOURMAN changes are likely 3-5 days of work for manual QA, plus some input from PO.

7. What is the roll back plan in case the fix does not work? Revert to previous version, though rollback should not be needed because these old records are not accessible in the database, even though they are retained.

NOLANA Critical service patch details

1. Describe issue impact on business: to speed up queries, searching, and insert operation in SRS, plus size of database overall can be decreased

2. What institutions are affected? (field “Affected Institutions” in Jira to be populated): Any that use SRS or Data Import

3. What is the workaround if exists? None, really, since this is an infrastructure change

4. What areas will be impacted by fix (i.e. what areas need to be retested):

5. Brief explanation of technical implementation and the level of effort (in workdays) and technical risk (low/medium/high):

   • Summary: Work is already completed (3-4 days). This work cleaned up "marc_indexers" rows associated with "OLD" source records since they are not needed during search in the "marc_indexers" table.

   • Approach

     • Modify the existing query for "marc_indexers" table cleanup with condition by "OLD" records state.

     • Add index on the "records_lb"."state" field

     • Add migration script to clean up rows from "marc_indexers" associated with "OLD" records in "marc_records_lb".

     • Add test

   • Technical risk: Low

6. Brief explanation of testing required and level of effort (in workdays). Provide test plan agreed with by QA Manager and PO: After the MODSOURCE and MODSOURMAN patches are applied, we need to retest the Smoke and Critical Path Data Import tests (most of which are automated), and perhaps selected Extended Manual tests. Manual testing across these MODSOURCE and MODSOURMAN changes are likely 3-5 days of work for manual QA, plus some input from PO.

7. What is the roll back plan in case the fix does not work? Revert to previous version, though rollback should not be needed because these old records are not accessible in the database, even though they are retained.