I'm using Postgres 11 and would like to use a Hash Partitioning on a table where the primary key is a UUID. I understand I need to select a number of partitions up front, and that the modulus of a hash function on the primary key will be used to assign rows to each partition.
Something like this:
CREATE TABLE new_table ( id uuid ) PARTITION BY HASH (id);
CREATE TABLE new_table_0 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE new_table_1 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE new_table_2 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 2);
The documentation mentions "the hash value of the partition key" but doesn't specify how that hashing takes place. I'd like to test this hash function against my existing data to see the distribution patterns for different numbers of partitions. Something like this:
SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records
FROM existing_table
GROUP BY 1
Is there a way to use this hash function in a SELECT statement?
It should use hash_any. It doesn't seem to be exposed in any way that's directly accessible.