Nuxeo and Postgresql: upgrading to native UUID type
Currently, our system & database uses
varchar for the UUIDs. I suspect performance can be greatly improved by changing this type to the PG's
uuid type. Is it possible to convert the existing table/schema to the
uuid type? Would the
ancestors table need to be re-indexed somehow? How can I get a list of tables that I would need to convert?
Alternatively: is there a way to export and re-import or re-build the meta data, which postgresql holds?
Practically all tables hold an
id column which is the uuid. Also
proxies.targetid is a foreign key to
hierarchy.id therefore a uuid too. This does not applied to
nxp_logs* tables or the tables corresponding to directories.
For migration I think it's best to do a full PostgreSQL dump, change the column types in the dump then reload the dump in an empty database. You'll have to drop the
aclr_modified, tables and let them be re-created.
aceinfowill work properly? Are you saying nuxeo will detect and re-create ancestors, and these other two tables if they don't exist? How will it get the ACL info?
aceinfois a directory and will use a String too. Yes ancestors and the ACL-related tables will be re-created. Why don't you try on a small test database to see what issues you have?