Hacker News Reader

Comment by packetlost | Hacker News Reader

Aside from maybe size and generation speed, I don't really see why a UUID is any worse than any other synthetic identifier. I don't really see generating a UUID being a limiting factor in DBs if they're designed for it, so what's your reasoning?

View on HN

The problem is not "UUID" but "random UUID". The randomness makes it hard to index and query, due to missing locality. UUIDv7 is time-sortable, and so has locality. For details, see for example https://uuid7.com/ - the main reason (for me) is this part: "Since UUIDv7 is time-sortable, database indexing mechanisms can better optimize the storage and retrieval processes...".

I don't know. The leaking of information via time-based identifiers always strikes me as an annoying trade-off to work around an implementation detail in certain database engines. Sometimes you have to do it, but it shouldn't be a general rule.

UUIDs are globally unique (unique with such a high probability that in practise there are no collisions, if you follow the spec). But they are not cryptographically safe. So the time part is not a "leak" in this sense. If you need something cryptographically safe, then you would need to encrypt the UUID.

The time part is absolutely a disclosure of information. That can be fine, or it may not be. In the latter case that's called a leak. You need to consider that every time you decide to use a time-based identifier like UUIDv7.

UUIDv4 contains no information, so you cannot accidentally leak information through them.

UUIDs have nothing to do with cryptography. "Cryptographically safe" has no meaning when it comes to identifiers. Encrypting information-carrying identifiers like UUIDv7 is not a good way to hide the information, because you cannot rotate the key if/when it leaks. The moment your key leaks, all information in the identifier is public again.

There's only three ways to mitigate the risk of information-carrying identifiers:

* Don't have them. Use fully random identifiers instead. * Don't share them. Use a different identifier in public (including in URLs). * Carefully consider the information carried in the identifier to decide if the information can be made public.

If anything returns/uses the generated UUID where it is visible to the end user - which is typical, say in a UI or API - then you can know when that thing was created. Which could be a problem info leak wise.

For one similar example with sequentially generated IDs - https://www.albert.io/blog/german-tank-problem-explained-ap-....

You at worst pay cache coherency costs on the CPU for your indexes. That can add up, but on systems that are not write-heavy it won't matter that much in practice I suspect. Assuming a relational database model, even if you're inserting and then querying a bunch of rows at the same time in the same index/table, the rowid is what usually sorts rows on disk so you'll get locality of the bulk of your data regardless.

That being said, unless you have a specific reason to use a UUID over the standard incrementing integer, I wouldn't if for no other reason than dealing with UUIDs is visually complicated.

Depends on the database. Microsoft SQL Server typically uses the PK as the clustering index. This dictates the order the table data is stored on disk. If you row PK is random you're going to have write latency and a fragmented index.

Right, but most databases that I've seen don't do it that way. If yours does, it's going to be really, really bad time.

InnoDB, the default engine of MySQL, uses a clustering index. Last I checked, it's the most popular (in terms of installations) RDBMS in the world.

Clustering indexes make a lot of sense for many workloads, you just have to design your data model with them in mind.

The most deployed database these days is probably SQLite if you count semi-embedded contexts (ie. mobile apps). Excluding that it does look like MySQL is still on top, but Postgres is catching up fast. I completely forgot InnoDB uses clustered indices by default. Regardless, I was referring to most by implementations, not deployment numbers.

Even in non-clustered indexes (relevant to pretty much all SQL implementations not just MS SQL Server) random keys can cause the index to balloon over time due to many page splits, requiring extra space or extra maintenance rounds (rearranging the indexes to optimise them occasionally, if the relevant data grows rapidly or changes often).

The effect is just worsened by clustering, as done in SQL Server for significant (or at least measurable) benefits for many data patterns, because your base data is bigger due to wasted space due to excess page splits, as well as your supplementary indexes, so that needs reorganising more often too.

A common answer is to use an int (or maybe bigint) for your internal keys and a UUID for anything external, so you have the benefits of a UUID for external use (practically zero chance of collision in distributed systems, if appropriately random, and not potentially leaking information in some security contexts) but the efficiently of the integer otherwise. Or to use a partially ordered UUID format, which balances the compromises slightly differently (benefit: single value, dropping some of the UUID issues, keeping some of the benefits; detriment: letting some of the disadvantages of UUIDs, potentially reducing some of the benefits).

The index itself is also a b-tree (unless if it is fully in memory). You also have to consider writes and reads in this index. There, locality also matters. Sure, not as much as with the data itself. But I have seen cases where the index lookup (with randomly distributed UUIDs) was the bottleneck, and not reading the rows. Even thought the row where 1 KB on average.

I could maybe see how it helps with indexing performance, but in terms of lookups there’s typically not going to be correlation in terms of lookups based on record creation. Are you referring to indexing performance as locality or something else?

there’s typically not going to be correlation in terms of lookups based on record creation

Why would you think that? You think it's equally likely to need to look up a salesorder from ten years ago vs one from two weeks ago? Are Github issues from ten years ago read equally often as the ones created yesterday? Most systems have a long tail of historic data that's mainly kept as a reference.

I wasn’t thinking sales orders but account ID for example. The dangers of making absolute statements around data schema design.

    SELECT * FROM forum_post WHERE created_at > $DATE AND created_at < $DATE

If you were using UUIDv7, you could even use that to get the created_at [0], although you'd likely lose the indexing unless you made a functional index on it, like this:

    CREATE INDEX foo_ctime_idx ON foo(extract_timestamp_from_uuid_v7(uuid_v7_col))

[0]: https://stackoverflow.com/a/75869619

It would make more sense to make `timestamp_to_uuid_v7` with high bits 0 and use your PK index.

Reply on news.ycombinator.com