I have a transaction database that we recently went live with. The number of rows in the tables are starting to climb into the millions. So I am looking at how to purge the older data.
I have written purge processes before by just deleting the data I don't need anymore. But when you need to delete several million rows it can take a while and negatively impact performance and/or cause locks.
I have looked into table partitioning. The idea of partitioning the data and then just dropping a partition seems perfect.
However, from what I read, this cannot be done if the tables have foreign keys (my tables are mostly a chain of Master-Detail tables)
Is there a way to use partitioning with foreign keys, to delete lots of data?
Or some other cool way to delete large chunks of data without performance hits or locks?