6 ms·
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 differen
by parfe 14y ago
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.
- ExpiredLink 14y agoUUIDs are used as surrogate keys: http://en.wikipedia.org/wiki/Surrogate_key http://en.wikipedia.org/wiki/Surrogate_key
- parfe 14y agoIn a way that an integer is not?
- ExpiredLink 14y agoI may have misunderstood the question. A UUID is a (very long) integer.
- chris_wot 14y agoInteger values are also used as surrogate values. Use of a natural key, IMO, is bad practice.
- e12e 14y agoWhy do you think natural key(s) (w/index(es) as needed) are bad practice? Data model refactoring?
- chris_wot 14y agoA 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!
- pvh 14y agoThe 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.
- helper 14y agoYou 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).
- FooBarWidget 14y agoI'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.
- jacques_chester 14y agoIf 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.
- jacques_chester 14y agos/normally distributed/uniformly distributed/ This is what happens when I try to sound cleverer than I actually am. :(
- sunjain 14y agoUsing 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.
- sandGorgon 14y agoisnt a hotspot a good thing? since it is localized data, ergo less cache misses. is the complexity of an insert >>> cache miss penalty.
- Someone 14y agoYes, 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.
- deleted 14y ago[deleted]
- abalone 14y agoInstagram (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/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha...
- saurik 14y agoSort 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).
- stock_toaster 14y ago> Sort was a design requirement, but not a reason not to use UUIDs, which are trivially sorted by date Assuming UUID v1 right?
- saurik 14y agoCorrect. 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.
- jokull 14y agoI 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.
- gingerlime 14y agoI 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.
- eksith 14y agoActually don't use UUIDs. We use something very similar to what Instagram does with Postgres : http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram 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.
- jacques_chester 14y agoYou 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.