Thread: Hash partitioning, what function is used to compute the hash?
Regarding hash partitioning, what is the function/algorithm that is used to compute the hash for the partition key? I need to write a query like
“SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records
FROM existing_table
GROUP BY 1”
Sent from Mail for Windows 10
The documentation shows it is just a modulus operation. If you partition on object_key % 3 then you will create three partitions for remainder values 0-2 for instance.
Afaik, hash partition doesn't have real world expected use cases just yet. List or range is probably what you want to use.
On Mon, 2020-05-11 at 04:33 +0000, Dennis Ryan wrote:
> Regarding hash partitioning, what is the function/algorithm that is used to compute the hash for the partition key?
Ineed to write a query like
>
> “SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records
> FROM existing_table
> GROUP BY 1”
To find the function that PostgreSQL uses to hash a data type,
try something like
SELECT amp.amproc,
amp.amproclefttype::regtype
FROM pg_amproc AS amp
JOIN pg_opfamily AS opf ON amp.amprocfamily = opf.oid
JOIN pg_am ON opf.opfmethod = pg_am.oid
WHERE pg_am.amname = 'hash'
AND amp.amprocnum = 1;
Yours,
Laurenz Albe
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com
On 2020-May-11, Michael Lewis wrote: > Afaik, hash partition doesn't have real world expected use cases just yet. I don't think I agree with this assertion. While I understand that there might be things still to do in this area (as everywhere else), it should certainly have its uses already. If you have a wish-list for hash partitioning to become usable for you, would you please list the features you wish it'd have? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, May 11, 2020 at 3:13 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
On 2020-May-11, Michael Lewis wrote:
> Afaik, hash partition doesn't have real world expected use cases just yet.
I don't think I agree with this assertion.
I didn't mean to be critical at all, or even make a statement of fact. Just sharing my impression. I typically view partitioning from the perspective of multi-tenancy and with the restrictions on primary keys & partition keys, I can't typically use partitioning except for audit logging tables and then range partitions make the most sense there because of doing backups and dropping the oldest data. Perhaps it is just that hash has never been the right tool for my use cases. I'd love to know some real life examples of when hash partitioning was the best option.