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

On Fri, 13 Aug 2021 at 21:07, Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain <
> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>
>> ... use binary split for large partitions, to avoid large row movements.
>>
>
> Would you expound on this?
>

if we have range partitions based on year, 2018 -2019, 2019-2020, etc and
for
any one year we see uneven growth in data, we simply split that partition
by 2 and move only 6 months worth of data
to another new partition.
If we see similar issues not solved by the first split, we split each again
by 2 and only move the remainder of data to all four partitions.
So at any point of time, if the range partition which was planned earlier
does not work well for some periods, we split the partitions.
and if we see the trend growing with increased volume, the new partitions
are created qtry etc.

I have not done hash partitioning rebuild, but i think this video explains
how we could best do it.
Ways of sharding and resharding in a distributed system - YouTube
<https://www.youtube.com/watch?v=7KxPlw2NXb4&list=LL&index=1>

Thanks,
Vijay
Mumbai, India