Hacker News Reader

Comment by parfe | Hacker News Reader

The slides recommend UUIDs as a primary key ("just use UUIDs. seriously"). I took a look at the manual and there isn't too much of a Why (as well as 4 different versions of UUID to choose from). Anyone have a write up that explores UUIDs vs integer primary keys? I was only able to find people asking permission ("I have X with a UUID, can I use it as a pk?) rather than people talking about Why you would prefer UUID as the default.

Sidenote: Literally any button press you could reasonably expect to move to another slide works. PgDown, Right, Down, Space. Stop shitting up a decent submission to whine about it.

View on HN

The biggest reason is that your IDs become universally unique - across shards, across database recoveries, rollbacks and session problems, you name it. These are all the kinds of things that can happen over the lifespan of a dataset.

In Postgres, UUIDs are stored in 128 bits, so it's hardly any overhead in almost all use cases, so if you can save yourself a day or two, or three, or a week of work just by switching your mental default from an integer to a UUID, that's a pretty big win.

I see some very good points were already made, but I think UUIDs are also good for at least three other reasons:

1. Offline / sync - you can have out-of-sync or offline clients (e.g mobile apps) inserting new data without worrying about pk mismatch or collisions.

2. security - it's obviously a bit of security through obscurity, but not being able to guess the key of an object can be very useful some time. It shouldn't be relied on as a first line of defence, but it gives you a naturally impossible-to-guess reference. If you want to avoid someone crawling through your image-base or any object that is generally accessible, but you want to avoid having it accessible in-bulk, UUID is your friend.

3. Silly mistakes - If you pass an object id, you really want to know it can only be for one object. Maybe the front-end is sending an id of one object, but the backend is working on another (shit happens). When you use sequential ids, you increase the chance of weird stuff going on. With UUIDs you simply can't use the wrong id, even by mistake.

I've found UUID and other kinds of random primary keys to be very bad for performance once the data set no longer fits into memory. When that happens, the working set of the primary key index becomes that of the entire index, so you end up with a lot of random disk I/O. Unless you have SSDs, your database performance then goes down the drain.

Of course you can also use a UUID that's less random, e.g. something with an incrementing first part and a small random last part.

If you want UUIDs to double as a nonce (I use them this way), then you will need to use v4 UUIDs and yes, they are basically impossible to index because they should be normally distributed across a very large range of possibilities.

If you want incrementing UUIDs, use v1 UUIDs, which are based MAC address + a timestamp. These change in a predictable way and are more indexable.

s/normally distributed/uniformly distributed/

This is what happens when I try to sound cleverer than I actually am. :(

Using UUID(or anything other than sequentially increasing numbers) for primary keys is a good idea because when you have high number of concurrent inserts happening, if you have a sequentially increasing primary key(implemented via a b-tree index), the inserts into index will result in hotspot(as all the inserts will be at the end, in the same place). Whereas if it is random number (like UUID), the entry into indexes will happen at different places thus avoiding hot spots.

isnt a hotspot a good thing? since it is localized data, ergo less cache misses. is the complexity of an insert >>> cache miss penalty.

Yes, but with many treads modifying the same structures, you end up doing a lot of locking.

So, your system would not be memory bandwidtht constrained, not because it has many threads using roughly the same memory blocks, but because it has one thread at a time working on those memory blocks.

You will find that UUIDs are often used as primary keys in distributed systems. By their nature you can generate them independently without worrying about collision and without any coordination overhead (e.g. querying a central authority).

Instagram (a big postgres user) blogged about it. The main reasons they didn't use UUIDs: storage requirements and lack of a natural sort.

http://instagram-engineering.tumblr.com/post/10853187575/sha...

Sort was a design requirement, but not a reason not to use UUIDs, which are trivially sorted by date (in fact, Cassandra not only does this, but in many contexts actually renders UUIDs as timestamps). So, while that rules out various other implementations, the issue with UDID was just the size.

What they ended up with was pretty much UUID but for a more limited deployment: the real issue is just that UUID is extreme overkill, designed to allow every individual user to generate unique identifiers (something that sounds cool, but in practice is worthless as you can't trust the clients).

> Sort was a design requirement, but not a reason not to use UUIDs, which are trivially sorted by date

Assuming UUID v1 right?

Correct. However, that is both the generally fair assumption when dealing with use cases that actually care about UUIDs, and is the fair thing to compare Snowflake against, as Snowflake is pretty much a 64-bit limited-entropy implementation of the general concept of a v1 UUID.

I was under the impression they went with a home baked id generator was to be able to encode the logical shard id into the id. I think UUID 1 encodes info about the hardware (mac address), but their data changes physical hardware as they split their data up to new shards.

Where the data ends up is not important to its identifier: the reason to have a "logical shard ID" is to provide for sequence and timestamp uniqueness.

The way to think about the problem is that at the granularity of your timestamp you lose the ability to uniquely generate identifiers across multiple nodes (on the single node, this is handled with the sequence number), so you need some kind of identifier for the instance of the generator itself: with UUIDs, they spend a relatively immense number of bits storing the computer's MAC, but if you know you know you only have a smaller-scale deployment and are able/willing to centrally plan identifiers for the deployed nodes, you can get buy with something akin to this "shard ID".

(BTW, note that a v1 UUID need not be generated with a true MAC: the specification both notes that you can just by node addresses from them for a fairly small cost, or to use fake addresses that are marked as such by setting the multicast bit. The result is that if you want to use UUID v1 off-the-shelf with "shard IDs" you are more than welcome to do so, not just in the "analogously-equivalent" sense but in the "to the letter of the spec" sense. You can find more information in Section 4.5 of the UUID specification.)

Actually they also noted that it helps with easier mapping. By including the logical shard ID in the ID, they don't need to keep a giant index of IDs-to-shards to figure out which machine an ID lives on. Just a tiny mapping of logical to physical shards, which every app server instance can cache in memory.

One less moving part at the expense of wedding yourself to a prepartitioned logical shard scheme, I guess. (I wonder how painful it would be to rebucket data into a different logical shard should the need arise...)

The Instagram guys said they didn't actually bench using a UUID + created_at column when they spoke at SFPUG, they just used their approach because it was recommended to them and seemed to work. That said, it is a pretty awesome robust mechanism, it's just arguably complex and not proven to be necessary even at their scale.

Actually don't use UUIDs.

We use something very similar to what Instagram does with Postgres : http://instagram-engineering.tumblr.com/post/10853187575/sha...

This allows far greater flexibility with regard to sharding and scale. Bigint with a function generated nextval will sustain up to 9223372036854775807. If you need more than that, your next option is Numeric, but I don't think we'll exhaust bigint any time soon.

You still need to correctly merge the records for aggregate reporting.

If you use UUIDs, this is easy. If you used SERIAL, you now need a scheme to intelligently merge records. Not hard when everything is in a single table. A bit tougher when you have a wholly or partly normalised schema, because now you need to come up with correct renumbering schemes for every single table which has another table holding a foreign key into it.

I can tell you from personal experience that this is not fun.

v1 UUIDs are "guaranteed" to be unique, modulo people fiddling with your MAC addresses. v4 UUIDs are random keys and the odds of collision are so infinitesimal that it's worthless to fuss about it. v4s also have the nice property that they can double as nonces, but the sucky problem that they are not that great for indexes.

Then there's stuff like replaying logs reliably on multiple machines, blah blah blah. Every instance of 186360 is indistinguishable from every other instance of 186360. So you will need additional logic to keep them straight.

But take this uuid:

69c8e822-83b0-11e2-a6a5-001f5bfffe14

The odds that this has been created anywhere else, at any point, is as good as zero. You're more likely to have an integer key collision due to cosmic radiation flipping some bits.

So no special merging / field-source tracking logic is required.

I can't be too sure of the specifics, but our db admins decided against UUIDs after a few days debating and lots of coffee.

We aren't using serial, but instead using functions to generate our own nextvals with the server IDs, epoch etc..., kinda the same way as Instagram (though, obviously our databases are no where near the same size). It came down to how much logic we were comfortable implementing in the schema from the start to avoid continuing complexity in the applications themselves.

I’m not sure I understand. The way Instagram do it, they ensure uniqueness across shards so reporting should not have to merge anything.

I guess the point is, that you don't need to "ensure uniqueness across..."

UUIDs are used as surrogate keys: http://en.wikipedia.org/wiki/Surrogate_key

Integer values are also used as surrogate values. Use of a natural key, IMO, is bad practice.

Why do you think natural key(s) (w/index(es) as needed) are bad practice? Data model refactoring?

A natural key as a primary key is terrible practice because I've never even once seen a natural key that wasn't tied to business logic. Logic that potentially can change.

Don't do it, you only make it worse for yourself if you pick a natural key for a PK!

In a way that an integer is not?

I may have misunderstood the question. A UUID is a (very long) integer.

Reply on news.ycombinator.com