3 ms·

This approach has become my preferred way of generating IDs for database rows. It is relatively space efficient and doesn't require a round trip to the database

This approach has become my preferred way of generating IDs for database rows. It is relatively space efficient and doesn't require a round trip to the database to generate an ID like auto increment does. While working on a C# implementation for MySQL and we found that when the DB uses Little Endian Binary that the GUID must be reversed to store in order: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/bd1f8369e73a2555eef989e0c4503d84a5180fa0/src/EFCore.MySql/ValueGeneration/Internal/MySqlSequentialGuidValueGenerator.cs https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCo...

abhishekjha 6y ago
Does random UUID work as well as lets say an ordered integer id or ordered uuid? I thought the ids should have a definite order(not necessarily sequential) so that a binary search can be performed on the primary index. Similar for b-trees.
- masklinn 6y ago
  Random uuids are perfectly ordered, there’s no issue looking up a single record by uuid. The problems are that they’re scattered during reads and writes, and will require updating old pages.
  - kevincox 6y ago
    Note that scattering can actually be beneficial if you have too much load for a single node when the data is hot. Of course you can solve this with weird sharding such as sharding on the least significant byte.
    - SigmundA 6y ago
      Seems like this point is not understood very well by many. Ordered keys can create hot spots on insert and read, this can be good or bad depending on backend.
      - kevincox 6y ago
        The classic goldilocks problem. You want your pages to be hot, but not too hot.
      - mywittyname 6y ago
        A hot-spot in one database index is collocated data in another. Sometimes you want uniform distribution of keys, sometimes you want clusters of keys because different databases are optimized based on assumptions about the underlying distribution of keys.

abhishekjha 6y ago

Does random UUID work as well as lets say an ordered integer id or ordered uuid? I thought the ids should have a definite order(not necessarily sequential) so that a binary search can be performed on the primary index. Similar for b-trees.

masklinn 6y ago
Random uuids are perfectly ordered, there’s no issue looking up a single record by uuid. The problems are that they’re scattered during reads and writes, and will require updating old pages.
- kevincox 6y ago
  Note that scattering can actually be beneficial if you have too much load for a single node when the data is hot. Of course you can solve this with weird sharding such as sharding on the least significant byte.
  - SigmundA 6y ago
    Seems like this point is not understood very well by many. Ordered keys can create hot spots on insert and read, this can be good or bad depending on backend.
    - kevincox 6y ago
      The classic goldilocks problem. You want your pages to be hot, but not too hot.
    - mywittyname 6y ago
      A hot-spot in one database index is collocated data in another. Sometimes you want uniform distribution of keys, sometimes you want clusters of keys because different databases are optimized based on assumptions about the underlying distribution of keys.