Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-4803

VCS with PostgreSQL should use UUID type for doc id field

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.3.1, 5.5
    • Fix Version/s: 5.7.1
    • Component/s: Core VCS
    • Environment:
      PostgreSQL
    • Impact type:
      Content model Change, Configuration format change
    • Upgrade notes:
      Hide

      To activate, in the repository config, add:

      <idType>uuid</idType>

      Show
      To activate, in the repository config, add: <idType>uuid</idType>

      Description

      Using uuid instead of varchar(36), the size of the uuid field is 40% smaller on disk, the size of the btree index is 50% smaller.

      For instance a column of varchar(36) with a btree index on 1m lines takes 134 MB:

      • 72 MB of data
      • 62MB of index

      While the same value using uuid takes 80 MB :

      • 46 MB of data
      • 33 MB of index

      This reduce the memory footprint of the database. Furthermore having numeric value and smaller index should be more efficient.

      This should be used as the default for new instance, the code should keep backward compatibility with doc id stored as varchar(36).

      Also update of varchar index tend to bloat while btree index on number or uuid stay compact.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 2 days, 2 hours
                  2d 2h
                  Remaining:
                  Remaining Estimate - 2 days, 2 hours
                  2d 2h
                  Logged:
                  Time Spent - Not Specified
                  Not Specified