Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-17108

prop_value field in PostgreSQL schema should be limited by size

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 6.5.0
    • Component/s: Database
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                shayb Shay Bagants
                Reporter:
                yehudah Yehuda Hadad
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: