Search code examples
postgresqlpostgresql-11

What is the index tuple maximum size in postgres?

I'm quoting https://www.postgresql.org/docs/11/sql-createindex.html in regards to the INCLUDE clause:

It's wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches.

I'm wondering what is this index tuple max size for btree indexes which might fail inserts.

Use case: I am considering INCLUDEing a json column (<1000 chars) to a unique index.

Solution

  • It is a function of the block size, slightly less than 1/3 of it for btree indexes. For the default 8kB block size, the maximum is 2704 bytes:

    insert into foo select string_agg(x::text,'') from generate_series(1,940) f(x);
ERROR:  index row size 2728 exceeds btree version 4 maximum 2704 for index "foo_pkey"