Partitioning strategy and optimal number of partitions for varying data formats (string, numeric, and UUID) in a large table

Question

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:

1. a string with a prefix (ORD3242343),

2. numeric strings, and

3. UUIDs.

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:

1. Which partitioning strategy (range or hash) would be more suitable for each scenario?

2. How can I determine the optimal number of partitions for each scenario?

3. 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!

Update:

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.

Answer 1

The binary logarithm of the number of distinct rows is a nonsensical measure for the number of partitions. It would mean that a table with 1024 rows needs 10 partitions, while I seriously doubt the usefulness of partitioning with row counts under 10 million or so.

Hash partitioning is the least useful of all partitioning strategies. It is normally only used if all you want is to reduce the size of the tables, for example to speed up autovacuum. With hash partitioning, you don't get to choose the hash function.

To your specific questions:

1. It is impossible to make a recommendation without knowing the SQL statements that will be run against the tables. If your queries are already using indexes, it is likely that partitioning will make them slower rather than faster. Partitioning can speed up certain queries, among them

   • queries that have the partitioning key in the WHERE condition and either perform a sequential scan or can perform a sequential scan instead of an index scan on a partition

   • queries that calculate aggregates that can be calculated partitionwise

   • queries that perform joins that can be calculated partitionwise

2. partition so that each partition contains no less than a couple of million rows and that there are no more than a few thousand partitions

3. you will have to live with reduced consistency guarantees if you use partitioning, as primary keys and foreign keys cannot be defined the way you would like to