On Wed, Nov 11, 2020 at 3:58 PM Ron <ronljohnsonjr@xxxxxxxxx> 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?
