A transaction history table holds ten years of data, and policy requires keeping only the most recent seven years. The monthly DELETE that removes expired rows runs for hours, generates heavy logging and blocks other work. What design change best addresses this?
Select an answer to reveal the explanation.
Short Explanation
Deleting old rows one by one is like emptying a filing cabinet a sheet at a time. Partition by date and each month becomes its own drawer you can pull out whole. That's the trick here: horizontal, date-based partitioning turns a long purge into a quick metadata operation.
Full Explanation
Range partitioning divides a table's rows into partitions based on a column value, commonly a date. When each partition holds one month or year of history, removing expired data becomes a partition-level operation, such as dropping, truncating or switching out the oldest partition, which is far faster than deleting millions of rows individually, generates much less logging and avoids long-held locks on the rest of the table. Vertical partitioning separates columns into different tables; it can reduce row width for frequently queried columns, but every remaining table still contains all the rows, so the purge remains a large row-by-row delete. Adding an index on every column may help a DELETE locate rows slightly faster, but each additional index must also be maintained during the delete, increasing logging and work, and it adds overhead to every insert. Denormalizing into summary tables changes the data model and may duplicate data, but it does not remove the need to purge detail rows that exceed retention. Exam caveat: partitioning is a maintenance and manageability tool first; it does not automatically make every query faster, and the partition key must match how data is filtered and aged out. Operational check: design monthly or yearly partitions on the date column, test removing the oldest partition in non-production, and confirm the purge window and log volume shrink.