Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

postgresql - How to know which partition will be used in Postgres hash partitioning?

Is there a way to know which partition will be used for a new row in a table partitioned by a column with text values?

Use case is: I have a big table with many partitions according to a text column. I need to insert-update millions of new rows to this table. If I know which partitions will be used for each row, I can rearrange my bulk inserts-updates to only work on 1 partition at a time.

-> hashtext function is tested and it is not giving the correct partition value.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Reverse engineering the code, you can get the partition number with the following statement:

SELECT (hashtextextended('value', 8816678312871386365)::numeric + 5305509591434766563) % 8;

Replace 8 with the number of partitions and 'value' with the string in question.

You can test the partition number with satisfies_hash_partition. To test if 'value' would end up in partition 6 of 8 in table tab, you can run

SELECT satisfies_hash_partition('tab'::regclass, 8, 6, 'value'::text);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...