> In what context would a primary key change, even when sharding?

The only time I've seen that happen is when a DBA evangelized for just using 'natural' keys, which in this case were slugs based on the item name. The problem came when users decided they wanted to rename things and then wondered why the IDs they were seeing in URLs didn't match the new names.

I'm not a believer in natural keys anymore.

> If you need sorting in your table, provide some kind of indexed timestamp.

A v4 UUID contains a timestamp. So it seems a bit wasteful to add the separate timestamp column. Why not just use the timestamp built into the ID? This is what MongoDB's internally-generated IDs do. (Though they're not quite UUIDs.)

There's a catch with v4 UUIDs though: the timestamp portion of the ID is not the most significant digits, so a simple ORDER BY doesn't do what you want.

I like the solution developed by Instagram, which Rob Conery discusses at the link below. It uses a pretty simple plpgsql function to generate a 64 bit integer based on the current time, a shard ID, and a per-shard sequence. This lets you generate 1024 IDs per second per shard. It also puts the timestamp portion of the ID first, so if all the shards' data ends up in Hadoop or something you can order consistently across the whole set.

http://rob.conery.io/2014/05/29/a-better-id-generator-for-po...

Item name was never suitable as a key... It's not stable. There are natural keys, they are just very rare. And the thing is even when you think you've got one it is often better to err conservatively. A good anti-example is SSN, often used in text books. They do change. Other than DNA sequences I can't think of a good person key. Just use an internal surrogate and be done with it.

> Other than DNA sequences I can't think of a good person key.

In theory, one could use 3D geoposition at time of birth, time of birth, and sibling order (for twins / triplets / etc delivered surgically), where said order is dictated by the parent(s) or ob/gyn present. Of course, the main problem with this in practice is that not everybody has this information.

I would have said you could do something via retinal imagery, but not everybody has eyes. If we had non-invasive neural imagery, would it maybe be possible to derive a key from a simplification of a person's physical brain topology?

Even DNA changes!

http://www.sciencemuseum.org.uk/whoami/findoutmore/yourgenes...

Not to mention all the identical twins out there.

In a some countries national ID numbers are good primary keys, but that's only when they're designed (and used internally) as such by governments. e.g. Finnish, French, or Israeli ID numbers. Even then, though, using them limits you to people who are in the system - no dealing with international customers, for example.

At least in Finland, national ID can change in some cases, e.g. when you change your legal gender or if it's repeatedly misused by someone else (like in identify theft scenario).

I mixed up uuid versions above. v1 are the timestamp ones, and v4 purely random.

It's a DBA rant.

Unfortunately, the author doesn't really get UUIDs -- the whole point of generating them and using them as a primary key is that you get a unique entry that can be used between systems. That is a magical thing that gives you superpowers.

In a past life, we would keep the customers basic data in the production database forever, but denormalize and export most historical records out of the transaction system to a data warehouse after 18 months of account activity. We also dumped login/audit data to a big flat file for compliance purposes.

The beauty of using the UUID as the key in that scenario is that the data became fungible between the systems. We didn't care about the audit logs, but had to keep them -- if we had to process them, we could do so with text tools (i.e. Perl/Awk) and do whatever querying we needed to do with a subset of data.

Relational databases are shitty and expensive when used as file systems. If you don't need the relational magic, you don't need the cost and operational overhead of a RDBMS. Literally billions of dollars are wasted keeping unused data in databases.

Another wonderful reason to use UUIDs across systems: Consider what happens when one system's database has an accident, gets restored from a backup, and then starts generating numeric IDs which -- from the perspective of the other systems -- are duplicates.

Best case scenario, things break cleanly and you have to fix up your auto-incrementing counters. Worse is when you start getting data corruption because some remote system has its information about "Customer 11492" (who registered yesterday) overwritten by details for "Customer 11492" who registered today.

Exactly.

Also consider that you commonly have systems that you cannot control. What's the internal representatation of s user or action in a SaaS app?

Don't know, don't care. I slurp in data, with UUID as an attribute!

> Also any sane person would never sort random values.

I think sorting by a arbitrary, unique value as the last sorting dimension is useful to ensure you always get the same ordering when first dimensions are equal. For instance sorting users by (last_name, first_name, user_id) ensures homonymous users don't move around in the list from request to request.

This is quite a sane thing to do, don't you think?

Yes, I do that all the time. Even for tables that have a sequence column to indicate the desired display order, I'll add the primary key on the end of the order by to ensure consistency just on the off chance of two rows having the same seq value.

Have you never needed to merge datasets, that grew separately, in parallel? It is a royal pain when the rows have consecutive sequential primary IDs. Something has to make way for the other.

IMO, this is one thing Mongo got right: keys, by default, are a timestamp + some random string. This way, they still sort nicely, but don't collide with their cousins in a different instance of the DB.

Even if you come across a situation where your primary key has to change, it seems like you could "simply" migrate your initial primary key to the "additional column" concept referred to in the article, and add your new primary key as needed.

I can't imagine that a primary key migration would be easy even if you'd followed the author's suggestions around having an internal key and an external key, and I feel like if you're optimizing for a future primary key migration you might be focusing on the wrong part of your product.

> In what context would a primary key change, even when sharding?

If you're using natural keys, and one of them is displayable text, and then the business says "We decided to rename the 8th sales district. Can you change it for us?"

Was this a good design? Maybe not. But legacy happens.

We're dealing with a vendor right now that has references to look-up tables based on display text. They should have used surrogate keys of some kind. Or at the least, code columns so that the database is mostly unaffected when values shown to the users get renamed. (Also useful for I18N work)

> In what context would a primary key change, even when sharding?

When the model underlying the data is significantly incorrect, and needs to change. I.e., the relation you modeled does not map to the real-world concept like you thought it did. (Or, it did, but no longer does.)

I feel like this most often happens when two concepts end up being modeled by a singular table. You find out later that the table really represents separate, distinct things, and needs splitting. The split off things can — depending on how you do PKs / your migration — get new PKs.

I recently wrote about changing the Primary Key as a way to organize/cluster the data on disk for better query performance (MySQL, unsharded environment). http://techtime.getharvest.com/blog/improving-report-perform...

Some databases will pre-sort a stream of primary keys in order to read a table sequentially.