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

461–470 of 568 posts

Re: You'll regret using natural keys

#461

Post by sgarland »

Cyph0n wrote: post #33
parpfish wrote: post #24

There another reason not mentioned — if your key is something like a UUID, it’s very easy to define the logic for joining and filtering based on that key. If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

You could also have CHECK constraints on those columns to ensure that nothing is written incorrectly, and/or a pre-write trigger that casts them to lowercase.

Or you could make the email column case-insensitive, since it’s generally accepted to be CI anyway.

Re: You'll regret using natural keys

#462

Post by cogman10 »

rav wrote: post #450
reissbaker wrote: post #368

Earlier quoted context omitted.

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.

This is insecure. Assuming the user can get a few key examples (which, we assume they would be able to if the german tank problem is a problem) then the secret can easily be revealed. [1]

[1] https://dev.to/wrongbyte/cryptography-basics-breaking-repeat...

Re: You'll regret using natural keys

#463

Post by Piskvorrr »

naasking wrote: post #325
aidos wrote: post #103

Earlier quoted context omitted.

Yeesh. I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me.

> I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.

Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.

Re: You'll regret using natural keys

#465

Post by hosh »

There's another gotcha I found out at my workplace -- for Postgres, tables without a primary key has trouble when you want to use logical replication. The times you might want to use logical replication includes using AWS's database migration service to shrink allocated storage.

Re: You'll regret using natural keys

#466

Post by WorldMaker »

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…

A different approach to solve both 1 and 2 is timestamp-oriented IDs. You can get useful cache locality/less "index bloat"/fast initial inserts if your keys can be easily ordered in time. Sorted by timestamp means very similar behavior to B-Tree appends of a monotonic integer, even sometimes in the worst cases where "same moment" IDs aren't monotonic and rely more on random entropy.

I got some great DB cache/index performance from ULIDs with a bit of work to order the ULID timestamp bits in the way the DB's 128-bit column "uuid" sort best supported.

Now that UUIDv7 is standardized we should hopefully see good out-of-the-box collation for UUIDv7 in databases sooner rather than later.

Re: You'll regret using natural keys

#467

Post by shkkmo »

parpfish wrote: post #24

There another reason not mentioned — if your key is something like a UUID, it’s very easy to define the logic for joining and filtering based on that key. If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE

Side note about using email addresses.

It is pretty common to assume that email addresses aren't case sensitive since many email providers treat them that way. Email addresses absolutely are case sensitive so you need to preserve case when storing them and be careful about case when using them.

This makes email addresses particularly unsuited to being used as a natural key. Most people treat them as case insensitive and you need to work around that, but you can't safely just treat them as case insensitive yourself.

Re: You'll regret using natural keys

#468

Post by WorldMaker »

smaudet wrote: post #241
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 wonder if Unicode could be used to alter the characters such that these mistakes would be less possible, e.g. using ⓪.

If you are trying for URL safe, Unicode is problematic because of Punycode conversions and differing browser behavior with Unicode URLs. (Some browsers always show Unicode as Unicode in URLs. Some browsers always show Unicode as Punycode in URLs. Some browsers switch between the two based on a huge number of variables such as gTLD, user preference, phase of the moon, etc.)

Re: You'll regret using natural keys

#469

Post by shkkmo »

the_gipsy wrote: post #87

In Spain the ID numbers are assigned at birth, carry no information, and cannot be changed. Each police comissary that registers birth gets a unique set of IDs to assign (per year or whatever). However. Mistakes still happen. A colleague had the same ID as someone else. He said he tried to change it, but it was impossible because it was such an impossible concept to any public servant involved. In the end he gave up…

Even if this is true now (which it isn't entirely), that is no guarantee that it will be true in the future. Any time you use an external value as a key, you run the risk of a policy change. An internally generated artificial key is the only safe way to ensure immutability and lack of semantic content.

Re: You'll regret using natural keys

#470

Post by bigstrat2003 »

brazzy wrote: post #272
ThePowerOfFuet wrote: post #216

Earlier quoted context omitted.

I hear Scunthorpe is lovely this time of year.

I hear someone got buttbuttinated there recently.

At first I thought that this was the result of my cloud-to-butt extension, and I was trying to figure out what "cloudcloudinated" meant.
Post reply on HN