Thanks again for your questions - they gave me pause for thought and I
will try to apply them in future partitioning scenarios. (Unfortunatly
:-) ) there is no magic number of partitions for, say, a given size of
table - otherwise it would be the default and would be done
automatically!
no worries, it seems you have a fair idea how things would grow. so partitioning would be of help.
My biggest worry around partitioning is around moving data around partitions/ rebalancing partitions.
It requires making use of triggers, syncing data and then attach/remove partitions if it is simple as
list/range and use binary split for large partitions, to avoid large row movements.
But when it comes to hash, it requires rebuilding the entire setup either via triggers if there is significant space on the db,
or logically replicating to another server
with a new hash scheme. because i do not have lot of exp around sharding/partitioning,
nor about how others dbs on how they claim automatic movements of data between
partitions, I think i wore myself out due to poor design.