Partitioning recommendation
Jola Soares, March 24, 2021 - 5:46 pm UTC
Thank you for your feedback . .
is there any recommendation for minimum partition size when considering partitions?
FYI . .the table will grow to a much higher volume, that is why I'm considering 16 partitions at this point and take advantage of the partition-wise joins on already high volume tables that are hash 16 partitioned.
Best Regards
Jola
March 26, 2021 - 7:00 am UTC
is there any recommendation for minimum partition size when considering partitions?
Only based on your business requirements, not a hard and fast rule.
For example, I might have a high transaction system that I want to minimize indexes on. To enable queries to run fast on a date range *without* a date index, I might choose to partition at a fine grained level.
Conversely, if in the same system I don't mind an additional index, I might partition at a much higher level (week/month/etc).
Partitioning is like *every* database feature - you utilise it to meet business objectives.
Partitioning recommendation
Jola Soares, March 29, 2021 - 1:43 pm UTC
Thank you!
I have an additional scenario that I would like your input on:
table: 130 M records, 25 G size
Composite PK: coustomer_id, order_date, product_id, transaction_code
queries are using "where order_date " . ..
joins are on where customer_id =customer_id or product_id= product_id
I'm considering
1) interval range partition on order_date (3 months)
2) Hash partition on all primary key columns: coustomer_id, order_date, product_id, transaction_code - hash 16
3) local unique index on PK columns
Please note: the table will be partition by order_date 2 times-
range partition then in hash partition - can this approach present any possible issues?
Please advise
Best Regards
Jola
Partitioning based on data volume
Narendra, April 26, 2022 - 11:06 am UTC
Hello Connor/Chris,
Is there a way to achieve LIST partitioning based on the volume of data in a table? For e.g. how can I LIST partition a table that stores transactions for accounts, based on number of transactions per account? Assume I want to have only 3 partitions for 3 categories of ACCOUNTS (SMALL, MEDIUM, LARGE) and the accounts will be stored/distributed in partitions based on the volume of transactions in the table. I do understand there will be challenges when accounts (and its transactions) move from one category to another but am wondering whether there is a use case for this or if I am thinking this in a wrong way.
I am happy to define the criteria for the volumes explicitly.
Thanks in advance
April 28, 2022 - 4:19 am UTC
Hash partitioning will generally evenly distribute data across partitions, but that is volume not rate, and you need something that is going to be a good hash input.
If you looking for something more explicit, ie, "Based on this set of rules, I want customer X to be in partition Y", then you'd need to implement that yourself. It might be something that initially puts people in a default partition but then you come along later and split that partition into its desired distribution.
(That is beginning to sound like overkill to me though)