4 ms·

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 local

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/ 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...".

lucumo 3y ago
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.
- thomasmg 3y ago
  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.
  - lucumo 3y ago
    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.
  - lazide 3y ago
    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-statistics-review/#:~:text=In%20World%20War%20II%2C%20each,machinery%20in%20the%20German%20arsenal https://www.albert.io/blog/german-tank-problem-explained-ap-....
packetlost 3y ago
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.
- theta_d 3y ago
  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.
  - packetlost 3y ago
    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.
    - sgarland 3y ago
      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.
      - packetlost 3y ago
        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.
vlovich123 3y ago
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?
- tremon 3y ago
  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.
  - vlovich123 3y ago
    I wasn’t thinking sales orders but account ID for example. The dangers of making absolute statements around data schema design.
- sgarland 3y ago
  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 https://stackoverflow.com/a/75869619
  - ndriscoll 3y ago
    It would make more sense to make `timestamp_to_uuid_v7` with high bits 0 and use your PK index.

lucumo 3y ago

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.

thomasmg 3y ago
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.
- lucumo 3y ago
  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.
- lazide 3y ago
  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-statistics-review/#:~:text=In%20World%20War%20II%2C%20each,machinery%20in%20the%20German%20arsenal https://www.albert.io/blog/german-tank-problem-explained-ap-....

packetlost 3y ago

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.

theta_d 3y ago
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.
- packetlost 3y ago
  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.
  - sgarland 3y ago
    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.
    - packetlost 3y ago
      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.

vlovich123 3y ago

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?

tremon 3y ago
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.
- vlovich123 3y ago
  I wasn’t thinking sales orders but account ID for example. The dangers of making absolute statements around data schema design.
sgarland 3y ago
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 https://stackoverflow.com/a/75869619
- ndriscoll 3y ago
  It would make more sense to make `timestamp_to_uuid_v7` with high bits 0 and use your PK index.