0

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.
2
  • 2
    "number of partitions equal to log2(number of distinct rows)" - You know what inherently is log2(n)?...indexes, particularly B-Tree indexes. Partitioning isn't really a tool for improving performance. If you're having performance issues, you should focus on solving them properly and describe them in your Post.
    – J.D.
    Commented Jun 13, 2023 at 3:14
  • 1
    If the people who make the recommendation don't explain it, maybe you shouldn't pay any attention to the recommendation.
    – jjanes
    Commented Jun 13, 2023 at 11:21

1 Answer 1

2

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

3
  • I have added the reasons why I need to partition the table. Please have a look. Also, for my case, I don't see any other better option rather than going with Hash. As stated, the order_id can be of three different patterns. So, I can't use List, maybe by list(lower(right(order_id, 2))) But it will be so much skewed. Range, certainly not, can't pass date in all the queries.
    – sujeet
    Commented Jun 15, 2023 at 5:04
  • ...and I would be passing the partition key in all the queries, So I settled with Hash partition.
    – sujeet
    Commented 2 days ago
  • That's fine, as long as it works for you. Commented 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.