Purge Records from Database
We have requirement to purge records from DB on periodic basis. What is the best way to purge this records and also release the DB space.
Simply running the RDB delete command only deletes the records but does not release the DB space and hence leads to fragmentation.
Note: OOTB Purge cannot be used as it does not support conditions
Updated: 13 Apr 2021 7:26 EDT
Proximus
BE
Hello, Purging the data can be organized and scheduled from the application and it's better to have it daily running on a small set of data, than weekly or even more on a bigger set (of course, if not disturbing the application). By having it running daily, the fragmentation should be smaller as your DB should be able to retrieve some of the space by itself.
Yet, a global maintenance should be foreseen at DB level to shrink the most fragmented tables and to rebuild the index and I think that for this, it should be done by DBA while application is down. They've got powerful tools and it's easier and faster with application down.
For this second part, I guess it also depends on your DB platform/infra as well whether you can have downtime from time to time. You can also consider working with partitions.
Purging is to be defined by application team maybe with help of DBA. Maintenance of the DB/Schemas/tables is the be defined by DBA with info provided by application team.
Regards
Anthony