Postgres Pro
Downloads
Home   >   mailing lists

Re: Partitioning a table by integer value (preferably in place) - Mailing list pgsql-general

From Vijaykumar Jain
Subject Re: Partitioning a table by integer value (preferably in place)
Date
Msg-id CAM+6J9630Hd26gfbxLcGs_Nhshk4X2rFxMxXuMnzZJvKJufXwg@mail.gmail.com
Whole thread Raw
In response to Re: Partitioning a table by integer value (preferably in place)  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
Tree view
On Fri, 13 Aug 2021 at 21:07, Michael Lewis <mlewis@entrata.com> wrote:
On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain <vijaykumarjain.github@gmail.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.


Thanks,
Vijay
Mumbai, India

pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: Partitioning a table by integer value (preferably in place)
Next
From: ourdiaspora
Date:
Subject: Re: php connection failure