I am using Azure SQL database server. I have a requirement to delete records from two tables which gets older than x days. Also, deleting records with these two tables means 150 M records on the regular basis. Those are Read/Write operations heavy tables. I don’t want to hit the SLA too with any of the solution. My estimation is that size of these two tables will continue to grow in future.
My question - What could be the best solution to purge records older than x days on regular basis. What factor should I consider before opting nto any of the below solution.
- Application level job: Application job to make DB calls and delete in batches.
- DB level job: Same logic as application log. Only reducing DB calls in this solution
- Horizontal partitioning: Partition those table horizontally and then delete tables with older records.
- Move to NoSQL DB like Cosmos to use its TTL feature.