When would one NOT want to partition a database? (thinking MySQL partitioning)
In my case
- I'll be starting with a couple millions rows, it should grow from there.
- Primary key on a character field that serves as the most frequent query restraint (and lookups are frequent - at least a few per second).
- Primary key would be hashed to serve as the partition key
- Updates will be made to every row that is pulled in the frequent queries mentioned above
- Less frequent lookups (against date columns or other) will need to hit all partitions
Even for the last point, doesn't the lookup run in parallel so in all cases, is this a win? What are the downsides to partitioning? Why isn't it something that EVERYONE uses by default, at least when you are looking at a million+ records?
UPDATE - I selected zgguy's answer but note that I added my own answer with the results of my own research including a link to a really good answer on a similar question that was highly useful to me.