We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and including the accountid.
In order to keep things a bit performant, I suggested to partition the table on day level (done), and create a local index (prefixed or non-prefixed) on accountid. Now, one of the teammembers says it will not be possible, since they need to create an index on only the last five partitions of the table, and that would require too much effort.
AFAIK, the creation of the index needs to be done only once, and then daily, the index on the new partition only needs to be created. Besides that, indexes on partitions older than 5 days will never be used anymore, so there's nothing to do there.
What are your ideas on this? How to best solve this issue, with performance in mind?