We are building a Loyalty system where the data is centered around the customer. We have the customer, all his transactions and the loyalty points derived from the transactions. The volume is expected to be more than 14 million customers and point transactions more than 30-40 million.
In such a scenario, if I want to partition the tables of customer, his transaction and point tables, which strategy is better.
The point transactions are to be retained only for three quarters(current qrtr + 2 previous qrtrs). Only the customer information is to be retained. All cusotmers who are deactivated can be removed periodically.
We are planning to retreive based on indexes and we are having multiple file systems.
I am trying to understand how we can have partitions that store records based on say the customer status(active, deactive), the status of various request placed by the cutomer(like change in personal details, redemption of loyalty points etc.) whose status gets closed on a daily basis. The processing would be real fast if I have all the open request alone in one partition. Can we have such dynamic partitions? Can we try to move closed status records to other partition on a periodic basis?is it feasible?What is the overhead?
If you are looking to truncate the data, go RANGE. HASH partitioning is used to spread the rows across the partitions, thereby making the partitions the same size and balancing the load. I also depend on your retention period for the data and the way in which data is archived or deleted/truncate.
A huge benefit of RANGE partitioning, is that you can partition on a date and then truncate the paritition once the data becomes unwanted. You can't perform this on HASH paritions, simple becasue the data (iin my case) would be spread across all paritions, therefore truncating a partition based on date wouldn't be possible.