为什么不*错误:索引行大小xxxx超过索引"foo“的最大值2712？

Question

我们多次看到试图索引值超过最大值的列的尝试失败。Postgres 10为它提供了此类错误消息：

错误:索引行大小xxxx超过索引"foo_idx“提示的最大值2712 :不能索引大于缓冲区页1/3的值。考虑值的MD5散列的函数索引，或者使用全文索引。

示例：

• 字符变化索引开销&长度限制
• 索引最大行大小误差

等。

现在，具有更大的一个_马_使用_不是_名字证明了一个案例值(10000个字符)的text似乎仍然适用于Postgres9.6中的UNIQUE索引。引用他的测试用例：

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

没有错误，而且列值确实用10000个字符进行了测试。

最近有变化吗?这是怎么可能的？

Answer 1

简短答案:压缩.

数据类型text允许(无损！)默认情况下，压缩和存储超出行数：

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

手册pg_type.typstorage：

p:价值必须永远保存在普通的地方。e:值可以存储在“次要”关系中(如果关系有，请参阅pg_class.reltoastrelid)。m:值可以内联存储，压缩。x:值可以内联存储，也可以存储在“次要”存储中。x是烤面包类的常用选择.注意，m值也可以移出二级存储，但只能作为最后手段(首先移动e和x值)。

用pg_column_size()代替length()进行测试。确保测试实际的表列(使用压缩)，而不仅仅是输入值。请参见：

CREATE TABLE tbl (
  id int
, col text  -- with default pglz compression
);

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

压缩后，为元组标头添加8个字节，将其舍入到MAXALIGN的下一个倍数(通常为8个字节)，并为项标识符添加4个字节，以获得索引元组的总存储空间。

小提琴 - Postgres 15

小提琴 - Postgres 11

在Postgres 15中，最大索引元组大小减少到2704字节。此外，添加了新的压缩算法LZ4，对于高度重复的模式非常有效。见小提琴中的演示。

请注意，如何强制使用no-op表达式：pg_column_size(col || '')从其存储格式中解压该值。

第5行太大，无法适应索引元组(即使使用压缩)并触发标题中的错误消息。或者这个给Postgres 15：

错误:索引行大小5784超过索引"tbl_col_idx“的btree版本4最大2704

第6行将大到适合索引页，并触发相关的错误消息：

错误:索引行需要11504字节，最大大小为8191

使用rpad()生成的测试值具有重复模式，可以进行大规模压缩。即使是非常长的字符串仍然很容易适应最大。这样压缩后的尺寸。

相关信息：

• varchar(N)的开销是多少？

长答案

我运行了更广泛的测试，篡改存储内部以验证我的理解。只为测试目的！

• db<>fiddle 这里

dbfiddle不允许对系统目录进行写访问。但这些问题都是为了“在家里”。