Index row size 2976 exceeds btree version in postgresql

Question

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

Answer 1

The error suggests that the indexed column col1 has values that are too large to be indexed directly. Since your column has a length of 4000, and the maximum size for the indexed column is 2704 bytes, you're exceeding this limit.

Solutions:

Use a Hash Index:

Instead of indexing the column directly, you can create an index on a hashed version of the column. This significantly reduces the size of the indexed data.

CREATE INDEX idx1 ON table1 USING btree (md5(upper(col1)));

Use Text Pattern Ops:

If your queries mostly involve LIKE or ILIKE queries, consider using text pattern ops.

CREATE INDEX idx1 ON table1 USING btree (upper(col1) text_pattern_ops);

Truncate Data Before Indexing:

If the data permits, consider indexing only a portion of the column.

CREATE INDEX idx1 ON table1 USING btree (left(upper(col1), 255));