[RTFACT-17108] prop_value field in PostgreSQL schema should be limited by size Created: 11/Jul/18  Updated: 22/Oct/18  Resolved: 22/Oct/18

Status: Resolved
Project: Artifactory Binary Repository
Component/s: Database
Affects Version/s: None
Fix Version/s: 6.5.0

Type: Improvement Priority: Normal
Reporter: Yehuda Hadad Assignee: Shay Bagants
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Dependency
depends on RTFACT-17337 Performance improvement: Create a mor... Resolved

 Description   

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.



 Comments   
Comment by Shay Bagants [ 22/Oct/18 ]

Clone of RTFACT-17337

Generated at Tue Aug 11 16:16:25 UTC 2020 using Jira 8.5.3#805003-sha1:b4933e02eaff29a49114274fe59e1f99d9d963d7.