Re: Fast, stable, portable hash function producing 4-byte or 8-bytevalues?

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.