9

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:

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?

  • I wonder if that has something to do with the "abbreviated keys" feature introduced in 9.6 – a_horse_with_no_name Sep 8 '18 at 16:07
  • @a_horse_with_no_name: Note how I renamed the column val in your original test case to col. That's the first hint at a solution. The size of the column value is relevant, not the size of the value in RAM ... – Erwin Brandstetter Sep 8 '18 at 16:26
11
+50

Short answer: compression.

The data type text allows (lossless!) compression and storage out of line by default:

SELECT typstorage FROM pg_type WHERE typname = 'text';  -- 'x'

The manual about pg_type.typstorage:

p: Value must always be stored plain.
e: Value can be stored in a “secondary” relation (if relation has one, see pg_class.reltoastrelid).
m: Value can be stored compressed inline.
x: Value can be stored compressed inline or stored in “secondary” storage.

Note that m columns can also be moved out to secondary storage, but only as a last resort (e and x columns are moved first).

Test with pg_column_size() instead of length(). Be sure to test actual table columns (with compression applied) not just input values. See:

CREATE TABLE tbl (id int, col text);
INSERT INTO tbl(id, col) VALUES 
   (1, rpad(md5('non_random'::text),     100, md5('non_random'::text)))
 , (2, rpad(md5('non_random'::text),    1000, md5('non_random'::text)))
 , (3, rpad(md5('non_random'::text),   10000, md5('non_random'::text)))
 , (4, rpad(md5('non_random'::text),  100000, md5('non_random'::text)))
 , (5, rpad(md5('non_random'::text),  500000, md5('non_random'::text)))
 , (6, rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));
SELECT id, left(col, 10) || ' ...' AS col
     , length(col) AS char_length
     , pg_column_size(col) AS compressed
     , pg_column_size(col || '') AS uncompressed
FROM   tbl ORDER BY id;
id | col            | char_length | compressed | uncompressed
-: | :------------- | ----------: | ---------: | -----------:
 1 | 67ad0f29fa ... |         100 |        101 |          104
 2 | 67ad0f29fa ... |        1000 |       1004 |         1004
 3 | 67ad0f29fa ... |       10000 |        160 |        10004
 4 | 67ad0f29fa ... |      100000 |       1191 |       100004
 5 | 67ad0f29fa ... |      500000 |       5765 |       500004
 6 | 67ad0f29fa ... |     1000000 |      11487 |      1000004
SELECT pg_column_size(rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));
| pg_column_size |
| -------------: |
|        1000004 |

db<>fiddle here

Note how the value is forced to be unpacked from its storage format with the noop expression: pg_column_size(col || '').

The 5th row would be too big to fit the index tuple (even with compression) and trigger the error message in the title.

The 6th row would be to big to fit even the index page and trigger the related error message:

ERROR: index row requires 11504 bytes, maximum size is 8191

The test values generated with rpad() have repeating patterns, which allow for massive compression. Even very long strings still easily fit the max. size after compression this way.

Related:

Long answer

I ran more extensive tests, tampering with storage internals to verify my understanding. Only for testing purposes!

dbfiddle does not allow write access to system catalogs. But the queries are there to try "at home".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.