Thread: identify partitioning columns and best practices of partitioning in prod enviornments
identify partitioning columns and best practices of partitioning in prod enviornments
From
Atul Kumar
Date:
Hi, I want to about best practices of partitioning in prod environments and how to identify partitioning columns. Regards, Atul
Re: identify partitioning columns and best practices of partitioning in prod enviornments
From
Ron
Date:
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. -- Angular momentum makes the world go 'round.
Re: identify partitioning columns and best practices of partitioning in prod enviornments
From
Michael Lewis
Date:
On Wed, Nov 11, 2020 at 3:58 PM Ron <ronljohnsonjr@gmail.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?