Hash partitioning, what function is used to compute the hash?

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? 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”

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.