-1

We have seen some of the indexes are getting failing with below error.

ERROR: index row size 2976 exceeds byte version 4 maximum 2704 for index "idx1" 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.

Index : Create index idx1 on table1 using btree(upper(col1));

Table column length: 4000 pg_column_size for that column: 2931

Can you please suggest how to fix this issue

Ram
  • 147
  • 1
  • 2
  • 6

1 Answers1

0

Can you please suggest how to fix this issue

Sure:

Consider a function index of an MD5 hash of the value, or use full text indexing.

There is a limit to the size of an index based on the length of the column because of how much data can fit inside a data page. That limit is 2,704 bytes (roughly 1/3rd the size of an 8 KB page).

This is why you get the error:

ERROR: index row size 2976 exceeds byte version 4 maximum 2704 for index "idx1"

J.D.
  • 40,776
  • 12
  • 62
  • 141