Unique hash indexes in Postgres

Published: April 18, 2024

I recently discovered via the excellent PostgreSQL 14 Internals book that it’s possible to have a hash index in Postgres with a unique constraint.

The trick is to use an exclude constraint like this:

constraint my_index exclude using hash(my_column with =)

There are currently a few caveats when using this trick:

The first one is that for on conflict statements the constraint must be explicitly named. This is not a big deal.

The second one is that exclude constraints cannot currently be added as not valid, meaning that you cannot add the constraint to an existing table without acquiring an exclusive lock and therefore it’s a migration that cannot run with zero downtime.

For an example of how this approach correctly handles potential hash collisions see this dbfiddle.