Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

441–450 of 568 posts

Re: You'll regret using natural keys

#441

Post by throwaway8486k »

mgkimsal wrote: post #295
yellowapple wrote: post #191

Earlier quoted context omitted.

> I have been in a startup where competitors used our sequential keys to scrape a list of customers. If your system allows customers to see each other (or worse: unauthenticated users to see customers) in this fashion in the first place then whether you're using a sequential integer v. a random UUID is the least of your problems.

The 'customers' could be free tier users - a social media type system where everyone has a public profile - intended for the public - would still be scrapable by /profile/1, profile/2, etc. Doesn't necessarily require 'authentication' for the exposing of sequential integers to have a bad outcome.

You're right. The urls were public to be shared (think of marketing material / ecommerce), so there was not a security incident.

But it did give our competitor free highly qualified leads that they could use to poach customers. This product was new to our customers, and we had spent a lot of time selling and convincing them that it was useful.

Re: You'll regret using natural keys

#442

Post by shkkmo »

spoiler wrote: post #136
maweki wrote: post #125

Earlier quoted context omitted.

> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…

Another example is where you use a service that provides you with a stable id. It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation. Data integrity/quality matters. Adding friction to prevent accidents also matters. I don't want something accidentally and trivially updating a field that's used to reference thing externally.…

> It makes little sense to add a surrogate id and a fk on that surrogate id. It violates data quality and integrity just for a hypothetical situation.

I would still almost always use an internal artificial key on top of the external id. If you want to enforce data integrity, you can still enforce uniqueness on the external id. "Stability" of an external identifier is almost always an assumption and one that I've seen fail enough times to want that internal id by default.

Re: You'll regret using natural keys

#443

Post by groestl »

cogman10 wrote: post #432
groestl wrote: post #412

Earlier quoted context omitted.

> A problem with this approach is it's not monotonical Whether or not that's bad fully depends on your platform and the number of writes you do. If you're using a massively distributed database like Datastore, Spanner etc, you want random keys as to avoid hot spots for writes. They produce contention.

Well, you'd still likely want psuedo-random keys. You'd rather not have the underlying database doing extra work to shuffle around records as the pages get jumbled. One solution to that is having more complex keys. For example, in one of our more contentious tables the index includes an account id (32bit int) and then the id of the entity being inserted. This causes inserts for a given account to still be contiguous…

Not disagreeing. Point is, you need to know your domain, your technology, your write patterns, your downstream systems, etc to decide if a specific key scheme works to your advantage or not. All the more reason not to use natural keys, as they lock you in in that regard.

Re: You'll regret using natural keys

#444

Post by Piskvorrr »

fhars wrote: post #312
smaudet wrote: post #241

Earlier quoted context omitted.

I wonder if Unicode could be used to alter the characters such that these mistakes would be less possible, e.g. using ⓪.

That is "AT", isn't it? (No, it isn't, if you look closely enough.)

It's obviously a �. Or perhaps a □ . Maybe an ¾, on odd Wednesdays?

(Unicode has its strengths. Making up replacement characters isn't one.)

Re: You'll regret using natural keys

#445

Post by hehdhdjehehegwv »

M95D wrote: post #130
hehdhdjehehegwv wrote: post #62

Earlier quoted context omitted.

And always kids, write code for one person, and one person only: your future self.

Yeah, fsck your co-workers and your replacement when you quit.

If you’re good to your future self you’re also good to them.

Re: You'll regret using natural keys

#446

Post by SoftTalker »

prmoustache wrote: post #175
otherme123 wrote: post #95

Earlier quoted context omitted.

In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key. Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it ( https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/ ), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number…

I've seen banks or insurers use DNI as user login.

The user login (hopefully) is not the internal primary key for the user. It should be unique at a given point in time, obviously, but certainly there are reasons it might need to change.

Re: You'll regret using natural keys

#447

Post by sgarland »

chuckadams wrote: post #398
sgarland wrote: post #364

Earlier quoted context omitted.

You’ll have to worry about performance tanking instead. If you’re using UUIDv7 then less so, but it’s still (at best) 16 bytes, which is double that of even a BIGINT. Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).

Most databases with a UUID type store them as 128-bit integers, typically the same as a BIGINT. It's not like 378562875682765 is the bit representation of a bigint either. And if you're not using uuidv7 or some other kind of cluster-friendly id, you'd best be using a hash index, and if you're doing neither, you probably don't care about their size or performance anyway. You don't pick UUIDs blindly, but on balance, t…

Postgres’ UUID type is 16 bytes. MySQL can store them as BINARY(16) once encoded. Conversely, a BIGINT for either is 8 bytes. Not sure about SQL Server or Oracle.

> You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.

IME, this is precisely the problem – devs choose them blindly, because then you don’t have to think about proper modeling, you can arbitrarily create a key in your app and be nearly guaranteed of its uniqueness, etc.

Re: You'll regret using natural keys

#448

Post by ss64 »

kstenerud wrote: post #316
biztos wrote: post #231

Earlier quoted context omitted.

I think that 0 and 1 are likely to cause problems when customers end up reading their "user ID" back to your employees in Customer Support Country over the phone. "It's one-three-oh-dee-ee-el. Yes, I'm sure, EL as in elephant."

I developed safe32 for this reason. https://github.com/kstenerud/safe-encoding/blob/master/safe3... Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3... So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those…

Another approach is using base31 with all vowels removed https://ss64.com/ps/syntax-base31.html

Re: You'll regret using natural keys

#449

Post by sgarland »

mrkeen wrote: post #395
sgarland wrote: post #332

Earlier quoted context omitted.

This is less of a debate, and more of an indicator of who has had to work with a DB at scale using UUIDv4 everywhere. Don’t blow up your B+trees.

I guess. Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.

A common method is to have a small app (which can quite easily be HA) that hands out sequential chunks to each shard, interleaving and/or gapping as necessary.

Re: You'll regret using natural keys

#450

Post by rav »

reissbaker wrote: post #368
cogman10 wrote: post #298

Earlier quoted context omitted.

A problem with this approach is it's not monotonical. Especially if you want to use this thing as an index in a database, you'll run into problems where you try doing middle insertions frequently, which causes fragmentation. The solution to this problem is making the higher order characters time sorted [1]. You don't need to go all out like uuid, you can have a pretty low resolution. It's more important that new inse…

IMO it's nice to have two keys: 1. An auto-incremented 64-bit (unless you have a good reason, in which case 32-bit is fine) primary key, used internally for foreign key relations. This will generally result in less index bloat on associated tables, and fast initial inserts. 2. A public-facing random string ID. Don't use this internally (other than in an index on the table it's defined for), since it's large. But this…

Instead of a random string ID, you can devise a fixed secret key and expose the auto-incremented ID xor the fixed secret key as the public-facing ID. This saves you the separate index but still avoids the German tank problem. But it gives you a new problem, namely a secret that's hard or impossible to rotate.
Post reply on HN