PostgreSQL – Conflict Resolution for Hash-Backed Unique Constraints

hashingindexpostgresqlpostgresql-9.6

So we've just run into

ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un"
  Hint: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

(foo is table, bar is column and un for "unique")

Consider a function index of an MD5 hash of the value

Alright, let's.
This is simple enough to do:

ALTER TABLE foo DROP CONSTRAINT foo__bar__un;
CREATE UNIQUE INDEX foo__bar__un ON foo(md5(bar));

So far, so good, this is the solution both proposed in the error hint and found online in numerous places.

What I haven't found yet, though, is how to deal with collisions.

In my case, I have a database with results for biochemical experiments. I can't just go to the scientists and say "can you please change your result a tiny bit so we can actually store it in our database? yes, I understand this may cost a couple of your customers' lives and you personally a potentially prohibitive amount of money, but I can't allow you to store it otherwise".

(Realistically, we'd just drop the constraint way before that, but you get my point.)

I do understand that collisions are almost negligibly rare, but I'd rather know I can deal with them if against all odds one should happen. And given that this isn't a niche issue only we are having, I'm sure I'm not the only one to have ever wondered about this.

So what is the way to deal with collisions, in this case? I'd rather not have to replace the unique index with a DIY trigger-based workaround, if possible.

Can I somehow shoehorn in an additional "equals" check to be executed for entries with the same hash, for example, and only throw an exception if they actually are?

Best Answer

I don't know why the results of a biochemical experiment would be enforced to be unique in the first place, but you can use an exclusion constraint that tests for equality. It will automatically resolve hash collisions by doing the character-by-character comparison.

alter table foo add constraint foo__bar__un exclude using hash (bar with =);

But you should upgrade to something newer than 9.6.

Related Solutions

MySQL Unique Constraint – Handling Large Columns

You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.

You may have to resort to triggers or stored procedures to check for the key well in advance.

You could also think about performing an SHA1 function call using the VARCHAR(3071) field. SHA1 will return a 40-character field. This hash may be just what you need to index.

Suppose you have this

CREATE TABLE mytable
(
    id int not null auto_increment,
    txt VARCHAR(3071),
    primary key (id)
) ENGINE=InnODB;

and you want to make a UNIQUE index on txt. Try the SHA1 approach

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;

Then, count them

SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;

If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:

ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

This could be more atomically as pointed out in the comments below:

RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;

Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT.

The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).

Give it a Try !!!

Index on foreign key makes query extremely slow

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)

Best Answer

Related Solutions

MySQL Unique Constraint – Handling Large Columns

Index on foreign key makes query extremely slow

Related Question