On-premise databases, since the customer could access the DB directly would sometimes cause sequences to get out of sync. UUIDs made it possible to take a on-premise database and easily merge it with one of our existing cloud databases, essentially adding them as a tenant.Ģ. As a large multi-tenant enterprise class application deployed both on-premise and in the cloud, customers would want to migrate between these deployments. In our case we switched to UUIDs for the following reasons:ġ. This article, while focused on MySQL is an interesting comparison of UUIDs and bigint from a performance standpoint: One possible solution would be to create own UUID-mangling function to create “sequential” UUIDs on INSERT instead parsing them in ORDER: UUIDs make this harder than possible: by not having an option to use different order. If so, the natural ordering of resulting object MIGHT be it’s intristic feature. In order to guarantee uniqueness without rechecks, some kind of sequencing/time-derivation process is required. 2 – this might be solved by async/external/delayed UUID generation, what means we also/still need the PKEY serial in the first place. Since 2-column PKEY with SERIAL makes no sense, the UUID becomes simple “anti-enumeration” access-scheme for front-end application, but doesn’t replace the sequence itself.Īd. 1 – this might be solved …using additional SERIAL. like everything “random”, they eat system entropy, which might generate additional work and hard to debug (load/hw-specific) slowdowns.Īd. they appear to be random, but the collisions still might happen – one extra thing to take care,Ģ. There are two more drawback when using UUIDs:ġ. 4 – WHAT advantageS? Randomness to prevent enumeration is singular. IPv6 should all be flushed down to the ocean and started over.Īd. A great example of university-style bs with solving problems that noone had, not solving problems that existed (except for the address-space) and generating even bigger problems with impractical solutions. 3 – yes, I do hate IPv6 because of that (and many others). poor’s man security) is often not the best practice, especially when used (as 128-bit random) instead actual access policy.Īd. 2 – only if the data is to be innumerable, but this approach (a.k.a. And having your `created_at` we can easily count absolute number of transactions or create a histogram of rollbacks as a function of time of day to investigate bottlenecks.Īd. many gaps in sequence suggest there are many rolled back transactions. But if we change our rationale context to debugging, serial values give a direct hint about what’s going on inside, e.g. To test it I wrote a 1 – only if we’re talking about general purpose (application, final) sorting. Since we know that searching (select * from table where id = …) will have virtually the same speed, let's see how fast does it take to load data. If you have such case, please let me know in comments.įinally, out of curiosity, let's try to see some performance tests. Of course there might be some situations where UUID is the only acceptable solution. If you need something that will allow sharing ids between multiple machines – check instagram ids (which I coded in C and pl/PgSQL in this blogpost. If you need just unique value – int8 sequence/identifier will be usually more than enough. It uses 16 bytes per value, which is twice what is used by int8.īased on these problems – my point is that in majority of cases, you don't really need or want uuid, and can use simpler keys. Well, you can use uuid_generate_v1() but it has the drawback of containing also some form of MAC. Second problem – you can't use uuids to sort in a way that gives you newest rows first. I can make some guesses about expected number of rows, or their order. If you're using natural keys, or something like serial, and you have two tables with very different numbers of rows, you can, usually see that value “12345678' doesn't really look OK as a key in table that has ~ 1000 rows.Īdditionally, looking at list of uuids doesn't really give you any insight:ĭoesn't mean anything to me. That means – uuids generated for table sessions will be indistinguishable from the ones for table users. I opposed, and tried to explain, but IRC doesn't really allow for longer texts, so figured I'll write a blogpost.įirst problem – UUID values are completely opaque. Recently, on irc, there were couple of cases where someone wanted to use uuid as datatype for their primary key.

Sqlite count transactions per year