I'm working on designing a partitioning strategy for a large table in my database, and I'm considering using hash-based partitioning. I came across a recommendation to have the number of partitions equal to
log2(number of distinct rows).
Can someone explain the reasoning behind this recommendation?
Additionally, I have three different scenarios:
a string with a prefix (ORD3242343),
numeric strings, and
I would like to know which partitioning strategy would be better suited for each of these scenarios and how to determine the optimal number of partitions based on my specific case.
I have a table called
order with the following structure:
CREATE TABLE order ( order_id TEXT, status TEXT, -- Other columns );
For the three scenarios mentioned, here's some additional information:
String with a prefix: The
order_id column follows the format ORD+some number. I want to extract the numeric portion and use it for partitioning.
Numeric strings: Some
order_id values are purely numeric strings. I need to convert them into actual numbers for partitioning purposes.
UUIDs: There are
order_id values in the form of UUIDs. I'm considering using the
hashtext() function for partitioning based on these UUIDs.
Based on these scenarios, I would appreciate any advice on the following:
Which partitioning strategy (range or hash) would be more suitable for each scenario?
How can I determine the optimal number of partitions for each scenario?
Are there any other considerations I should keep in mind while implementing these partitioning strategies?
Any insights or examples would be greatly appreciated. Thank you in advance!
Why do I want to partition the table?
- The table has around 2TB of data and around 2 billion rows.
- I can't remove old data.
- The table is growing very fast.
- For some users, the queries get timed out due to slow processing even with having indexes.