I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly designed -- string keys, denormalized and so on, and performance is poor.
In the cases I am considering, the records could be considered "expired" based on some business logic, and while the data is needed for historical and statistical transactions, at runtime, operations need only apply to unexpired rows. Unexpired rows account for something like 5% of the whole data set.
It's runtime performance I want to improve.
We're not in a position to redesign the system yet, but it occurs to me that some combination of partitions or perhaps conditional indexes might help. For example some data expires monthly, and we could run a monthly task to mark the records expired in a column of the table. One table has a date column which appears to have been designed for this purpose, although it is not even indexed!
So if our runtime queries are scoped to include a condition like "expired_on is not null" my hope is that we could get near-steady-state performance using an index ... But is this a good case to use either or both of a conditional index (only unexpired rows) or partitions, perhaps creating a new one each month to include the prior month's now-expired records?
We could create some sort of archiving system, or roll up tables for historical stats, but for now I am looking to buy time :-). Any suggestions greatly appreciated.