Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 14:13, Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> wrote:

> 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.

--
Thanks,
Vijay
Mumbai, India