Archiving Old Rows: The Cheapest Index Is a Smaller Table
Most tables that got slow got slow because they kept everything forever while queries only ever ask about the last few months. Moving old rows to an archive table, or partitioning by time so the planner can prune whole chunks, shrinks what every scan, index and vacuum has to touch. It is near the bottom of the ladder because it needs a retention decision from the business and a migration, but when it applies it gives back more than any index can.
Questions this Concept answers
- Why can removing old rows give back more than any index can?
Archiving Old Data Safely With SQL
Tables that keep every row forever get slower because every scan has to wade through history nobody queries. The usual fix is to pick a cutoff date, create an archive table with the same shape, and m…