Do Not Recommend: User Provided Primary Keys

https://cendyne.dev/posts/2022-02-18-user-provided-primary-keys.html

by tempodox • 4 years ago

81 72 4 years ago

mbroshi• 4 years ago

Did you use a regex to verify the data looks like a uuid before putting it into the database?

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.

solumos• 4 years ago

Did you use a regex to verify the data looks like a uuid before putting it into the database? Is your database (like sqlite) using a case sensitive collation for the column? Then you have a problem.

yeah, it seems like the premise is flawed. I don't know that there's actually a problem here.

hans_castorp4• 4 years ago

Might be related to the use of SQLite which doesn't really support data types to begin with and allows to store anything in a column regardless of the declared data type (e.g. you can store 'fourty two' in a column declared as integer).

Apparently it's a deliberate decision because they don't really want to be a "database" just something better then "a file".

dec0dedab0de• 4 years ago

The only thing I can think of is if the user can export data from one system and import it to another, and for some reason there was a requirement to keep the UUIDs the same.

...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

hans_castorp4• 4 years ago

Well, obviously UUDs should not be stored as a string but using a proper "uuid" data type (which also makes storing them much more efficient). Then the database will automatically reject the value with an error.

lysecret• 4 years ago

ah interesting, didnt even think about storing them this way makes them more efficient. Do you have any ressource on that?:)

silvestrov• 4 years ago

https://www.postgresql.org/docs/14/datatype-uuid.html

KronisLV• 4 years ago

In case anyone needs the equivalent for MariaDB: https://mariadb.com/kb/en/uuid-data-type/

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?

BongoMcCat• 4 years ago

uuid = 128-bit int for a database. the "abc-1234-abc...." is just a representation of that int.

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.

ciupicri3• 4 years ago

Why wouldn't a dedicated data type be more efficient than an improvisation with CHAR, a pair of two BIGINT/INT8, INT16* or BLOB?

* Is there a SQL database with support for 128 bit integers?

bullen4• 4 years ago

I use secure random base-58 16-digit keys as primary keys in my distributed database, as long as you keep collisions rare with enough entropy (58^16 = 16400152899115243850138976256) you don't need to worry too much, generating a new ID/key is easy/fast when you have all the keys locally to match against.

toomanydoubts3• 4 years ago

Why not uuids?

bullen4• 4 years ago

Why UUIDs?

samcheng• 4 years ago

UUIDs are pretty big when serialized to text; this makes use cases like QR codes and SMS unwieldy in my experience. YMMV

magicalhippo• 4 years ago

Learned that the hard way recently, when a client changed ERP system and suddenly order numbers went from being 6 digit integers to 20 chars.

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.

Akronymus3• 4 years ago

I personally almost always use surrogate keys, because of having been burned by natural ones once too many times as well. Altough, sometimes I end up having a unique constraint on a natural column as well, and then the temptation is there to make that the key.

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.

hashimotonomora2• 4 years ago

In my experience it’s best to work with a surrogate PK and generate a bijective customer-facing natural key.

Cthulhu_• 4 years ago

I'm glad to have gotten this confirmed again; in school you learn to use natural keys, because e.g. someone's social security number or e-mail address is unique, right?

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.

samus• 4 years ago

Natural keys are perfectly fine. You just have to realize that you don't have full control over their sematics. But you absolutely require that to be able to construct the foreign key links in your datbase schema.

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.

tuatoru• 4 years ago

in school you learn to use natural keys, because e.g. someone's social security number or e-mail address is unique, right?

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.

dcow5• 4 years ago

This is a really long winded way of describing a few things to be careful about when using UUIDs as primary keys (and mostly due to [ab]using strings and regular expressions to handle them). It _doesn't matter_ who generates them, all the points still stand. Mainly: make sure to canonicalize the things when comparing and use a real UUID type. The leap to broadly not recommending “user provided primary keys” feels hyperbolic.

dale_glass• 4 years ago

It does matter who generates them.

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.

chrsig• 4 years ago

For that matter, they may not even be trying to do so. Depending on generation method, it can be really easy to generate duplicate uuids from two different processes on the same machine that aren't synchronizing with each other.

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)

jfindley• 4 years ago

Yes, this reads like a user who is dead set against all typing in software and is then surprised when that works out badly. There are some real problems with UUIDs as primary keys but they fail to mention any of them (some examples: UUIDs are quite large, which make them an expensive option for large tables, v1 UUIDs don't work well for distributed databases as the timestamps cause hotspotting, etc etc).

fabian2k2• 4 years ago

I don't get this, as far as I understand there is no reason at all to use a string column for a UUID. Your primary keys are important for performance and extremely important for integrity. Making them slower and less robust by using a string column just doesn't make any sense, why would you ever do that?

UUIDs are binary data, they should be stored as such.

nhoughto• 4 years ago

Yeah it is essentially storing bytes in a encoded form.. it is never the most efficient way. uuid, hex, base64 whatever.. when it hits the db it can be decoded into bytes and stored more efficiently which is generally a good idea.

Aeolun• 4 years ago

I’m fairly certain I heard someone say that it was inconvenient for the devs, or something like that.

A lot of technical problems are born of laziness.

yardstick• 4 years ago

From the article:

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.

mercantile2• 4 years ago

Real UUIDs in the DB are definitely the right answer. I couldn't help but think the article has the right idea but reaches the wrong conclusion — it is possible to use user-specified IDs, but make sure you're taking them as UUID and storing them as such too.

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.

dspillett• 4 years ago

There is a fourth reason: momentum.

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.

toolslive• 4 years ago

Microsoft's SQL Server used to pad a blob that was stored in the database with an arbitrary amount of zeroes. This bit us when trying to verify the hash signature of that blob. I don't know if that's still the case.

nomilk• 4 years ago

I think postgreSQL's 'citext' datatype gets around this:

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).

dtech3• 4 years ago

Note that most of the examples are UUIDs, and postgres has a datatype for that that evades all these problems and is a lot more efficient too.

IiydAbITMvJkqKf• 4 years ago

UUIDs are not text. Most uuid parsers ignore hyphens and braces, so citext will not help you.

Use the native uuid type, or as a fallback, store it in a 16 byte binary blob.

bob10292• 4 years ago

I've developed a database normalization form wherein absolutely no business knowledge may ever serve as the identity of a thing or relation key between tables. All things are keyed and related either using a global sequential identifier (in synchronous systems) or Type4 uuids (in distributed/async systems).

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.

jrochkind12• 4 years ago

The example at the top of OP was of using UUIDs though. If they are supplied by a client in a distributed/async system, I guess they count as user-supplied.

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.

marcosdumay2• 4 years ago

Yes, they count.

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.

3pt14159• 4 years ago

The posted article does not go far enough. Due to regulations like GDPR, user ids are considered PII if they're ever transmitted to a device outside the control of the organization. So if you must follow European regulations (and other countries like Canada are catching up too!) then to make your life easier I recommend TWO id columns. One as a random uuid4 generated in server side software (makes sharding easier, and collision is effectively impossible) that can be used for API calls and one internal id in the DB that can be whatever you want.

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.

mamcx• 4 years ago

I go against the trend here: I start with natural with FoxPro, then I use surrogate keys for more than a decade (following dogma...) and revert to natural ones a few years ago.

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.

Do Not Recommend: User Provided Primary Keys

Related Stories