On using UUIDs
I want to talk about using UUIDs (as primary keys) and why it may be a pretty decent default.
Let's first quickly go over all the situations for which we would absolutely need UUIDs:
- You need primary keys to be generated in a decentralised manner and consolidate them later
- You need 128 bits and you need a standard format supported in a lot of places
- You need to support a system already using UUIDs
Personally, I think those about the only valid reasons you'd actually need UUIDs as primary keys for your database table(s) and there are no other alternatives. Ordinary sequential integers would simply conflict at some point in time. Having some kind of system to determine which nodes may generate what kind of primary keys quickly runs into the risk of becoming overly complicated, hard to change and perhaps still not guaranteeing conflict-free operation. Another possibility is that you want to allow your customers to Bring-Your-Own-Primary-Key (BYOPK) and if they properly generate an actual UUID every time, it should work quite well. Using random 64-bit integers may actually be sufficient as well, but if you really want to avoid dealing with conflicts, 128-bits are your best bet.
Let's now quickly go over some common reasons found on the internet on why you would choose UUIDs:
- You need a lot of identifiers. Yes, but 64-bit integers already have plenty of range. If you generate a billion of them every second, you have almost 300 year before you run out of them.
- You want to obfuscate records, or make them hard to guess. If you expect to have less than billions of records, random 64-bit integers have you covered here as well. For these cases, you don't really need UUIDs, but they are definitely very convenient choice. Probably more convenient than using (random) 64-bit integers.
Why UUIDs make sense: they are a well known and supported standard. The standard formatting in hexadecimal with dashes makes them instantly recognisable. Pretty much every common database and programming language support them out-of-the-box, allowing you to efficiently store them in 128 bits.
Some people would prefer something more “human readable” or "identifiable" such as the object identifiers that Stripe uses. These often prefix a 128-bit number encoded with some "human readable" encoding format such as base58, with some kind of human-readable identifier, so you can see that an identifier is for an invoice, shipment or payment. This is an interesting idea and definitely has these described benefits, but they come with some caveats. You need extra libraries to generate, parse and persist these identifiers. If you store them as strings, you waste a ton of space and make indexing less efficient. If you store them as UUID in your database, you need to convert the format every single time you go back or forth.
Another argument in favor of the object id is that you immediately know what kind of resource it is. While this is indeed true, you can also create a separate database table that associates every UUID with the type of resource. This makes looking one up slightly less efficient, but since UUIDs are universally unique, it is a perfectly acceptable alternative and in fact, we implemented this in our own application. Pasting a UUID in the search box will look up what kind of record it is and then attempt to look that record up with the appropriate access scope. In most cases you’ll have some more context anyway, such as a URL that includes the name of the resource besides the identifier itself.
Then there are identifier formats like ULID, which are basically the same kind of random 128 bit identifiers but with a different format. These are supposed to be shorter, easier to copy/paste, easier to write down, et cetera. While these are all good things, these identifiers are not generally supported across all the tools you use and require more libraries again to format and parse.
It is also true that truly random UUID v4 identifiers are “bad” for your database as they are not sortable and thus cause more work for the database. These days most programming languages and databases have support for version 7 UUIDs which include the timestamp of creation, making them sortable. This means it could leak information if you don’t want to share, for example, at what time someone became a customer, so you might still want to use version 4 UUIDs when referring to sensitive data where the time of creation should not be visible. Every sortable identifier has this issue in one way or another, so that may not be a good reason to pick something else over UUID version 7.
I’ve converted existing tables with sequential integers to UUIDs and it’s quite a pain in the ass. You’ll probably have to support the old primary keys for some time as well, unless you don’t care about breaking older links.
The ability to generate the ID in your application and not worry about running into conflicts when persisting to the database opens up some interesting possibilities as well.
Finally, a useful benefit of using UUIDs is that they are (supposed to be) unique across your entire database, adding an extra guard against accidentally fetching the wrong record when copy/pasting an identifier somewhere. A UUID assigned to a widget will not exist in your users table and vice versa.
In short, I would recommend using UUID version 7 as the default primary key if you’re building something new because it is the most convenient option.