Script to hard delete OLD records

The following script hard deletes records from the records_lb table with state=OLD and related entries in the indexers table.

SET SEARCH_PATH = '{tenantId}_mod_source_record_storage';
BEGIN;

DELETE FROM marc_indexers
WHERE EXISTS(
  SELECT 1
  FROM records_lb
  WHERE records_lb.id = marc_indexers.marc_id
    AND records_lb.state = 'OLD'
);

DELETE FROM records_lb WHERE state = 'OLD';
 
COMMIT;

The script that uses batches for deletion:

SET SEARCH_PATH = '<schema>_mod_source_record_storage';
DO $$
DECLARE
    batch_size INTEGER := 10000 -- Size of the batch; 
    deleted_count INTEGER;
BEGIN
    DROP TABLE IF EXISTS temp_ids;
    LOOP
        CREATE TEMP TABLE temp_ids AS
        SELECT rl.id
        FROM records_lb rl
        WHERE rl.state = 'OLD'
        LIMIT batch_size;

        GET DIAGNOSTICS deleted_count = ROW_COUNT;      
        RAISE NOTICE 'Number of records to delete: %', deleted_count;

        IF deleted_count = 0 THEN
            RAISE NOTICE 'No more records to delete. Exiting process.';
            EXIT; 
        END IF;

        DELETE FROM marc_indexers
        WHERE marc_id IN (SELECT id FROM temp_ids);

        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE 'Deleted % records from marc_indexers.', deleted_count;

        DELETE FROM records_lb
        WHERE id IN (SELECT id FROM temp_ids);

        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        RAISE NOTICE 'Deleted % records from records_lb.', deleted_count;

        DROP TABLE temp_ids;
    END LOOP;
END $$;