UUID v Integers

After some preliminary development, we’ve come to realise that we will need to use UUID as the primary key for all our database models. However, we were concerned that this would incur a performance penalty as UUID were huge random numbers.

So, we read up on some other experiences and concluded that it would not significantly mess things up for us and would actually be good as we build our database for future growth, if we are careful.

Some points to take away:

1. Sequential IDs
   Use a time-based UUID to make it incremental in order to exploit the clustered index capabilities of a database. Since the Boost::UUID library does not provide a time-based generator, one will need to be created.
2. Storage size
   A UUID is only 16-bytes but the Wt::Dbo ORM does not support mapping of such a type. So, the UUIDs will need to be mapped to a VARCHAR(36) type instead. This is not ideal. There is support for custom types but we will need to look into this.
3. Key constraints
   It is shown that how the field is constrained can have significant effect on the performance. So, we will need to create the database carefully and not rely on the ORM library to do the automatic creation for us.