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.
New contributor
Geeta is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • In its current form, this question is a Shopping List question (e.g. questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer.). If you have a question about a particular aspect of one of these approaches, please edit your question accordingly so it's asking about a specific issue. – John Eisbrener Mar 24 at 16:42