The Problem With UUIDs 

"hey guys! today i will be talking about something i clearly don't understand" is a great summary of this video

UUID versions are NOT supposed to replace each other. They are just different implementations that address different problem domains. Once humans become a serious space-faring civilization, we probably need yet another UUID version for problems like time dillation. But such a UUID V99 is not supposed to replace V4, but to complement it. V4 can still be used within certain bounds.

It's amazing watching somebody talk shit about something he obviously doesn't understand beyond his sponsors blog post

I propose v9, it is the same as v4, but instead of a 4 it has a 9. This changes everything.

Oh I am still waiting on the video about PlanetScale removing the free plan and the cheaper price twice as much as competitors...

17:58 Just so you know, "I'm not telling you the difference between them, that's your problem", takes a lot more time to say than "1 byte is 8 bits"

The proble with UUIDs is that you are not using PostgreSQL.

Dude literally just read an article and nothing more. Offer some POV at least. - I don't understand if this is really an issue with mysql or with postgres too? - Is this even as big a problem? or is it just clickbait? - What's the actual performance impact? there's a lot of "perf issues" thrown around with no(?) benchmarks.

3:49 To my knowledge, not all versions in UUID should be seen as "the next version". Some are more like variants than versions. That's why both v1 and v4 are still used often. You could say that variant 5 is a new version of variant 3 though, because we now have better hashing algs that might have not been considered in v3. Variant 6 similarly is a new version of v1, where they reordered the date to be sortable (with v7 being a variant of that). You shouldn't think of every new --"version"-- variant being better than the previous, but that they complement each other.

Thinking about the different UUID variants as 'versions' that replace previous versions isn't quite right... the idea is that the different variants are used in different circumstances, so a deterministic variant is useful in some circumstances. Which is why there's a 'variant' number in it, so different variants can be checked differently.

V1 was a pretty good idea because collisions were basically guaranteed to never occur. But that was in a world where MAC addresses were guaranteed to be unique, and VMs basically killed that.

"I spent years researching this to make sure I got it right. Turns out, I don't need to. Here's what my sponsor has to say." Sheeesh, DarkViper's video accurately notes a lot of key issues, like this one. Something always felt off about this channel and getting a different perspective really made things clear for me. This channel really is just regurgitation with, as you admit yourself in the first few seconds, little research.

Using a char(36) to store a uuid just seems like the worst idea every, does anyone actually do this? Postgres has a specific datatype for uuid, does MySQL still not have that?

Javascript programmer using MySQL gives us a tech talk about the importance of performance at scale lol

This channel does read a lot of blog posts

instead of PlanetScale sponsoring Theo, they should have used that money to keep reasonable free plan

btw it is an RFC, not just a proposal anymore. But there were many wrong assumptions in this video.

Why are we mixing "version" and "variant"?

I believe there is some confusion about sortability. All primary keys are sortable, regardless of the value. They will fit in the B+ Tree pages with different packing sizes, and with the balancing issues described in the video. What some people want is that records are sorted by creation time. Which is what the CUID post talks about. Don't worry about the record being naturally sorted by creation time. Use a random key to avoid hot spots, and use a createAt (or reverse CreatedAt) index if you need to scan the table the naturally created order.

Curious why there is no evidence regarding performance, only vague handwaving that storage space is a factor. What this does show is that MySQL has an incrementing index type and an indexing mechanism that is optimised for incrementing indexes. My (naive) understanding was that with a B-Tree, if the index values were random, the tree will likely tend towards being balanced (at least over time), and that B-Trees don't perform well with sequential index values, hence the need for an optimisation. Sequential values don't scale horizontally, hence the need for a larger 'namespace'. Sequential 32 bit values and random 128 bit values are apples and oranges, and meet different requirements. Choose your index keys appropriately, if horizontal scalability is a likely to be a factor (which is often a guess when we don't know just how large or distributed a storage system is going to be). Clearly a UUID can't compete with a 32bit sequential when you have a single key generator, but a 32bit sequential can't even function when there are multiple key generators (without segmenting the address space). Horses for courses.

"Planetscale is not very liked atm..." -- "Thank you planetscale for sponsoring this video"

What I don't see mentioned here and should be mentioned is that your Database IDs, which you use for indexing, and referencing relationships, doesn't HAVE to be the same value you use to reference records from the API's PoV. You can just use an integer pirmary key, then add a binary UUID field with a unique key constraint, which you don't use for joining / referencing in the database but is used at api-surface-level to identify the records.

ULIDs resolve most, if not all of the problems mentioned. Is my go to now

did he really just say storing 20x more is no big deal casually?, all computer engineers biting their nails rn

RIP ULIDs got no mention even when shown in article

“this video is sponsored by planetscale” is a good way to make sure we know the video is not credible

Solution I've always used is have an incrementing int id primary key and a guid for external reference (and in some cases a unique human reasonable code name). Id for performance and internal joining, code name if you need to reference it in code, and guid for matching across environments in staging situations where you push items from one environment to another.

I generally use an integer sequence as the primary key and have an associated UUID in a unique index. The primary key is only ever used internally in the DB model and in application code. When exposing the entity in public references, the UUID is used. In addition to the issues touched on in the article, this saves us from another drawback: having to use that same 128bit UUID in foreign keys all over the DB. This goes for any globally unique identifier scheme. Because these unique identifiers need to be globally unique, they are generally so long that they are unwieldy to use as primary keys, even if they are sortable.

This article appears to be entirely misunderstanding UUID as incremental versions, along with MySQL not handling them as well as something like PostgreSQL. UUID4 for guaranteed random, the rest have different, but valid use cases.

As others also pointet out, they are not versions, they are _variants_ with purposes per variant.

UUIDs event account for Time Travel but only as back as 1568, apparently, in the 1567 they were using Integers

Another problem with UUIDs is logging. First, logs almost always store UUIDs as text, which as you pointed out is very inefficient at 38 bytes per. Second, since they are designed to be unique, they tend to be relatively uncompressable compared to normal text. The time based UUIDs may be less bad in that respect. But the bottom line is that if you have a requirement to log your identifiers, then using UUIDs will practically guarantee huge storage costs for those logs.

UUIDv7 is suitable for all the relational dbs, except MSSQL which would require V8 and a custom generator. Our largest table is many billions of rows and UUIDv7 still performs well in Postgres.

UUIDs were not designed to be database primary keys. They were designed to solve problems with distributed networking, not data storage. There is no problem with UUIDs if used as designed. Kids these days see UUIDs and immediately think databases. Ha!

MySQL does care about a distributed database, so they are interested mostly in the performance of a single B-Tree. Imagine how hard it would be to create an auto-incrementing ID in a distributed insertion. If you use an auto-incrementing ID, you can only insert in sequence. This is why people use UUID. Theo needs to learn about databases. Everyone writing enterprise software needs to learn about databases.

The bad part about the size comparison, is that where it matters you are probably using bigint already, making binary(16) only twice as big as bigint, without collisions

I used snowflakes a lot and like them slightly more, than other type of identifiers, especially considering that each of them have datetime baked in.

Why do we need to sort by ID? Why not sort through other fields, like a user’s name, creation date, etc…? Point of the ID is… as the name implies, identify some data. We don’t need to sort it using that field.

So... "The Problem with InnoDB".

Having sequential IDs create contention hotspots in the B-Tree. No good for horizontal scalability or vertical scalability.

Why even care about planetscale? For production, you have AWS RDS, for small projects you have Supabase.

You can always use UUIDs (or any stable data whatsoever) to compare them orderly. The problem with non-ordered ID's that that when inserting data, they are not growing according to that order. Sequential IDs do that, the next ID will always be greater than the previous one. When inserting random IDs, they tend to fill pages better (it's random on which side of a node the tree grows). Not all indexes are necessarily trees. The problem with such statements is that there is no A is better than B in all generality. You have to actually look at what your systems do with your data. If you need distributed keys, e.g. keys where there are multiple authorities (instances) that generate synthetic keys (keys that are not data driven), then UUIDs and other mechanisms make the handling of these authorities easy. That has a price. If you don't want to pay this price, you need to pay another one. Maybe you need to use data storage that works efficiently with your keys. Maybe you organize your key-namespaces or scopes manually such that you can use the most efficient local storage and key management and save runtime performance but then have to invest in deployment configuration. Any attempt to find a silver bullet can at best work for were wolfs and vampires but will fail for mosquitos or blue whales. These generalizations and over-simplifications are just stupid. UUIDs are perfectly fine and battle proven data types for certain applications. When used in the wrong context or in the wrong way (version, ...), they fail, just like any other mechanism does, 32 bit numbers or 640K were proven not to always be enough, despite timely expectations.

Some reference to the collisions that included the context around them would be appreciated

UUIDv7 can have counters which makes them sequential as well. The UUIDv7 that could be shipped with Postgres 17 has a randomly-seeded counter.

Who needs standards, its all about the vibes. UUID v8 is by far the best

Sounds more like a problem with MySQL. Some non-relational databases would actually benefit from the randomness of UUIDs.

@Theo : Funny. About 4 Decades ago I designed a UID that incorporated brute force and accident aspects - such as mentioned herein - for incorporation into (truck) weigh scale software. The software which utilized this UID was smaller than 1.44MByte, including graphics, credit card processing, remote access and update, and recording of transactions. The software was stored on an EEPROMish type device which emulated, as you MAY have guessed, a floppy disk. For those curious, the OS was (a form of) DOS and it was programmed in BASIC and assembly.

Sounds like the UUIDv7 would make the most sense if you are using a UUID as a primary key because it combines the date/time with a random number, which to me actually sounds perfect if you need a primary key and just an incrementing value won't do. I also really like that it uses the UNIX Epoch for the time.

Cuid2 sounds unnecessary. Its just a seeded sha3 from my reading. What is wrong with just combining a sequential counter and any secure prng? What I tend to use is an N-bit sequential part and an X-bit random number (usually 64/448). This allows to generate sequential numbers up to N, while having collision resistance of X). If you really wanted to, you could just use SHA3 to generate the X bits yourself if you want the same randomness as cuid2. Of course adjust as needed.

I haven't heard of CUID, but I have heard of the ULID. I don't need the 128bit of random data. I am fine with the 64-80 bits of random data. It doesn't even need to be cryptographically secure, to be honest.

I saw the year 1568 and immediately thought 'Gregorian calendar', no joke, for real

15:20 I don't understand this argument. If you're happy to store 32 bit integer in binary form, you should be happy to store the 128 bit UUID in binary form, too. The database can convert from binary value to human readable value on the fly, just the way it happens for binary 32 bit integers, too! With the same logic you could argue that 128 bit binary UUID is actually better format than 32 bit integer stored as base-10 number as a string. That said, if your database doesn't support UUID column type, maybe you should switch database.

You'd have to be crazy to pay PlanetScale today

My first startup I worked for, the main DB admin insisted on using primary keys made up of real user data or composite keys made up of real information. It was so insane. Social Security # as primary ID for a user? This was a benefits enrollment company. Shocker, some of our customers had illegals that shared SS #'s.

Random ids also have the disadvantage that for a large table, if you need to access 1000 rows, they'll be stored in 1000 different pages, even if the rows are conceptually consecutive. Non random ids instead create hotspots during inserts. So a semi-random index would tick both boxes, but every use case is different.

It turns out I accidentally invented my own UUID for references created on different systems. I wasn't smart enough to realize that this is what I was doing, but I was smart enough to have a sortable timestamp built in. I suppose I can pat myself on the back while kicking myself.

5:00 complains about 1568 as an implementation detail… proceeds to use 1970 instead.

Is indexes nowadays an accepted form of indices? When did that happen 😭 Edit: typo

If splitting your database is an expected requirement at some point, you can still add a local auto-increment number which is NOT your primary key, but only locally unique. This way you can still scale horizontally but also split by number locally on every DB.

Add to the advice including 'we don't write tests'. If you see this guy on a proposal, run.

4am, waiting for my medical procedure to start and I'm spending it learning about UUIDs.

Is the versioning done by the USB forum? It sure looks like it might be.

Regardless of the video's contents, what Firefox extension did you use for the tabs?

UUID v4 do not only have the "4" statically at index 12, but also there is a "8, 9, a or b" at index 16 (starting index by 0) So its /^[0-9A-F]{8}-[0-9A-F]{4}-4[0-9A-F]{3}-[89AB][0-9A-F]{3}-[0-9A-F]{12}$/i

Using MySQL is the first mistake. FWIW, PostgreSQL has a native UUID type that occupies 16 byes of space. But yeah... I don't like UUIDs as primary keys.

I personally like ULID's, not just because they are incremental, but also the fact that I can just swap to using them in the same BINARY(16) column that was used for UUIDs (probably not the best idea to switch and have mixed content, but eh)

35 years as a data architect. Inevitably, I run into uuid's being used by people that do not understand databases. Use an incrementing integer for your clustered index, preferably make it your primary key. The primary key is included in every index on a table, so if you are using the uuid as the primary key you just increased the size of every single index by a lot compared to an integer.

also for a start date/time to begin counting i have a good one :P , theres this relly weird but consistent thing the eath does , its like a wobble, and the whole thing lasts about 26000 years (25800) but as a consequence of this the sun rises in a different constellation on the spring equinox every 2160 years... so if we pick the beginning of one of those as the starting point to begin counting time wouldnt that be a great idea ? coincedentally the last time we shifted (from Ram to Pisces (its reverse form the horoscope) , its at the same time we shift from BC to AD so 1 jan of the year 1 seems like a nice spot to start counting time not?

Couple Things. UUIDS can be stored as a regular column with an index for fast lookup while using an integer for the primary key. The UUID is used for externally representing the resource, while the integers are used internally for performance (it is more resource intensive to do a 128-bit compare than a 32 or 64 bit compare on most CPUs and that is if you use the binary format). This approach even allows for distributed systems to coordinate via message passing, only using the UUIDs instead of the internal integers which are treated as an implementation detail. This approach is less attractive than just storing the UUIDs as the primary key which makes coordination and replication trivially simple by comparison. It is an option worth exploring based on your needs. The second thing is that the rebalancing problem introduced by random UUIDs is more severe than communicated here. While it is true that random UUIDs can cause databases to not utilize a larger portion of page sizes, the real performance hit is the stochastic nature of B-tree rebalances. B-trees are a compromise between the theoretically optimal B-tree and the reality of modern computers where data retrieval is severely slower than multiple comparisons. B-trees lean into splitting up the tree more aggressively at each level and requiring more value comparisons over-all in order to reduce the number of costly data-fetches per look-up. This causes each rebalance to be more expensive than in a comparably unbalanced binary search tree and require touching more nodes than in a BST. In particular, unlike in a BST. b-tree balance procedures requires frequent changing of the node's content rather than just changing the links between nodes. Coupled with the ripple effect that can occur where b-trees might have to traverse all the way back to the root node in order to rebalance, the performance hit is worse than it first seems and is slightly random itself. When b-tree insertions are roughly ordered, these copies and extra comparisons are more akin to amortized costs like we see with auto-scaling Vectors. When the insertions are random, the frequency of multi-level rebalancing operations increases. If you support key removals, even more so.

I avoid sequential ids because they make it too easy to accidentally reference an incorrect record. With uuids, there is no chance of this.

Editor: 'If you use the word "represented" too many times, too closely, it's bad writing, grammar and meaning be damned.'

I think in UUID the v stands for variant, not version

I always love the large discussion around databases, because they’re nearly useless to me, as in, in fifteen years I’ve never once used a database, and only three of the thousands of my applications would’ve been an ok use of a database, with the rest just needing a config file

The guy who came up with the 1568 wins a paddlin. That's a paddlin well deserved if I ever seen that. Unixtime was already the established standard and this new one does nothing better.

Can someone explain what is wrong with storing number based id's but then using an encrypted version of that id for anywhere visible by the user? I have been using red-explosion/laravel-sqids

Of course they hurt performance, they will continue to do so until 128-bit architecture is a thing. They're not supposed to match integers in terms of performance.

Jebus. I couldn't last the full video. I don't know how you followed this all. I need to wash my brain now with something else and pretend this isn't real.

Can we all agree that mysql is more of a pain in the ass than a decent database? So it's not a relevant metric for any solution.

As long as you are using UUIDs with PostGres in your DB and Nanoid everywhere else, there is no problem.

What do you think about to use UUIDvTimebased for Businessobjects and snowflakes for provessingIdentifyers what are also not lived "long". integer/bigint/.. are amazingly fast because of the processor structure and also because of the storage structure.

click baited into a blog screenreader again

Regarding the sortability being a potential security: Security by obscurity is not actual security.

Bigger data size is extremely important. Sure it's irrelevant for disk storage or row size, but database are often RAM constrained. And perform terribly if they can't fit their indices (including UUID PKs, FKs) in memory.

I really like this guy drawing attention to these subtle issues.

I just use v4 and write my code with the idea of "can this collide? Oh it can? I'll check before generating it" Adding a read before generating and a write is a worthwhile step to ensure I don't run into a collision

Autoincrement (number) cannot be used by developers who care that an entity should have an id before being persisted, so for me any argument pro them is irrelevant.

This makes me feel justified in using my own function, that literally just uses the crypto safe random byte generator of the OS. It's slower then the regular one, but slower in this context means: several megabytes per second. So it's just unfit to overwrite entire drives. I just took the shortcut and it turned out to be just way less of a mess, while being better at doing the job it is supposed to do.

I use internal sequence as PKEY and UUID as "public id". PK is never used in the code or visible in any API, it is only used on the database level for relational stuff. That aside, are UUIDs equally problematic in PostgreSQL?

And all that hazzle, just because incrementing an integer is too boring for some people ...

imagine using mysql in 2024 holy..

So how do NanoIds (and other alternate ids) solve the sortability/paging issue? Not to mention those likely won't be natively handled by the database and language of choice, i don't think it's a great idea to go dependency heavy either

I don't use them, simply because collisions can occur. The thought of trying to find that non-existent bug upsets me too much.

"The calculated probability of at least one collision occurring when generating 1 billion UUIDv4 identifiers is effectively 0. " Are collisions even real ? Ok, so you might get an error when creating few billions of records, so what ? Try, catch, retry. And about performance - if your UUIDv4 is too slow for your billions of records, perhaps it's time to archive the data ? I mean everyone was using UUIDv4 for a long time. And suddently it's a problem ? How so ? Or is it just someone wants to push their own agenda while introducing their own type of IDs?

classic theo use someone else's work when you don't want to do it. man i love your work but you got to stop being so obvious about it

"barely a standard, more a vibe" is iconic

I work a mot with time series data from measuring devices, and my biggest problem with UUIDs (each device has an uuid as a PK) is that you can basically not compress them. You can compress a 8 byte int by only storing the difference between each int.

As I understand, the database does not need to rebalance the B-Tree very often if the indexed value is sequential (the id is sortable). If the ID is random, the tree deforms much faster.

it might be database engine specific, I know microsoft loves the crap out of there uuid primary keys so sqlserver might be good for it, dunno though.

Love these kind of videos, help me to shift the way I see technology and it's uses

Thanks, this has been very educational for me. I will admit my OCD kicked in when, at 11:00, you fixed all the arrows except for the green one. xD