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)
Example:
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.
Questions:
- is this approach going to work
- is this the best approach, if not what are some other ideas
Notes:
- Most records will fall into the larger partitions