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?
 |
otheus uibk
Member (184) 2 2 2 |
11/02/2016 ( History) |
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 ancestors, hierarchy_read_acl and aclr_modified, tables and let them be re-created.
 |
Florent Guillaume
Member (36K) 6 8 8 |
11/03/2016 ( History) |
aceinfo will 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?aceinfo is 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?