Proposal: UUID as primary key

[ajolipa]

It feels as though we are currently sacrificing some amount of efficiency in how we query data by using sequential numerical primary IDs in the database tables (and using these for all of the foreign keys and relationship tables), while both our consuming applications and data ingestion scripts use uuid as the unique key for records.

Possible advantages:

• Model and relationship IDs could be created for the initial schema file and used in the data import files, and could be shared between staging and production instances of a project, making the process of cloning data from one instance to another simpler;
• Avoid ID collision between records in different models/projects (not sure that this is really a problem in need of a solution, but would make e.g. querying the relationships table much simpler);
• Make it possible to import the relationships.csv file in a bulk import without needing to import the related models as well, which would greatly simplify the ingestion workflow in cases that data is being ingested in batches from a variety of sources (possibly a rare use case but has come up several times recently);
• Make querying by UUID more efficient, possibly speeding up the public API (?) -- not sure how much of a difference it would make, but having the UUID columns be indexed as keys ought to improve lookup times to some extent?

Possible issues:

• Loses some efficiency of inserting new records into the database (probably not a very noticeable difference and might not matter now that bulk import is a background job?);
• Significant effort to implement for possibly insufficient benefit?