the field "prop_value" in the table "node_props" is configured as VARCHAR(4000), since it is possible that the ascii characters in the value will exceed the 4000 chars limit, we have created the index "node_props_prop_key_value_idx" i the following way:
CREATE INDEX CONCURRENTLY node_props_node_prop_value_idx ON node_props(node_id, prop_key, substr(prop_value, 1, 2400));
CREATE INDEX node_props_prop_key_value_idx ON node_props (prop_key, substr(prop_value, 1, 2400) varchar_pattern_ops);
we are using the substr function in order to make sure that we will not exceed the index max allowed size, however, using the substr way is not efficient and cause for long running DB queries.
when we exceed the max allowed size, the error we receive is:
org.postgresql.util.PSQLException: ERROR: index row requires 8216 bytes, maximum size is 8191
in server with a lot of nuget packages, where the entire package description is inserted to the 'prop_value' field, the performance of the DB is slow and package resolution can take more than 100 seconds.