PostgreSQL OK to use HASH exclude constraint for uniqueness?

Question

Since hashes are smaller than lengthy text, it seems to me that they could be preferred to b-trees for ensuring column uniqueness.

For the sole purpose of ensuring uniqueness, is there any reason the following isn't OK in PG 10?

CREATE TABLE test (
  path ltree,
  EXCLUDE USING HASH ((path::text) WITH =)
);

I assume hash collisions are internally dealt with. Would be useless otherwise.

I will use a GiST index for query enhancement.

Answer 1

I think quoting the manual on this sums it up:

Although it's allowed, there is little point in using B-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn't do better. So in practice the access method will always be GiST or SP-GiST.

All the more since you want to create a GiST index anyway. The exclusion constraint with USING GIST will create a matching GiST index as implementation detail automatically. No point in maintaining another, inefficient hash index not even being used in queries.

For simple uniqueness (WITH =) a plain UNIQUE btree index is more efficient. If your keys are very long, consider a unique index on a hash expression (using any immutable function) to reduce size. Like:

CREATE UNIQUE INDEX test_path_hash_uni_idx ON test (my_hash_func(path));

Related:

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

md5() would be a simple option as hash function.

• Convert hex in text representation to decimal number
• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Before Postgres 10 the use of hash indexes was discouraged. But with the latest updates, this has improved. Robert Haas (core developer) sums it up in a blog entry:

• PostgreSQL's Hash Indexes Are Now Cool

CREATE INDEX test_path_hash_idx ON test USING HASH (path);

Alas (missed that in my draft), the access method "hash" does not support unique indexes, yet. So I would still go with the unique index on a hash expression above. (But no hash function that reduces information can fully guarantee uniqueness of the key - which may or may not be an issue.)