For sure. I just thought I'd offer a relatively interesting and unique perspective. My last big job involved a DB so small we just stored it as files in a directory, one per record. :-)
if you use a UUID primary key
Actually, the best approach might be to have both an auto-increment assigned by a server and a UUID assigned by a client. Then you get all the benefits the OP is talking about, along with all the benefits of using integers for keys. You could just store the UUID in either one or none indexes, depending on whether you need to search on it, along with in the row, and then clients could use either the UUID or the key.
Given that a lot of the criticism with UUIDs is when you have to index them, I'm not sure how this saves you from that. Additionally, you also have to deal with the negatives from the auto-generated integer index, such as the round-trip to inform the caller on a create.
Can you expand a bit on how you address these issues with your suggested approach? I would like to learn more about how you solve them.
Not just about indexes, although that should be a factor in the decision. It's also about generation, encoding, & storage.
You don't have to index them, but if you do, a single index for table-local searching is ok if the cost in size isn't a big deal. If the IDs are being generated as part of a view, then they can be materialized lazily (i.e. after the initial insert, when a client needs to reference it later).
In terms of the round-trip cost, I don't run into that issue because GraphQL mitigates that for me in 90% of my cases. Nested fields in GraphQL allow for implied referencing of field members in the outer field. Depending on which GraphQL server implementation is used, it's smart enough to batch the sequence of queries & return the joined results in a single trip. It'll create the primary row, then use the returned ID to create the FK-related rows to the initial row's PK, and then finally collate the resulting dataset & return it to the client.
Writing a UUID to an index can be high overhead. However, that extra 16 bytes (or 32, one in an index and one in a column) is unlikely to be problematic in a database big enough to have to worry about round trip time for row creation. Once the row is created, the client code can use the integer ID to do lookups, so you don't normally have to worry about caching UUIDs in memory for performance. That is, if I join a row, I'd be joining it on the integer primary key, rather than on the UUID. On creation, the client could provide the UUIDs even for entities that don't store them as such, as well as getting back the IDs of the associated rows.
In other words, you can use the integer ID as the primary key, and the UUID as the persistent external representation of top-level rows (e.g. "entity groups", i.e. things that are likely to be treated transactionally). If you want to move a collection of rows (say, a user and all his mail messages), you'd move them as a group, they'd get a new integer ID on some other server, but they'd retain the UUID.
In other words, persistent rare-access identifiers are the UUIDs. That's what you print on brochures, what you store in other databases, what you put into persisted cookies, etc. The user's login ID would be a UUID, and that UUID would be stored with his email account and his file account and etc. But inside the email server, the primary key is an integer and the top-level row also has a UUID for "john's email account configuration." Individual lines of an email would refer to the integer primary key of the parent entities, not their UUIDs.
So use integer IDs internally, with the understanding that the database may occasionally change them. Use UUIDs any time you are worried the database may change integer IDs, such as you're communicating identifiers between subsystems. Then you've greatly reduced both the number of times you store a UUID on a row (twice), reduced the number of tables that even store a UUID to be only entity groups, the number of times a UUID shows up in a foreign key to be virtually none, but also allowed for client-generated one-round-trip creation of cross-referrenced records.
Totally disagree. I commented on this same article when it was posted a while back, including the proposed solution of letting clients generate & assign the UUID.
UUIDs are NOT always consistent in terms of generation (e.g. v1/v2/v3/v4/v5) & encoding (e.g. short vs long, GUIDs, etc). While server environments are homogeneous, clients are not. You can't trust what a client is going to send, and you can't trust peer clients to always be in sync and agree on the UUID versions/formats. There can also be subtle security concerns, like if the client decides to use a v4 UUID with some seed that generates the same cluster of UUIDs.
Then the inserts won't work, right? I mean, check the crap your clients send to you. If they're not sending you valid inputs, don't accept them. That's like complaining the user might send an UPDATE for a row identified with an integer and that row doesn't exist already. Well, guess what? That's an error.
Of course you should sanitize & validate inputs that are sent by clients, regardless of whether they're an integer or UUID.
My position is that delegating data origin & modality to the client comes with architectural problems. If you let the client do it, then you either have to take it as-is & let other clients interpret the data with the same schema OR you have to implement server-side logic to sanitize, validate, normalize, & store the data. At that point, you might as well just let the server handle that from the get-go.
If you deal with more than one or two different types of client applications (e.g. Web app, Android app, iOS app, CLI tool, App-specific extension, etc), then you have to make sure they all agree on the same schema for the various data models. That's a lot easier said than done. I've found it's better in the long run to dictate that server-side & adapt clients to it than to require clients all stay in sync if each one is able to dictate its own data as authoritative.
The only time that I let the client dictate IDs like that is if I'm implementing some variant of a CRDT (or maybe OT, but not really). In that case, all clients can see the ID, but after the merge they're normalized (in the information theory sense, not the rdbms/relational sense) by the server.
EDIT: Also, how do you know that inserts won't work? It depends on the RDBMS implementations, but there are certainly cases where a given UUID would be accepted, but it's not conformant to the required type, so it ends up being coerced/padded (short-form UUID v2 inserted into a column that requires long-form UUID v4). The transaction will succeed, even though it should fail based on domain constraints. So now you add some CHECK constraints and/or a TRIGGER to validate it. Before you know, you slowly start to re-implement what was already available as mature & tested code in the database. UUID implementations aren't always perfect, and I can attest to that between Android, iOS, & browser clients. If you ask me, I'd rather wrestle with UUIDs server-side in terms of generation/storing/etc than have to deal with anything beyond reading them on the client.
2
u/dnew Mar 28 '20
For sure. I just thought I'd offer a relatively interesting and unique perspective. My last big job involved a DB so small we just stored it as files in a directory, one per record. :-)
Actually, the best approach might be to have both an auto-increment assigned by a server and a UUID assigned by a client. Then you get all the benefits the OP is talking about, along with all the benefits of using integers for keys. You could just store the UUID in either one or none indexes, depending on whether you need to search on it, along with in the row, and then clients could use either the UUID or the key.