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

531–540 of 568 posts

Re: You'll regret using natural keys

#531

Post by reissbaker »

senderista wrote: post #479
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…

I don't understand why you need to maintain two separate keys: instead of generating a random key, why not just encrypt the auto-increment key using a secret key? This is the approach used by e.g. cloud providers that use auto-increment keys internally but don't want them to be guessable.

Sure, that works too. The upside of just storing a random string is you don't ever need to deal with secret management, key rotation, ciphers getting broken, etc — the random string truly has nothing to do with the real underlying ID, so there's just less work involved in hiding it. The downside is some extra bytes and index space.

Re: You'll regret using natural keys

#532

Post by reissbaker »

cogman10 wrote: post #407
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…

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id. OK for smaller entities but might be a pain if there's a lot of those ids to wrangle. You can throw a secondary index on it, but that will still have some minor fragmentation issues. One benefit of a random id is if you are working with more complex data models…

Assuming you've set up an index on the public ID string, that should be a super fast query even on a table with billions of rows. Beyond billions of rows you're looking at something a lot more custom than a standard SQL DB, but assuming you shard the data, it should still be a very fast query regardless of dataset size.

Worst case you can cache, as others have mentioned, but TBH I think you don't need to for a query that simple and should save the cache space for something more complicated. Most SQL DBs are excellent for ready-heavy workloads; it's writes that tend to make them fall over.

Re: You'll regret using natural keys

#533

Post by reissbaker »

cogman10 wrote: post #439
caeril wrote: post #433

Earlier quoted context omitted.

That's why the Good Lord invented caching. In most applications, 90% of your workload will be over ids less than a week old, so your hit rate is likely to be pretty high for this sort of mapping.

First hit can be a beast. It's workload/entity determinant if caching is enough for this. Not great if you are spending 1 minute on the first lookup just to do the mapping.

Although I think it'll be fine even without a cache for most workloads (it's super easily indexed since the cardinality of random data is extremely high), I'm curious what cache setup you've got that it would take a minute to do a KV lookup — Memcached or Redis for example should be single digit milliseconds or better.

Re: You'll regret using natural keys

#534

Post by reissbaker »

WorldMaker wrote: post #466
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…

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 pe…

Yep, that works too as long as the timestamp data isn't sensitive.

Re: You'll regret using natural keys

#535

Post by Piskvorrr »

naasking wrote: post #476
Piskvorrr wrote: post #463

Earlier quoted context omitted.

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.

Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.

ISO 3166-2:XK - self-assigned, in use, but I wouldn't go as far as calling it "stable." I mean, it's been in an explicitly declared unstable state ("about to be assigned an official code") for over a decade.

Re: You'll regret using natural keys

#537

Post by mst »

shkkmo wrote: post #430
mst wrote: post #333

Earlier quoted context omitted.

Using a surrogate UUID for communicating with the outside world is often very useful. This is true for an internal PK that's an auto-inc id as well as for natural keys, though. Using a natural PK -inside- your own database can still be a lot more pleasant to work with, even if you don't let it escape.

> Using a natural PK -inside- your own database can still be a lot more pleasant to work with Until you need to do anything like described above. The advantage of artificial keys is that they have no semantic content. Anything with semantic content carries the risk that the role that semantic content plays in your system can change and cause problems. Having a non-semantic identifier protects you from that. This is n…

I said "can still be" because, yes, it's a trade-off.

I guess if you're trying to give a rule of thumb to juniors, "always have an auto-inc key in case you've misjudged whether the natural key is a good idea" is probably safest (though if you do tell them that, keep an eye out for them trying to add an auto-inc to things like a many-many join table which should've been PKed on the pair of FKs).

But every database design decision can potentially result in problems if the meaning or usage of part of the data changes, and while you should absolutely take that into account when selecting an initial design, adding complexity in case it's needed later is, itself, also a trade-off.

It used to be that "always use the natural key if there is one, because adding a surrogate key is denormalisation and should be done only when necessary" was a common rule, and that was also overly absolutist.

Basically, my rule of thumb is something like "use a natural key if you're confident that you can DBA your way through any required changes easily enough to make the advantages the rest of the time a net win overall" and I find that has better results than 'always' or 'never' would.

(I'd also note that "the role that semantic content plays" changing also applies to e.g. cardinality of relationships and when -those- change it's Interesting Times no matter what you used for PKs and FKs, so you have to have a plan for things like that anyway ... as ever, it's trade-offs all the way down)

Re: You'll regret using natural keys

#538

Post by Akronymus »

bigstrat2003 wrote: post #470
brazzy wrote: post #272

Earlier quoted context omitted.

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.

Why would you need a butt-to-butt extension?

Re: You'll regret using natural keys

#539

Post by naasking »

Too wrote: post #527
naasking wrote: post #477

Earlier quoted context omitted.

I'm not sure what you mean by "break". If you mean that touching or double-clicking on a block of text only extends up to the nearest symbols, that's true. But if your text selection UX is not terrible then it should be simple to extend that further. That said, iOS and Android text selection have gotten worse recently, IMO.

Usually one is not in control over every place where text can be selected. As a developer I will be exposed to ids being displayed in code, terminals, browsers of various sorts, database editors, json dumps, text editors, api responses, chat messages, you name it.

Sure, I agree. I'm not sure what point you're trying to make though.

Re: You'll regret using natural keys

#540

Post by praestigiare »

naasking wrote: post #539
Too wrote: post #527

Earlier quoted context omitted.

Usually one is not in control over every place where text can be selected. As a developer I will be exposed to ids being displayed in code, terminals, browsers of various sorts, database editors, json dumps, text editors, api responses, chat messages, you name it.

Sure, I agree. I'm not sure what point you're trying to make though.

Because - and _ break text selection in existing systems you do not have control over, if you use those characters your ids will become harder to select.
Post reply on HN