Do Not Recommend: User Provided Primary Keys
cendyne.dev
cendyne.dev
UUIDs are binary data, they should be stored as such.
A lot of technical problems are born of laziness.
> If you use an embedded type like PostgreSQL UUID everywhere, then this is not an issue for you.
So only applies where your language/system doesn’t support strong types.
That said, I understand how they got there. Although using real UUIDs in the backend is obviously the right path, it's amazing how rare their use is in industry. Developers either (1) don't know about the UUID type, (2) don't understand the advantages of such and therefore use a string instead because it's more familiar, or (3) in hubris, cast off the use of UUID because they know better and are doing their own thing.
We're using real UUIDs where I work now, but after a full ten years of industry experience, it's the first job where we're doing it right, despite previous jobs being at top name Silicon Valley companies who you'd think would know what they're doing.
We are just (finally) retiring a product with a large less-organised-than-we-care-to-about codebase that has been around in various states longer than our younger staff have been alive, that stored UUIDs wrong due to early misunderstanding by a long-gone expert. Several times during its life I made the case for converting it's use of UUIDS in strings to proper UUID types, even demonstrating significant performance improvements under load in a PoC, and storage savings. Everyone agreed it should be done that way, but there was considerably more work involved than just switching types (changing procs or type conversion could kill good query plans, dealing with the fact that at some point in history a smart person had used other strings as special values in one or two places, finding ad-hoc query preparation everywhere and making sure that declared the right type, ..., and of course the dreaded full regression test) so it never got done.
Especially in the last decade and a bit, because it would be retired in a year or two (ahem) so the time & effort would be better put to use elsewhere.
Couldn't actually find something similar for MySQL apart from using BINARY as the datatype, historical sources also refer to that though are (hopefully?) out of date: https://dev.mysql.com/blog-archive/storing-uuid-values-in-my...
An observation: seems like PostgreSQL is HN's darling (and maybe the industry's as a whole), i wonder why MariaDB doesn't get similar amounts of attention, apart from maybe not being as actively developed? Or maybe its relative simplicity or history works against it here?
I can obviously only speak for myself. But (in my opinion) Postgres simply offers the better overall package. I don't think there is a single "killer feature" that makes it better, but the sum of numerous things (JSON support, indexing, parallel query) that make it more efficient (and easier) to work with.
Note that the UUID type in MariaDB is not yet part of an officially released version. 10.7 is still in preview/alpha
[1] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...
[2] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...
So, if we think about it as using a int-128 or a string[~25], one of them takes more space than the other.
So, except for taking up more space, the question then becomes, are larger keys/indexes less efficient, I would say yes.
Other people can probably explain this better than me, but at least for me, it seems intuitive that going through and comparing entries in a list of small things is easier than doing it on a list of large things.
* Is there a SQL database with support for 128 bit integers?
Since you asked for "SQL database" and not really "OLTP database": ClickHouse natively supports UUID and 128-bit numeric datatypes. I think by now it also supports 256-bit.
[1]: https://clickhouse.com/docs/en/sql-reference/data-types/int-...
I had made their order number the primary key for their integration tables, as I was much more inexperienced at the time and had read so much about using natural keys.
Learned my lesson since, and this project just confirmed it again: natural keys are rarely a good idea.
Wasn't the worst issue ever to convert given that an ALTER TABLE fixes it, but had to drop all the FK's first, just quite tedious going over the code checking where it made assumptions about it being an integer.
But to keep it consistent I pretty much always end up using a surrogate key anyways. (I think I used natural keys like 2 times in a few years)
Unrelated: The blogs mascot is cute.
So, I don't really see a need to generate a natural key.
I'm currently rebuilding a system where a user-provided 'name' property is used as a primary key (no foreign keys though). I can't change its mechanics much - the name will be used as an ID in a different application as well - but I can add an artificial ID for internal use. Who knows, maybe long-term I can support changing the name property.
Therefore, by all means feel free to introduce and save any natural keys that the application requires. But do not rely on them for foreign keys.
Not sure if this is ironic, but a couple of subtleties about uniqueness aren't always conveyed in school. (Not very subtle subtleties given a few minutes' thought, but still...)
It's not enough to have single-point-in-time uniqueness, i.e all entities of the type under discussion have unique values of the candiate key now, which is what a uniqueness constraint guarantees.
It is also necessary that the value of the candidate key for each entity also does not change for the expected life of the database (or 250 years, whichever is longer--yes, the life of the database, not merely of the entity concerned), so no using people's names, for instance; and as the population of entities changes, values that disappear cannot later reappear (like SSNs of dead people being re-used for a new person).
If you don't control key allocation, it's only rarely possible to have ironclad, law-of-physics guarantees about these aspects of keyness.
Is that really a thing? I have used UUIDs as primary keys many times in my life, and never had a need to do such a thing. I am struggling to even think of a valid use case.
yeah, it seems like the premise is flawed. I don't know that there's actually a problem here.
Apparently it's a deliberate decision because they don't really want to be a "database" just something better then "a file".
...or maybe if you're storing data from a remote API that uses UUID's as primary keys, and you want to make sure you catch it if they change things. Though in that case I would likely have my own primary key and store the remote primary key in a separate column
UUIDs are generally assumed not to collide, since that's extremely unlikely. But if you allow an user to generate them, then they can try various shenanigans such as generating duplicated IDs, generating IDs that might have some sort of special meaning (eg, all zeroes), generating non-random IDs that could degrade performance, or storing some sort of data inside the ID.
In general, the other party generating your identifiers takes all the control out of your hands, so you have to start safeguarding against malicious/antagonistic/non-compliant inputs. And lose out on optimizing opportunities (e.g., opting to use a generation method that offers partial ordering)
Depending on any 3rd party keys to build your relations upon is a huge mistake in my experience. Doesn't matter who gave them to you or if they are of high quality or not. If you relate by way of outside key information, all it would take to fuck your entire weekend is one of your customers saying they are moving to a new vendor who uses an integer key instead of a magic string to identify their customer records.
The root of the problem with regard to UUIDs, though, seems to be that MySQL doesn't actually provide a UUID type, so the system in question was just storing their hex-dash representation as a string.
There's several things going on in the OP example that deserve more discussion than they get.
It doesn't matter that they are UUIDs and you can in theory continue to make them after the client changes something. If any user sees them, they aren't fit for a primary key.
I do go further. Anything the user touches is unfit as a primary key. If they want some number to identify the records (in a spreadsheet, in a customer number somewhere on their work or in a public API), that number is unfit as a primary key, it doesn't matter if you are the one creating it.
It's not "keep them secret", it's "this data is for developers eyes only", it doesn't matter a lot where the developers are, only that they know the rules.
Just so we are clear, the customer pays our salaries.
Client-issued UUIDs seem fine in theory but then start to impose haunting dread when you consider the total space of all possible client devices, operating systems and browsers. At least some of those scenarios are going to have shitty rng implementation or use the wrong type of UUID. At least one of those problematic scenarios will have multiple concurrent users.
What do you reach for when your client-controlled supposedly un-collideable identities begin colliding? Why even take the chance?
To make it more fun, the data come from DOZENS of different ERPs sources, with massively divergent ideas about what is data (like: One of them store money in different STRINGS formats with different decimal separators... in the SAME column). Plus, normal shenigans like nulls inside chars, uncommon ideas about what is ascii, etc.
And I sync the data in near real time, and the app is used concurrently by many users... etc.
---
The major reason: A LOT easier to sync data. A LOT.
UUIDs is one of the biggest anti-patterns to use for this. It means NOTHING. It demand to correlate with the REAL thing.
It makes harder to locate stuff later.
And when sync, it looks like are 2 different records. Still need to correlate with the actual natural keys.
But, how about changes on the key? How about mistakes? The natural keys can and will change, and using UUIDs or Integers NOT CHANGE THAT FACT. So deal it upfront. Is not that hard.
The only real true major drawbacks is to alter the rest of the places where it could be. Hapily, DBs have something called triggers. It works fine.
--
What is far better than UUIds where natural keys can't fit is to compute a hash of the relevant rows and use that as ID. It also make wonders for sync logic.
--
The key here is that the relational model is made for modeling the facts of the "real world".
Embracing this make everything nicer: Is easier to correlate things ("this line of invoice already tell me the Invoice Sequence and the TaxId of the customer and the City code!") making easier to do reporting, to use the DB admins, etc.
I know it seems strange to have three effective columns that all do the same thing, but the reality is that we actually have three seperate concerns here anyway. One key to our user's data. One key to our (untrusted) API data. One key between the tables. The user's key can change (e.g., someone changes a SIN / SSN after fleeing domestic abuse), the external key can leak (so it's PII which should—and eventually must—be deleted), the internal key can be very difficult to delete or obfuscate due to DB constraints, backups, etc.
If you don't do this you'll be paying a lot more for privacy engineers to write your erasure code. Or you'll be limited to countries without strong privacy protections.
What benefits are you getting from the ad-hoc solution that you wouldn't with uuids? They were made for this.
Because now you depend on an UUID library and its attack surface.
In some environments it is crucial to reduce dependencies.
I can appreciate reducing dependencies, but this is bordering on paranoia.
UUIDs use time and origin to make things unique something that is both an advantage and also a problem, say for example you want to hide origin and time?!
Also as danuker said, why add a dependency for no real benefit, Log4J should have proved to you that it's better to write minimalistic implementations yourself.
Finally there is a difference between reinventing the wheel and improving the wheel: http://move.rupy.se/file/wheel.jpg
I made a distributed database in 2000 lines of Java.
Some UUID formats do. Format 4 has all bits (except the format bits) drawn from (P)RNG.
> the annual risk of a given person being hit by a meteorite is estimated to be one chance in 17 billion, which means the probability is about 0.00000000006 (6 × 10−11), equivalent to the odds of creating a few tens of trillions of UUIDs in a year and having one duplicate. In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%.
So in a theoretical sense, no, but in a practical sense, yes. The same is true for any custom ID format like yours as well. 128 bits is enough to never hit a dup though, so you don't need to go crazy.
Your database should be what authoritatively guarantees uniqueness at the end of the day — generate UUIDs assuming no collisions (which will ~always be true), but store in a UNIQUE index so things'll fail in case of a duplicate or a bug that results in trying to store the same ID twice.
> The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive.
https://www.postgresql.org/docs/9.0/citext.html
TL;DR If users choose their usernames, and 'MyUserName' was registered and someone tried to register 'myusername', it would detect a collision and error, thus not causing any problems (the second user would simply have to choose another username).
Use the native uuid type, or as a fallback, store it in a 16 byte binary blob.