I have a User model with a username field. The username is unique, not null, and generated externally by a third party. It seems like the perfect candidate for a primary key. Assigning a random integer to each user seems superfluous at best and painful at worst – I have a lot of other models (will be associations) that will store the username anyway as it is part of those data sets.
That said, there seems to be a lot of pressure in Rails to use an auto-incrementing integer as the primary key. Certainly there is a fair amount of tweaking to be done to change that convention.
Can any of you comment on what you would choose, and whether there are downsides to dispensing with the conventional integer primary key apart from the extra configuration it requires on the model?
I have always chosen the auto-increment ids mainly because that has been the default, but the answer as always is that it depends.
In a recent company we were on MySQL and we went through a database audit for performance performed by Percona, we were dinged for using UUIDs as our primary keys and it seems like this is because of the way MySQL performs writes and indexes. Other database systems may perform differently.
When discussing why we chose UUIDs it was stated that this was done for “security” purposes. To me this is just a bad use of UUIDs. Making them “un-guessable” does not make a secure system. Other than that there was no benefit for using UUIDs that we could explain. We used a gem to handle the configuration side of things. However we had issues with the natural sort order of the records. It was based on the UUIDs sort order, which caused headaches for our users.
There are other considerations for using UUIDs that I am aware of such as offline writes, sharding strategies and distributed system references. However I don’t have any experience with these needs that I could actually address. I have built distributed systems and have still used ints for referencing external entities without any problem.
