Sample Header Ad - 728x90

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

14 votes
1 answer
14537 views
We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: > ERROR: index row size xxxx exceeds maximum 2712 for index "foo_idx" > 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. Examples: - https://dba.stackexchange.com/questions/69161/character-varying-index-overhead-length-limit/69164 - https://dba.stackexchange.com/questions/25138/index-max-row-size-error Etc. Now, a_horse_with_no_name demonstrated a case with much larger text values (10000 characters) that still seems to work with a UNIQUE index in Postgres 9.6. Quoting his test case: create table tbl (col text); create unique index on tbl (col); insert into tbl values (rpad(md5(random()::text), 10000, md5(random()::text))); select length(val) from x; -- 10000 No error, and the column value indeed tested with a length of 10000 characters. Have there been recent changes or how is this possible?
Asked by Erwin Brandstetter (185747 rep)
Sep 8, 2018, 03:56 PM
Last activity: Jul 21, 2024, 02:05 AM