What impact has uniqueness constraint jsonb on postgres db performance

Question

I'm designing a postgres database in which there is a table that has a jsonb type column. I would like this column to be unique. There is no need to have two objects with the exact same json configuration in the table. Down the line it would save me about 5 minutes of computation time per duplicate not saved in the db. I'm aware of the risk of json uniqueness when it comes to dicts (order of keys is not guaranteed), but I think a good json encoder can mitigate this.

My worry is about db performance. I want to make sure we're doing anything possible to make sure inserts will not be slowed down horribly with this uniqueness constraint on jsonb. How bad would a uniqueness constraint on jsonb be compared to a uniqueness on varchar or int? Are we talking milliseconds, seconds or minutes?

I've looked into the Hash Index which does sound like all I would ever need to go for optimal performance. But. Only B-tree type of Index can be unique, which is weird. Why?

Answer 1

I'll ignore the "why" question. Look at the pgsql-hackers archives; it is all open source.

You need a B-tree index for a unique constraint.

Putting the index directly on the JSON column is not an option, because a JSON value can easily be longer than the allowed maximum for a B-tree index entry.

You can create a unique index on a hash of the JSON column. Assuming the data type is jsonb (where you don't have to worry about the order of attributes), that could be

CREATE UNIQUE INDEX ON tab (jsonb_hash_extended(col, 0));

There is always a small risk of false positive hash collisions...