Nuxeo and Postgresql: upgrading to native UUID type

[otheus_uibk]

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?

[Florent_Guillau]

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.

[otheus_uibk]

If postgresql uses the UUID type, will they look the same as they are now, just not stringified? Will nuxeo create these strings to look the same way they are now, so that tables like 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?

[Florent_Guillau]

Yes for Nuxeo the document id will still be a String. 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?