Comment by pphysch
16 hours ago
Is the Hash Index method strictly superior to creating a unique "hash" column and precomputing the hash in the application or query?
16 hours ago
Is the Hash Index method strictly superior to creating a unique "hash" column and precomputing the hash in the application or query?
Sibling comment mentions less space but also your alternative doesn’t (naively) handle supporting values that differ but hash to the same value.
Of course the hash index also outperforms a unique (btree) index on top of separately calculating the hash, in addition to the storage overhead, row bloat, lack of guarantees regarding the hash unless you expose it to Postgres as a user-defined function AND add a check constraint.
It'll use less storage space