Request for an advisory on best practice and possible performance pitfalls
Scenario:
Given a table that continually has rows added, we want to purge entries fulfilling a certain criteria. However, we want to persist entries before purging and we want to ensure that only persisted entries are purged.
Solution (so far):
We do this as a scheduled Job, that executes 3 steps implemented as separate procedures:
- Step 1:
Copy entries from source table that meets the criteria to a structurally identical table (henceforth: the buffer table). The buffer table is only accessed by the Job. - Step 2:
Save rows from the buffer table to the file system. - Step 3:
Delete rows from the source that are present in the buffer table. Rows are deleted in chunks in order to avoid long locking.