Re: identify partitioning columns and best practices of partitioning in prod enviornments

On Wed, Nov 11, 2020 at 3:58 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> On 11/11/20 4:31 PM, Atul Kumar wrote:
> > Hi,
> >
> > I want to about best practices of partitioning in prod environments
> > and how to identify partitioning columns.
>
> It depends on what you want to do. If your purpose is to simplify the
> deletion of old records, then partition by an unchanging date field.
> If your purpose is to increase locality of data (because many of your
> queries are an equality on a specific "group id"), then partition by that
> "group id" field.
>

Additionally, while partitioning is hugely improved in v12 (and perhaps 13,
I forget), there are still restrictions on what you can partition on & what
you can have a primary key on. Also of note that having more than hundreds
or low thousands of partitions may have a significant impact on planning
and execution times. It is a great tool, but sometimes is implemented badly
or prematurely and the cost may not be worth a theoretical benefit.

Are you just wanting to learn about partitioning, or do you have a specific
situation that you think would benefit from partitioning?