I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added.
Here are some of my concerns:
- How many partitions are too many
- Is having small partitions bad (could have less than 150 records per partition)
- Large partitions will be 10,000 or more records
- partitions are created by field, not a date
- new records could be added daily to the larger partitions but the smaller partitions might be monthly
- possibly need to separate partitioned data legally (some cases but not all)
Let's say 10,000 out of 100,000 will need to be broken into partitions of 100 records, so 1000 partitions will be created and 9 additional partitions will be created with 10,000 records each, for a total of 1009 partitions.
- is this approach going to work
- is this the best approach, if not what are some other ideas
- Most records will fall into the larger partitions