On 12/10/19 3:11 PM, Erwin Brandstetter wrote:
I am looking for stable hash functions producing 8-byte or 4-byte hashes
from long text values in Postgres 10 or later.
There is md5(), the result of which can be cast to uuid. This reliably
produces practically unique, stable 16-byte values. I have usecases where
an 8-byte or even 4-byte hash would be good enough to make collisions
reasonably unlikely. (I can recheck on the full string) - and expression
indexes substantially smaller. I could truncate md5 and cast back and
forth, but that seems like a lot of wasted computation. Are there
suggestions for text hash functions that are
- fast
- keep collisions to a minimum
- stable across major Postgres versions (so expression indexes don't break)
- croptographic aspect is not needed (acceptable, but no benefit)
What about a CRC32 function? It's fast, and an SSE4 instruction has been in
Intel CPUs for about 10 years.
There is an old post from 2012 by Tom Lane suggesting that hashtext() and
friends are not for users:
https://www.postgresql.org/message-id/24463.1329854466%40sss.pgh.pa.us
Postgres 11 added hashtextextended() and friends to generate bigint
hashes. In a more recent post from 3 months ago, Tom suggests to use it in
user-land - if portability is not needed:
https://www.postgresql.org/message-id/9434.1568839177%40sss.pgh.pa.us
Is pghashlib by Marko Kreen my best option?
https://github.com/markokr/pghashlib
Or the "version-independent hash functions for PostgreSQL" from Peter
Eisentraut:
https://github.com/petere/pgvihash
Neither received updates for a couple of years. Unmaintained? Or obsolete?
And neither is available on most hosted services like RDS or Heroku (which
would be required in come cases).
So what are my best options?
Regards
Erwin
--
Angular momentum makes the world go 'round.
